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