Using EPPlus how can I generate a spreadsheet where numbers are numbers not text

Question

I am creating a spreadsheet from a List<object[]> using LoadFromArrays

The first entry of the array is a title, the other entries are possibly numbers, text or dates (but the same for each array in the list).

The generated Excel sheet has the green triangle warning that numbers are formatted as text.

I loop through all the cells and set their format to Number like so ws.Cells[i, j].Style.Numberformat.Format = "0";

However the problem remains and I still see the green warning, even though the number format is set to number when I look in the Format Cell... dialogue.

What are my options here? It is possible for me to know a bit more about what type is in each column, but how do I then set a column title?

Is there a better solution than EPPlus? or some more post processing of the spreadsheet I can do before downloading it?

Answer 1

The trick is to not pass the numbers as "raw objects" to EPPlus but casting them properly.

Here's how I did that in a DataTable-to-Excel export method I made with EPPlus:

if (dc.DataType == typeof(int)) ws.SetValue(row, col, !r.IsNull(dc) ? (int)r[dc] : (int?)null);
else if (dc.DataType == typeof(decimal)) ws.SetValue(row, col, !r.IsNull(dc) ? (decimal)r[dc] : (decimal?)null);
else if (dc.DataType == typeof(double)) ws.SetValue(row, col, !r.IsNull(dc) ? (double)r[dc] : (double?)null);
else if (dc.DataType == typeof(float)) ws.SetValue(row, col, !r.IsNull(dc) ? (float)r[dc] : (float?)null);
else if (dc.DataType == typeof(string)) ws.SetValue(row, col, !r.IsNull(dc) ? (string)r[dc] : null);
else if (dc.DataType == typeof(DateTime))
{
    if (!r.IsNull(dc))
    {
        ws.SetValue(row, col, (DateTime)r[dc]);
        // Change the following line if you need a different DateTime format
        var dtFormat = "dd/MM/yyyy";
        ws.Cells[row, col].Style.Numberformat.Format = dtFormat;
    }
    else ws.SetValue(row, col, null);
}

IMPORTANT: It's worth noting that DateTime values will require more work to be handled properly, since we would want to have it formatted in a certain way AND, arguably, support NULL values in the column: the above method fullfills both these requirements.

I published the full code sample (DataTable to Excel file with EPPlus) in this post on my blog.