Always show two decimal points in excel cells using Apache poi

15
votes

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.4 in the specified cell. It should be 12.40. The value 12 is displayed as 12. which is quite unnecessary.

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.

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

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.

2
javaexcelapache-poiexcel-2007
Did you try this stackoverflow.com/questions/9789627/…? – Abhiroop Sarkar
Yes that behaves exactly the same as mentioned in the question. – Tiny
What cell style do you need to use if you're formatting the cell in Excel, to get it to look how you want it to? – Gagravarr
Edited the question. @Gagravarr – Tiny

2 Answers

45
votes

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

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

1
votes

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