CSV with EPPlus parsing issues

Question

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 1

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);
}