iReport Designer Dates with an Excel Data Source

Excel stores its dates as numbers counting days since January 1, 1900.  This presents a problem creating Jasper Reports that expects a number of milliseconds since January 1, 1970 used in the JSE standard java.util.Date class.   This blog shows how to use a library embedded in iReport Designer to correctly handle the Excel dates in a report.

In this spreadsheet, the first column "Payroll Date" is a date formatted for the US (MM/dd/yyyy).

When you construct a Jasper Report using iReport Designer, your fields are imported as Strings.  Changing the numeric values (Social Security Earnings, etc) to Strings isn't a problem, but you'll get an error trying to convert the Payroll Date to a String.  This is in spite of Excel presenting the data '7/1/2012' in a String format.

The Excel Date Format

In the Report Inspector, set the field type to Long.  This Long value is different that many of the values that you see in the UNIX and Java worlds.  Usually, a Long value representing a date is a millisecond value from January 1, 1970.  However, when dealing with Excel, this value is days since 1900.  Read this Microsoft article on the different Excel formats.

So, the typical "new java.util.Date(ms)" won't work, you'll need to use another library.  Fortunately, iReport Designer ships with the Apache library POI that has a call 'getJavaDate()' in a class called DateUtil that can construct a Java Date object.  Once this conversion is done, then you can use the date formatting properties in iReport Designer to display the column in the desired format.

This screenshot shows a field imported from an Excel Data Source being set to Long.  Alternatively, this can be set to Double, but in today's post Java 5 SDK's, this distinction isn't important.

Field from Excel Data Source set to 'Long'

This field will produce a result set where the field 'Payroll Date' will be a number.  This is used in an expression that will display Payroll Date using a conversion from POI.  This report will run because POI is available on iReport Designer's classpath.  If you're running Jasper Reports in your own webapp, you may need to add POI to your /WEB-INF/lib folder.

The POI Call

In "Text Field Expression", the field value "${Payroll Date}" is an argument to the DateUtil.getJavaDate() method.  Note the fully-qualified expression that is pasted here for convenience.


org.apache.poi.ss.usermodel.DateUtil.getJavaDate($F{Payroll Date})

Expression with Date Conversion and Formatting

Finally, the result of the report is a formatted date.

A Jasper Report with a Date from Excel

Excel stores it's dates as a number, but not the same number that is typically used in databases or Java.  Fortunately, iReport Designer ships with the POI library which provides a getJavaDate() method that can do the conversion in an expression.

XML Referenced in Comments

<textField pattern="EEE, MMM d, yyyy" isBlankWhenNull="true">
<reportElement uuid="e80a2f5c-d96d-43fd-b666-3f0226f3b663" x="0" y="0" width="57" height="20">
<property name="net.sf.jasperreports.export.xls.pattern" value="ddd, mmm d, yyyy"/>
</reportElement>
<textElement/>
<textFieldExpression><![CDATA[$F{Payroll Date}]]></textFieldExpression>
</textField>