Exporting Crosstab Formula "SUM" To Excel

He wants to use and to export dynamically 'SUM' function which can take column names(C, D, E, etc) and Row numbers(3, 4, 5, etc) instead of hard coding in the below code (for each column):

<propertyExpression name="net.sf.jasperreports.export.xls.formula"><![CDATA["SUM(C3:C9)"]]></propertyExpression>[/code]

Suggested Resolutions:

Excel formula feature in TIBCO JasperReports® Server

The JasperReports® Server engine provides a feature which allows using a formula as content of a text field. 

The formula will affect the text field value only when the document will be exported to XLS or XLSX format, and will be neglected when exporting the document to other formats. All other exporters will take into account the value given by the <textFieldExpression /> element. 

But first of all one have to ensure that cells are enabled to detect their own data type. By default all data are exported as text only. Enabling the cell type detection can be done setting the export hint property net.sf.jasperreports.export.xls.detect.cell.type to true:   

<property name="net.sf.jasperreports.export.xls.detect.cell.type" value="true"/>[/code]

In JasperReports® Server a formula can be stored using the PROPERTY_CELL_FORMULA text field property. 

The property name is net.sf.jasperreports.export.xls.formula, and usually its value is a string containing the formula expression. The expression can be a very simple one, or a more and more complex, as needed. 

If the expression contains only constants or static data, then using the <property /> element is recommended. 

If the expression contains one or more dynamic data, then the <propertyExpression /> element should be used instead. 

Although in Excel any formula expression should start with the "=" sign, the JasperReports® Server engine is more permissive and allows a formula expression to start also without the "=" sign. Both formula expressions are considered as valid, and are further evaluated and prepared to be successfully exported to the XLS output format. 

You can use a dynamic formula in this case, based on the following dynamic Excel functions:

• ADDRESS() - Returns a reference as text to a single cell in a worksheet
• INDIRECT() - Returns a reference indicated by a text value
• COLUMN() - Returns the column number of a reference
• ROW() - Returns the row number of a reference

More information about these dynamic functions you can find out there -> Lookup and reference functions

If you need to export to XLS format, the formula should look like:

<propertyExpression name="net.sf.jasperreports.export.xls.formula">    <![CDATA["=SUM(INDIRECT(ADDRESS(3,COLUMN())&":"&ADDRESS(ROW()-1,COLUMN())))"]]></propertyExpression>[/code]

The only problem here is that the prior XLS format is too old to support exactly the same formula as the XLSX format. So, if you need to export to XLSX, the formula should be:

<propertyExpression name="net.sf.jasperreports.export.xls.formula">    <![CDATA["=SUM(INDIRECT(ADDRESS(3,COLUMN())&":"&ADDRESS(ROW()-1,COLUMN())))"]]></propertyExpression>[/code]

As example, please see attached .jrxml

VO - 20181009, case #01640521