Search code examples
asp.net.netexcelexcel-2013epplus

Is Excel/EPPlus number format compatible with DateTimeFormat.ShortDatePattern?

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 NumberFormat, I can apply a short date format:

ws.Column(1).Style.Numberformat.Format = "yyyy-MM-dd";

That outputs a date in my spreadsheets as 2015-07-21. Although this is universal, I would prefer a localised (user locale-based) format if possible. I can retrieve the user locale's short date format with this:

String s = System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat.ShortDatePattern;

For me in the UK, this returns dd/MM/yyyy. Other authors using EPPlus refer to the the Open Office XML ECMA-376 spec (50MB) for applying number formats. Page 1768 shows this:

enter image description here

But the m's here are all lower-case, which in .Net is not months but minutes, so is something wrong/non-compatible if I do this? 

ws.Column(1).Style.Numberformat.Format = System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat.ShortDatePattern;

I haven't the resources to test for every culture's date format, so am wondering whether anything is wrong with my approach?

Or, should I cast the ShortDatePattern ToLower to disregard the upper-case MM as per the spec? Excel (2013) itself uses lower case for custom dates formats, so I assume the lower-case approach is better? I've done tests with both formats (using upper/lower case mm) and the result is the same, but the cross-culture thing concerns me.

Solution

  • 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.