Patrick Aleman Patrick Aleman - 1 month ago 14
C# Question

CSV with EPPlus parsing issues

I am using EPPlus to read data.
This data consists of either a

.xlsx
or
.csv
.

When the file is a
.csv
file I use the
LoadFromText
functionality.
But EPPlus decides that it also has to parse all the values, which it shouldn't.

For instance:

Id;Double;
1;-3,88;

ExcelTextFormat format = new ExcelTextFormat();
format.Delimiter = ';';
worksheet.Cells[1, 1].LoadFromText( "file", format );


Result is that the
-3,88
value becomes:
-388
in the worksheet. This i found out is because EPPlus sees the -3,88 as a number and parses it as a number with the default culture being
InvariantCulture
which in this case is (similar to)
us-US
.

How can i achieve that EPPlus loads the csv without parsing? (takes all values as strings)

Answer

It seems that EPPlus always parses imported data with the en-US format. So first import the column with the decimal values as a string. That way there is no attempt at conversion.

ExcelTextFormat format = new ExcelTextFormat
{
    Delimiter = ';',
    DataTypes = new eDataTypes[] { eDataTypes.Number, eDataTypes.String }
};

And after the values are imported with the correct decimal separators for your localization, loop the values, convert them to decimal and set the correct number format.

int columnNumber = 2;

for (int i = 2; i <= rowCount; i++)
{
    worksheet.Cells[i, columnNumber].Style.Numberformat.Format = "0.00";
    worksheet.Cells[i, columnNumber].Value = Convert.ToDecimal(worksheet.Cells[i, columnNumber].Value);
}