This is for 3.0.2 FINAL. AreaPtg and similar objects store row numbers as _shorts_ even though row offsets may range from 0-65535. Various things do not work for row offsets >= 32768. For example: 1. When HSSFFormulaEvaluator handles a cell reference it does: else if (ptgs[i] instanceof ReferencePtg) { ReferencePtg ptg = (ReferencePtg) ptgs[i]; short colnum = ptg.getColumn(); short rownum = ptg.getRow(); HSSFRow row = sheet.getRow(rownum); HSSFCell cell = (row != null) ? row.getCell(colnum) : null; pushRef2DEval(ptg, stack, cell, row, sheet, workbook); } if the row is 32768 the short value is -32768 and you get: java.lang.IndexOutOfBoundsException: Row number must be between 0 and 65535, was <-32768> at org.apache.poi.hssf.usermodel.HSSFRow.setRowNum(HSSFRow.java:211) at org.apache.poi.hssf.usermodel.HSSFSheet.getRow(HSSFSheet.java:330) at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.internalEvaluate(HSSFFormulaEvaluator.java:402) at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluate(HSSFFormulaEvaluator.java:203) 2. An expression involving a range, such as SUM(A32769:A32770) fails because AreaPtg passes the signed values into CellReference and you get an error like: java.lang.RuntimeException: Cannot Parse, sorry : Found reference to named range "A", but that named range wasn't defined! @ 6 [Formula String was: 'SUM(A-32767:A-32766)'] HSSFFormulaEvaluator uses shorts in a number of places to represent row numbers so there are a lot of things that would exhibit this problem. Trivial XLS attached which demonstrates the second example.
Turns out to be a big change. There's many places beyond the two pointed out where 16 unsigned values are represented with java primitive shorts. Here are two test cases that identify these problems: public void testSetFormulaWithRowBeyond32768_Bug44539() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(); wb.setSheetName(0, "Sheet1"); HSSFRow row = sheet.createRow(0); HSSFCell cell = row.createCell((short)0); cell.setCellFormula("SUM(A32769:A32770)"); if("SUM(A-32767:A-32766)".equals(cell.getCellFormula())) { fail("Identified bug 44539"); } assertEquals("SUM(A32769:A32770)", cell.getCellFormula()); } public void testEvaluateFormulaWithRowBeyond32768_Bug44539() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(); wb.setSheetName(0, "Sheet1"); HSSFRow row = sheet.createRow(0); HSSFCell cell = row.createCell((short)0); cell.setCellFormula("SUM(A32769:A32770)"); // put some values in the cells to make the evaluation more interesting sheet.createRow(32768).createCell((short)0).setCellValue(31); sheet.createRow(32769).createCell((short)0).setCellValue(11); HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(sheet, wb); fe.setCurrentRow(row); CellValue result; try { result = fe.evaluate(cell); } catch (FormulaParseException e) { if(e.getMessage().equals("Found reference to named range \"A\", but that named range wasn't defined!")) { fail("Identifed bug 44539"); } throw new RuntimeException(e); } assertEquals(HSSFCell.CELL_TYPE_NUMERIC, result.getCellType()); assertEquals(42.0, result.getNumberValue(), 0.0); } //
Created attachment 21633 [details] svn diff of 23 changed files
Thanks for the patch Josh. Needed a little bit of tweaking to not break the whole-column stuff, but now applied to svn trunk
(In reply to comment #3) fix was applied in svn r634630