C# changing variable type dynamically while saving in excel file

Question

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.

[Faster](https://stackoverflow.com/a/8458496/1997232) is to use `TryParse`. — Sinatr, Aug 04 '17 at 12:51
It seems that double.Parse throws a `FormatException`. Try this code: `double.Parse(cellValue, System.Globalization.CultureInfo.InvariantCulture)` — Andriy Shevchenko, Aug 04 '17 at 12:52

MakePeaceGreatAgain · Accepted Answer · 2017-08-04T13:04:00.867

2

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)

edited Aug 04 '17 at 13:04

answered Aug 04 '17 at 12:52

MakePeaceGreatAgain

35,491
6
60
111

Your `CultureInfo` is incorrect, it will ignore the comma instead of treating it as a decimal point. You could use a different `CultureInfo` which does understand the comma, for example `new CultureInfo("de-DE")` since the OP mentions German numbers. – Ned Howley Aug 04 '17 at 13:02

C# changing variable type dynamically while saving in excel file

1 Answers1