EPPlus number format

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?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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_-;_-$* "-"??_-;<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="5b04761b">[email protected]</a>_-";

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";

Method 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);

Method 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.

Method 4

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
}


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x