JasperReports 4.6.0 - XLS Formula Sample

JasperReports Ultimate Guide - Sample Reference - Schema Reference - Configuration Reference - API (Javadoc)

JasperReports - XLS Formula Sample (version 4.6.0)

Shows how formulas could be introduced in reports exported to XLS format.

Download All Sample Source Files
Browse Sample Source Files on SVN


Main Features in This Sample
	Exporting XLS Formulas


top

	Exporting XLS Formulas		Documented by Sanda Zaharia

	Description / Goal	How to generate formulas, when exporting the report to XLS.
	Since	3.0.0

	Why to use a formula A formula represents one of the most useful features in Excel. It is designated to perform various calculations in order to determine the value contained in a given cell. These calculations can be very simple ones, like just adding two numbers, or with a higher degree of complexity, derived from any application's requirements specification. A formula can be written using number or string constants, or, better, cell references. A cell reference locates the cell position within the sheet. It is formed of the cell's related column letter and row number. When a cell reference appears in a formula, Excel will perform the calculation using the data located in the referenced cell. An interesting consequence of using cell references is that once written, a formula keeps its result up-to-date at any moment. When the content of a referenced cell changes, the calculation result will be automatically updated. This is a very important feature when cells are dynamically filled with their data. Another useful fact is that there are several optimized built-in functions that can be called in order to perform a calculation using a large set of data categories: Financial Date & Time Math & Trig Statistical Text Logical etc. Note: As seen above, formulas can operate with several data types. But data types cannot be mixed in a formula. For example, Date & Time functions cannot be applied on numbers, Math & Trig functions cannot be applied on text values, etc. Therefore is very important to provide the appropriate data type to a formula in order to obtain a successful result. All these above are good reasons to use formulas in Excel as frequent as possible. Excel formula feature in JasperReports The JasperReports 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"/>` In JasperReports 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 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. Formula usage examples The XlsFormulaReport.jrxml sample report illustrates how to use the cell formula property in several cases. It contains 2 cells, A2 and A3, having simple static values (given by very simple formulas, as will be seen below), a cell A4 containing the sum of A2 and A3 calculated using static data, and a cell A5 containing the difference between A2 and A3 calculated using a dynamic expression. A2 = 7; A3 = 4; A4 = SUM(7,4) = 11; A5 = A2 - A3 = 3. Below are some pieces of code showing how formulas should be written: The text field containing the A2 cell's value: <textField isBlankWhenNull="false"> <reportElement key="textField-1" x="0" y="150" width="280" height="23"> <property name="net.sf.jasperreports.export.xls.formula" value="7"/> </reportElement> <box rightPadding="5"> <topPen lineWidth="4.0" lineColor="#C0C0C0"/> <leftPen lineWidth="4.0" lineColor="#C0C0C0"/> <bottomPen lineWidth="1.0" lineColor="#C0C0C0"/> <rightPen lineWidth="1.0" lineColor="#C0C0C0"/> </box> <textElement textAlignment="Right" verticalAlignment="Middle"/> <textFieldExpression class="java.lang.Integer"><![CDATA[Integer.valueOf(0)]]></textFieldExpression> </textField> Two things of interest are here: The `<property name="net.sf.jasperreports.export.xls.formula" value="7"/>` element, containing the most simple formula possible. The number 7 could be considered itself as a formula result. The `<textFieldExpression class="java.lang.Integer"><![CDATA[Integer.valueOf(0)]]></textFieldExpression>` element, containing also the value of 0. This value will be exported to all other output formats but XLS, instead of the formula property. Next, the text field containing the A4 cell's value: <textField isBlankWhenNull="false"> <reportElement key="textField-3" x="0" y="196" width="280" height="23"> <property name="net.sf.jasperreports.export.xls.formula" value="SUM(A2,A3)"/> </reportElement> <box rightPadding="5"> <leftPen lineWidth="4.0" lineColor="#C0C0C0"/> <bottomPen lineWidth="1.0" lineColor="#C0C0C0"/> <rightPen lineWidth="1.0" lineColor="#C0C0C0"/> </box> <textElement textAlignment="Right" verticalAlignment="Middle"/> <textFieldExpression class="java.lang.Integer">Integer.valueOf(0)</textFieldExpression> </textField> Again, the `<property name="net.sf.jasperreports.export.xls.formula" value="SUM(A2,A3)"/>` element contains only a sum formula, and not the effective value. When exported to XLS format, Excel will calculate the correct value and will write it in the cell. All other exporters will consider that the A4 value is 0, as shown in the `<textFieldExpression />` expression. Finally, the text field containing the A5 cell's value: <textField isBlankWhenNull="false"> <reportElement key="textField-4" x="0" y="219" width="280" height="23"> <propertyExpression name="net.sf.jasperreports.export.xls.formula"><![CDATA["A" + Integer.valueOf(2).intValue() + "-A" + 3]]></propertyExpression> </reportElement> <box rightPadding="5"> <leftPen lineWidth="4.0" lineColor="#C0C0C0"/> <bottomPen lineWidth="2.0" lineColor="#C0C0C0"/> <rightPen lineWidth="1.0" lineColor="#C0C0C0"/> </box> <textElement textAlignment="Right" verticalAlignment="Middle"/> <textFieldExpression class="java.lang.Integer"<<![CDATA[Integer.valueOf(0)]]></textFieldExpression> </textField> Here was used a `<propertyExpression />` instead of simple `<property />`, and the formula expression is more complicated, and needs a CDATA section to be written. In a CDATA section could be used any dynamic expression we need, containing also variable names, parameter names, field names, and any other valid java expression. The value of A5 will be calculated by Excel when opening the generated .xls document, and for other output formats the 0 value will be exported. Running the Sample Running the sample requires the Apache Ant library. Make sure that `ant` is already installed on your system (version 1.5 or later). In a command prompt/terminal window set the current folder to `demo/samples/xlsformula` within the JasperReports source project and run the `> ant test view` command. It will generate all supported document types containing the sample report in the `demo/samples/xlsformula/build/reports` directory. Then the report will open in the JasperReports internal viewer.