Tonto Tonto - 1 month ago 57
C# Question

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?

Answer

Here are some number format options for EPPlus:

//integer (not really needed unless you need to round numbers, Excel with 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%";

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

Comments