JasperReports 4.6.0 - Advanced Excel Features

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

JasperReports - Advanced Excel Features (version 4.6.0)

Shows how the specific Excel features could be turned on when exporting to Excel formats.

Download All Sample Source Files
Browse Sample Source Files on SVN


Main Features in This Sample
	Advanced Excel Features

top

Advanced Excel Features Documented by Sanda Zaharia

Description / Goal How to turn on Excel specific features to improve the generated document in Excel formats.

Since 1.2.0

Other Samples

/demo/samples/xlsformula

Pixel-Perfect Document Representation and Excel Output Format

One of the main purposes of the JasperReports engine is to prepare documents ready to be printed out in a pixel-perfect representation. In this approach objects own dimensions, absolute and relative positions within the document should be totally preserved. This is completely convenient for usually read-only or layout-oriented output formats, such as PDF or Graphics2D, but could generate some inconvenients for data-oriented output formats like the Excel-based ones. In this case more important than the pixel-perfectness of the document is to allow the generated data in the report to be properly manipulated by the Excel engine, therefore to generate as much as possible Excel-like documents, instead of rigorously pixel-perfect ones.
More, there are certain features at the moment available for Excel documents only, such as freezing panes, grouping rows, stretching row heights or column widths, applying formulas, etc. To provide support for all these specific features in generated Excel documents would amazingly increase their functionality and applicability.
That's why a number of custom export properties were added, in order to allow the Excel features to take precedence over the fixed pixel-perfect document layout. This sample shows one by one these recently added properties and how they work.

Dynamic Sheet Names

One of the main preoccupations when exporting to Excel output format is to ensure that every sheet in the document has a proper name, because no-named sheets are not allowed in Excel. Older JasperReports versions provided several ways to accomplish this, to cover as many as possible situations. Users either can rely on the engine's default sheet naming procedure, or they can provide themselves custom sheet names at runtime based on the SHEET_NAMES export parameter. This second option is very useful when dynamic sheet names are involved, but it cannot cover all possible situations. One of the main troubles when using the SHEET_NAMES parameter is the impossibility to be set at design time, so it has to be Java hardcoded at runtime.
To solve such kind of problems, a new export custom property was added: net.sf.jasperreports.export.xls.sheet.name. It may be set at element level and, if present, it overrides all other settings for the current sheet name (ie the sheet name provided by the SHEET_NAMES parameter or by the document-level sheet names properties). Since this new property supports also expressions, it's completely suitable for dynamic sheet names.
In our demo/samples/xlsfeatures/reports/XlsFeaturesReport.jrxml sample one could see an example of usage in the text element labeled ID in the page header:

  <propertyExpression name="net.sf.jasperreports.export.xls.sheet.name"><![CDATA[$P{Customers}+ " " + $V{PAGE_NUMBER}]] ></propertyExpression>

Therefore each sheet will be labeled with the Customers i text instead of the default Page i (where i stands for the sheet index).

Format Pattern Property

Format patterns are useful when data have to be presented in a completely readable and meaningful form. To see an example, dates are often stored as numbers (the number of milliseconds that have passed since January 1, 1970 00:00:00.000 GMT). But reading such kind of numbers does not fit our usual representation of a date, with perfectly delimited year, month and day fields. A format pattern is absolutely necessary here, to get a meaningful representation of these numbers.
Here comes the pattern attribute of textfields to solve the problem. For almost all output formats, excepting the Excel ones. That's because the pattern attribute contains standard Java format patterns. Unfortunately, not all patterns valid for Java are valid for Excel too. There are situations where such a pattern should be translated in order to be recognized in Excel.
An older way to realize this translation is to use the FORMAT_PATTERNS_MAP export parameter at runtime, with the same inconvenient as shown in the Dynamic Sheet Names section: the need of Java hardcoding at runtime.
Instead of this, now one has the possibility to specify the proper format pattern for Excel right in the report sample, at element level, using the custom property net.sf.jasperreports.export.xls.pattern
To see it in action, take a look at the current date textfield in the XlsFeaturesReport.jrxml sample's <pageHeader/> section:

  <textField pattern="EEE, MMM d, yyyy">
  <reportElement style="Sans_Bold" mode="Opaque" x="0" y="30" width="515" height="30" forecolor="#000000">
    <property name="net.sf.jasperreports.export.xls.pattern" value="ddd, mmm d, yyyy"/>
  </reportElement>
  ...
  </textField>

In this case the EEE, MMM d, yyyy pattern, completely valid in Java but generating unreadable content for Excel, is replaced with the equivalent ddd, mmm d, yyyy pattern when exporting report to Excel.

Column Width Adjustment Properties

One of the most frequently encountered problems when exporting documents to Excel consists in the inadequate resulting column width. In some cases one could observe that the generated column width differs completely from the expected one. This is due to the fact that Excel uses character-width units to measure column widths, while all sizes defined in a JasperReport document are measured in pixels. Because one cannot guess at design time which will be the default font set in the Excel Normal style template, the JR engine performs an approximative calculation which doesn't fit always the Excel's own calculations. In all these cases, we have the possibility to adjust the column width by setting a more appropriate width (in pixels) for that column, in order to minimize the difference between JR and Excel calculations. The net.sf.jasperreports.export.xls.column.width and net.sf.jasperreports.export.xls.column.width.ratio properties are introduced to accomplish this task.

If the width adjustment is required for particular columns, it could be done using the net.sf.jasperreports.export.xls.column.width property. Its value is represented in pixels, and can be set at element level only, to values greater or less than the element's width. When exporting to Excel, the value of this property will be considered instead. One can see this property set in the XlsFeaturesReport.jrxml sample for the element labeled State:
```
  <property name="net.sf.jasperreports.export.xls.column.width" value="110"/>
```
This means that the column width is no more dependent on element widths on that column, its value being calculated as the equivalent value in character widths for 110 pixels. When running the sample one can see that the State column width remains fixed while other column widths are changing according to other settings present in the report.
If the width adjustment is required for all columns in a sheet or in the entire document, the use of the net.sf.jasperreports.export.xls.column.width property per each distinct column could be bypassed with the net.sf.jasperreports.export.xls.column.width.ratio property. Its value, a floating point number, represents the ratio used to adjust all column widths in the sheet or in the document. When it's defined at report level all columns in the document will be affected, and when it's defined at element level only columns present in the current sheet will be affected. Settings at element level override settings at document level. This behavior is illustrated in the JRXML sample as it follows:
First, the net.sf.jasperreports.export.xls.column.width.ratio property is declared at document level:
```
  <property name="net.sf.jasperreports.export.xls.column.width.ratio" value="1.25f"/>
```
This means that all column widths in the document should be multiplied by a 1.25 factor, unless conditions with higher priority are met. This is what happens in the first page of the generated document for all columns excepting State one, with the fixed 110 pixels width (see the net.sf.jasperreports.export.xls.column.width.ratio) property discussed above. But on the second page one can see that columns are no more enlarged, on the contrary, they are visibly narrowed down. This is because the Street textfield in the detail section contains a conditional property expression which overrides the document-level setting:
```
  <propertyExpression name="net.sf.jasperreports.export.xls.column.width.ratio"><![CDATA[$F{id}.equals(44) ? "0.75f" : null]] ></propertyExpression>
```
When the record with field value id = 44 is rendered (and this happens in the second page) the condition is satisfied, so all columns on the current sheet will be multiplied by 0.75 instead of 1.25. The State column width remains unchanged on the second page too.

Freeze Pane Properties

Freeze panes represent an Excel-specific feature with considerable visual impact, so they have their well deserved place among special features supported by JR. There are multiple ways to define freeze panes in JR and they can also be combined, in order to cover a large variety of situations.

The first category of properties contain settings for the entire document. There are separate properties for rows and column in order to allow possible combinations with properties at element level. The 2 document-level properties are
- net.sf.jasperreports.export.xls.freeze.row and
- net.sf.jasperreports.export.xls.freeze.column
Their values should represent valid row indexes and column names in Excel. The freeze row (or column) property defined at report level can be combined with a freeze column (or row) property defined at element level to delimit the freeze pane edges. As usual, element-level settings will override document-level settings.
The element-level properties define the freeze pane edges for the current sheet only. If multiple elements in the same sheet provide freeze pane properties, the maximum values for these properties are taken into account. Element-level properties are
- net.sf.jasperreports.export.xls.freeze.row.edge and
- net.sf.jasperreports.export.xls.freeze.column.edge
The element-level properties can take a predefined set of values: Left and Right for the net.sf.jasperreports.export.xls.freeze.column.edge, Top and Bottom for the net.sf.jasperreports.export.xls.freeze.row.edge.

The last 2 freeze pane properties are illustrated in the XlsFeaturesReport.jrxml sample report: the column edge is set for the State element and the row edge is set for the Street element, both in the page header

  <property name="net.sf.jasperreports.export.xls.freeze.column.edge" value="Left"/>
  <property name="net.sf.jasperreports.export.xls.freeze.row.edge" value="Bottom"/>

These settings instruct the engine that the left columns to the State element and all rows above, including the current row, are "frozen".

The Autofilter Property

In editable Excel documents data can be autofiltered allowing users to show/hide only the desired data in a range. This feature is also supported in JR when the net.sf.jasperreports.export.xls.auto.filter property is explicitly set. This is an element-level property and may have 2 allowed values:

Start - the data range starts immediately below the current cell; on the current row will be placed the autofilter lists with column data.
End - the data range ends with the current cell (and row and column).

If multiple Start or End values are found in the same sheet, only the last encountered are considered.
To see how it works, take a look at the following settings in the XlsFeaturesReport.jrxml sample:

<property name="net.sf.jasperreports.export.xls.auto.filter" value="Start"/>
for the State element in the page header; all column data lists will be placed on this row, and the data range will start immediately below this cell
<propertyExpression name="net.sf.jasperreports.export.xls.auto.filter"><![CDATA[$V{PAGE_NUMBER}.equals(1) ? "End" : null]]></propertyExpression/>
for the Street field in the detail section; therefore for the first page only the data range will end on the last Street detail cell (because all Street detail cells do provide this property). For all other pages there is no autofilter End value defined, and the data range contains only data in the column enclosing the autofilter Start value.
<propertyExpression name="net.sf.jasperreports.export.xls.auto.filter"><![CDATA[$F{id}.equals(44) ? "Start" : null]]></propertyExpression/>
for the Name textfield in the detail section. There are 2 occurrences of the autofilter Start value in the second sheet. One is given by the setting of the State element in the page header, the other is set in the Name detail textfield. At export time, the last encountered setting is provided by the detail element, therefore in the second sheet the data range will start on the Name column, immediately below the row with field valueid=44. Because there is no autofilter End value defined on the second sheet, the data range contains only current column data.

The Outline Row Level Property

Another noticeable feature is the row outline grouping on different indentation levels. To turn this feature on, one has to know which group each row belongs to, and which outline level corresponds to this group. All these informations are managed using a property formed with the fixed net.sf.jasperreports.export.xls.outline.level prefix followed by a suffix representing the outline level: net.sf.jasperreports.export.xls.outline.level.<suffix>. It's recommended to use the outline level itself as property suffix, but it's not mandatory. The suffix may take any other string value, but one has to keep in mind that suffixes are used as sorted row level descriptors. For instance, the suffix "aaa" < "bbb", therefore the outline level associated with the suffix "aaa" will be smaller than the level associated with "bbb". To have the most simple and intuitive representation of the outline level property, the best practice is to use the row level as property suffix.
Doing so, a net.sf.jasperreports.export.xls.outline.level.2 property means that its value is correlated with the outline level 2, so the current row belongs to a row group with outline level 2. According to Office Open XML specs, allowed values for outline levels are positive integer values from 1 to 7.
The value of this property could be any expression (including null). When such a property occurrence is met, the suffix indicates the outline level for that row. If multiple properties with the same prefix are defined for the same row, the deepest outline level is considered for that row. To end an outline row group one has to set the related outline level property with the End value. This is a special property value which instructs the JR engine that the current row group of that level ends on that row. This is the most simple way to perform outline row grouping at Excel export time. The XlsFeaturesReport.jrxml report sample shows an example of howto:

<group name="cityGroup" >
  ...
  <groupFooter>
    <band height="20">
      <textField>
        <reportElement mode="Opaque" x="0" y="0" width="515" height="15" forecolor="#000000" backcolor="#e0e0e0" style="Sans_Bold">
          <property name="net.sf.jasperreports.export.xls.row.outline.level.1" value="End"/>
        </reportElement>
        ...
      </textField>
    </band>
  </groupFooter>
</group>
<group name="initialNameGroup" >
  ...
  <groupHeader>
    <band height="10">
      <textField>
        <reportElement mode="Opaque" x="0" y="0" width="515" height="10" forecolor="#000000" backcolor="#a0a0a0" style="Sans_Bold">
          <property name="net.sf.jasperreports.export.xls.row.outline.level.1" value="Body"/>
        </reportElement>
        ...
      </textField>
    </band>
  </groupHeader>
  <groupFooter>
    <band height="10">
      <textField>
        <reportElement mode="Opaque" x="0" y="0" width="515" height="10" forecolor="#000000" backcolor="#b0b0b0" style="Sans_Bold">
          <property name="net.sf.jasperreports.export.xls.row.outline.level.2" value="End"/>
        </reportElement>
        ...
      </textField>
    </band>
  </groupFooter>
</group>
...
<detail>
  <band height="15">
    <textField>
      <reportElement x="0" y="0" width="104" height="15">
        <property name="net.sf.jasperreports.export.xls.row.outline.level.2" value="Body"/>
      </reportElement>
      ...
    </textField>
    ...
  </band>
</detail>

Embedding Content From External Documents Into The Generated Document

Generated documents usually represent standalone, self-consistent pieces of information and don't require additional content from other documents in order to become meaningful. But sometimes a generated document may not uncover the whole picture. Let's imagine, for instance, a book where chapters are appended one at a time after they are completed. Or an Excel document where sheets are organized per years. Each new year comes with a new sheet to be added into the existing document. In this case there should be a possibility to concatenate our generated document with other existing ones.
A solution would be to use the batch export functionality as explained in the Batch Export Sample. The main inconvenient here is that all documents to be concatenated should be available as JasperPrint objects. But what if we have no JasperPrint objects, but valid Excel documents instead? The batch export has no answers for this.
In order to solve this kind of requirements, two new exporter properties were added starting with v4.5.1:

net.sf.jasperreports.export.xls.workbook.template - one can use this property to store the location of an existing Excel workbook template. The content of the exported report will be appended to the content of the template document at export time. All settings of the template document along with its embedded binary objects will be also exported into the generated document. Templates can be loaded from Excel template files (*.xlt) as well as from valid Excel documents (*.xls).
This property may be used in Excel exporters based either on Apache POI APIs (such as JRXlsExporter) or on the JExcelApi library (the JExcelApiExporter). There's no similar property for the JRXlsxExporter.
net.sf.jasperreports.export.xls.workbook.template.keep.sheets - specifies whether to keep the sheets of the existing template into generated document. Sometimes only macros and/or other global settings from an existing template are needed in the generated document, without keeping the actual content of the template. If set to false, this property prevent the template sheets to be also exported.
This property is used in conjunction with net.sf.jasperreports.export.xls.workbook.template described above.

To get an idea about how these properties work, see how they are set in the demo/samples/xlsfeatures/reports/MacroReport.jrxml sample, at report level. Excel template files are saved in the demo/samples/xlsfeatures/data directory, where you could examine the macro.xlt template for this particular case.
After running the sample take a look at generated MacroReport.xls and MacroReport.jxl.xls reports. You will see that the first sheet of these reports was loaded from the template file. In the following sheets, you can run the macro imported from the template file by pressing CTRL+SHIFT+T and see how fonts and column width changed.

Importing Macros Into The Generated XLSX Document

For the moment embedding entire content from external documents does not apply to the JRXlsxExporter. But it still keeps the possibility to import macros from an existing template into the generated document. To do so, just use the related net.sf.jasperreports.export.xlsx.macro.template property that stores the location of an existing workbook template containing the macro object.
The binary object found in the template document will be copied into the generated document.
Macros can be loaded from Excel macro-enabled template files (*.xltm) as well as from valid Excel macro-enabled documents (*.xlsm).
See how this property was set in the MacroReport.jrxml sample at report level and examine the macro.xltm template in the data directory. Then take a look at the generated MacroReport.xlsx document and press CTRL+SHIFT+T after selecting a cell to run the macro.

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/xlsfeatures within the JasperReports source project and run the > ant test command.
It will generate the three Excel document types based on the sample report in the demo/samples/xlsfeatures/build/reports directory and an equivalent PDF document to compare to.