In Apache POI HSSF, Cell Type Still Shows Up as "General" Excel, Even Though it's Number-Formatted

Question

I am using Apache POI HSSF to generate an Excel spreadsheet from my Java Web app.

I need a cell formatted as "Number" with 2 decimal points. (My values in Java are BigDecimals, but I can convert them to doubles, no problem.) I am using this code:

CellStyle numericStyle = wb.createCellStyle();
numericStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));

// output in this new style...
row.createCell(cellnum).setCellValue(((BigDecimal)value).doubleValue());
row.getCell(cellnum).setCellStyle(numericStyle);

The issue is that, even though this works, Excel still shows my cells as General. They need to be shown as Number. As a result, for example, 0 is shown as 0, but it should be 0.00, which would happen if the format was correct (Number).

I see that it gets generated as General because I can right-click on a cell and then choose "Format Cells" to see what it is right now. It needs to be set to "Number" by Apache POI HSSF.

Answer 1

Reviewing the Apache POI API it looks like you should be able to specify the cell's type.

...
Cell cell = row.createCell(...);
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
...

Answer 2

I'm assuming you haven't yet found an answer for your question.

One way to display numbers with two decimals is to set a cell style. This allows you to customize the number of decimal places and the decimal separator, among other things.

protected CellStyle getCellStyle(HSSFWorkbook workbook) {
    CellStyle cellStyle = workbook.createCellStyle();
    cellStyle.setDataFormat(
        workbook.getCreationHelper().createDataFormat().getFormat("#,##0.00"));
        return cellStyle;
}

Later on, when you fill your workbook, you apply this cellStyle to the desired cells:

CellStyle cellStyle = getCellStyle();
//...
// create your cell
cell.setCellStyle(cellStyle);
// set the value

It won't show as numeric type in Excel. It shows as Custom type. But visually it should be the same, or at least close enough.