You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by ye...@apache.org on 2009/02/08 17:35:28 UTC
svn commit: r742126 - in /poi/trunk/src: documentation/content/xdocs/
ooxml/java/org/apache/poi/xssf/usermodel/
ooxml/testcases/org/apache/poi/xssf/usermodel/
testcases/org/apache/poi/hssf/data/
Author: yegor
Date: Sun Feb 8 16:35:27 2009
New Revision: 742126
URL: http://svn.apache.org/viewvc?rev=742126&view=rev
Log:
improved XSSFSheet.shiftRows: 1. properly update cell references of the shifted cells (bugzilla 4663) 2. When shifting rows, update formulas on that sheet to point to the new location of those rows (bugzilla 46536)
Added:
poi/trunk/src/testcases/org/apache/poi/hssf/data/46536.xlsx (with props)
Modified:
poi/trunk/src/documentation/content/xdocs/changes.xml
poi/trunk/src/documentation/content/xdocs/status.xml
poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java
poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java
poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java
poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheet.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=742126&r1=742125&r2=742126&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/changes.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/changes.xml Sun Feb 8 16:35:27 2009
@@ -37,6 +37,8 @@
<!-- Don't forget to update status.xml too! -->
<release version="3.5-beta5" date="2008-??-??">
+ <action dev="POI-DEVELOPERS" type="fix">46536 - When shifting rows, update formulas on that sheet to point to the new location of those rows</action>
+ <action dev="POI-DEVELOPERS" type="fix">46663 - Fixed XSSFSheet.shiftRows to properly update references of the shifted cells</action>
<action dev="POI-DEVELOPERS" type="fix">46535 - Remove reference from calculation chain when a formula is deleted</action>
<action dev="POI-DEVELOPERS" type="fix">46654 - HSSFRow/RowRecord to properly update cell boundary indexes</action>
<action dev="POI-DEVELOPERS" type="fix">46643 - Fixed formula parser to encode range operator with tMemFunc</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=742126&r1=742125&r2=742126&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/status.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/status.xml Sun Feb 8 16:35:27 2009
@@ -34,6 +34,8 @@
<!-- Don't forget to update changes.xml too! -->
<changes>
<release version="3.5-beta5" date="2008-??-??">
+ <action dev="POI-DEVELOPERS" type="fix">46536 - When shifting rows, update formulas on that sheet to point to the new location of those rows</action>
+ <action dev="POI-DEVELOPERS" type="fix">46663 - Fixed XSSFSheet.shiftRows to properly update references of the shifted cells</action>
<action dev="POI-DEVELOPERS" type="fix">46535 - Remove reference from calculation chain when a formula is deleted</action>
<action dev="POI-DEVELOPERS" type="fix">46654 - HSSFRow/RowRecord to properly update cell boundary indexes</action>
<action dev="POI-DEVELOPERS" type="fix">46643 - Fixed formula parser to encode range operator with tMemFunc</action>
Modified: poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java?rev=742126&r1=742125&r2=742126&view=diff
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java (original)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java Sun Feb 8 16:35:27 2009
@@ -826,17 +826,4 @@
return cell;
}
- /**
- * update cell reference when shifting rows
- *
- * @param row
- */
- protected void modifyCellReference(XSSFRow row) {
- this.cell.setR(new CellReference(row.getRowNum(), cellNum).formatAsString());
-
- CTCell[] ctCells = row.getCTRow().getCArray();
- for (CTCell ctCell : ctCells) {
- ctCell.setR(new CellReference(row.getRowNum(), cellNum).formatAsString());
- }
- }
}
Modified: poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java?rev=742126&r1=742125&r2=742126&view=diff
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java (original)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java Sun Feb 8 16:35:27 2009
@@ -21,8 +21,16 @@
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
+import org.apache.poi.ss.util.CellReference;
+import org.apache.poi.ss.formula.FormulaParser;
+import org.apache.poi.ss.formula.FormulaType;
+import org.apache.poi.ss.formula.FormulaRenderer;
+import org.apache.poi.xssf.model.CalculationChain;
+import org.apache.poi.hssf.record.formula.Ptg;
+import org.apache.poi.hssf.record.SharedFormulaRecord;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTRow;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCellFormula;
/**
* High level representation of a row of a spreadsheet.
@@ -391,4 +399,61 @@
public String toString(){
return row.toString();
}
+
+ /**
+ * update cell references when shifting rows
+ *
+ * @param n the number of rows to move
+ */
+ protected void shift(int n) {
+ XSSFSheet sheet = getSheet();
+ CalculationChain calcChain = sheet.getWorkbook().getCalculationChain();
+ int rownum = getRowNum() + n;
+ for(Cell c : this){
+ XSSFCell cell = (XSSFCell)c;
+
+ //remove the reference in the calculation chain
+ if(calcChain != null) calcChain.removeItem((int)sheet.sheet.getSheetId(), cell.getReference());
+
+ CTCell ctCell = cell.getCTCell();
+ String r = new CellReference(rownum, cell.getColumnIndex()).formatAsString();
+ ctCell.setR(r);
+
+ if(ctCell.isSetF()){
+ CTCellFormula f = ctCell.getF();
+ String fmla = f.getStringValue();
+ if(fmla.length() > 0) {
+ String shiftedFmla = shiftFormula(fmla, n);
+ f.setStringValue(shiftedFmla);
+ }
+ if(f.isSetRef()){ //Range of cells which the formula applies to.
+ String ref = f.getRef();
+ String shiftedRef = shiftFormula(ref, n);
+ f.setRef(shiftedRef);
+ }
+ }
+ }
+ setRowNum(rownum);
+ }
+
+ /**
+ * Shift a formula by the specified number of rows
+ * <p>
+ * Example: shiftFormula("A1+B1+C1", 3) will return "A4+B4+C4"
+ * </p>
+ *
+ * @param formula the formula to shift
+ * @param n the number of rows to shift
+ * @return the shifted formula
+ */
+ private String shiftFormula(String formula, int n){
+ XSSFSheet sheet = getSheet();
+ XSSFWorkbook wb = sheet.getWorkbook();
+ int sheetIndex = wb.getSheetIndex(sheet);
+ XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(wb);
+ Ptg[] ptgs = FormulaParser.parse(formula, fpb, FormulaType.CELL, sheetIndex);
+ Ptg[] fmla = SharedFormulaRecord.convertSharedFormulas(ptgs, n, 0);
+ return FormulaRenderer.toFormulaString(fpb, fmla);
+ }
+
}
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=742126&r1=742125&r2=742126&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 Sun Feb 8 16:35:27 2009
@@ -24,14 +24,17 @@
import javax.xml.namespace.QName;
import org.apache.poi.hssf.util.PaneInformation;
-import org.apache.poi.ss.usermodel.CellStyle;
-import org.apache.poi.ss.usermodel.Footer;
-import org.apache.poi.ss.usermodel.Header;
-import org.apache.poi.ss.usermodel.Row;
-import org.apache.poi.ss.usermodel.Sheet;
+import org.apache.poi.hssf.record.formula.Ptg;
+import org.apache.poi.hssf.record.SharedFormulaRecord;
+import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
+import org.apache.poi.ss.util.AreaReference;
+import org.apache.poi.ss.formula.FormulaParser;
+import org.apache.poi.ss.formula.FormulaType;
+import org.apache.poi.ss.formula.FormulaRenderer;
import org.apache.poi.xssf.model.CommentsTable;
+import org.apache.poi.xssf.model.CalculationChain;
import org.apache.poi.xssf.usermodel.helpers.ColumnHelper;
import org.apache.poi.POIXMLDocumentPart;
import org.apache.poi.POIXMLException;
@@ -1438,7 +1441,7 @@
*/
public void shiftRows(int startRow, int endRow, int n, boolean copyRowHeight, boolean resetOriginalRowHeight) {
for (Iterator<Row> it = rowIterator() ; it.hasNext() ; ) {
- Row row = it.next();
+ XSSFRow row = (XSSFRow)it.next();
if (!copyRowHeight) {
row.setHeight((short)-1);
@@ -1451,10 +1454,7 @@
it.remove();
}
else if (row.getRowNum() >= startRow && row.getRowNum() <= endRow) {
- row.setRowNum(row.getRowNum() + n);
- if (row.getFirstCellNum() > -1) {
- modifyCellReference((XSSFRow) row);
- }
+ row.shift(n);
}
}
//rebuild the rows map
@@ -1463,16 +1463,6 @@
rows = map;
}
-
- private void modifyCellReference(XSSFRow row) {
- for (int i = row.getFirstCellNum(); i <= row.getLastCellNum(); i++) {
- XSSFCell c = row.getCell(i);
- if (c != null) {
- c.modifyCellReference(row);
- }
- }
- }
-
/**
* Location of the top left visible cell Location of the top left visible cell in the bottom right
* pane (when in Left-to-Right mode).
Modified: poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheet.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheet.java?rev=742126&r1=742125&r2=742126&view=diff
==============================================================================
--- poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheet.java (original)
+++ poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheet.java Sun Feb 8 16:35:27 2009
@@ -27,16 +27,11 @@
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.model.CommentsTable;
import org.apache.poi.xssf.model.StylesTable;
+import org.apache.poi.xssf.model.CalculationChain;
import org.apache.poi.xssf.usermodel.helpers.ColumnHelper;
import org.apache.poi.xssf.XSSFTestDataSamples;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
-import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCol;
-import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCols;
-import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTComments;
-import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTRow;
-import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet;
-import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTXf;
-import org.openxmlformats.schemas.spreadsheetml.x2006.main.STPane;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.*;
public class TestXSSFSheet extends TestCase {
@@ -608,7 +603,46 @@
assertNull(sheet6.getRow(7));
assertEquals(8, sheet6.getPhysicalNumberOfRows());
}
-
+
+ /**
+ * When shifting rows, update formulas on that sheet to point to the new location of those rows
+ * (see bugzilla 46536)
+ */
+ public void testShiftRows_46536() {
+ XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("46536.xlsx");
+ CalculationChain calcChain = wb.getCalculationChain();
+ int numItems = calcChain.getCTCalcChain().getCArray().length;
+ assertEquals(3, numItems);
+
+ XSSFSheet sheet = wb.getSheet("Test");
+ XSSFRow row2 = sheet.getRow(1);
+ XSSFCell cell_A2 = row2.getCell(0);
+ assertEquals("A2", cell_A2.getReference());
+
+ XSSFRow row3 = sheet.getRow(2);
+ XSSFCell cell_B3 = row3.getCell(1);
+ assertEquals("B3", cell_B3.getReference());
+
+ XSSFCell cell_E2 = row2.getCell(4);
+ CTCellFormula f = cell_E2.getCTCell().getF();
+ assertEquals("B2+C2+D2", f.getStringValue());
+ assertEquals("E2:E3", f.getRef());
+
+ sheet.shiftRows(1, sheet.getLastRowNum(), 3, false, true);
+
+ assertEquals(4, row2.getRowNum());
+ assertEquals(5, row3.getRowNum());
+ assertEquals("A5", cell_A2.getReference());
+ assertEquals("B6", cell_B3.getReference());
+
+ assertEquals("B5+C5+D5", f.getStringValue());
+ assertEquals("E5:E6", f.getRef());
+
+ numItems = calcChain.getCTCalcChain().getCArray().length;
+ assertEquals(1, numItems);
+
+ }
+
public void testGetCellComment() {
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet();
Added: poi/trunk/src/testcases/org/apache/poi/hssf/data/46536.xlsx
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/data/46536.xlsx?rev=742126&view=auto
==============================================================================
Binary file - no diff available.
Propchange: poi/trunk/src/testcases/org/apache/poi/hssf/data/46536.xlsx
------------------------------------------------------------------------------
svn:executable = *
Propchange: poi/trunk/src/testcases/org/apache/poi/hssf/data/46536.xlsx
------------------------------------------------------------------------------
svn:mime-type = application/octet-stream
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org