JavaFX Tutorials

Saturday, September 22, 2012

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).

Excel Spreadsheet with a Date Column

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>

7 comments:

  1. The better approach to address this is to use net.sf.jasperreports.export.xls.pattern.






    This has been in JasperReports since version 4.1.1 - August 2011.

    It is outlined here: http://jasperreports.sourceforge.net/sample.reference/xlsfeatures/index.html#formatPattern


    The information on the JasperReports samples at the above site is great. Also check out the JasperReports Ultimate Guide at http://jasperreports.sourceforge.net/JasperReports-Ultimate-Guide-3.pdf. It was previously a document you had to pay for, but now it is free.

    Sherman
    Jaspersoft

    ReplyDelete
    Replies
    1. Hi Sherman,

      I'm trying out your answer, but am getting the following error:

      Error filling print... Malformed pattern "EEE, MMM d, yyyy"
      java.lang.IllegalArgumentException: Malformed pattern "EEE, MMM d, yyyy" 

      Delete
    2. (See the XML listed at the end of the post)

      Delete
  2. This comment has been removed by a blog administrator.

    ReplyDelete
  3. 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.

    You should to use excel to format date EXACTLY like your locale is set on ireport and JasperReport. For Spanish(Venezuela) the excel format is:

    MM/DD/YY HH:MM AM/PM

    ReplyDelete
  4. Thank you so much....
    You made my day with this solution...

    ReplyDelete
  5. This solution worked.. I made the date format in excel as yyyy-mm-dd hh:mm:ss
    In Ireport, declared the field as java.util.long
    then in the chart category expression, used as below:
    org.apache.poi.ss.usermodel.DateUtil.getJavaDate($F{Payroll Date})

    Thanks,
    Gayathri M.

    ReplyDelete