You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by ni...@apache.org on 2008/01/08 18:18:43 UTC
svn commit: r610072 - in /poi/trunk/src: documentation/content/xdocs/
java/org/apache/poi/hssf/usermodel/ testcases/org/apache/poi/hssf/data/
testcases/org/apache/poi/hssf/usermodel/
Author: nick
Date: Tue Jan 8 09:18:38 2008
New Revision: 610072
URL: http://svn.apache.org/viewvc?rev=610072&view=rev
Log:
Fix bug #34023 - when shifting rows, update the any formula references to those rows to point to their new location
Added:
poi/trunk/src/testcases/org/apache/poi/hssf/data/ForShifting.xls (with props)
Modified:
poi/trunk/src/documentation/content/xdocs/changes.xml
poi/trunk/src/documentation/content/xdocs/status.xml
poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java
poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestSheetShiftRows.java
Modified: poi/trunk/src/documentation/content/xdocs/changes.xml
URL: http://svn.apache.org/viewvc/poi/trunk/src/documentation/content/xdocs/changes.xml?rev=610072&r1=610071&r2=610072&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/changes.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/changes.xml Tue Jan 8 09:18:38 2008
@@ -36,6 +36,7 @@
<!-- Don't forget to update status.xml too! -->
<release version="3.0.2-FINAL" date="2008-??-??">
+ <action dev="POI-DEVELOPERS" type="add">34023 - When shifting rows, update formulas on that sheet to point to the new location of those rows</action>
<action dev="POI-DEVELOPERS" type="add">Support getting all the cells referenced by an AreaReference, not just the corner ones</action>
<action dev="POI-DEVELOPERS" type="add">43510 - Add support for named ranges in formulas, including non-contiguous named ranges</action>
<action dev="POI-DEVELOPERS" type="add">43937 - Add support for hiding and un-hiding sheets, and checking their current hidden status</action>
Modified: poi/trunk/src/documentation/content/xdocs/status.xml
URL: http://svn.apache.org/viewvc/poi/trunk/src/documentation/content/xdocs/status.xml?rev=610072&r1=610071&r2=610072&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/status.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/status.xml Tue Jan 8 09:18:38 2008
@@ -33,6 +33,7 @@
<!-- Don't forget to update changes.xml too! -->
<changes>
<release version="3.0.2-FINAL" date="2008-??-??">
+ <action dev="POI-DEVELOPERS" type="add">34023 - When shifting rows, update formulas on that sheet to point to the new location of those rows</action>
<action dev="POI-DEVELOPERS" type="add">Support getting all the cells referenced by an AreaReference, not just the corner ones</action>
<action dev="POI-DEVELOPERS" type="add">43510 - Add support for named ranges in formulas, including non-contiguous named ranges</action>
<action dev="POI-DEVELOPERS" type="add">43937 - Add support for hiding and un-hiding sheets, and checking their current hidden status</action>
Modified: poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java?rev=610072&r1=610071&r2=610072&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java Tue Jan 8 09:18:38 2008
@@ -28,6 +28,7 @@
import org.apache.poi.hssf.model.Workbook;
import org.apache.poi.hssf.record.*;
import org.apache.poi.hssf.record.formula.Ptg;
+import org.apache.poi.hssf.record.formula.ReferencePtg;
import org.apache.poi.hssf.util.HSSFCellRangeAddress;
import org.apache.poi.hssf.util.HSSFDataValidation;
import org.apache.poi.hssf.util.Region;
@@ -1215,12 +1216,52 @@
// Update any formulas on this sheet that point to
// rows which have been moved
-
- // Update any named ranges defined for this workbook
- // that point to this sheet and had rows they reference
- // moved
- for(int i=0; i<workbook.getNumberOfNames(); i++) {
- HSSFName name = workbook.getNameAt(i);
+ updateFormulasAfterShift(startRow, endRow, n);
+ }
+
+ /**
+ * Called by shiftRows to update formulas on this sheet
+ * to point to the new location of moved rows
+ */
+ private void updateFormulasAfterShift(int startRow, int endRow, int n) {
+ // Need to look at every cell on the sheet
+ // Not just those that were moved
+ Iterator ri = rowIterator();
+ while(ri.hasNext()) {
+ HSSFRow r = (HSSFRow)ri.next();
+ Iterator ci = r.cellIterator();
+ while(ci.hasNext()) {
+ HSSFCell c = (HSSFCell)ci.next();
+ if(c.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
+ // Since it's a formula cell, process the
+ // formula string, and look to see if
+ // it contains any references
+ FormulaParser fp = new FormulaParser(c.getCellFormula(), workbook.getWorkbook());
+ fp.parse();
+
+ // Look for references, and update if needed
+ Ptg[] ptgs = fp.getRPNPtg();
+ boolean changed = false;
+ for(int i=0; i<ptgs.length; i++) {
+ if(ptgs[i] instanceof ReferencePtg) {
+ ReferencePtg rptg = (ReferencePtg)ptgs[i];
+ if(startRow <= rptg.getRowAsInt() &&
+ rptg.getRowAsInt() <= endRow) {
+ // References a row that moved
+ rptg.setRow(rptg.getRowAsInt() + n);
+ changed = true;
+ }
+ }
+ }
+ // If any references were changed, then
+ // re-create the formula string
+ if(changed) {
+ c.setCellFormula(
+ fp.toFormulaString(ptgs)
+ );
+ }
+ }
+ }
}
}
Added: poi/trunk/src/testcases/org/apache/poi/hssf/data/ForShifting.xls
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/data/ForShifting.xls?rev=610072&view=auto
==============================================================================
Binary file - no diff available.
Propchange: poi/trunk/src/testcases/org/apache/poi/hssf/data/ForShifting.xls
------------------------------------------------------------------------------
svn:executable = *
Propchange: poi/trunk/src/testcases/org/apache/poi/hssf/data/ForShifting.xls
------------------------------------------------------------------------------
svn:mime-type = application/octet-stream
Modified: poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestSheetShiftRows.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestSheetShiftRows.java?rev=610072&r1=610071&r2=610072&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestSheetShiftRows.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestSheetShiftRows.java Tue Jan 8 09:18:38 2008
@@ -237,5 +237,48 @@
comment4_shifted = sheet.getCellComment(4,0).getString().getString();
assertEquals(comment4,comment4_shifted);
}
+
+ /**
+ * See bug #34023
+ */
+ public void testShiftWithFormulas() throws Exception {
+ String filename = System.getProperty( "HSSF.testdata.path" );
+ filename = filename + "/ForShifting.xls";
+ FileInputStream fin = new FileInputStream( filename );
+ HSSFWorkbook wb = new HSSFWorkbook( fin );
+ fin.close();
+
+ HSSFSheet sheet = wb.getSheet("Sheet1");
+ assertEquals(19, sheet.getLastRowNum());
+
+ assertEquals("cell B1 (ref)", sheet.getRow(0).getCell((short)3).getRichStringCellValue().toString());
+ assertEquals("CONCATENATE(B1,\" (ref)\")", sheet.getRow(0).getCell((short)3).getCellFormula());
+ assertEquals("cell B2 (ref)", sheet.getRow(1).getCell((short)3).getRichStringCellValue().toString());
+ assertEquals("CONCATENATE(B2,\" (ref)\")", sheet.getRow(1).getCell((short)3).getCellFormula());
+ assertEquals("cell B3 (ref)", sheet.getRow(2).getCell((short)3).getRichStringCellValue().toString());
+ assertEquals("CONCATENATE(B3,\" (ref)\")", sheet.getRow(2).getCell((short)3).getCellFormula());
+ assertEquals("cell B2 (ref)", sheet.getRow(6).getCell((short)1).getRichStringCellValue().toString());
+ assertEquals("CONCATENATE(B2,\" (ref)\")", sheet.getRow(6).getCell((short)1).getCellFormula());
+
+ sheet.shiftRows(1, 1, 10);
+
+ // Row 1 => Row 11
+ // So strings on row 11 unchanged, but reference in formula is
+ assertEquals("cell B1 (ref)", sheet.getRow(0).getCell((short)3).getRichStringCellValue().toString());
+ assertEquals("CONCATENATE(B1,\" (ref)\")", sheet.getRow(0).getCell((short)3).getCellFormula());
+ assertEquals(0, sheet.getRow(1).getPhysicalNumberOfCells());
+
+ // still save b2
+ assertEquals("cell B2 (ref)", sheet.getRow(11).getCell((short)3).getRichStringCellValue().toString());
+ // but points to b12
+ assertEquals("CONCATENATE(B12,\" (ref)\")", sheet.getRow(11).getCell((short)3).getCellFormula());
+
+ assertEquals("cell B3 (ref)", sheet.getRow(2).getCell((short)3).getRichStringCellValue().toString());
+ assertEquals("CONCATENATE(B3,\" (ref)\")", sheet.getRow(2).getCell((short)3).getCellFormula());
+
+ // one on a non-shifted row also updated
+ assertEquals("cell B2 (ref)", sheet.getRow(6).getCell((short)1).getRichStringCellValue().toString());
+ assertEquals("CONCATENATE(B12,\" (ref)\")", sheet.getRow(6).getCell((short)1).getCellFormula());
+ }
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org