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. --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
