|Summary:||Cell Range in Formula not being Evaluated in Excel|
|Product:||POI||Reporter:||Jeff Rhodes <jrhodes>|
|Component:||HSSF||Assignee:||POI Developers List <dev>|
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