EXCEL

Example for XLS Report with Formulas

So that the formula adjusts in each line, this must be calculated via a variable:

Example

To create a formula field inside an excel report, a new text field should be added:

<textField pattern="#,##0.00#;#,##0.00#-" isBlankWhenNull="true">
	<reportElement x="580" y="0" width="66" height="13" uuid="11d892f7-2e06-4241-bc8d-bc7b53e2a72f">
		<property name="com.jaspersoft.studio.unit.height" value="px"/>
		<propertyExpression name="net.sf.jasperreports.export.xls.formula"><![CDATA["=ROUND(F" + ($V{RowNo}+1) + "/1.077,2)"]]></propertyExpression>
	</reportElement>
	<textElement textAlignment="Right"/>
	<textFieldExpression><![CDATA[(new BigDecimal($F{total}.floatValue() / 1.077)).setScale(2,BigDecimal.ROUND_HALF_UP)]]></textFieldExpression>
</textField>

Details to consider:

<propertyExpression name="net.sf.jasperreports.export.xls.formula">
<![CDATA["=ROUND(F" + ($V{RowNo}+1) + "/1.077,2)"]]>
contains the formula definition
- CDATA section: used when we want to include a dynamic expression
- $V{RowNo}: variable defined in the report to store the last position with a value in a column
<textFieldExpression>
<![CDATA[(new BigDecimal($F{total}.floatValue() / 1.077)).setScale(2,BigDecimal.ROUND_HALF_UP)]]>
this value will be used when exporting to all other output formats but XLS.