EPPLus Excel is swapping day with the month in datetime column

Question

I have website, where user can download excel generated via EPPLus extension.

using (ExcelPackage pck = new ExcelPackage())
{
      ExcelWorksheet ws = pck.Workbook.Worksheets.Add("dates");

      // Create table from dataTable with header
      ws.Cells["A1"].LoadFromCollection(dates, true, TableStyles.Medium6);

      string dateformat = System.Globalization.DateTimeFormatInfo.CurrentInfo.ShortDatePattern;
      ws.Column(2).Style.Numberformat.Format = dateformat;

      // Autofit Columns               
      ws.Cells[ws.Dimension.Address].AutoFitColumns();

      // Send to browser
      fileBytes = pck.GetAsByteArray();
}
return File(fileBytes, "application/vnd.ms-excel", "dates.xlsx");

Download works fine. Downloaded excel has two columns, the second column is a datetime and I am formatting it as a ShortDate

string dateformat = System.Globalization.DateTimeFormatInfo.CurrentInfo.ShortDatePattern;
ws.Column(2).Style.Numberformat.Format = dateformat;

When I open the excel, for United States Region it works fine (datetime value match short date in region settings)

But when I change region to Czech

The dates in excel does not match the region settings for short date, because day and month is swapped. Why this is happening and how can I solve this issue ? What am I missing ?

Answer 1

In your Controller you can capture the current users language settings from the Request Object, like so:

string language= Request.UserLanguages.FirstOrDefault() ?? "en-US";

If, for whatever reason, the collection is empty, you set a fallback language, here it is US English. You then pass this string to your Excel function, and the first things you do in that function, is to create a new CultureInfo object with the users language and change it in the current thread:

CultureInfo currentCulture = new CultureInfo(language, false);
Thread.CurrentThread.CurrentCulture = currentCulture;

You can now style the cells of your choice, with the correct format by using the DateTimeFormatInfo.CurrentInfo type:

// Load collection into excel AND FORMAT THE CELLS
workSheet.Cells["A1"].LoadFromCollection(dates, true, TableStyles.Medium6).Style.Numberformat.Format = DateTimeFormatInfo.CurrentInfo.ShortDatePattern;