Bug 44997

Summary: Cell Range in Formula not being Evaluated in Excel
Product: POI Reporter: Jeff Rhodes <jrhodes>
Component: HSSFAssignee: POI Developers List <dev>
Status: RESOLVED FIXED    
Severity: major    
Priority: P2    
Version: unspecified   
Target Milestone: ---   
Hardware: PC   
OS: Windows XP   

Description Jeff Rhodes 2008-05-14 08:36:09 UTC
Placing a cell range in a formula results in a #VALUE! in the excel sheet if the range rows differ. For example,

"SUM(G22:G22)" evaluates properly by summing the contents of cell G22
"SUM(G22:G28)" results in a #VALUE! in the excel document.

This functionality worked fine as of poi-bin-3.0.1-FINAL-20070705, but does not work in poi-3.1-beta1.

A sample code snippet that sets the formula contents can be found below:


HSSFCellStyle summaryDataStyle = wb.createCellStyle();
summaryDataStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
summaryDataStyle.setFont(summaryItemFont);
summaryDataStyle.setDataFormat((short) 3);

...

HSSFCell currentCell = row.createCell((short)columnIndex++);
currentCell.setCellStyle(summaryDataStyle);				currentCell.setCellType(HSSFCell.CELL_TYPE_FORMULA);		 		currentCell.setCellFormula("SUM(G"+(previousItemStartRow+1)+":G"+row.getRowNum()+")");

where previousItemStartRow is an int.

Very strange that it evaluates correctly for a range that resolves to a single cell, but not for a real range of cells. Again, the EXACT same code works fine under the above 3.0 release.
Comment 1 Jeff Rhodes 2008-05-14 09:00:41 UTC
Note that SUM(G7,G8) Works fine as well
Comment 2 Josh Micich 2008-05-14 10:26:56 UTC
Reproduced in 3.1-beta1

This problem was resolved in the follow-up fix for bug 44675 (svn r652994).

You can either use the latest svn trunk or wait for final 3.1 to get this fix.