ClosedXML parsing comma in number

Question

I just downloaded ClosedXML for use in a small app I'm writing. In the app, I load in some data from a database to output to an Excel file. My local number format uses comma for a decimal indicator. It looks like ClosedXML doesn't recognize these as numbers, with the result that they get saved as text and I have to manually convert them when I open the file.

I've tried setting the DataType of the cell to XLCellValues.Number, but when I do, an exception is thrown, like "Cannot set data type to Number because '1534,8250' is not recognized as a number." It appears that this comes from ClosedXML's use of CultureInfo.InvariantCulture, rather than CultureInfo.CurrentCulture.

Is there any way to get around this, short of replacing all commas with periods and/or recompiling the ClosedXML project to use CurrentCulture?

And what is the issue with replacing the commas with periods? That seems like the logical fix to me.. — Ben
Just seems like it ought to be unnecessary. It works, of course, but I'd rather be able to just set the formatting. — schneiju

toadflakz toadflakz · Accepted Answer · 2017-01-30T09:18:15

You should always be storing data in a Culture-inspecific format. Culture should be used for presenting data only.

This means, the correct solution is that when saving to the XML you should not be using the formatted strings for the number. Excel should do the presentation of the number for the specific culture.

ClosedXML parsing comma in number

1 Answers