Author: onealj
Date: Wed Feb 8 07:20:31 2017
New Revision: 1782111
URL: http://svn.apache.org/viewvc?rev=1782111&view=rev
Log:
bug 59983: correctly update shared formulas when shifting rows. Thanks to Luca
Martini for the initial failing unit test with test file and Chiara Marcheschi
for the patch
Modified:
poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java
poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheetShiftRows.java
poi/trunk/test-data/spreadsheet/TestShiftRowSharedFormula.xlsx
Modified:
poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java?rev=1782111&r1=1782110&r2=1782111&view=diff
==============================================================================
---
poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java
(original)
+++
poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java
Wed Feb 8 07:20:31 2017
@@ -142,22 +142,27 @@ public final class XSSFRowShifter extend
int si = (int)f.getSi();
CTCellFormula sf = sheet.getSharedFormula(si);
sf.setStringValue(shiftedFormula);
+ updateRefInCTCellFormula(row, shifter, sf);
}
}
}
//Range of cells which the formula applies to.
- if (f.isSetRef()) {
- String ref = f.getRef();
- String shiftedRef = shiftFormula(row, ref, shifter);
- if (shiftedRef != null) f.setRef(shiftedRef);
- }
+ updateRefInCTCellFormula(row, shifter, f);
}
}
}
+ private void updateRefInCTCellFormula(Row row, FormulaShifter shifter,
CTCellFormula f) {
+ if (f.isSetRef()) { //Range of cells which the formula applies to.
+ String ref = f.getRef();
+ String shiftedRef = shiftFormula(row, ref, shifter);
+ if (shiftedRef != null) f.setRef(shiftedRef);
+ }
+ }
+
/**
* Shift a formula using the supplied FormulaShifter
*
Modified:
poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheetShiftRows.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheetShiftRows.java?rev=1782111&r1=1782110&r2=1782111&view=diff
==============================================================================
---
poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheetShiftRows.java
(original)
+++
poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheetShiftRows.java
Wed Feb 8 07:20:31 2017
@@ -427,8 +427,7 @@ public final class TestXSSFSheetShiftRow
return cell.getCellFormula();
}
- // This test is written as expected-to-fail and should be rewritten
- // as expected-to-pass when the bug is fixed.
+ // bug 59983: Wrong update of shared formulas after shiftRow
@Test
public void testSharedFormulas() throws Exception {
XSSFWorkbook wb =
XSSFTestDataSamples.openSampleWorkbook("TestShiftRowSharedFormula.xlsx");
@@ -437,17 +436,44 @@ public final class TestXSSFSheetShiftRow
assertEquals("SUM(D2:D4)", getCellFormula(sheet, "D5"));
assertEquals("SUM(E2:E4)", getCellFormula(sheet, "E5"));
+ assertEquals("SUM(C3:C5)", getCellFormula(sheet, "C6"));
+ assertEquals("SUM(D3:D5)", getCellFormula(sheet, "D6"));
+ assertEquals("SUM(E3:E5)", getCellFormula(sheet, "E6"));
+
sheet.shiftRows(3, sheet.getLastRowNum(), 1);
- // FIXME: remove try, catch, and testPassesNow, skipTest when test
passes
- try {
- assertEquals("SUM(C2:C5)", getCellFormula(sheet, "C6"));
- assertEquals("SUM(D2:D5)", getCellFormula(sheet, "D6"));
- assertEquals("SUM(E2:E5)", getCellFormula(sheet, "E6"));
- testPassesNow(59983);
- } catch (AssertionError e) {
- skipTest(e);
- }
-
+
+ assertEquals("SUM(C2:C5)", getCellFormula(sheet, "C6"));
+ assertEquals("SUM(D2:D5)", getCellFormula(sheet, "D6"));
+ assertEquals("SUM(E2:E5)", getCellFormula(sheet, "E6"));
+
+ assertEquals("SUM(C3:C6)", getCellFormula(sheet, "C7"));
+ assertEquals("SUM(D3:D6)", getCellFormula(sheet, "D7"));
+ assertEquals("SUM(E3:E6)", getCellFormula(sheet, "E7"));
+ wb.close();
+ }
+
+ // bug 59983: Wrong update of shared formulas after shiftRow
+ @Test
+ public void testShiftSharedFormulas() throws Exception {
+ XSSFWorkbook wb =
XSSFTestDataSamples.openSampleWorkbook("TestShiftRowSharedFormula.xlsx");
+ XSSFSheet sheet = wb.getSheetAt(0);
+ assertEquals("SUM(C2:C4)", getCellFormula(sheet, "C5"));
+ assertEquals("SUM(D2:D4)", getCellFormula(sheet, "D5"));
+ assertEquals("SUM(E2:E4)", getCellFormula(sheet, "E5"));
+
+ assertEquals("SUM(C3:C5)", getCellFormula(sheet, "C6"));
+ assertEquals("SUM(D3:D5)", getCellFormula(sheet, "D6"));
+ assertEquals("SUM(E3:E5)", getCellFormula(sheet, "E6"));
+
+ sheet.shiftRows(sheet.getFirstRowNum(), 4, -1);
+
+ assertEquals("SUM(C1:C3)", getCellFormula(sheet, "C4"));
+ assertEquals("SUM(D1:D3)", getCellFormula(sheet, "D4"));
+ assertEquals("SUM(E1:E3)", getCellFormula(sheet, "E4"));
+
+ assertEquals("SUM(C2:C4)", getCellFormula(sheet, "C6"));
+ assertEquals("SUM(D2:D4)", getCellFormula(sheet, "D6"));
+ assertEquals("SUM(E2:E4)", getCellFormula(sheet, "E6"));
wb.close();
}
Modified: poi/trunk/test-data/spreadsheet/TestShiftRowSharedFormula.xlsx
URL:
http://svn.apache.org/viewvc/poi/trunk/test-data/spreadsheet/TestShiftRowSharedFormula.xlsx?rev=1782111&r1=1782110&r2=1782111&view=diff
==============================================================================
Binary files - no diff available.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]