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.

It seems that double.Parse throws a FormatException. Try this code: double.Parse(cellValue, System.Globalization.CultureInfo.InvariantCulture) — Andriy Shevchenko
– Andriy Shevchenko, Commented Aug 4, 2017 at 12:52

MakePeaceGreatAgain · Accepted Answer · 2017-08-04 13:04:00Z

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 4, 2017 at 13:04

answered Aug 4, 2017 at 12:52

MakePeaceGreatAgain

37.4k6 gold badges67 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ned Howley Over a year ago

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.

Collectives™ on Stack Overflow

C# changing variable type dynamically while saving in excel file

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related