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.

Are you sure that's the correct format string for the built in format? — Gagravarr, Oct 20 '11 at 16:30
It is correct according to this listing: http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/BuiltinFormats.html But if you have any other format strings in mind, please share. — gene b., Oct 20 '11 at 16:53

score 0 · Answer 1 · answered Aug 07 '13 at 15:37

0

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

answered Aug 07 '13 at 15:37

axiopisty

4,972
8
44
73

score 0 · Answer 2 · answered Oct 19 '16 at 14:01

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.

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

2 Answers2