Bug 54509

Summary: Cells in shifting rows are not used in column-referencing formulas.
Product: POI Reporter: Sylvain Delafoy <sylvain.delafoy>
Component: HSSFAssignee: 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.

Description Sylvain Delafoy 2013-01-31 10:33:29 UTC
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.
Comment 1 Dominik Stadler 2014-08-31 12:05:17 UTC
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.
Comment 2 Sylvain Delafoy 2014-09-05 09:26:18 UTC
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.
Comment 3 Dominik Stadler 2014-09-11 05:54:28 UTC
reopen to investigate some more
Comment 4 Sylvain Delafoy 2016-01-31 17:53:35 UTC
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?