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