总是使用Apache poi在excel单元格中显示两个小数点 [英] Always show two decimal points in excel cells using Apache poi

问题描述

例如，

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

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

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

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

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

If the value is 0 then, 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.

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

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

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

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

我应用了一些excel公式来对数字单元执行一些计算，预计在应用之后执行

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格式中，一个＃表示仅在需要时才能在此处放置数位，但是 0 意味着永远在这里放置数字，即使这是不必要的。您可以在Apache POI 中指定数据格式 就像在Excel本身一样。如果您想要显示 0 数位，则需要使用 0 作为格式化数字。尝试

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

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

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

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

这篇关于总是使用Apache poi在excel单元格中显示两个小数点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！