Bug 46948

Summary: java.lang.IllegalArgumentException: Unexpected ref arg class (org.apache.poi.ss.formula.LazyAreaEval)
Product: POI Reporter: kevinrmckee
Component: HSSFAssignee: POI Developers List <dev>
Status: RESOLVED FIXED    
Severity: normal CC: kevinrmckee
Priority: P2    
Version: 3.5-dev   
Target Milestone: ---   
Hardware: PC   
OS: Windows Vista   

Description kevinrmckee 2009-03-31 17:22:20 UTC
I have a formula in an Excel 97-2003 spreadsheet as follows
=AVERAGE(J6:OFFSET(I6,0,DAY(TODAY())-1))
which basically does an average on a number of cells dependant on todays date.

When I run the sample code at the bottom of  http://poi.apache.org/spreadsheet/eval.html to recalculate all formulas in a workbook (which I had to modify as follows to get it to compile properly)

FileInputStream fis = new FileInputStream("c:\\test.xls");
FileOutputStream fos = new FileOutputStream("c:\\test2.xls");
Workbook wb = new HSSFWorkbook(fis); //or new XSSFWorkbook("/somepath/test.xls")
FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
for(int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); sheetNum++) {
    Sheet sheet = wb.getSheetAt(sheetNum);
    for(Row r : sheet) {
        for(Cell c : r) {
            if(c.getCellType() == Cell.CELL_TYPE_FORMULA) {
                System.out.println(c.getCellFormula().toString());
                evaluator.evaluateFormulaCell(c);
            }
        }
    }
}
wb.write(fos);
fos.close();
fis.close();

I am getting an error at the above formula

Exception in thread "main" java.lang.IllegalArgumentException: Unexpected ref arg class (org.apache.poi.ss.formula.LazyAreaEval)
        at org.apache.poi.hssf.record.formula.eval.RangeEval.evaluateRef(RangeEval.java:62)
        at org.apache.poi.hssf.record.formula.eval.RangeEval.evaluate(RangeEval.java:40)
        at org.apache.poi.ss.formula.WorkbookEvaluator.invokeOperation(WorkbookEvaluator.java:394)
        at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:329)
        at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:216)
        at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:180)
        at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateFormulaCellValue(HSSFFormulaEvaluator.java:297)
        at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluate(HSSFFormulaEvaluator.java:159)
        at poitest.Main.main(Main.java:41)
Java Result: 1

Is this a bug, or something I'm doing wrong?
Comment 1 Josh Micich 2009-04-01 12:50:43 UTC
Fixed in svn r761023 

junit added

POI can now *evaluate* formulas which apply the range operator to area refs. (e.g. "SUM((C1:D2):(D2:E3))").  So the example code should run OK now.


However, POI still cannot *parse* these formulas. For example, these calls will fail:
cell.setCellFormula("AVERAGE(J6:OFFSET(I6,0,DAY(TODAY())-1))");
cell.setCellFormula("SUM((C1:D2):(D2:E3))");

Bug 46951 has been opened to track this as a separate bug.