Proper Excel/PDF date formatting output

[panam]

Hi,

I am rather new to Jasper/iReport and have to figure out whether and how certain things can be accomplished.

We need to export timestamp data (java.util.date) with millisecond precision to Excel (to do some further calculations) and to PDF (just for printing and archiving).

Currently, I am stuck with proper formatting for the Excel output. In iReport, I specified the timestamp field with Expression Class java.util.Date and Pattern HH:mm:ss.SSS. This works fine for viewing the data in Excel and PDF.

But whatever I do, Excel does not recognize it as a date field, but as a user defined or standard (and sometimes it even tells the sheet was corrupted), depending on the Excel exporter used and the settings in the iReport exporting preferences. I.e. using the HH:mm:ss.SSS formatting, the date information (months, years) is lost in Excel (as can be seen when a different formatting for the resulting cells is set). We'd like to retain the whole date information but have it formatted to show only HH:mm:ss.SSS as a default.

I've spent some hours to get it working using the different options (e.g.  <property name="net.sf.jasperreports.export.xls.detect.cell.type" value="true" />) without success, and now somehow lost the confidence that this is possible at all. IS IT, or am I wasting my time?

So far,I've only tried the preview from iReport (4.01.) and not programmatically (e.g. using FORMAT_PATTERNS_MAP). Could it be made working that way?

Thanks

Panam

[ernestoo]

Panam - in iReport: Tools -> Options -> Export Options -> Excel -> Detect Cell Type.

 

I exported my java.util.Timestamp successfully to Excel (detected it as a proper date w/ precision)

 

This sets JRXlsExporterParameter.IS_DETECT_CELL_TYPE, Boolean.TRUE);

 

See samples/jasper as well in the jasperreports distribution.