Can IS_DETECT_CELL_TYPE = True parse dates in a specified time zone?

[twoods]

 

When exporting a report, the parameter REPORT_TIME_ZONE works great to format dates in a desired time zone. However when exporting to Excel (JExcelApi in Jasper version 4.0.1) and IS_DETECT_CELL_TYPE is True, the Time Zone reverts to the time zone of the server. Is there a parameter or some way to make the parser used when IS_DETECT_CELL_TYPE is True do its parsing for the desired time zone, which would not necessarily be the server's (or the JVMs) default time zone?

-Tad

Post Edited by twoods at 09/10/2011 15:13

[twoods]

The problem seems to be that Date values (i.e. produced when IS_DETECT_CELL_TYPE is True) will display in Excel as they were "formatted" on the server that produced the Worksheet regardless of the TimeZone of the client that views the Worksheet. Excel doesn't display any TimeZone indicator (e.g. EST or PST). In other words, the TimeZone that gets applied when Dates are formatted as Strings by Jasper is apparently not used by JExcelApi or Excel (furthermore excel doesn't appear to support any time zone formatting characters), so a raw Date VALUE given to JExcelApiExporter needs to be parsed to a Date value that will display for the desired time zone but when formatted in the TimeZone of the server.

For example, assume your Jasper server is running in EST. A 10am EST / 7am PST Date that you want to report as "7am" PST will show "10am" in Excel because JExcelApi ignores Jasper's formatting TimeZone information when it creates the Excel worksheet. So Jasper needs to create a date that will display as "7am" in the server's TimeZone.

To fix the problem, I overrode the JExcelApiExporter's getTextTimeZone method (which appears to only be called when IS_DETECT_CELL_TYPE is True).

 private class FixDateTimeZoneExcelApiExporter extends JExcelApiExporter
 {
  @Override
  protected TimeZone getTextTimeZone(JRPrintText text)
  {
      return TimeZone.getDefault();
  }
 }

My reports now show correct Date-Times in HTML, PDF, and Excel (when IS_DETECT_CELL_TYPE is True or False). I tested this with JExcelApiExporter but NOT JRXlsExporter.

Any comments?

Post Edited by twoods at 09/10/2011 21:47