excel-formulajasper-reportsexport-to-excel
JasperReports: Export a xls/ods file with formulas
Recently, I had to export some reports using JasperReports under ods /xls format.
The export works fine but I didn't manage to find a way to add some excel formulas between the exported columns (let's simplify it by saying a sum of some columns as described below).
When user modifies the column A in the excel file, column C gets modified (basic excel formulas, nothing new...)
Based on some comments and answers here is what I've done
<detail>
<band height="134" splitType="Stretch">
<componentElement>
<reportElement x="-20" y="0" width="120" height="60" uuid="884b6c49-9006-464d-982e-e2a5f2cb3e3e">
<property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.VerticalRowLayout"/>
</reportElement>
<jr:table xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd">
<datasetRun subDataset="Empty Dataset1" uuid="b60c3bee-0624-4a6a-bbb1-e706521c0a9a">
<dataSourceExpression><![CDATA[new net.sf.jasperreports.engine.data.JRBeanCollectionDataSource(java.util.Arrays.asList($F{dto}.getNbLgtA()))]]></dataSourceExpression>
</datasetRun>
<jr:column width="40" uuid="1dbb56d0-f25f-410b-9e02-d08b7fe84388">
<property name="com.jaspersoft.studio.components.table.model.column.name" value="Colonne1"/>
<jr:tableHeader height="30">
<staticText>
<reportElement x="0" y="0" width="40" height="30" uuid="ff8f394e-39ea-4d08-898f-601e92e6d1f3"/>
<text><![CDATA[A]]></text>
</staticText>
</jr:tableHeader>
<jr:detailCell height="30">
<staticText>
<reportElement x="0" y="0" width="40" height="30" uuid="c0c2b023-2d00-4080-88a3-a73f19fe9cda"/>
<text><![CDATA[1]]></text>
</staticText>
</jr:detailCell>
</jr:column>
<jr:column width="40" uuid="33f41b08-3d77-4076-9712-9514211dd3af">
<property name="com.jaspersoft.studio.components.table.model.column.name" value="Colonne2"/>
<jr:tableHeader height="30">
<staticText>
<reportElement x="0" y="0" width="40" height="30" uuid="ad2b74aa-95bf-43d7-83a4-6528f344b410"/>
<text><![CDATA[B]]></text>
</staticText>
</jr:tableHeader>
<jr:detailCell height="30">
<staticText>
<reportElement x="0" y="0" width="40" height="30" uuid="6d121f9e-e92d-45ba-9a48-82bf8b1245e0"/>
<text><![CDATA[2]]></text>
</staticText>
</jr:detailCell>
</jr:column>
<jr:column width="40" uuid="42d367d1-2be7-46bf-838b-ba4c1c6f3399">
<property name="com.jaspersoft.studio.components.table.model.column.name" value="Colonne3"/>
<jr:tableHeader height="30">
<staticText>
<reportElement x="0" y="0" width="40" height="30" uuid="63e3dd54-0297-463f-b061-4c536baf62a8"/>
<text><![CDATA[C]]></text>
</staticText>
</jr:tableHeader>
<jr:detailCell height="30">
<property name="net.sf.jasperreports.export.xls.formula" value="SUM(A2, B2)"/>
</jr:detailCell>
</jr:column>
</jr:table>
</componentElement>
</band>
</detail>
But it doesn't work.
Solution
Here you could find some useful information about using Excel formulas: http://jasperreports.sourceforge.net/sample.reference/xlsformula/index.html#xlsformula
For instance, below is an example of using SUM formula (the expression should be enclosed within quotes):
<propertyExpression name="net.sf.jasperreports.export.xls.formula">
<![CDATA["SUM(A1,B1)"]]>
</propertyExpression>
You need to ensure that isDetectCellType property is set to true for your report in order to make a formula to work. A complete working example (using an empty datasource) is the following:
<detail>
<band height="134" splitType="Stretch">
<componentElement>
<reportElement x="-20" y="0" width="120" height="60" uuid="884b6c49-9006-464d-982e-e2a5f2cb3e3e">
<property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.VerticalRowLayout"/>
</reportElement>
<jr:table xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd">
<datasetRun subDataset="Empty Dataset1" uuid="b60c3bee-0624-4a6a-bbb1-e706521c0a9a">
<dataSourceExpression><![CDATA[new net.sf.jasperreports.engine.JREmptyDataSource()]]></dataSourceExpression>
</datasetRun>
<jr:column width="40" uuid="1dbb56d0-f25f-410b-9e02-d08b7fe84388">
<property name="com.jaspersoft.studio.components.table.model.column.name" value="Colonne1"/>
<jr:tableHeader height="30">
<staticText>
<reportElement x="0" y="0" width="40" height="30" uuid="ff8f394e-39ea-4d08-898f-601e92e6d1f3"/>
<text><![CDATA[A]]></text>
</staticText>
</jr:tableHeader>
<jr:detailCell height="30">
<textField pattern="">
<reportElement x="0" y="0" width="40" height="30" uuid="b989d9e8-64d8-467f-a8a0-7b92a1746a0d"/>
<textFieldExpression><![CDATA[1]]></textFieldExpression>
</textField>
</jr:detailCell>
</jr:column>
<jr:column width="40" uuid="33f41b08-3d77-4076-9712-9514211dd3af">
<property name="com.jaspersoft.studio.components.table.model.column.name" value="Colonne2"/>
<jr:tableHeader height="30">
<staticText>
<reportElement x="0" y="0" width="40" height="30" uuid="ad2b74aa-95bf-43d7-83a4-6528f344b410"/>
<text><![CDATA[B]]></text>
</staticText>
</jr:tableHeader>
<jr:detailCell height="30">
<textField pattern="">
<reportElement x="0" y="0" width="40" height="30" uuid="3448c5dc-f887-415d-9833-4a22ea5b06c8"/>
<textFieldExpression><![CDATA[2]]></textFieldExpression>
</textField>
</jr:detailCell>
</jr:column>
<jr:column width="40" uuid="42d367d1-2be7-46bf-838b-ba4c1c6f3399">
<property name="com.jaspersoft.studio.components.table.model.column.name" value="Colonne3"/>
<jr:tableHeader height="30">
<staticText>
<reportElement x="0" y="0" width="40" height="30" uuid="63e3dd54-0297-463f-b061-4c536baf62a8"/>
<text><![CDATA[C]]></text>
</staticText>
</jr:tableHeader>
<jr:detailCell height="30">
<textField pattern="">
<reportElement x="0" y="0" width="40" height="30" uuid="63e3ed54-0567-463f-b0c1-4c676baf62a8">
<propertyExpression name="net.sf.jasperreports.export.xls.formula"><![CDATA["SUM(A" +($V{PAGE_COUNT}+2) + ",B" + ($V{PAGE_COUNT}+2) +")"]]></propertyExpression>
</reportElement>
<textFieldExpression><![CDATA[3]]></textFieldExpression>
</textField>
</jr:detailCell>
</jr:column>
</jr:table>
</componentElement>
</band>
</detail>
- Spill formula on % calculation
- Indexing folder content by alphabetical order in Excel
- Best way to offset the target reference of another cell?
- VBA or formula to repeat a list of items, and for each repeated list, add a location name from a separate locations list
- SUM of 2-way Weighted Grid in Excel
- How to consume data of 1 column based on other column
- How to match large data on excel based on some criteria?
- EXCEL Search of multiple possible strings of text
- VLOOKUP Returns 0 even though there is a value in the line below the first result
- transpose a table with multiple values in excel
- Apply FILTER formula to array (and use filter criteria on specific column in the array)
- Excel function to construct an array from values without VBA
- Excel - Aggregate dynamically values in a Table using array formulas (MAP, BYROW, CHOOSECOLS, TEXTJOIN, LAMBDA, FILTER, HSTACK) in a single Cell
- EXCEL: How do I return multiple columns using XLOOKUP, when the lookup_value is a range?
- Filtering an Array (not Range) based on a SUM of CHOOSECOLS applied to it
- Add rows to table produced with filter function based on additional argument
- Epoch Unix Value formula
- SORT formula with empy column in data range and descending order based on another column (not the first column in the data range)
- Update the nth element of an array on Excel using formulas
- Is there a way to Indirectly call an array of Table names?
- Power Query Dynamic Column Reference
- Excel(VBA) Formula in added column needing logical workaround
- Converting Feet-Inches to Inches Excel
- Daytime and Nightime Occurance Duration of an Event
- compile error : Ambiguous name detected: Worksheet_Change
- Excel - Formula to see if IP address is contained within list of subnets
- How to find duplicate values in one column while checking if a condition in another column is met
- How to update excel formula to work for all columns continuously to the right and not just the current 2 it's set up for
- How to do consecutive subtraction of adjacent columns and return the result concatenated in cell with comma separator?
- In Excel, create table showing the row,column of the cells in another table