can you name a cell when exporting XLSX?

[eraskin]

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?

[anish.rai]

Thank you for posting to the Jaspersoft Community. Our team of experts has read your question and we are working to get you an answer as quickly as we can. If you have a Jaspersoft Professional Subscription plan, please visit https://support.tibco.com/s/ for direct access to our technical support teams offering guaranteed response times.

[Thomas Tibcken]

 

Hi Eraskin, 

can you please provide an example jrxml so we can use this for a repro and solution for your request? 

Can you also please provide us more about your environment? 

OS and Version?

Jaspersoft Product and Version? 

 

THX TT

[Mehak Rajkumar]

We are eagerly anticipating your response to the comment mentioned above. Please provide the requested information so that we can assist you in addressing your question effectively.
 

[eraskin]

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

 

[rpeguet]

Hi Eraskin,
Thank you for the feedback. If you believe this would be a good enhancement request you may fill out a bug tracker at https://community.jaspersoft.com/bug-tracker?field_bug_project_target_id=202