LinqToExcel - Reading a decimal from an *.xlsx file

Question

I've been fiddling around with this for a couple of hours now and i'm kind of walking in circles. I'm missing something.

I'm trying to read an excel sheet with Linq to Excel. Everything works in order, except for decimals in the sheet. I've tried a couple of different approaches, none of which seem to work.

The column I'm trying to read is a currency column. My model is decimal. When I leave the column in excel on the Currency format, I get 0's from the linq query.

When I set the colum to number, I get values with no decimal seperator.

The testfile I created has a sheet called DecimalSheet. The column is called DecimalTest and contains a number; 4587,61

I've got a testmodel like so;

public class DecimalModel
{
    [ExcelColumn("DecimalTest")]
    public decimal DecimalTest { get; set; }
}

Querying the file is implemented like this:

var testFile = new ExcelQueryFactory(fileLocation)
{
    DatabaseEngine = LinqToExcel.Domain.DatabaseEngine.Ace,
    TrimSpaces = LinqToExcel.Query.TrimSpacesType.Both,
    UsePersistentConnection = false, 
    ReadOnly = true
};

var decimalTestModel = from tb in testFile.Worksheet<DecimalModel>("DecimalSheet")
                       select tb;

var lines = decimalTestModel.ToList();

The readout is like this: 45876100000000000M

EDIT 1

In the meantime I've tried:

1. Reading the values as string. This presented me with the scientific notation of the value in the cell, no matter what kind of cell format i used. It reads "4,58761e+016" instead of 4587,61.
2. Setting the complete worksheet to text format and handling it as string as above. It reads "4,58761e+016" instead of "4587,61"
3. Reading the values as `Float. This made no difference.
4. I tried to work with culture settings as in setting the cultre to nl-NL hardcoded in the thread just before creating the factory and setting it back to the original culture after i'm done. This made no difference.

I noticed:

1. When the Excel document is kept open, the readouts with the above code are perfect. I don't want Excel kept open because of batch processing of Excel documents (on the server, no user interaction).
2. When I run the above testcode on a different server (Dutch Windows, Dutch Excel, Dutch Locale nl-NL) it works fine. It's on the English server 'en-US' locale that it doesn't work. Makes me think this has something todo with the localisation or culture?

Can anyone point me in the right direction how I can configure excel/change the code so that it reads the decimal correct?

Answer 1

After trying a couple of other options I also went to the github projectpage for this nuGet package and posted a question there as well (https://github.com/paulyoder/LinqToExcel/issues/143).

The solution was quite simple; change my locale in the windows date settings. Which makes sense for me since all the documents are in Dutch. Only windows is in English.

This is done via Control Panel -> Region