Jasper Reports Excel Cross Sheet Formulas

A member of the Data team brought my attention to an iReports bug affecting Excel Output containing formulas which reference a following sheet. If the formula is let us say referring Sheet1 from Sheet2 there is no problem but if it refers Sheet2 from Sheet1 Jasper will fail to generate a correct XLS file. This is not the case when we try to generate an XLSX file. In that case it does work as expected.

When running with "Excel 2007 (XLSX) Preview" the cell does get what we expect:

=mySheet2!A1

However when we pick "XLS Preview" we get:

=#REF!A1

Here is the screen shot showing a successful rendering when using XLSX:

Here is the failure when using XLS:

This is clearly a problem related to different exporters. I still need to test this from Java with different exporter options but it would be ideal if a simple cross sheets formula like this could work from either XLS or XLSX files.

Below is the source code for this report. I have picked one jrxm posted in forums and I have added a couple of lines just to make sure I generate two different spreadsheets.

The output contains two sheets which reference each other through a simple formula that brings the content of the A1 cell from one sheet to the other in both directions. As stated at the beginning the formula in Sheet1 will fail when exporting to XLS but will succeed when exporting to XLSX. The formula in Sheet 2 will always succeed.

<?xml version="1.0" encoding="UTF-8"?>
<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="formulaSample" pageWidth="595" pageHeight="842" whenNoDataType="AllSectionsNoDetail" columnWidth="535" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0">
 <property name="net.sf.jasperreports.export.xls.detect.cell.type" value="true"/>
 <property name="net.sf.jasperreports.export.xls.one.page.per.sheet" value="true"/>
 <property name="net.sf.jasperreports.export.xls.sheet.names.all values" value="mySheet1/mySheet2"/>
 <property name="ireport.zoom" value="1.0"/>
 <property name="ireport.x" value="0"/>
 <property name="ireport.y" value="0"/>
 <import value="net.sf.jasperreports.engine.*"/>
 <import value="java.util.*"/>
 <import value="net.sf.jasperreports.engine.data.*"/>
 <title>
  <band height="262" splitType="Stretch">
   <textField>
    <reportElement key="textField-1" x="0" y="0" width="68" height="23"/>
    <textElement/>
    <textFieldExpression><![CDATA[new Integer(2)]]></textFieldExpression>
   </textField>
   <textField>
    <reportElement key="textField-2" x="0" y="23" width="68" height="23"/>
    <textElement/>
    <textFieldExpression><![CDATA[new Integer(1)]]></textFieldExpression>
   </textField>
   <textField>
    <reportElement key="textField-3" x="0" y="46" width="68" height="23">
     <propertyExpression name="net.sf.jasperreports.export.xls.formula"><![CDATA["mySheet2!A1"]]></propertyExpression>
    </reportElement>
    <textElement/>
    <textFieldExpression><![CDATA[]]></textFieldExpression>
   </textField>
   <break>
    <reportElement x="0" y="116" width="1" height="1"/>
   </break>
   <textField>
    <reportElement key="textField-3" x="0" y="163" width="68" height="23">
     <propertyExpression name="net.sf.jasperreports.export.xls.formula"><![CDATA["mySheet1!A1"]]></propertyExpression>
    </reportElement>
    <textElement/>
    <textFieldExpression><![CDATA[]]></textFieldExpression>
   </textField>
   <textField>
    <reportElement key="textField-2" x="0" y="140" width="68" height="23"/>
    <textElement/>
    <textFieldExpression><![CDATA[new Integer(9)]]></textFieldExpression>
   </textField>
   <textField>
    <reportElement key="textField-1" x="0" y="117" width="68" height="23"/>
    <textElement/>
    <textFieldExpression><![CDATA[new Integer(7)]]></textFieldExpression>
   </textField>
  </band>
 </title>
</jasperReport>

Note that this example contains a useful hint that allows to quickly come up with a test case that can be shared with the community. This is just using java.lang.* package to include hard coded values in text fields (that later become Excel cells) without the need to having a datasource defined (Use "Empty Datasource" to run this jrxml)

Last but not least we set the property "whenNoDataType="AllSectionsNoDetail"" because we are not using any datasource. If you are rendering the jrxml with no datasource from Java you will need it otherwise you will just get blank pages or corrupted XML files depending on the options you use.