Re: Getting UK-locale formatted date string from a cell

[David Beaumont <D....@kent.ac.uk>]

Hi Nick,

Thanks for your very quick reply yesterday.

On 30 Nov 2011, at 13:05, Nick Burch wrote:

> I suspect that your cell is formatted in "default date format" mode, 
> rather than explicitly being set to the UK date format
> 
> (If you open the file on a machine set to a US locale, I strongly suspect 
> you'll see the US format there too)

Yep I'm sure you're correct. I think the trouble comes from POI assuming that all locales share the US format and telling POI not to (DataFormatter(Locale)) doesn't help.

> One option is to explicitly set the format in Excel to be the UK date 
> format. The way to do that varies between excel versions, but it can be 
> done, and the spreadsheet will then show as UK formatted no matter what 
> machine it opens on. At that point, POI (or at least a newer version of 
> it) will then format the cells to the UK format. POI will format it to 
> whatever is set in the file, which may not always be what excel chooses to 
> display!

Unfortunately the spreadsheets being parsed are from a variety of third parties, each using different versions of Excel, operating systems, though they should all be in the same locale (UK dates). Dictating to them probably isn't an option (if it was we wouldn't be using spreadsheets!). Its intended to be an automated system, so manually formatting columns for them through Excel is something I'd strongly want to avoid.

> Otherwise, in POI, just override the date format to be UK style before 
> formatting.

Do you mean, if the cell is date formatted, get a Java Date using POI's methods, then format it using SimpleDateFormat? The problem with that is different date formats are used in different circumstances and I don't want to override that where the spreadsheet has specifically chosen a format (e.g. mmm-yy).

Or do you mean I can override POI's BuiltinFormats class somehow? I.e. replace Excel style 14 to be dd/mm/yyyy rather than m/dd/yy. That would be ideal.

Thanks again,
David
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

Re: Getting UK-locale formatted date string from a cell

[Nick Burch <ni...@alfresco.com>]

On Thu, 1 Dec 2011, David Beaumont wrote:
> On 30 Nov 2011, at 13:05, Nick Burch wrote:
>> I suspect that your cell is formatted in "default date format" mode,
>> rather than explicitly being set to the UK date format
>>
>> (If you open the file on a machine set to a US locale, I strongly suspect
>> you'll see the US format there too)
>
> Yep I'm sure you're correct. I think the trouble comes from POI assuming 
> that all locales share the US format and telling POI not to 
> (DataFormatter(Locale)) doesn't help.

Not quite. The problem is more that Excel will magically ignore certain 
formatting strings in the file, and instead use different ones for display 
to what gets written out. POI is giving you what your file specified, 
which isn't always what you get to see...

The Locale is used by Java once we have a formatting string, to control 
how the formatting string is used. It doesn't affect which formatting 
string to use, that comes from the file

>> Otherwise, in POI, just override the date format to be UK style before
>> formatting.
>
> Do you mean, if the cell is date formatted, get a Java Date using POI's 
> methods, then format it using SimpleDateFormat? The problem with that is 
> different date formats are used in different circumstances and I don't 
> want to override that where the spreadsheet has specifically chosen a 
> format (e.g. mmm-yy).

You'd need to have a lookup from what the file specifies its format string 
to be (eg mmm-dd-yyyy) to what you want displayed to your user (eg 
dd-mmm-yyyy).

> Or do you mean I can override POI's BuiltinFormats class somehow? I.e. 
> replace Excel style 14 to be dd/mm/yyyy rather than m/dd/yy. That would 
> be ideal.

That might work for you, but it isn't something we could change globally 
for everyone (tempting as it may be to try to get the Americans using our 
style of dates...)

What might be good is if someone could try changing their copy of excel 
into all the different locales, and report back what the different format 
string change into. We could then provide that lookup list for everyone. 
It'd probably be quite a bit of work to do though...

Nick

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org