CODEWITHSUNDEEP
c#exceltype-conversion
MehdiB
MehdiB

Reputation: 906

C# changing variable type dynamically while saving in excel file

I use an excel library to convert Dictionary<string, List<string>> to an excel file. the values in the dictionary are saved as strings, but they can be actually doubles or strings. suppose myDic is a sample Dictionary. this is for example myDic[0]:

[0] "\"Nm\""string
[1] "1,0"   string
[2] "2,0"   string
[3] "3,2"   string
[4] "0,0"   string
[5] "0,0"   string
[6] "0,0"   string
[7] "0,0"   string
[8] "0,0"   string
[9] "0,0"   string
[10]    "0,0"   string

While saving this to excel I want to convert the values to numbers if they are actually numbers. I used this method but seems to be awfully wrong:

try
{
    row.CreateCell(j).SetCellValue(Double.Parse(cellValue));
}
catch (Exception e)
{
    row.CreateCell(j).SetCellValue(cellValue);
}

This is extremely slow, as it throws exceptions on most of the cells. Additionally the double values are set wrong, which I think is because of comma in my numbers (german numbers) for example 1,0 is saved as 10.

So there seems to be 2 problems. first: how to convert the type of cell values dynamically and correctly. second: how to save numbers with comma correctly.

Could somebody please help.

Upvotes: 1

Views: 225

Answers (1)

MakePeaceGreatAgain
MakePeaceGreatAgain

Reputation: 37000

To test if a string can be converted to double, use Double.TryParse:

double d;
if(Double.TryParse(cellValue, out d))
    row.CreateCell(j).SetCellValue(d);
else
    row.CreateCell(j).SetCellValue(cellValue);

This way you don´t get an exception when parsing fails. Instead TryParse just returns false making you jump into the else-branch.

To handle a comma and point appropriately you may use the overload for TryParse that is aware on the culture:

Double.TryParse(cellValue, NumberStyles.Any, new CultureInfo("de-DE"), out d)

Upvotes: 2

Related Questions