Bug 53729 - Shift rows wrongly updates static reference
Summary: Shift rows wrongly updates static reference
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.8-FINAL
Hardware: PC All
: P2 critical (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2012-08-16 13:27 UTC by Michael
Modified: 2016-02-14 20:23 UTC (History)
1 user (show)

Testcase input file (23.00 KB, application/vnd.ms-excel)
2012-08-30 12:31 UTC, Thies Wellpott
Proposed bug fix (8.25 KB, patch)
2012-08-30 13:40 UTC, Thies Wellpott
Details | Diff
Compiled class of the patch (7.46 KB, application/octet-stream)
2012-08-30 13:45 UTC, Thies Wellpott

Note You need to log in before you can comment on or make changes to this bug.
Description Michael 2012-08-16 13:27:47 UTC
In my xlsx file I have a value at M17, many formulas uses it for counting.
This value is referenced as $M$17 with two dollar signs (to avoid auto modification if generated cells).
When I am trying to shift rows up, calling sheet.shiftRows(18, 100, -3);
All formulas references in shifting rows were updated correctly, but $M$17 was  replaced with #REF! sign. I think it is a defect, cause it should be still $M$17.

Seems like POI doesn't know about $ sign and updates reference to M17 to M14, which leads to error.
Comment 1 Thies Wellpott 2012-08-30 12:30:04 UTC
I can confirm this bug, also for HSSF in POI 3.8.

Java source code I use for the test:

	private static void poiBugTest1() throws IOException {
		final String filename = "d:\\temp\\POI_Bug_Test.xls";
		Workbook wb = new HSSFWorkbook(new FileInputStream(filename), true);
		System.out.println("WB geladen");

		Sheet sh = wb.getSheet("Tab1");
		sh.shiftRows(0, sh.getPhysicalNumberOfRows(), 1);

		OutputStream outStream = new FileOutputStream(filename);
		System.out.println("WB geschrieben");

The used XLS file is added as attachment.
After execution of the above code the reference in 'Second'!A1 has changed from "=Tab1!A$3" to "=Tab1!A$4".

I changed the bug importance to "critical" because shiftRows() destroys working XLS sheets and this can be a silent destruction nobody recognises.
Comment 2 Thies Wellpott 2012-08-30 12:31:55 UTC
Created attachment 29304 [details]
Testcase input file
Comment 3 Thies Wellpott 2012-08-30 13:40:12 UTC
Created attachment 29307 [details]
Proposed bug fix

I think the bug can be fixed by changing org.apache.poi.ss.formula.FormulaShifter to check for absolute references. The attached patch does exactly this (it bases on the poi trunk but the source file did not change since poi 3.8).
I only did a quick check with my own test case, it works. I did NOT test the modifications to AreaRef. A POI expert for this stuff should have a closer look.

Also TODO is a recalculation of the formula because the base data has changed (is shifted!). Current solution is to call workbook.setForceFormulaRecalculation(true); manually when you use shiftRows() and are not sure about absolute references.
Comment 4 Thies Wellpott 2012-08-30 13:45:24 UTC
Created attachment 29308 [details]
Compiled class of the patch

This is the compiled class of my proposed bug fix. You can use this and replace the class (poi.jar\org\apache\poi\ss\formula\FormulaShifter.class) in your used poi.jar (you should create a copy of poi.jar with a speaking prefix and use this new jar) to have a quick fix available.
Comment 5 Dominik Stadler 2013-12-26 18:32:10 UTC
Hmm, when I do the shifting in LibreOffice, the reference is also changed from "=Tab1!A$3" to "=Tab1!A$4", so it seems POI behaves the same way as this one, are you sure the $-sign works this way also for shifting complete rows? As far as I know $-sign mostly is used when you copy formulas or drag formulas across multiple cells, not when you actually insert new rows.
Comment 6 Dominik Stadler 2016-02-14 20:23:18 UTC
No update for a long time, therefore I am closing this, please reopen with more information if this is still a problem for you.