EPPlus number format

Question

I have an Excel sheet generated with Epplus, I am experiencing some pain points and I wish to be directed by someone who have solved a similar challenge.

I need to apply number formatting to a double value and I want to present it in Excel like this.

• 8 → 8.0
• 12 → 12.0
• 14.54 → 14.5
• 0 → 0.0

Here is my code

ws.Cells[row, col].Style.Numberformat.Format = "##0.0";

The final Excel file always append E+0 to the end of this format and therefore presents the final values like this instead.

• 8 → 8.0E+0
• 12 → 12.0E+0
• 14.54 → 14.5E+0
• 0 → 000.0E+0

When I check in the format cells of the generated Excel sheet, I see that my format appears as ##0.0E+2 instead of ##0.0 that I applied.

What may be wrong?

Furthermost

##0.0 is for Currency format. "0.00" is for Number format – Aconite

Answer 1

Here are some number format options for EPPlus:

//integer (not really needed unless you need to round numbers, Excel will use default cell properties)
ws.Cells["A1:A25"].Style.Numberformat.Format = "0";

//integer without displaying the number 0 in the cell
ws.Cells["A1:A25"].Style.Numberformat.Format = "#";

//number with 1 decimal place
ws.Cells["A1:A25"].Style.Numberformat.Format = "0.0";

//number with 2 decimal places
ws.Cells["A1:A25"].Style.Numberformat.Format = "0.00";

//number with 2 decimal places and thousand separator
ws.Cells["A1:A25"].Style.Numberformat.Format = "#,##0.00";

//number with 2 decimal places and thousand separator and money symbol
ws.Cells["A1:A25"].Style.Numberformat.Format = "€#,##0.00";

//percentage (1 = 100%, 0.01 = 1%)
ws.Cells["A1:A25"].Style.Numberformat.Format = "0%";

//accounting number format
ws.Cells["A1:A25"].Style.Numberformat.Format = "_-$* #,##0.00_-;-$* #,##0.00_-;_-$* \"-\"??_-;_-@_-";

Don't change the decimal and thousand separators to your own localization. Excel will do that for you.

By request some DateTime formatting options.

//default DateTime pattern
worksheet.Cells["A1:A25"].Style.Numberformat.Format = DateTimeFormatInfo.CurrentInfo.ShortDatePattern;

//custom DateTime pattern
worksheet.Cells["A1:A25"].Style.Numberformat.Format = "dd-MM-yyyy HH:mm";

Tuttifrutti

Is there any similar reference for Date Time formats. I am trying to read a file that has the format specified as dd-mm-yy h:mm but in debug I see the format as : "[$-10409]m/d/yyyy\\ h:mm:ss\\ AM/PM" – Botanomancy

Added some examples, but basically it works the same as numbers. – Tuttifrutti

Thanks for that. What if I'd like to use the number format not with a currency symbol but for example with "USD" or "CHF"? – Centrepiece

Nvm, found it. Instead of just using a symbol, one has to escape multiple characters. For CHF it could look like this: _ \"CHF\" * # ##0.00_ ;_ \"CHF\" * -# ##0.00_ ;_ \"CHF\" * \"-\"??_ ;_ @_ – Centrepiece

Don't change the decimal and thousand separators to your own localization. Excel will do that for you. That! Attempting to "fix" decimals is a huge source of bugs and broken formulas! – Elbrus

If you want to format your cells as text, set Style.Numberformat.Format = "@"; – Linkboy

Can setting this format of Excel : "[>=99]0;[>=0.95]0.0;0.00"? – Interregnum

Answer 2

Addition to Accepted Answer, because value Accept Object you must pass Number to Value For Example if your input is in string :

var input = "5";    
ws.Cells["A1:A25"].Value = double.Parse(input);

Advice

Answer 3

Another addition to the accepted answer: you can use nullable values and the formatting all looks good BUT it ends up being a string in Excel and you can't SUM, AVG etc.

So make sure you use the actual Value of the nullable.

Denudate

what mean by value nullable? – Hausa

say you have Decimal? myDecimal = 0.4M; then to get a proper decimal into Excel (not just a string) you should do ws.Cells["A5"].Value = myDecimal.Value; so you send a non-nullable decimal to Excel by adding that ".Value" – Denudate

Answer 4

I solved it as follows, so I just load the model and change as per my model if it is int ordatetime

var li = typeof(Model)
              .GetProperties()
              .ToArray();


 using (var package = new ExcelPackage(stream))
            {
                var workSheet = package.Workbook.Worksheets.Add("Sheet1");

var i = 0;
                foreach (var c in li)
                {
                    i++;
                    if(c.PropertyType.Name == typeof(DateTime).Name || c.PropertyType.Name == typeof(DateTime?).Name)
                        workSheet.Column(i).Style.Numberformat.Format = DateTimeFormatInfo.CurrentInfo.ShortDatePattern; ;

                    if (c.PropertyType.Name == typeof(int).Name || c.PropertyType.Name == typeof(int?).Name)
                        workSheet.Column(i).Style.Numberformat.Format = "0";                    
                }

}

Behre

Answer 5

And if you want to format a specific column like column "B" to number format you can do it this way-

using (var package = new ExcelPackage())
{
  var worksheet = package.Workbook.Worksheets.Add("SHEET1");
  worksheet.Cells["A1"].LoadFromDataTable(dataTable, PrintHeaders: true);
  for (var col = 1; col < dataTable.Columns.Count + 1; col++)
  {
    if (col == 2)//col number 2 is equivalent to column B
    {
      worksheet.Column(col).Style.Numberformat.Format = "#";//apply the number formatting you need
    }
    worksheet.Column(col).AutoFit();
  }
  return File(package.GetAsByteArray(), XlsxContentType, "report.xlsx");//downloads file
}

Grilled