Bug 49169

Summary: Updates to cells on one worksheet are not reflected in references in other sheets
Product: POI Reporter: William.Plummer
Component: HSSFAssignee: POI Developers List <dev>
Severity: normal    
Priority: P2    
Version: unspecified   
Target Milestone: ---   
Hardware: PC   
OS: Windows XP   

Description William.Plummer 2010-04-22 06:22:08 UTC
Release 3.6 appears to have a bug that was reportedly fixed in a much earlier release (See bug 12730)

I have used the test code given below from the original bug report and while "Sheet 1" is updated to the value 1, on opening the spreadsheet cell A1 on "Sheet 2" has not been updated. This 'refresh' only happens when the cursor is placed in the formula bar field and the enter key pressed.

1) Tested using Excel 2003.
2) Ran code below and also version using replacements for deprecated methods - result is the same.

From original Bug:

Avik Sengupta 2003-10-28 18:07:50 EST 
Works for me.. Excel2002, following code, no need to press F2, excel sheet has
empty Sheet1, "=Sheet1!A1" in Sheet2!A1

               String filename="12730.xls";
               FileInputStream in = new FileInputStream(filename);
               HSSFWorkbook wb = new HSSFWorkbook(in);
               HSSFSheet sheet = wb.getSheetAt(0);
               HSSFRow row = sheet.getRow(0);
               if (row==null) row=sheet.createRow(0);
               HSSFCell cell = row.getCell((short)0);
               if (cell==null) cell=row.createCell((short)0);
               File f = new File("test12730.xls");
               FileOutputStream fileOut = new FileOutputStream(f);
Comment 1 Nick Burch 2010-04-22 06:44:52 UTC
This is entirely expected. After changing values in cells, you need to re-created the cached formula values

See http://poi.apache.org/spreadsheet/eval.html for how to do this