Java 始终使用 Apache poi 在 excel 单元格中显示两个小数点

Always show two decimal points in excel cells using Apache poi

提问by Tiny

For example,

例如，

XSSFCellStyle style=(XSSFCellStyle) workbook.createCellStyle();
style.setDataFormat(workbook.createDataFormat().getFormat("#.##"));

productCell.setCellValue(12.4);
productCell.setCellType(Cell.CELL_TYPE_NUMERIC);
productCell.setCellStyle(style);

This displays 12.4in the specified cell. It should be 12.40. The value 12is displayed as 12.which is quite unnecessary.

这显示12.4在指定的单元格中。应该是12.40。该值12显示为12.这是非常不必要的。

If the value is 0then, it displays a dot .. It should always display two decimal digits - 0.00, in this case regardless of the value being stored in a cell.

如果值为0then，则显示一个点.。它应始终显示两位十进制数字 - 0.00，在这种情况下，无论存储在单元格中的值如何。

How to force excel to always show two decimal digits in a numeric cell?

如何强制excel始终在数字单元格中显示两位十进制数字？

---

---

I use one of the following styles to display numeric cells.

我使用以下样式之一来显示数字单元格。

XSSFColor commonColor = new XSSFColor(new java.awt.Color(240, 240, 240));
XSSFColor cellBorderColour = new XSSFColor(new java.awt.Color(0, 76, 153));

Font font = workbook.createFont();
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
font.setColor(IndexedColors.DARK_BLUE.index);

XSSFCellStyle style = (XSSFCellStyle) workbook.createCellStyle();
style.setFillForegroundColor(commonColor);
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setAlignment(CellStyle.ALIGN_RIGHT);
style.setFont(font);

style.setBorderLeft(BorderStyle.HAIR);
style.setBorderColor(XSSFCellBorder.BorderSide.LEFT, cellBorderColour);
style.setBorderTop(BorderStyle.HAIR);
style.setBorderColor(XSSFCellBorder.BorderSide.TOP, cellBorderColour);
style.setBorderRight(BorderStyle.HAIR);
style.setBorderColor(XSSFCellBorder.BorderSide.RIGHT, cellBorderColour);
style.setBorderBottom(BorderStyle.DOUBLE);
style.setBottomBorderColor(cellBorderColour);

I applied some excel formulae to perform some calculations on numeric cells that are expected to be performed after applying a number format (rounding half up) on numeric cells.

我应用了一些 excel 公式来对数字单元格执行一些计算，这些计算在对数字单元格应用数字格式（四舍五入）后预计会执行。

采纳答案by rgettman

In Excel formatting, a #means "place a digit here only if needed", but a 0means "always place a digit here, even if it's an unnecessary 0". You can specify the data format in Apache POIexactly as you would in Excel itself. If you want the 0digits to show up, then you need to use 0s as formatting digits. Try

在 Excel 格式中，a#表示“仅在需要时在此处放置数字”，而 a0表示“始终在此处放置数字，即使它是不必要的 0”。您可以指定在Apache的POI数据格式正是因为你在Excel中本身就。如果要显示0数字，则需要使用0s 作为格式化数字。尝试

style.setDataFormat(workbook.createDataFormat().getFormat("0.00"));

This will make the value 0show up as 0.00and 12.4as 12.40.

这将使值0显示为0.00和12.4作为12.40。

回答by Elm

This one worked for me:

这个对我有用：

1. Set Cell Style

   private CellStyle formatDecimalStyle(Workbook workbook, CreationHelper createHelper) {  
       CellStyle style = workbook.createCellStyle();
       style.setDataFormat(createHelper.createDataFormat().getFormat("0.00"));
       return style;   
   }
   

2. Apply Style on Cell

   CellStyle style = formatDecimalStyle(workbook, createHelper);
   Cell creditAmountCell = row.createCell(3);
   creditAmountCell.setCellValue(amount);
   creditAmountCell.setCellStyle(style);
   

1. 设置单元格样式

   private CellStyle formatDecimalStyle(Workbook workbook, CreationHelper createHelper) {  
       CellStyle style = workbook.createCellStyle();
       style.setDataFormat(createHelper.createDataFormat().getFormat("0.00"));
       return style;   
   }
   

2. 在单元格上应用样式

   CellStyle style = formatDecimalStyle(workbook, createHelper);
   Cell creditAmountCell = row.createCell(3);
   creditAmountCell.setCellValue(amount);
   creditAmountCell.setCellStyle(style);