CODEWITHSUNDEEP
javaapache-poiexcel-formula
rafaelrezend
rafaelrezend

Reputation: 1015

Excel formula evaluation using Apache POI

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!

Upvotes: 0

Views: 2991

Answers (1)

Aprillion
Aprillion

Reputation: 22304

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)

Upvotes: 1

Related Questions