Author: yegor
Date: Tue Dec 4 12:44:33 2012
New Revision: 1416917
URL: http://svn.apache.org/viewvc?rev=1416917&view=rev
Log:
Bug 54206: ValueRecordsAggregate.updateFormulasAfterRowShift doesn't update
shared formulas
Added:
poi/trunk/test-data/spreadsheet/54206.xls (with props)
poi/trunk/test-data/spreadsheet/54206.xlsx (with props)
Modified:
poi/trunk/src/documentation/content/xdocs/status.xml
poi/trunk/src/java/org/apache/poi/hssf/record/aggregates/ValueRecordsAggregate.java
poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java
poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java
poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestSheetShiftRows.java
Modified: poi/trunk/src/documentation/content/xdocs/status.xml
URL:
http://svn.apache.org/viewvc/poi/trunk/src/documentation/content/xdocs/status.xml?rev=1416917&r1=1416916&r2=1416917&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/status.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/status.xml Tue Dec 4 12:44:33
2012
@@ -34,6 +34,7 @@
<changes>
<release version="4.0-beta1" date="2013-??-??">
+ <action dev="poi-developers" type="fix">54206 - Ensure that shared
formuals are updated when shifting rows in a spreadsheet</action>
<action dev="poi-developers" type="fix">Synchronize table headers
with parent sheet in XSSF</action>
<action dev="poi-developers" type="fix">54210 - Fixed rendering text
in flipped shapes in PPT2PNG and PPTX2PNG</action>
</release>
Modified:
poi/trunk/src/java/org/apache/poi/hssf/record/aggregates/ValueRecordsAggregate.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/aggregates/ValueRecordsAggregate.java?rev=1416917&r1=1416916&r2=1416917&view=diff
==============================================================================
---
poi/trunk/src/java/org/apache/poi/hssf/record/aggregates/ValueRecordsAggregate.java
(original)
+++
poi/trunk/src/java/org/apache/poi/hssf/record/aggregates/ValueRecordsAggregate.java
Tue Dec 4 12:44:33 2012
@@ -292,12 +292,14 @@ public final class ValueRecordsAggregate
for (int j = 0; j < rowCells.length; j++) {
CellValueRecordInterface cell = rowCells[j];
if (cell instanceof FormulaRecordAggregate) {
- FormulaRecord fr =
((FormulaRecordAggregate)cell).getFormulaRecord();
- Ptg[] ptgs = fr.getParsedExpression();
// needs clone() inside this getter?
- if (shifter.adjustFormula(ptgs,
currentExternSheetIndex)) {
- fr.setParsedExpression(ptgs);
- }
- }
+ FormulaRecordAggregate fra = (FormulaRecordAggregate)cell;
+ Ptg[] ptgs = fra.getFormulaTokens(); // needs clone()
inside this getter?
+ Ptg[] ptgs2 =
((FormulaRecordAggregate)cell).getFormulaRecord().getParsedExpression(); //
needs clone() inside this getter?
+
+ if (shifter.adjustFormula(ptgs, currentExternSheetIndex)) {
+ fra.setParsedExpression(ptgs);
+ }
+ }
}
}
}
Modified: poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java?rev=1416917&r1=1416916&r2=1416917&view=diff
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java
(original)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java Tue
Dec 4 12:44:33 2012
@@ -2617,7 +2617,8 @@ public class XSSFSheet extends POIXMLDoc
* @param sid shared group index
* @return a CTCellFormula bean holding shared formula or
<code>null</code> if not found
*/
- CTCellFormula getSharedFormula(int sid){
+ @Internal
+ public CTCellFormula getSharedFormula(int sid){
return sharedFormulas.get(sid);
}
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=1416917&r1=1416916&r2=1416917&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
Tue Dec 4 12:44:33 2012
@@ -28,10 +28,7 @@ import org.apache.poi.ss.formula.Formula
import org.apache.poi.ss.formula.ptg.Ptg;
import org.apache.poi.ss.formula.ptg.AreaPtg;
import org.apache.poi.ss.formula.ptg.AreaErrPtg;
-import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell;
-import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCellFormula;
-import
org.openxmlformats.schemas.spreadsheetml.x2006.main.CTConditionalFormatting;
-import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCfRule;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.*;
import java.util.List;
import java.util.ArrayList;
@@ -156,7 +153,13 @@ public final class XSSFRowShifter {
String shiftedFormula = shiftFormula(row, formula,
shifter);
if (shiftedFormula != null) {
f.setStringValue(shiftedFormula);
+ if(f.getT() == STCellFormulaType.SHARED){
+ int si = (int)f.getSi();
+ CTCellFormula sf =
row.getSheet().getSharedFormula(si);
+ sf.setStringValue(shiftedFormula);
+ }
}
+
}
if (f.isSetRef()) { //Range of cells which the formula applies
to.
Modified:
poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestSheetShiftRows.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestSheetShiftRows.java?rev=1416917&r1=1416916&r2=1416917&view=diff
==============================================================================
---
poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestSheetShiftRows.java
(original)
+++
poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestSheetShiftRows.java
Tue Dec 4 12:44:33 2012
@@ -21,6 +21,7 @@ import junit.framework.TestCase;
import org.apache.poi.ss.ITestDataProvider;
import org.apache.poi.ss.util.CellRangeAddress;
+import org.apache.poi.ss.util.CellReference;
/**
* Tests row shifting capabilities.
@@ -41,8 +42,6 @@ public abstract class BaseTestSheetShift
* After each shift, writes the workbook to file and reads back to
* check. This ensures that if some changes code that breaks
* writing or what not, they realize it.
- *
- * @param sampleName the sample file to test against
*/
public final void testShiftRows(){
// Read initial file in
@@ -324,4 +323,61 @@ public abstract class BaseTestSheetShift
assertEquals(expectedValue, cell.getNumericCellValue(), 0.0);
assertEquals(expectedFormula, cell.getCellFormula());
}
-}
+
+ public final void testShiftSharedFormulasBug54206() {
+ Workbook wb = _testDataProvider.openSampleWorkbook("54206." +
_testDataProvider.getStandardFileNameExtension());
+
+ Sheet sheet = wb.getSheetAt(0);
+ assertEquals("SUMIF($B$19:$B$82,$B4,G$19:G$82)",
sheet.getRow(3).getCell(6).getCellFormula());
+ assertEquals("SUMIF($B$19:$B$82,$B4,H$19:H$82)",
sheet.getRow(3).getCell(7).getCellFormula());
+ assertEquals("SUMIF($B$19:$B$82,$B4,I$19:I$82)",
sheet.getRow(3).getCell(8).getCellFormula());
+
+ assertEquals("SUMIF($B$19:$B$82,$B15,G$19:G$82)",
sheet.getRow(14).getCell(6).getCellFormula());
+ assertEquals("SUMIF($B$19:$B$82,$B15,H$19:H$82)",
sheet.getRow(14).getCell(7).getCellFormula());
+ assertEquals("SUMIF($B$19:$B$82,$B15,I$19:I$82)",
sheet.getRow(14).getCell(8).getCellFormula());
+
+ // now the whole block G4L:15
+ for(int i = 3; i <= 14; i++){
+ for(int j = 6; j <= 8; j++){
+ String col = CellReference.convertNumToColString(j);
+ String expectedFormula =
"SUMIF($B$19:$B$82,$B"+(i+1)+","+col+"$19:"+col+"$82)";
+ assertEquals(expectedFormula,
sheet.getRow(i).getCell(j).getCellFormula());
+ }
+ }
+
+ assertEquals("SUM(G24:I24)",
sheet.getRow(23).getCell(9).getCellFormula());
+ assertEquals("SUM(G25:I25)",
sheet.getRow(24).getCell(9).getCellFormula());
+ assertEquals("SUM(G26:I26)",
sheet.getRow(25).getCell(9).getCellFormula());
+
+ sheet.shiftRows(24, sheet.getLastRowNum(), 4, true, false);
+
+ assertEquals("SUMIF($B$19:$B$86,$B4,G$19:G$86)",
sheet.getRow(3).getCell(6).getCellFormula());
+ assertEquals("SUMIF($B$19:$B$86,$B4,H$19:H$86)",
sheet.getRow(3).getCell(7).getCellFormula());
+ assertEquals("SUMIF($B$19:$B$86,$B4,I$19:I$86)",
sheet.getRow(3).getCell(8).getCellFormula());
+
+ assertEquals("SUMIF($B$19:$B$86,$B15,G$19:G$86)",
sheet.getRow(14).getCell(6).getCellFormula());
+ assertEquals("SUMIF($B$19:$B$86,$B15,H$19:H$86)",
sheet.getRow(14).getCell(7).getCellFormula());
+ assertEquals("SUMIF($B$19:$B$86,$B15,I$19:I$86)",
sheet.getRow(14).getCell(8).getCellFormula());
+
+ // now the whole block G4L:15
+ for(int i = 3; i <= 14; i++){
+ for(int j = 6; j <= 8; j++){
+ String col = CellReference.convertNumToColString(j);
+ String expectedFormula =
"SUMIF($B$19:$B$86,$B"+(i+1)+","+col+"$19:"+col+"$86)";
+ assertEquals(expectedFormula,
sheet.getRow(i).getCell(j).getCellFormula());
+ }
+ }
+
+ assertEquals("SUM(G24:I24)",
sheet.getRow(23).getCell(9).getCellFormula());
+
+ // shifted rows
+ assertTrue( sheet.getRow(24) == null || sheet.getRow(24).getCell(9) ==
null);
+ assertTrue( sheet.getRow(25) == null || sheet.getRow(25).getCell(9) ==
null);
+ assertTrue( sheet.getRow(26) == null || sheet.getRow(26).getCell(9) ==
null);
+ assertTrue( sheet.getRow(27) == null || sheet.getRow(27).getCell(9) ==
null);
+
+ assertEquals("SUM(G29:I29)",
sheet.getRow(28).getCell(9).getCellFormula());
+ assertEquals("SUM(G30:I30)",
sheet.getRow(29).getCell(9).getCellFormula());
+
+ }
+}
\ No newline at end of file
Added: poi/trunk/test-data/spreadsheet/54206.xls
URL:
http://svn.apache.org/viewvc/poi/trunk/test-data/spreadsheet/54206.xls?rev=1416917&view=auto
==============================================================================
Binary file - no diff available.
Propchange: poi/trunk/test-data/spreadsheet/54206.xls
------------------------------------------------------------------------------
svn:mime-type = application/octet-stream
Added: poi/trunk/test-data/spreadsheet/54206.xlsx
URL:
http://svn.apache.org/viewvc/poi/trunk/test-data/spreadsheet/54206.xlsx?rev=1416917&view=auto
==============================================================================
Binary file - no diff available.
Propchange: poi/trunk/test-data/spreadsheet/54206.xlsx
------------------------------------------------------------------------------
svn:mime-type = application/vnd.ms-excel
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
