none
How to store a number as a number? RRS feed

  • Question

  • I'm writing a small application that fetches data (text and numbers) from a database and output this in a Excel document for presentation. Inserting text as text works fine, but I'm having problems storing numbers as numbers in Excel.

    The number seems to still be formatted as text. The cell in Excel gets this little green arrow stating "The number in this cell is formatted as text or preceded by an apostrophe.". To determine if the value from the database is a number i try to parse it to a double:       

              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(cellDataD.ToString());
              }
              else
              {
                // data was not parsed as double
                cell.DataType = new EnumValue<CellValues>(CellValues.String);
                cell.CellValue = new CellValue(cellDataStr);
              }

    The replacement of ',' and '.' is because ',' is the separator of decimals in my country. 1000 = 1000,00, but double is using '.' so in order to parse it correctly i just replace the character.


    Tuesday, November 3, 2015 10:01 AM

Answers

  • Hi Lars,

    What the number format in your excel? Is it ‘,’ or ‘.’? Based on your description, ‘,’ is the separator of decimals in your country. If your number in excel is “1000.00”, it was string instead of number. What is the number you want? If you set File-Options- Advanced-Decimal separator as ‘,’, and with the code below, you would get “1000,15”.

                Cell cell = new Cell()
                {
                    CellReference = "A1",
                    DataType = new EnumValue<CellValues>(CellValues.Number),
                    CellValue = new CellValue("1000.15")                
                };

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, November 4, 2015 9:41 AM