XLS Time Format & IS_DETECT_CELL_TYPE

[jlong]

The time format does not work when either XLS parameter IS_DETECT_CELL_TYPE or IS_AUTO_DETECT_CELL_TYPE is set to true.

 

pattern=h:mm:ss a

 

The time reverts to 24 hour clock and sticks the "a" on the end. E.g. It returns "23:59:00 a" instead of "11:59:00 PM".

 

The date format works fine.

pattern=MM/dd/yyyy

 

I've tried version 1.2.5, 1.3.0, and 1.3.2.

 

Does anyone else experience this problem?

[edikk]

Im not sure is it help, but here is my experience...

 

When you export date to XLS (any format), Excel may convert it in their own format, when you open the file.

 

I had problem with export numbers which have leading zeros (I need them), and I found that IS_AUTO_DETECT_CELL_TYPE work in reverse way - true value is a false, and false is a true (I'm unsure of this bug). So if you set IS_AUTO_DETECT_CELL_TYPE to false, it will (? ;) ) auto detect cell type :) .

[teodord]

Hi,

 

First of all, note that the IS_AUTO_DETECT_CELL_TYPE is deprecated (as indicated in the Javadoc) and should no longer be used.

 

Secondly, you should tell us which XLS exporter are you using, the POI one or the JExcelApi one, because each of them is able to recognize slightly different date-time patterns.

 

If the pattern used in JRXML to format the text field is not recognized by the XLS exporter, you have the chance to override the pattern at export time using the FORMAT_PATTERNS_MAP exporter parameter.

 

I hope this helps.

Teodor

[jlong]

Thanks for the inputs.

 

Using the POI exporter (JXlsExporter) ...

 

Setting the IS_AUTO_DETECT_CELL_TYPE to false resulted in leading zeros & correct time format.

OK. Desirable output.

 

Setting the parameter IS_DETECT_CELL_TYPE to false resulted in no leading zeros & correct time format.

 

Setting the parameter IS_DETECT_CELL_TYPE to true resulted in leading zeros & incorrect time formats.

 

 

Then, I used the JExcelAPIExporter.

 

Setting the IS_AUTO_DETECT_CELL_TYPE to false resulted in leading zeros and correct time format.

OK. Desirable output.

 

Setting the IS_DETECT_CELL_TYPE to false resulted in no leading zeros and correct time formats.

 

Setting the IS_DETECT_CELL_TYPE to true resulted in leading zeros and correct time formats.

Yeah! Desirable code & output.

 

OK, so what we have is ...

 

POI & JExcelAPI exporters work with the deprecated IS_AUTO_DETECT_CELL_TYPE = false.

JExcelAPI works with IS_DETECTED_CELL_TYPE = true.

 

I'll be using the JExcelAPI exporter.

 

 

Much obliged to you both.

[rrrrkkkk]

 

 

Dear All,

 

 I am using jasper 2.0.4 with

 

 

JRXlsExporter  exporter=new JRXlsExporter ();

 

exporter.setParameter(JRXlsAbstractExporterParameter.

IS_DETECT_CELL_TYPE, Boolean.TRUE);

 

i using to export as csv JasperReportsUtils.renderAsCsv(jasperReport,

generalData, reportData, csvWriter2);

if my input is  000sample then it displaying in excel as 000sample but if my input is 000345 then it displaying like 345. please suggest me the solution, any jasper updated verison i have to use please suggest me the correct way.

Post Edited by rrrrkkkk at 02/22/2011 11:01

[trkavya]

adding

<entry key="MMM d, yyyy h:mm:ss a" value="MMM d, yyyy h:mm:ss AM/PM"/><entry key="h:mm:ss a" value="h:mm:ss AM/PM"/> [/code]

to formatPatternsMap in applicationContext.xml resolved the issue.