poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rafael Ribeiro Rezende <rafaelrez...@gmail.com>
Subject Error -60 when evaluating match of columns with SUMPRODUCT function.
Date Sun, 25 Mar 2012 03:23:59 GMT
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!

-- 
Rafael R Rezende

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message