<html xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:jr="http://jasperreports.sourceforge.net/jasperreports">
<head>
<META http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>JasperReports 4.0.2 - XLS Formula Sample</title>
<style type="text/css">
.title {
font-family: Arial, Verdana, Helvetica, sans-serif;
font-size: 28px;
font-weight: normal;
}
.toc {
font-family: Courier New, Courier, serif;
font-size: 12px;
font-weight: normal;
}
.name {
font-family: Courier New, Courier, serif;
font-size: 16px;
font-weight: bold;
}
.label {
font-family: Arial, Verdana, Helvetica, sans-serif;
font-size: 12px;
font-weight: bold;
font-style: italic;
}
.description {
font-family: Arial, Verdana, Helvetica, sans-serif;
font-size: 12px;
font-weight: normal;
}
.value {
font-family: Courier New, Courier, serif;
font-size: 12px;
font-weight: normal;
}
.element {
font-family: Courier New, Courier, serif;
font-size: 12px;
font-weight: normal;
}
.attribute {
font-family: Courier New, Courier, serif;
font-size: 12px;
font-weight: bold;
}
.code {
font-family: Courier New, Courier, serif;
font-size: 12px;
font-weight: normal;
}
.copy {
font-decoration: none;
font-family: Verdana, Arial, Helvetica, sans-serif;
font-size: 8pt;
font-style: normal;
color: #000000;
}
.subtitle {
font-family: inherit;
font-size: inherit;
font-style: inherit;
font-weight: bold;
text-decoration: none;
color: inherit;
}
</style>
</head>
<body bgcolor="#FFFFFF">
<a name="top"></a>
<table cellspacing="0" cellpadding="0" border="0" width="100%">
<tr>
<td colspan="2" align="right"><span class="element"><a href="../../sample.reference.html">Sample Reference</a> - <a href="../../schema.reference.html">Schema Reference</a> - <a href="../../config.reference.html">Configuration Reference</a> - <a href="http://jasperreports.sourceforge.net/api/index.html">API (Javadoc)</a></span>
<br>
</td>
</tr>
<tr>
<td colspan="2">
<hr size="1">
</td>
</tr>
<tr valign="middle">
<td nowrap="true"><span class="title">JasperReports - XLS Formula Sample (version 4.0.2)</span></td><td align="right"><img src="../../resources/jasperreports.png" border="0"></td>
</tr>
<tr>
<td colspan="2">
<hr size="1">
</td>
</tr>
</table>
<br>
<span class="description"><span class="description">Shows how formulas could be introduced in reports exported to XLS format.</span></span>
<br>
<br>
<span class="element"><a href="http://sourceforge.net/projects/jasperreports/files/jasperreports/JasperReports%204.0.2/jasperreports-4.0.2-project.zip/download" target="_blank">Download All Sample Source Files</a></span>
<br>
<span class="element"><a href="http://jasperforge.org/scm/viewvc.php/tags/jr-4-0-2/jasperreports/demo/samples/xlsformula/?root=jasperreports" target="_blank">Browse Sample Source Files on SVN</a></span>
<table width="100%" border="0" cellpadding="0" cellspacing="0">
<tr>
<td style="width: 20px;">
<br>
</td><td>
<br>
</td>
</tr>
<tr>
<td colspan="2"><span class="label">Main Features in This Sample</span></td>
</tr>
<tr>
<td>
<br>
</td><td><span class="element"><a href="#xlsformula">Exporting XLS Formulas</a></span></td>
</tr>
</table>
<table width="100%" cellspacing="0" cellpadding="0" border="0">
<tr>
<td><img src="../../resources/px.gif" border="0" width="20" height="1"></td><td><img src="../../resources/px.gif" border="0" width="20" height="1"></td><td><img src="../../resources/px.gif" border="0" width="20" height="1"></td><td><img src="../../resources/px.gif" border="0" width="20" height="1"></td><td width="80%">
<br>
</td><td width="20%">
<br>
</td>
</tr>
<tr>
<td colspan="6" align="right"><a name="xlsformula"></a><a href="#top" class="toc">top</a></td>
</tr>
<tr>
<td colspan="6">
<hr size="1">
</td>
</tr>
<tr valign="top">
<td><img src="../../resources/jr-16x16.png" border="0"></td><td colspan="4"><span class="name">Exporting XLS Formulas</span></td><td align="right"><span class="copy">Documented by
<a href="mailto:shertage@users.sourceforge.net" class="copy">Sanda Zaharia</a></span></td>
</tr>
<tr>
<td colspan="6">
<br>
</td>
</tr>
<tr valign="top">
<td>
<br>
</td><td nowrap="true"><span class="label">Description / Goal</span></td><td>
<br>
</td><td colspan="3"><span class="description">
How to generate formulas, when exporting the report to XLS.
</span></td>
</tr>
<tr valign="top">
<td>
<br>
</td><td colspan="1"><span class="label">Since</span></td><td>
<br>
</td><td colspan="3"><span class="description">3.0.0</span></td>
</tr>
<tr>
<td colspan="6">
<br>
</td>
</tr>
<tr>
<td>
<br>
</td><td colspan="5"><span class="description">
<b>Why to use a formula</b>
<br>
<br>
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.
<br>
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.
<br>
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.
<br>
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:
<ul>
<li>Financial</li>
<li>Date & Time</li>
<li>Math & Trig</li>
<li>Statistical</li>
<li>Text</li>
<li>Logical</li>
<li>etc.</li>
</ul>
<b>Note: </b>
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.
<br>
<br>
All these above are good reasons to use formulas in Excel as frequent as possible.
<br>
<br>
<b>Excel formula feature in JasperReports</b>
<br>
<br>
The JasperReports engine provides a feature which allows using a formula as content of a text field.
<br>
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 <span class="code"><code><textFieldExpression /></code></span> element.
<br>
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 <span class="code"><code>net.sf.jasperreports.export.xls.detect.cell.type</code></span> to true:
<br>
<br>
<span class="code"><code>
<property name="net.sf.jasperreports.export.xls.detect.cell.type" value="true"/>
</code></span>
<br>
<br>
In JasperReports a formula can be stored using the <span class="element"><a href="http://jasperreports.sourceforge.net/api/net/sf/jasperreports/engine/JRAbstractExporter.html#PROPERTY_CELL_FORMULA" target="_blank">PROPERTY_CELL_FORMULA</a></span> text field property.
<br>
The property name is <span class="code"><code>net.sf.jasperreports.export.xls.formula</code></span>, 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.
<br>
If the expression contains only constants or static data, then using the <property /> element is recommended.
<br>
If the expression contains one or more dynamic data, then the <propertyExpression /> element should be used instead.
<br>
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.
<br>
<br>
<b>Formula usage examples</b>
<br>
<br>
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.
<br>
<br>
A2 = 7;
<br>
A3 = 4;
<br>
A4 = SUM(7,4) = 11;
<br>
A5 = A2 - A3 = 3.
<br>
<br>
Below are some pieces of code showing how formulas should be written:
<br>
<br>
The text field containing the A2 cell's value:
<br>
<br>
<span class="code"><code>
<textField isBlankWhenNull="false">
<br>
<reportElement key="textField-1" x="0" y="150" width="280" height="23">
<br>
<property name="net.sf.jasperreports.export.xls.formula" value="7"/>
<br>
</reportElement>
<br>
<box rightPadding="5">
<br>
<topPen lineWidth="4.0" lineColor="#C0C0C0"/>
<br>
<leftPen lineWidth="4.0" lineColor="#C0C0C0"/>
<br>
<bottomPen lineWidth="1.0" lineColor="#C0C0C0"/>
<br>
<rightPen lineWidth="1.0" lineColor="#C0C0C0"/>
<br>
</box>
<br>
<textElement textAlignment="Right" verticalAlignment="Middle"/>
<br>
<textFieldExpression class="java.lang.Integer"><![CDATA[Integer.valueOf(0)]]></textFieldExpression>
<br>
</textField>
</code></span>
<br>
<br>
Two things of interest are here:
<ul>
<li>The <span class="code"><code><property name="net.sf.jasperreports.export.xls.formula" value="7"/></code></span> element, containing the most simple formula possible.
The number 7 could be considered itself as a formula result.</li>
<li>The <span class="code"><code><textFieldExpression class="java.lang.Integer"><![CDATA[Integer.valueOf(0)]]></textFieldExpression></code></span> element, containing also the value of 0.
This value will be exported to all other output formats but XLS, instead of the formula property.</li>
</ul>
Next, the text field containing the A4 cell's value:
<br>
<br>
<span class="code"><code>
<textField isBlankWhenNull="false">
<br>
<reportElement key="textField-3" x="0" y="196" width="280" height="23">
<br>
<property name="net.sf.jasperreports.export.xls.formula" value="SUM(A2,A3)"/>
<br>
</reportElement>
<br>
<box rightPadding="5">
<br>
<leftPen lineWidth="4.0" lineColor="#C0C0C0"/>
<br>
<bottomPen lineWidth="1.0" lineColor="#C0C0C0"/>
<br>
<rightPen lineWidth="1.0" lineColor="#C0C0C0"/>
<br>
</box>
<br>
<textElement textAlignment="Right" verticalAlignment="Middle"/>
<br>
<textFieldExpression class="java.lang.Integer">Integer.valueOf(0)</textFieldExpression>
<br>
</textField>
</code></span>
<br>
<br>
Again, the <span class="code"><code><property name="net.sf.jasperreports.export.xls.formula" value="SUM(A2,A3)"/></code></span> 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.
<br>
All other exporters will consider that the A4 value is 0, as shown in the <span class="code"><code><textFieldExpression /></code></span> expression.
<br>
Finally, the text field containing the A5 cell's value:
<br>
<br>
<span class="code"><code>
<textField isBlankWhenNull="false">
<br>
<reportElement key="textField-4" x="0" y="219" width="280" height="23">
<br>
<propertyExpression name="net.sf.jasperreports.export.xls.formula"><![CDATA["A" + Integer.valueOf(2).intValue() + "-A" + 3]]></propertyExpression>
<br>
</reportElement>
<br>
<box rightPadding="5">
<br>
<leftPen lineWidth="4.0" lineColor="#C0C0C0"/>
<br>
<bottomPen lineWidth="2.0" lineColor="#C0C0C0"/>
<br>
<rightPen lineWidth="1.0" lineColor="#C0C0C0"/>
<br>
</box>
<br>
<textElement textAlignment="Right" verticalAlignment="Middle"/>
<br>
<textFieldExpression class="java.lang.Integer"<<![CDATA[Integer.valueOf(0)]]></textFieldExpression>
<br>
</textField>
</code></span>
<br>
<br>
Here was used a <span class="code"><code><propertyExpression /></code></span> instead of simple <span class="code"><code><property /></code></span>, 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.
<br>
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.
<br>
<br>
<b>Running the Sample</b>
<br>
<br>
Running the sample requires the <a href="http://ant.apache.org/" target="_blank" class="element">Apache Ant</a> library. Make sure that <span class="code"><code>ant</code></span> is already installed on your system (version 1.5 or later).
<br>
In a command prompt/terminal window set the current folder to <span class="code"><code>demo/samples/xlsformula</code></span> within the JasperReports source project and run the <span class="code"><code>> ant test view</code></span> command.
<br>
It will generate all supported document types containing the sample report in the <span class="code"><code>demo/samples/xlsformula/build/reports</code></span> directory.
<br>
Then the report will open in the JasperReports internal viewer.
</span></td>
</tr>
<tr>
<td colspan="6">
<br>
</td>
</tr>
</table>
<br>
<table cellspacing="0" cellpadding="0" border="0" width="100%">
<tr>
<td>
<hr size="1">
</td>
</tr>
<tr>
<td align="center"><span class="copy">© 2001-2010 Jaspersoft Corporation <a href="http://www.jaspersoft.com" target="_blank" class="copy">www.jaspersoft.com</a></span></td>
</tr>
</table>
</body>
</html>
