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?

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