Tonto - 1 month ago 57

C# 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

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.

Source (Stackoverflow)

Comments