Excel formula evaluation using Apache POI

Question

I have a formula like this which works fine on Excel/OOCalc applications:

=SUMPRODUCT(--($Panels.$A$5:$A$6999=K$80);--($Panels.$B$5:$B$6999=K$81);INDEX($Panels.$C$5:$J$6999;0;MATCH("Voc";$Panels.$C$3:$J$3;0)))

Since I may have cells with different types, I'm checking it first then performing the evaluation if needed:

int cellType = cell.getCellType();

    if (cellType == Cell.CELL_TYPE_FORMULA){
        FormulaEvaluator evaluator = wb.getCreationHelper()
                .createFormulaEvaluator();
        cellType = evaluator.evaluateFormulaCell(cell);
    }

    System.out.println("CELL TYPE: " + cell.getCellType());

    switch (cellType) {
    case Cell.CELL_TYPE_BOOLEAN:
        System.out.println ("BOO: " + cell.getBooleanCellValue());
        break;
    case Cell.CELL_TYPE_NUMERIC:
        System.out.println ("NUM: " + cell.getNumericCellValue());
        break;
    case Cell.CELL_TYPE_STRING:
        System.out.println ("STR: " + cell.getStringCellValue());
        break;
    case Cell.CELL_TYPE_BLANK:
        System.out.println ("BLK: ");
        break;
    case Cell.CELL_TYPE_ERROR:
        System.out.println("ERRF: " + cell.getCellFormula());
        System.out.println("ERR: " + cell.getErrorCellValue());
        break;
    case Cell.CELL_TYPE_FORMULA:
        System.out.println("FOR: " + cell.getCellFormula());
                break;
    default:

    }

This code works fine for numeric and string cells, as well as most of formulas I needed to evaluate by now. However, it does not work for this formula. Also, according to the POI doc http://poi.apache.org/spreadsheet/eval-devguide.html, all functions are supported. Here is the output:

CELL TYPE: 2
ERRF: SUMPRODUCT(--(Panels!$A$5:$A$6999=K$80),--(Panels!$B$5:$B$6999=K$81),INDEX(Panels!$C$5:$J$6999,0,MATCH(Formulas!$K$83,Panels!$C$3:$J$3,0)))
ERR: -60

Note: Cell Type 2 should be CELL_TYPE_FORMULA. But I'm falling into the CELL_TYPE_ERROR anyway. If it is a POI issue, I would be glad to replace the formula to another one which performs the same calculation: match of columns with multiple criteria.

Thanks in advance!

What is cellType before you evaluate the formula? Do you actually enter the evaluation block? — Jim Garrison
I checked now that the cellType before was also two, and it really enters the evaluation block. One thing I forgot to mention, I'm using POI 3.8 beta 5, because I needed a function from this build. — rafaelrezend

Aprillion Aprillion · Accepted Answer · 2012-04-02T09:35:48

even if sumproduct looks like it is not an array formula, it is just a syntax sugar for one - and your use of INDEX probably makes it too complicated for POI, where array formulas are not fully supported yet..

you can rewrite the INDEX to OFFSET, e.g.:

=SUMIFS(OFFSET(panels!$C$5:$C$6999;0;MATCH($K$10;panels!$C$3:$J$3;0)-1);
        Panels!$A$5:$A$6999;K$80;
        Panels!$B$5:$B$6999;K$81)

Excel formula evaluation using Apache POI

1 Answers