In Apache POI HSSF, the cell type is still displayed as "generic" Excel, even if it is number-formatted

Question

I am using Apache POI HSSF to create an Excel spreadsheet from my Java web application.

I need a cell formatted as "Number" with two decimal points. (My values ​​in Java are BigDecimals, but I can convert them to doubles, no problem.) I use 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 problem is that although this works, Excel still shows my cells as General . They should appear as a Number . As a result, for example, 0 is displayed as 0, but should be 0.00, which will happen if the format was correct ( Number ).

I see that it is generated as General , because I can right-click on a cell and then select "Format Cells" to see what it is now.

Thanks for any help. Apache POI HSSF must be set to "Number". Any advice is appreciated.

Answer 1

Considering the Apache POI API it looks like you should specify the type of cell.

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

Answer 2

I assume that you have not yet found the answer to your question.

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

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

Later, when you fill out your book, you apply this cellStyle to the desired cells:

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

It will not display as a numeric type in Excel. It is displayed as custom. But visually it should be the same, or at least close enough.