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