Decimal numbers in cultures with comma as decimal separator are written as dates by ExcelWriter

[Rebel028]

Decimal numbers turn into dates if current culture uses comma (,) as decimal separator.

The reason is ClosedXML and the fact that every type is converted to string before writing it to excel (I suppose it's limitation of IParser interface)

Problem in ClosedXML described here: https://github.com/ClosedXML/ClosedXML/issues/1713

To Reproduce Create a sample class with decimal property:

public class TestClass
{
    public decimal Value { get; set; }
}

Now assign the value to something that fits the criteria described in https://github.com/ClosedXML/ClosedXML/issues/1713 and write it to a file

public void Main()
{
    IEnumerable<TestClass> records = new[]
    {
        new TestClass() { Value = 1999.2m },
    };

    using ExcelWriter writer = new ExcelWriter("bug.xlsx", CultureInfo.CurrentCulture);
    writer.WriteRecords(records);
}

As a result I get this:

(result may depend on your system regional settings)

Expected behavior Decimals are written as decimals in Excel not dates 🙃

Versions (please complete the following information):

• CSVHelper: 22.1.1
• CSVHelper.Excel.Core: 22.1.1

Possible solution Tests I made showed me that if string with dot (.) is used (i.e. "1999.3") it is turned into a correct value in excel (1999,3).

Is there anything I can define to make ExcelWriter parse decimals differently? (UPD. yes )

[Rebel028]

As a temporary solution for people like me I can offer this:

writer.Context.TypeConverterOptionsCache.AddOptions<decimal>(
new TypeConverterOptions
{
    CultureInfo = CultureInfo.InvariantCulture
});

before calling WriteRecords(). This will make string representation of decimal look like this "1999.12" before it is sent to a cell value so in the end we get correct "1999,12" instead of date.

[youngcm2]

@Rebel028 Can you see if the update fixed your issue?

Released

• Releases: 27.2.1
• Nuget: 27.2.1

[Rebel028]

@youngcm2 sorry for the late reply, unfortunately it is not.

You still have to add TypeConverterOptions for decimal manually. Anyway, thanks for the update!