POI: output blank if formula returns blank

Question

I have a formula like this in Excel:

=IF(A1="foo";"";"0")

If the formula returns a blank value I want no value in my resultiong csv file created by POI. If the formula returns a 0 I want a 0 in my csv file.

This is a part of my code (it's always the question of how much stripping the code down):

Iterator<Row> rowIterator = sheet.rowIterator();
while (rowIterator.hasNext()) {

    Row row = rowIterator.next();
    Iterator<Cell> cellIterator = row.cellIterator();
    boolean isFirst = true;

    for (int cn = 0; cn < row.getLastCellNum(); cn++) {
        Cell cell = row.getCell(cn, Row.CREATE_NULL_AS_BLANK);
        if (!isFirst) {
            buffer.write(delimiter.getBytes(charset));
        } else {
            isFirst = false;
        }

        // Numeric Cell type (0)
        // String Cell type (1)
        // Formula Cell type (2)
        // Blank Cell type (3)
        // Boolean Cell type (4)
        // Error Cell type (5)
        if (cell.getCellType() == 0 || cell.getCellType() == 2) {
            try {
                if (DateUtil.isCellDateFormatted(cell)) {
                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                    Date value = cell.getDateCellValue();
                    SimpleDateFormat sdf = new SimpleDateFormat("dd.MM.yyyy");
                    if (cell.getNumericCellValue() < 1) {
                        sdf.applyPattern("HH:mm:ss");
                    }
                    buffer.write(sdf.format(value).getBytes(charset));
                } else {
                    double valueDouble = cell.getNumericCellValue();
                    if (valueDouble == Math.ceil(valueDouble)) {
                        buffer.write(String.format("%d", (long) valueDouble).getBytes(charset));
                    } else {
                        valueDouble = round(valueDouble, roundingPlaces);
                        String value = String.valueOf(valueDouble).replace(".", ",");
                        buffer.write(value.getBytes(charset));
                    }
                }
            } catch (Exception e) {
                // Formula returns a string
                cell.setCellType(Cell.CELL_TYPE_STRING);
                String value = cell.getStringCellValue();
                buffer.write(value.getBytes(charset));
            }
        } else {
            cell.setCellType(Cell.CELL_TYPE_STRING);
            String value = cell.getStringCellValue();
            buffer.write(value.getBytes(charset));
        }
    }
    buffer.write("\r\n".getBytes(charset));
}

This code results in a 0 in the csv file in every case. It results from the line

double valueDouble = cell.getNumericCellValue();

The documentation is pretty clear about this point:

double getNumericCellValue()

Get the value of the cell as a number.

For strings we throw an exception. For blank cells we return a 0. For formulas or error cells we return the precalculated value;

How can I analyze the cell if it contains a NULL value?

Answer 1

I think your problem is that the formula returns a character "" or "0" but you are treating it as a numeric.

Answer 2

cell.getCellType() == Cell.CELL_TYPE_BLANK should be true for a blank Cell, false otherwise.

Edit: I forgot you have a Formula cell. In this case, augment to: cell.getCellType() == Cell.CELL_TYPE_FORMULA ? Cell.getCachedFormulaResultType() == Cell.CELL_TYPE_BLANK : cell.getCellType() == Cell.CELL_TYPE_BLANK

Also you should probably not filter blank cells, but non-numeric cells.

Answer 3

@llogiq brought me on the right track!

I added the following code before the if statement:

        if (cell.getCellType() == 2 && cell.getCachedFormulaResultType() == 1) {
            logger.log(Level.DEBUG, "Formula returns a string. (1)");
            cell.setCellType(Cell.CELL_TYPE_STRING);
            String value = cell.getStringCellValue();
            buffer.write(value.getBytes(charset));
        } else if (cell.getCellType() == 0 || cell.getCellType() == 2) {

Now the formula results in a blank field in my csv file! And I can even get rid of the nasty try catch block. Of course now I still have to prettify the code...