Bug 54509 - Cells in shifting rows are not used in column-referencing formulas.
Summary: Cells in shifting rows are not used in column-referencing formulas.
Status: REOPENED
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.9-FINAL
Hardware: PC Windows XP
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2013-01-31 10:33 UTC by Sylvain Delafoy
Modified: 2016-01-31 17:53 UTC (History)
1 user (show)


Attachments
maven projet with an input file and a sample main method. (2.25 KB, application/zip)
2013-01-31 10:33 UTC, Sylvain Delafoy 		Details
View All Add an attachment (proposed patch, testcase, etc.)

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