Apache POI , Date cells with different date formats generates Excel cell with custom data type instead of Date

[a***@gmail.com]

Hi,
I am using below code

HSSFCellStyle w_dateStyle = a_workBook.createCellStyle();
HSSFDataFormat w_hssDateFormat = a_workBook.createDataFormat();
w_dateStyle.setDataFormat(w_hssDateFormat.getFormat("dd-MMM-yyyy"));

HSSFRow w_row = null;
HSSFCell w_cell = null;
w_row = a_sheet.createRow((short) 0);
w_cell = w_row.createCell((short) 0);
w_cell.setCellValue(new Date());
w_cell.setCellStyle(w_dateStyle);

So this code generates Excel file with one cell which is having date value
in dd-MMM-yyyy format, but if you verify data type of same in Excel..it
shows as custom instead of Date.
Where as if style is set as

w_dateStyle.setDataFormat((short)0xe);

Then it gives date cell but it is not in dd-MMM-yyyy format, i want to get
cell in dd-MMM-yyyy format as well as it should be of Date type instead of
custom, how is it possible?
Thanks.
I am using POI 2.5.1 & MS Excel 2003.



--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-Date-cells-with-different-date-formats-generates-Excel-cell-with-custom-data-type-insteade-tp5711889.html
Sent from the POI - User mailing list archive at Nabble.com.

[Nick Burch]

Post by a***@gmail.com
w_dateStyle.setDataFormat((short)0xe);
Then it gives date cell but it is not in dd-MMM-yyyy format

IIRC, that's one of the special "localisable" date formats, so it'll
display in different formats depending on the machine you open it in.
Quite a few of the built-in ones (dates and numbers) will look very
different when opened on eg US and French locales

Post by a***@gmail.com
I am using POI 2.5.1 & MS Excel 2003.

POI 2.5.1? That's practically old enough to remember the time of the
dinosaurs! It's 9 years old now, and the list of fixes since then is a
small book...
http://poi.apache.org/changes.html#2.5.1-FINAL

Try upgrading to something from this decade and see if it helps!

Nick

[Mark Beardsley]

Could this not be simple confusion between type and format?

Using Excel, when you choose to format a cell, the application will open the
'Format Cells' dialog box for you to make selections. If you click on the
Date 'Category' then the display will change to show you a Type and a Locale
combo box. The key, to my mind, is the range of options available to you in
the Type combo box. If one of these does match your specific format, then
the cell will appear to be a Date type cell. If not, then the only option
left is for it to be a Custom formatted cell. Also, remember to distinguish
between format and type; a Custom format cell can still ne a Date type cell.

So, if your preferred format of dd-MMM-yyyy is available by default in the
Type box for the specific locale selected in the Locale combo box, then it
is likely that the cell will show as a Date cell, otherwise it will be a
Custom formatted cell. This is also the key, at least I think it is, to
Nick's previous point about Locale's being important, it affects the set of
Types.



--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-Date-cells-with-different-date-formats-generates-Excel-cell-with-custom-data-type-insteade-tp5711889p5711898.html
Sent from the POI - User mailing list archive at Nabble.com.

[a***@gmail.com]

Nick,Mark;
Thanks for your quick responses.
I tried upgrading POI version to latest stable release 3.9 & with MS Office
2010..still issue is reproducible.
Let me explain complete requirement from my side:
I have web based application where in users can select different date
formats like DD-MMM-YYYY,MM-DD-YYYY,YYYY-MM-DD so on.
So user selects date format as per his/her choice & date field related data
gets rendered on browser as per user selected date format.Application also
provide option to export such data in excel so whats happens is this data
gets exported into Excel using POI APIs..& data gets displayed in format set
in date format of machine[columns are exported as Date columns].If i right
click & choose format set & set format as per what is set in application
then format data get displayed with selected format permanently.But i want
default behavior as when data is exported in excel it should get rendered
with date format in application; my user's do not want additional work of
formatting cells for each date column every time they export excel
file.Hope, problem is clear now; my guess is that during format cells
operation in cell, it must be setting some metadata flag; so is it possible
to set such flag from POI side while generating excel file?



--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-Date-cells-with-different-date-formats-generates-Excel-cell-with-custom-data-type-insteade-tp5711889p5712001.html
Sent from the POI - User mailing list archive at Nabble.com.

[Mark Beardsley]

I think that there are two parts to any complete answer to your question if I
understand the problem correctly. The heart of the problem, I think, is that
your users wish to see that when they open the Format Cell dialog box for a
cell that holds a Date value, the word 'Date' is highlighted in the Type
combo box rather than the word 'Custom'.

The first is that irrespective of the way the value appears in the cell, it
should still be a date value. It does not matter whether the type is stated
as Date or as Custom in the format cells dialog box, the cell will still
hold a date value - actually a rather large number. Hence, they may be
confusing the appearance of the cell with the type of the value it holds. It
may well be that the only way around the problem is to expand this set of
Date type formats but I do not know how to do that nor if it is even
possible without hacking the Excel application itself. That really is a
question for an Excel forum I feel. If they know how to add to the range of
Date formats and have this persist in the workbook file then it may - may -
be possible to emulate that using poi.

The second is to suggest that you use Excel directly to try to achieve what
you want to see how it behaves 'normally'. I suspect you will find that it
will only allow you to select from a limited number of formats if you wish
to keep the type set as Date. If you wish to allow users access to the full
range of formats for date values then the only option is to specify a custom
date format - one that is not a standard pattern for a date value under the
workbooks' locale.

Remember that the dd-MMM-yyyy formatting string does not affect the value
stored in the cell but only it's appearance. If your users need to perform
complex calculations using the values in these cells, then Excel will still
be regarding them as dates irrespective of whether the format cell dialog
box says they are Date or Custom type cells; assuming of course that your
application handles inserting these values into the cells correctly

To sum up; if you wish to see the Date type selected in the Format Cells
combo box, then you have to limit the formatting strings to just those
supported for the workbooks Locale - unless you can find a way to modify
this list. If you wish to allow users access to the full range of date
formats then sometimes, the Excel will tell you that the cell has a Custom
rather than a Date type even though it holds a valid date value.

Yours

Mark B.



--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Apache-POI-Date-cells-with-different-date-formats-generates-Excel-cell-with-custom-data-type-insteade-tp5711889p5712002.html
Sent from the POI - User mailing list archive at Nabble.com.