Jump to content

can you name a cell when exporting XLSX?


Recommended Posts

I am trying to generate formulas for some of the cells in my XLSX spreadsheet.  I need to reference a cell in my formula that is at the top of the page, but not necessarily in a fixed position.  It would be great if I could give it a defined name and reference that name in the formula.  Excel has that provision, of course.  The JRXlsxExporter class does generate definedNames XML, but it only puts page anchors in there.  

Is there any way to add my own cell defined names?  If not, is this a feature that is planned?

Link to comment
Share on other sites

  • Replies 5
  • Created
  • Last Reply

Top Posters In This Topic

I am using Linux (Oracle Linux 7.9) running JasperReports Library 6.18.1.  My dev environment is Windows 11 using Jaspersoft Studio 6.20.1.  I realize that I need to upgrade the JasperReports library version soon.

I would like to modify this report to put formulas in  place of the numbers I am currently calculating.  The problem is that the formulas reference other columns in the report and I can not guarantee where those numbers will be located.  The INDIRECT(ADDRESS(...)) solution is very cumbersome.  If I could define Excel names for particular fields instead, then I could reference them in the formulas and it would be much easier.

Here is an excerpt from one report - this is my header section:

<?xml version="1.0" encoding="UTF-8"?><!-- Created with Jaspersoft Studio version 6.18.1.final using JasperReports Library version 6.18.1-9d75d1969e774d4f179fb3be8401e98a0e6d1611  --><jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="altmlgresponse" pageWidth="1000" pageHeight="1000" columnWidth="996" leftMargin="2" rightMargin="2" topMargin="2" bottomMargin="2" uuid="4f1f795c-8186-44a7-86fa-28aaab31d1fe">    <property name="net.sf.jasperreports.export.xls.one.page.per.sheet" value="false"/>    <property name="net.sf.jasperreports.export.xls.remove.empty.space.between.rows" value="true"/>    <property name="net.sf.jasperreports.export.xls.collapse.row.span" value="true"/>    <property name="net.sf.jasperreports.export.xls.collapse.column.span" value="true"/>    <property name="net.sf.jasperreports.export.xls.remove.empty.space.between.columns" value="true"/>    <property name="net.sf.jasperreports.export.xls.ignore.cell.border" value="false"/>    <property name="net.sf.jasperreports.export.xls.white.page.background" value="false"/>    <property name="net.sf.jasperreports.export.xls.detect.cell.type" value="true"/>    <property name="net.sf.jasperreports.export.xls.ignore.graphics" value="false"/>    <property name="net.sf.jasperreports.page.break.no.pagination" value="apply"/>    <property name="net.sf.jasperreports.print.keep.full.text" value="true"/>    <property name="net.sf.jasperreports.exports.xls.font.size.fix.enabled" value="true"/>    <property name="net.sf.jasperreports.exports.maximum.rows.per.sheet" value="0"/>    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="lsp-pg"/>    <property name="net.sf.jasperreports.export.xls.font.size.fix.enabled" value="false"/>

   <textField pattern="#,##0.00%">          <reportElement x="888" y="0" width="60" height="18" uuid="09749a0d-717f-46e3-b304-8eae3085a5c5">               <property name="net.sf.jasperreports.export.xls.definedName" value="myValue"/>                     <-- this is what I would like to see -->           </reportElement>           <textElement textAlignment="Right" verticalAlignment="Top">                 <font isBold="true"/>            </textElement>            <textFieldExpression><![CDATA[$F{resppcnt}]]></textFieldExpression>   </textField>

I would like to be able to give this field an Excel defined name so that I could write the following in a formula:

  <textField isBlankWhenNull="false">    <reportElement key="textField-3" x="0" y="196" width="280" height="23">      <property name="net.sf.jasperreports.export.xls.formula" value="myValue*100"/>     <-- note how this formula references the defined name myValue above -->    </reportElement>    <box rightPadding="5">      <leftPen lineWidth="4.0" lineColor="#C0C0C0"/>      <bottomPen lineWidth="1.0" lineColor="#C0C0C0"/>      <rightPen lineWidth="1.0" lineColor="#C0C0C0"/>    </box>    <textElement textAlignment="Right" verticalAlignment="Middle"/>    <textFieldExpression class="java.lang.Integer">0</textFieldExpression>  </textField>

If we had this capability, then we would no longer need the awkward INDIRECT(ADDRESS(...)) as referenced here: Exporting Crosstab Formula "SUM" To Excel

If you look in the code in the JRXlsxExporter.java file:  https://github.com/TIBCOSoftware/jasperreports/blob/master/jasperreports/src/net/sf/jasperreports/engine/export/ooxml/JRXlsxExporter.java, you can find this section as an example:

    protected void insertPageAnchor(int colIndex, int rowIndex)    {        if(!ignoreAnchors && startPage)        {            String anchorPage = JR_PAGE_ANCHOR_PREFIX + reportIndex + "_" + (sheetIndex - sheetsBeforeCurrentReport);            String ref = "'" + JRStringUtil.xmlEncode(currentSheetName) + "'!$A$1";        // + XlsxCellHelper.getColumIndexLetter(colIndex) + "$" + (rowIndex + 1);            definedNames.append("<definedName name="" + getDefinedName(anchorPage) +"">"+ ref +"</definedName>n");            startPage = false;        }    }

As you can see, it generates the definedName for an "anchorPage".  I would like the code to generate the same thing for my own defined name. 

The question is can you actually identify the Excel cell that the data is in?  I believe you could use the code in the comment to do it:

"'" + JRStringUtil.xmlEncode(currentSheetName) + "'!$" + XlsxCellHelper.getColumIndexLetter(colIndex) + "$" + (rowIndex + 1) + "'"[/code]

Hopefully this makes the request clearer.

Thank you for considering the feature.

   Eric Raskin


Link to comment
Share on other sites

  • 3 weeks later...

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...