Bug 44997 - Cell Range in Formula not being Evaluated in Excel
Summary: Cell Range in Formula not being Evaluated in Excel
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: unspecified
Hardware: PC Windows XP
: P2 major (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2008-05-14 08:36 UTC by Jeff Rhodes
Modified: 2008-05-14 10:26 UTC (History)
0 users


Note You need to log in before you can comment on or make changes to this bug.
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.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.