Jump to content
  • Exporting Crosstab Formula "SUM" To Excel

    • Features: Exporters Audience: Administrator, Audience, Business User


    User has a requirement of exporting crosstab output to Excel with existing formulas. Once he exported report to excel he wants to modify data (this case if he edit any column cell value then that particular column total should get changed automatically). He uses Excel export property in report by providing SUM(C3:C9). 
    This is working for only 'C column', and for the rest columns it is also printing SUM of 'C column' values. 



    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

    User Feedback

    Recommended Comments

    There are no comments to display.

    Create an account or sign in to comment

    You need to be a member in order to leave a comment

    Create an account

    Sign up for a new account in our community. It's easy!

    Register a new account

    Sign in

    Already have an account? Sign in here.

    Sign In Now

  • Create New...