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附加到此格式的末尾，因此显示最终值$ / b>

• 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.NumberformatFormat =＃; 
 
 //具有1个小数位的数字
 ws.Cells [A1：A25]。Style.Numberformat.Format =0.0; 
 
 //具有2个小数位的数字
 ws.Cells [A1：A25]。Style.Numberformat.Format =0.00; 
 
 //具有两个小数位和千位分隔符的数字
 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％; 
  

不要将十进制和千位分隔符更改为您自己的
本地化。 Excel将为您做这件事。

请求一些DateTime格式化选项。

  //默认DateTime模式
 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; 
  

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 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.

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屋！