ClosedXML Worksheet from DataTable isn't following Culture

Question

When I add a worksheet via a DataTable, I would expect the date formats to follow the locale/culture of the application but it appears to take that from the computer.

string culture = "en-GB";
var newCulture = new CultureInfo(culture);
Thread.CurrentThread.CurrentCulture = newCulture;
Thread.CurrentThread.CurrentUICulture = newCulture; //probably unnecessary?
CultureInfo.DefaultThreadCurrentCulture = newCulture;

// ... select myDateColumn, myIntCol, myString
workBook.AddWorksheet(myDataTableThatContainsQueryResults, tabName);

Now, you'd expect the first column (myDateColumn) to format using the short date format of the en-GB locale which should be '31/10/2017' but it's coming out as '10/31/2017' instead.

I checked the locale in the DataTable once I get it back from the SQLDataAdapter and it's correct as are the nested date formats. Note that there are multiple queries and I won't know where the dates are in advance.

Appreciate any direction someone can provide!

Apollinaire

Answer 1

I don't have the reputation to comment...

By looking at a few tests on the ClosedXML github repo, it appears that the library expects the culture to be set before instantiating the workbook. (https://github.com/ClosedXML/ClosedXML/blob/c2d408b127844ea3d4a5f6b060c548c953b6bcf3/ClosedXML_Tests/Excel/CalcEngine/LookupTests.cs#L17)

    private XLWorkbook workbook;

    [OneTimeSetUp]
    public void Init()
    {
        // Make sure tests run on a deterministic culture
        System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
        workbook = SetupWorkbook();
    }

It is unclear to me from your question if that is the case in your code.

If that is already the case, you should open a bug @ ClosedXML on Github.

Oldster

Are you trying to use multiple cultures per workbook based on the SQLAdapter results? Don't know if ClosedXml would support that... (would need more code digging) – Dialectologist

No, I'm using a single culture for the whole workbook. It's being set before we instantiate the new workbook. – Apollinaire

@ArisBerard Did you manage to find a workaround for your issue? – Dialectologist