ClosedXML cannot set cell data tyep to Number format with a long - v0.95.4.0

Question

Hi all for my first question!

I am using ClosedXML (great solution to go to Excel) and everything seems to work as prescribed with the exception of making/formatting a cell to a Number. At first I though it might be something with the value (it's a long due to the size of the number) so I added code to test if it's a number or not, the validation portion works fine. The code gens no errors, though when I look at the spreadsheet after, the cell is still set to General and not Number??
So I end up with (Type General in Excel) "1.04804E+11" in the spreadsheet.

Here is the code to help in the error of my ways :-)

var stringNumber = dataGridDrivePerf.Rows[r].Cells[i].Value;
long numericValue;
if (long.TryParse((string)stringNumber, out numericValue))
{
    ws.Cell(r + 2, i + 1).SetValue(numericValue);
    ws.Cell(r + 2, i + 1).SetDataType(XLDataType.Number);
}
else
{
    ws.Cell(r + 2, i + 1).Value = dataGridDrivePerf.Rows[r].Cells[i].Value;
}  

Answer 1

Welcome to SO.

ClosedXML should set the data type correctly by itself. But that is in terms of Excel not what you intended to achieve.

You want to format the value as number, the dropdown in Excel therefore even is called number format. In code this is done by modifying the cell style.

The following should work for you:

var stringNumber = dataGridDrivePerf.Rows[r].Cells[i].Value;
long numericValue;
if (long.TryParse((string)stringNumber, out numericValue))
{
    ws.Cell(r + 2, i + 1).SetValue(numericValue);
    ws.Cell(r + 2, i + 1).Style.NumberFormat.Format = "#,##0;[Red]-#,##0"; # <-- this line to format
}
else
{
    ws.Cell(r + 2, i + 1).Value = dataGridDrivePerf.Rows[r].Cells[i].Value;
}

Edit: fixed typos