/
EXCEL

EXCEL

https://community.jaspersoft.com/wiki/tips-exporting-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.

Related content

PDF Form
More like this
Text "Styled"
Text "Styled"
More like this
Parameters
Parameters
More like this