Receiving Cannot get a NUMERIC value from a BOOLEAN cell when exporting XML map

I have a workbook with an XML mapping defined (I am using POI 4.1.2).

A mapped cell contains a formula which evaluates to boolean: =AND(B70="";B71=""; B50="No")

I try to export the XML mapping with the following code:

// First ensure that all formulas are evaluated
wb.getCreationHelper().createFormulaEvaluator().evaluateAll();
// Export XML map
String file = "path/to/file.xml";
XSSFWorkbook xssfWorkbook = (XSSFWorkbook) wb;
XSSFMap xssfMap = xssfWorkbook.getCustomXMLMappings().stream().findFirst().get();
XSSFExportToXml exporter = new XSSFExportToXml(xssfMap);
OutputStream out = new FileOutputStream(file);
exporter.exportToXML(out, false);

The last line results to the following exception:

java.lang.IllegalStateException: Cannot get a NUMERIC value from a BOOLEAN cell at org.apache.poi.xssf.usermodel.XSSFCell.typeMismatch(XSSFCell.java:1035) at org.apache.poi.xssf.usermodel.XSSFCell.getNumericCellValue(XSSFCell.java:319) at org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(DateUtil.java:765) at org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(DateUtil.java:744) at org.apache.poi.xssf.extractor.XSSFExportToXml.mapCellOnNode(XSSFExportToXml.java:281)

When i look into XSSFExportToXml.mapCellOnNode(XSSFCell, Node) I see the following code:

private void mapCellOnNode(XSSFCell cell, Node node) {

    String value ="";
    switch (cell.getCellType()) {

    case STRING: value = cell.getStringCellValue(); break;
    case BOOLEAN: value += cell.getBooleanCellValue(); break;
    case ERROR: value = cell.getErrorCellString();  break;
    case FORMULA:
       if (cell.getCachedFormulaResultType() == CellType.STRING) { // cell.getCachedFormulaResultType() returns BOOLEAN
           value = cell.getStringCellValue();
       } else {
           if (DateUtil.isCellDateFormatted(cell)) { // Results in Exception
              value = getFormattedDate(cell);
           } else {
              value += cell.getNumericCellValue();
           }
       }
       break;
    
    case NUMERIC: 
         if (DateUtil.isCellDateFormatted(cell)) {
              value = getFormattedDate(cell);
          } else {
             value += cell.getRawValue();
          }
        break;

    default:

    }
    if (node instanceof Element) {
        Element currentElement = (Element) node;
        currentElement.setTextContent(value);
    } else {
        node.setNodeValue(value);
    }
}

From my understanding there is a bug in mapCellOnNode. Inside case FORMULA the expression cell.getCachedFormulaResultType() returns BOOLEAN but inside this block only STRING and NUMERIC are considered. This leads to executing DateUtil.isCellDateFormatted(cell) which results in an Exception.

So my question is: am I right? Is it a bug? Or am I missing something?

Answer

I have applied a fix to the POI code – see https://bz.apache.org/bugzilla/show_bug.cgi?id=65492 – this will be included in next POI release

Advertisement

Answer