I'm creating some Excel files using EPPlus within ASP.Net. All is working well, except that in some cases, I need to show a short date format. Using the EPPlus
ws.Column(1).Style.Numberformat.Format = "yyyy-MM-dd";
String s = System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat.ShortDatePattern;
ws.Column(1).Style.Numberformat.Format = System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat.ShortDatePattern;
I've spent two days testing on this. It seems that the
DateTimeFormat.ShortDatePattern should not be used. Instead, regardless of locale/language settings on the client browser, use this approach (ISO8601) for short dates.
ws.Column(1).Style.Numberformat.Format = "yyyy-MM-dd"
This is because the browser is not guaranteed to match the computer's locale (Firefox and Chrome can very easily be modified, and Chrome by default installs as en-US), and Excel uses the computer's locale.
MS Excel is smart enough to read the date values and convert them into the correct format based on the Windows Language settings. So, in my case, if my Windows language is English UK, I see dates in en-GB format within Excel automatically.
If I then close the spreadsheet, switch Windows to Norwegian, log back in and reopen the same spreadsheet, the dates automatically display in the respective Norwegian format.