| Summary: | Cells in shifting rows are not used in column-referencing formulas. | ||
|---|---|---|---|
| Product: | POI | Reporter: | Sylvain Delafoy <sylvain.delafoy> |
| Component: | HSSF | Assignee: | POI Developers List <dev> |
| Status: | REOPENED --- | ||
| Severity: | normal | CC: | onealj |
| Priority: | P2 | ||
| Version: | 3.9-FINAL | ||
| Target Milestone: | --- | ||
| Hardware: | PC | ||
| OS: | Windows XP | ||
| Attachments: | maven projet with an input file and a sample main method. | ||
I tried this with latest POI and it did work fine, seems some of the related fixes since POI 3.9 has fixed this as well. Looks like the difference comes from the software used to open the file. I checked my example this morning with Excel 2010 and the latest libreoffice and both showed 10. Unfortunately, I don't have access to the computer where I noticed this problem to check excel's version.
However, if I add
System.out.println(new HSSFWorkbook(new FileInputStream("out.xls")).getSheetAt(0).getRow(0).getCell(0).getNumericCellValue());
and use POI version 3.10.1 or 3.11-beta2 at the end of the test, the output is 7.
However, I wonder if it's a POI bug or not.
PS: My apologies to clean code, there is obviously a lot of stream closing to be done. If this bug is reopened, I volunteer for writing a clean unit test.
reopen to investigate some more I'm sorry I can't help more, I left the company at which I encountered this issue so no access to the original excel version anymore. Can I help in any other way? |
Created attachment 29910 [details] maven projet with an input file and a sample main method. When shifting rows in an HSSFSheet, formulas such as "=sum(C[1])" do not use new cells' values. You need to open the resulting file in excel and edit (F2) the cell in order for the formula to use the cell's value. Sample file and code in the attachment.