Monday, April 16, 2012

Evaluating more than 255 cells using Apache POI 3.8

I've recently migrated to Apache POI 3.8 in order to handle .xlsx files as well as .xls. There appears to be a bug when evaluating more than 255 cells. To test, I'm just summing a range of cells:



SUM(B2:IW2)



Each cell contains "10".



Here's the code to evaluate the formula:



FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
evaluator.clearAllCachedResultValues();
String result = DataFormatter().formatCellValue(cell, evaluator);


The result of my integration test is 2550; it should be 2560.



DataFormatter().formatCellValue(cell, evaluator)


Calls this code in org.apache.poi.ss.formula.LazyAreaEval:



public ValueEval getRelativeValue(int relativeRowIndex, int relativeColumnIndex) {

int rowIx = (relativeRowIndex + getFirstRow() ) & 0xFFFF;
int colIx = (relativeColumnIndex + getFirstColumn() ) & 0x00FF;

return _evaluator.getEvalForCell(rowIx, colIx);
}


which doesn't evaluate beyond 255.



Is there a workaround for this? The reason for this implementation is to allow client users to enter their own excel formulas for whatever requirements might need and for the system I'm working on to calculate the result using these formulas only. IE, the values of the cells (in my example, "10") are read dynamically form a separate source. I can't change the orientation of the cells being evaluated to use rows instead of columns as the existing structure is in production.



Any help much appreciated!





No comments:

Post a Comment