EPPlus 数字格式 [英] EPPlus number format

问题描述

我有一个用 Epplus 生成的 Excel 表格，我遇到了一些痛点，我希望得到解决类似挑战的人的指导.

我需要将数字格式应用于双精度值，我想像这样在 Excel 中显示它.

• 8 → 8.0
• 12 → 12.0
• 14.54 → 14.5
• 0 → 0.0

这是我的代码

ws.Cells[row, col].Style.Numberformat.Format = "##0.0";

最终的 Excel 文件总是将 E+0 附加到此格式的末尾，因此会显示这样的最终值.

• 8 → 8.0E+0
• 12 → 12.0E+0
• 14.54 → 14.5E+0
• 0 → 000.0E+0

当我检查生成的 Excel 工作表的格式单元格时，我看到我的格式显示为 ##0.0E+2 而不是 ##0.0已申请.

可能有什么问题?

解决方案

以下是 EPPlus 的一些数字格式选项:

//整数(不是真的需要，除非你需要四舍五入，Excel会使用默认的单元格属性)ws.Cells["A1:A25"].Style.Numberformat.Format = "0";//在单元格中不显示数字0的整数ws.Cells["A1:A25"].Style.Numberformat.Format = "#";//带1位小数的数字ws.Cells["A1:A25"].Style.Numberformat.Format = "0.0";//保留2位小数的数字ws.Cells["A1:A25"].Style.Numberformat.Format = "0.00";//带2位小数和千位分隔符的数字ws.Cells["A1:A25"].Style.Numberformat.Format = "#,##0.00";//带有2个小数位和千位分隔符和货币符号的数字ws.Cells["A1:A25"].Style.Numberformat.Format = "€#,##0.00";//百分比 (1 = 100%, 0.01 = 1%)ws.Cells["A1:A25"].Style.Numberformat.Format = "0%";//会计号格式ws.Cells["A1:A25"].Style.Numberformat.Format = "_-$* #,##0.00_-;-$* #,##0.00_-;_-$* "-"??_-;_-@_-";

<块引用>

不要把小数点和千位分隔符改成你自己的本土化.Excel 会为您做到这一点.

通过请求一些 DateTime 格式选项.

//默认日期时间模式worksheet.Cells["A1:A25"].Style.Numberformat.Format = DateTimeFormatInfo.CurrentInfo.ShortDatePattern;//自定义日期时间模式worksheet.Cells["A1:A25"].Style.Numberformat.Format = "dd-MM-yyyy HH:mm";

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?

解决方案

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

这篇关于EPPlus 数字格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！