refactored XSSFSheet.shiftRows to use FormulaShifter, use a common test superclass for both hssf and xssf

git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@744750 13f79535-47bb-0310-9956-ffa450edef68

4 files changed, 85 insertions, 17 deletions

diff --git a/src/ooxml/java/org/apache/poi/POIXMLDocumentPart.java b/src/ooxml/java/org/apache/poi/POIXMLDocumentPart.java
index f01cae8ce4..aa7f72df19 100755
--- a/src/ooxml/java/org/apache/poi/POIXMLDocumentPart.java
+++ b/src/ooxml/java/org/apache/poi/POIXMLDocumentPart.java
@@ -128,6 +128,15 @@ public class POIXMLDocumentPart {
     }

 

     /**

+     * Remove the specified part in this package.

+     */

+    public final void removeRelation(POIXMLDocumentPart part){

+        getPackagePart().removeRelationship(part.getPackageRelationship().getId());

+        getPackagePart().getPackage().removePart(part.getPackagePart());

+        relations.remove(part);

+    }

+

+    /**

      * Returns the parent POIXMLDocumentPart. All parts except root have not-null parent.

      *

      * @return the parent POIXMLDocumentPart or <code>null</code> for the root element.

diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java
index 88563ec268..7c8ff976a1 100644
--- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java
+++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java
@@ -27,7 +27,10 @@ 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.formula.FormulaShifter;
 import org.apache.poi.hssf.record.SharedFormulaRecord;
+import org.apache.poi.util.POILogger;
+import org.apache.poi.util.POILogFactory;
 import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell;
 import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTRow;
 import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCellFormula;
@@ -36,6 +39,7 @@ import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCellFormula;
  * High level representation of a row of a spreadsheet.
  */
 public class XSSFRow implements Row, Comparable<XSSFRow> {
+    private static final POILogger logger = POILogFactory.getLogger(XSSFRow.class);
 
     private static final String FILE_FORMAT_NAME  = "BIFF12";
     /**
@@ -406,34 +410,45 @@ public class XSSFRow implements Row, Comparable<XSSFRow> {
      * @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;
+        CalculationChain calcChain = sheet.getWorkbook().getCalculationChain();
+        int sheetId = (int)sheet.sheet.getSheetId();
         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());
+            if(calcChain != null) calcChain.removeItem(sheetId, cell.getReference());
 
             CTCell ctCell = cell.getCTCell();
             String r = new CellReference(rownum, cell.getColumnIndex()).formatAsString();
             ctCell.setR(r);
+        }
+        setRowNum(rownum);
+    }
+
+    protected void updateFormulasAfterCellShift(FormulaShifter shifter) {
+        for(Cell c : this){
+            XSSFCell cell = (XSSFCell)c;
 
+            CTCell ctCell = cell.getCTCell();
             if(ctCell.isSetF()){
                 CTCellFormula f = ctCell.getF();
-                String fmla = f.getStringValue();
-                if(fmla.length() > 0) {
-                    String shiftedFmla = shiftFormula(fmla, n);
-                    f.setStringValue(shiftedFmla);
+                String formula = f.getStringValue();
+                if(formula.length() > 0) {
+                    String shiftedFormula = shiftFormula(formula, shifter);
+                    if (shiftedFormula != null) {
+                        f.setStringValue(shiftedFormula);
+                    }
                 }
+
                 if(f.isSetRef()){ //Range of cells which the formula applies to.
                     String ref = f.getRef();
-                    String shiftedRef = shiftFormula(ref, n);
-                    f.setRef(shiftedRef);
+                    String shiftedRef = shiftFormula(ref, shifter);
+                    if(shiftedRef != null) f.setRef(shiftedRef);
                 }
             }
+
         }
-        setRowNum(rownum);
     }
 
     /**
@@ -443,17 +458,21 @@ public class XSSFRow implements Row, Comparable<XSSFRow> {
      * </p>
      *
      * @param formula the formula to shift
-     * @param n the number of rows to shift
-     * @return the shifted formula
+     * @param shifter the FormulaShifter object that operates on the parsed formula tokens
+     * @return the shifted formula if the formula was changed,
+     *   <code>null</code> if the formula wasn't modified
      */
-    private String shiftFormula(String formula, int n){
+    private String shiftFormula(String formula, FormulaShifter shifter){
         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);
+        String shiftedFmla = null;
+        if (shifter.adjustFormula(ptgs, sheetIndex)) {
+            shiftedFmla = FormulaRenderer.toFormulaString(fpb, ptgs);
+        }
+        return shiftedFmla;
     }
 
 }
diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java
index 90f920c3e7..e44208b75e 100644
--- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java
+++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java
@@ -25,6 +25,7 @@ import javax.xml.namespace.QName;
 
 import org.apache.poi.hssf.util.PaneInformation;
 import org.apache.poi.hssf.record.formula.Ptg;
+import org.apache.poi.hssf.record.formula.FormulaShifter;
 import org.apache.poi.hssf.record.SharedFormulaRecord;
 import org.apache.poi.ss.usermodel.*;
 import org.apache.poi.ss.util.CellRangeAddress;
@@ -60,7 +61,7 @@ import org.openxmlformats.schemas.officeDocument.x2006.relationships.STRelations
  * </p>
  */
 public class XSSFSheet extends POIXMLDocumentPart implements Sheet {
-    private static POILogger logger = POILogFactory.getLogger(XSSFSheet.class);
+    private static final POILogger logger = POILogFactory.getLogger(XSSFSheet.class);
 
     /**
      * Column width measured as the number of characters of the maximum digit width of the
@@ -1442,6 +1443,7 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet {
     public void shiftRows(int startRow, int endRow, int n, boolean copyRowHeight, boolean resetOriginalRowHeight) {
         for (Iterator<Row> it = rowIterator() ; it.hasNext() ; ) {
             XSSFRow row = (XSSFRow)it.next();
+            int rownum = row.getRowNum();
 
             if (!copyRowHeight) {
                 row.setHeight((short)-1);
@@ -1456,11 +1458,39 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet {
             else if (row.getRowNum() >= startRow && row.getRowNum() <= endRow) {
                 row.shift(n);
             }
+
+            if(sheetComments != null){
+                //TODO shift Note's anchor in the associated /xl/drawing/vmlDrawings#.vml
+                CTCommentList lst = sheetComments.getCTComments().getCommentList();
+                for (CTComment comment : lst.getCommentArray()) {
+                    CellReference ref = new CellReference(comment.getRef());
+                    if(ref.getRow() == rownum){
+                        ref = new CellReference(rownum + n, ref.getCol());
+                        comment.setRef(ref.formatAsString());
+                    }
+                }
+            }
         }
         //rebuild the rows map
+        int sheetIndex = getWorkbook().getSheetIndex(this);
+        FormulaShifter shifter = FormulaShifter.createForRowShift(sheetIndex, startRow, endRow, n);
         TreeMap<Integer, Row> map = new TreeMap<Integer, Row>();
-        for(Row r : this) map.put(r.getRowNum(), r);
+        for(Row r : this) {
+            XSSFRow row = (XSSFRow)r;
+            row.updateFormulasAfterCellShift(shifter);
+            map.put(r.getRowNum(), r);
+        }
         rows = map;
+
+        //update formulas on other sheets
+        for(XSSFSheet sheet : getWorkbook()) {
+            if (sheet == this) continue;
+            for(Row r : sheet) {
+                XSSFRow row = (XSSFRow)r;
+                row.updateFormulasAfterCellShift(shifter);
+            }
+        }
+
     }
 
     /**
diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java
index ce1724aed1..c2cdcd06d4 100644
--- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java
+++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java
@@ -1068,12 +1068,22 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook, Iterable<X
                 workbook.unsetDefinedNames();
             }
         }
+    }
 
+    private void saveCalculationChain(){
+        if(calcChain != null){
+            int count = calcChain.getCTCalcChain().getCArray().length;
+            if(count == 0){
+                removeRelation(calcChain);
+                calcChain = null;
+            }
+        }
     }
 
     @Override
     protected void commit() throws IOException {
         saveNamedRanges();
+        saveCalculationChain();
 
         XmlOptions xmlOptions = new XmlOptions(DEFAULT_XML_OPTIONS);
         xmlOptions.setSaveSyntheticDocumentElement(new QName(CTWorkbook.type.getName().getNamespaceURI(), "workbook"));