How can I insert a double as a number in Excel?

Question

Trying the following:

          string cellDataStr = row[col.ColumnName].ToString();
          double cellDataD = 0;

          Cell cell = new Cell();

          if (double.TryParse(cellDataStr.Replace(',','.'), NumberStyles.Any, new NumberFormatInfo(), out cellDataD))
          {
            // data was parsed as double;
            cell.DataType = new EnumValue<CellValues>(CellValues.Number);
            cell.CellValue = new CellValue(cellDataStr);
          }
          else
          {
            // data was not parsed as double
            cell.DataType = CellValues.String;
            cell.CellValue = new CellValue(cellDataStr);
          }

Where cellDataStr could be 353126,00. The comma is decimal seperator.

When I open this generated file Excel states that "We fould a problem with some content, do you want us to try to recover as much as we can?".

All whole numbers like 1, 3254 and 8 are fine. But every number with decimal, like 15592,52, 0,0600000, -15000,00 all have this green arrow in the cell telling me that the number in the cell is stored as text or proceeded with an apostrophe.

How do I insert the double value corretly into the cell?

Answer 1

Store the double as Text and then use:

range.NumberFormat = "0.0"; //as per your requirement

Answer 2

Probably the same reason you're doing a string replace to change the decimal separator. The resulting spreadsheet is marked with the thread's current culture which clearly doesn't match your data.

You're writing the number 353126,00 into the sheet but it can't be parsed using the current culture so it's not a number. To fix it (The content problem) Excel is changing the cell type to text. Now that Excel has opened the file the view is culture specific and it can see that it's a number so it prompts you effectively to undo the change it just made. Except that it will store it using the current culture so to fix...

change:

        cell.CellValue = new CellValue(cellDataStr);

to

        cell.CellValue = new CellValue(cellDataD.ToString());