Apache POI and Numeric Cell Types - Excel alerts of problem-CodePudding

I am finding numberic cells created by Apache POI is showing problems when I open the workbook in excel.

Here is my code

private void matrixToXlsx(@NonNull final List<List<Object>> matrix, 
                          @NonNull final String output) throws IOException {
    //'val' is from lombok and is equal to 'final var'
    val f = new File(StringUtils.defaultIfBlank(output, "temp.xlsx"));
    FileUtils.deleteQuietly(f);
    try (val wb = new XSSFWorkbook()) {
        val helper = wb.getCreationHelper();
        val sheet = wb.createSheet("Query Result");
        val rowNum = new AtomicInteger();
        matrix.forEach(listRow -> {
            val row = sheet.createRow(rowNum.getAndIncrement());
            val colNum = new AtomicInteger();
            log.info("Processing row #{}: {}", rowNum.get() - 1, listRow);
            listRow.forEach(listCell -> {
                log.info("Processing col #{} cell: {} (type: {})", colNum, listCell, listCell.getClass().getSimpleName());
                val cell = row.createCell(colNum.getAndIncrement());
                if (listCell instanceof BigDecimal) {
                    // ################ cell creation for double ####################
                    cell.setCellValue(((BigDecimal) listCell).doubleValue());
                    // ##############################################################
                } else if (listCell instanceof String) {
                    cell.setCellValue((String) listCell);
                } else {
                    cell.setCellValue(String.valueOf(listCell));
                }
            });
        });
        try (val os = new BufferedOutputStream(new FileOutputStream(f))) {
            wb.write(os);
        } finally {
            log.info("Output finished");
        }
    }
}

I followed the help reference, which seems to show that numeric cells can be made simply with row.createCell(1).setCellValue(1.2);.

I'm not sure what I am missing here. I just want Excel to accept the cell type as numeric. I have tried variations of the code such as:

val cell = row.createCell(colNum.getAndIncrement(), CellType.NUMERIC);
cell.setCellValue(((BigDecimal) listCell).doubleValue());

Where I explicitly tell it is of CellType.NUMERIC and this makes no difference. I also tried:

cell.setCellValue(((BigDecimal) listCell).doubleValue());
val style = wb.createCellStyle();
val format = wb.createDataFormat();
style.setDataFormat(format.getFormat("0.0"));
cell.setCellStyle(style);

Excel is unhappy no matter what it seems!

CodePudding user response：

The only case where this could happen using that code is when listCell never is instanceof BigDecimal. Then it is always converted to String and thus also set as text cell value.

Since listCell is an Object and Excel always stores all numeric values as double, the check wheter the Object is numeric should be like so:

...
if (listCell instanceof Number) {
 cell.setCellValue(((Number)listCell).doubleValue());
} ...

The class java.lang.Number is the super class of most of all numeric objects in Java. So this should fit the most cases.

If that also not get listCell as numeric, then the Object listCell is not a numeric object. Your code logs the class of each listCell object. So look into that log what classes get logged there. If that all are Strings then the problem is in creating the List<List<Object>> matrix.

CodePudding user response：

try

sheet.addIgnoredErrors(new CellRangeAddress(firstRowNum,lastRowNum,firstColumnNum,lastColumnNum), IgnoredErrorType.NUMBER_STORED_AS_TEXT);