Excel Export: Dynamically set sheet names

Very often the reports built with JasperReports needs to be exported to Excel. This is a practice very widely used. This is the reason why Jaspersoft has focused so much on the Excel exporter in the 4.5 release that was delivered in December 2011. One of the great features added is the ability to dynamically set sheet names at run time. So starting with version 4.5 it is now possible to give a name to the sheets generated from your report which depends on your parameter or data in your database. This is not something that has to be hard coded anymore.

Properties

When using JasperReports or iReport designer for some time you have probably noticed how properties are used for reports, bands or elements. Jasperreports has many more properties than the commonly used ones which are displayed in the Properties Pane in iReport Designer. To know more about using properties read the page Use extra properties to build enhanced reports.

the property net.sf.jasperreports.export.xls.sheet.name

The property we are interested in to set dynamically sheet name is net.sf.jasperreports.export.xls.sheet.name. The way it works is that when the report is exported to Excel, jasperReports will look at the values hardcoded to set the sheet names (as it is described in the API), if there is no names it will use the default ones (page1, page2, ...). But in addition to that when there is an element which has the property *net.sf.jasperreports.export.xls.sheet.name* set, it will override the sheet name that was meant to be used by the value of net.sf.jasperreports.export.xls.sheet.name.

in iReport Designer

Using iReport Designer you need to follow those steps:

• go to the properties of your element
• scroll to the property named 'Properties'
• click the 3 dots button to open the Properties window
• click add
• enter "net.sf.jasperreports.export.xls.sheet.name" in the name
• tick 'use an Expression'
• press the button on the right of the value field and
• pick your field, variable or parameter.

in the JRXML

if you want to change it in the JRXML file directly you need to find your element and add the following propertyExpression to it (for using a field as the input for your sheet name):

<propertyExpression name="net.sf.jasperreports.export.xls.sheet.name">
    <![CDATA[$F{FieldName}]]>
</propertyExpression>