始终使用 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 表示始终在此处放置一个数字，即使它是不必要的 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 显示为 12.40.

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

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