You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Rafael Ribeiro Rezende <ra...@gmail.com> on 2012/03/25 05:23:59 UTC
Error -60 when evaluating match of columns with SUMPRODUCT function.
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
Re: Error -60 when evaluating match of columns with SUMPRODUCT
function.
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Sorry, miss-read the post; I thought you were confused about the fact that
the cell type had seemingly changed following the calculation..
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Error-60-when-evaluating-match-of-columns-with-SUMPRODUCT-function-tp5592758p5594385.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Error -60 when evaluating match of columns with SUMPRODUCT function.
Posted by Rafael Ribeiro Rezende <ra...@gmail.com>.
Excel and OOCalc calculate that formula properly, and give me the correct
numeric number, which should be, for instance in this case, 220.
When POI calculates, however, I get CELL_TYPE_ERROR with value -60.
On Sun, Mar 25, 2012 at 9:32 AM, Mark Beardsley <ma...@tiscali.co.uk>wrote:
> Well, POI may be correct. When you call the evaluateFormulaCell() method
> the
> cell type returned will be the type of the result of calculating the
> formula. So, if the formula produces a numeric result, it will return
> Cell.CELL_TYPE_NUMERIC or 0. POI is here saying that evaluating this
> formula
> produces an error, have you opened the file with Excel to double check if
> this is correct?
>
> --
> View this message in context:
> http://apache-poi.1045710.n5.nabble.com/Error-60-when-evaluating-match-of-columns-with-SUMPRODUCT-function-tp5592758p5592901.html
> Sent from the POI - User mailing list archive at Nabble.com.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
--
Rafael R Rezende
Re: Error -60 when evaluating match of columns with SUMPRODUCT
function.
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Well, POI may be correct. When you call the evaluateFormulaCell() method the
cell type returned will be the type of the result of calculating the
formula. So, if the formula produces a numeric result, it will return
Cell.CELL_TYPE_NUMERIC or 0. POI is here saying that evaluating this formula
produces an error, have you opened the file with Excel to double check if
this is correct?
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Error-60-when-evaluating-match-of-columns-with-SUMPRODUCT-function-tp5592758p5592901.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org