[bug-66215] try to fix formulas in tables after row/column shifting

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

2 files changed, 46 insertions, 24 deletions

diff --git a/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFSheet.java b/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFSheet.java
index 8efbfcf557..0dee5466ae 100644
--- a/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFSheet.java
+++ b/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFSheet.java
@@ -3042,6 +3042,12 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet, OoxmlSheetEx
      */
     @Override
     public void shiftRows(int startRow, int endRow, final int n, boolean copyRowHeight, boolean resetOriginalRowHeight) {
+        List<XSSFTable> overlappingTables = new ArrayList<>();
+        for (XSSFTable table : getTables()) {
+            if (table.getStartRowIndex() <= endRow || table.getEndRowIndex() >= startRow) {
+                overlappingTables.add(table);
+            }
+        }
         int sheetIndex = getWorkbook().getSheetIndex(this);
         String sheetName = getWorkbook().getSheetName(sheetIndex);
         FormulaShifter formulaShifter = FormulaShifter.createForRowShift(
@@ -3057,6 +3063,10 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet, OoxmlSheetEx
         rowShifter.updateHyperlinks(formulaShifter);
 
         rebuildRows();
+
+        for (XSSFTable table : overlappingTables) {
+            rebuildTableFormulas(table);
+        }
     }
 
     /**
@@ -3070,6 +3080,12 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet, OoxmlSheetEx
      */
     @Override
     public void shiftColumns(int startColumn, int endColumn, final int n) {
+        List<XSSFTable> overlappingTables = new ArrayList<>();
+        for (XSSFTable table : getTables()) {
+            if (table.getStartColIndex() <= endColumn || table.getEndRowIndex() >= startColumn) {
+                overlappingTables.add(table);
+            }
+        }
         XSSFVMLDrawing vml = getVMLDrawing(false);
         shiftCommentsForColumns(vml, startColumn, endColumn, n);
         FormulaShifter formulaShifter = FormulaShifter.createForColumnShift(this.getWorkbook().getSheetIndex(this), this.getSheetName(), startColumn, endColumn, n, SpreadsheetVersion.EXCEL2007);
@@ -3082,6 +3098,35 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet, OoxmlSheetEx
         columnShifter.updateNamedRanges(formulaShifter);
 
         rebuildRows();
+
+        for (XSSFTable table : overlappingTables) {
+            rebuildTableFormulas(table);
+        }
+    }
+
+    private void rebuildTableFormulas(XSSFTable table) {
+        //correct all sheet table-reference-formulas which probably got damaged after shift rows/columns
+        for (CTTableColumn tableCol : table.getCTTable().getTableColumns().getTableColumnList()) {
+            if (tableCol.getCalculatedColumnFormula() != null) {
+                int id = Math.toIntExact(tableCol.getId());
+                String formula = tableCol.getCalculatedColumnFormula().getStringValue();
+                int rFirst = table.getStartCellReference().getRow() + table.getHeaderRowCount();
+                int rLast = table.getEndCellReference().getRow() - table.getTotalsRowCount();
+                int c = table.getStartCellReference().getCol() + id - 1;
+                final boolean cellFormulaValidationFlag = getWorkbook().getCellFormulaValidation();
+                try {
+                    getWorkbook().setCellFormulaValidation(false);
+                    for (int r = rFirst; r <= rLast; r++) {
+                        XSSFRow row = getRow(r);
+                        if (row == null) row = createRow(r);
+                        XSSFCell cell = row.getCell(c, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
+                        cell.setCellFormula(formula);
+                    }
+                } finally {
+                    getWorkbook().setCellFormulaValidation(cellFormulaValidationFlag);
+                }
+            }
+        }
     }
 
     private void rebuildRows() {
diff --git a/poi-ooxml/src/test/java/org/apache/poi/ss/tests/formula/TestStructuredReferences.java b/poi-ooxml/src/test/java/org/apache/poi/ss/tests/formula/TestStructuredReferences.java
index 03510d5b9d..17b71868d1 100644
--- a/poi-ooxml/src/test/java/org/apache/poi/ss/tests/formula/TestStructuredReferences.java
+++ b/poi-ooxml/src/test/java/org/apache/poi/ss/tests/formula/TestStructuredReferences.java
@@ -173,33 +173,10 @@ class TestStructuredReferences {
             table.setArea(newTableArea);
 
             XSSFRow row4 = sheet.getRow(4);
-            //the next formula has been adjusted more than it should but seems to return correct value
-            assertEquals("Tabelle2!E5:E5/Tabelle2!E8:E8", row4.getCell(5).getCellFormula());
             XSSFRow row7 = sheet.getRow(7);
-            //the next formula is completely wrong (should be the same as the value in the row4 assertion above)
-            assertEquals("SUBTOTAL(109,Tabelle1[Percentage])", row7.getCell(5).getCellFormula());
-
-            //correct all sheet table-reference-formulas which probably got damaged after shift rows
-            for (CTTableColumn tableCol : table.getCTTable().getTableColumns().getTableColumnList()) {
-                if (tableCol.getCalculatedColumnFormula() != null) {
-                    int id = Math.toIntExact(tableCol.getId());
-                    String formula = tableCol.getCalculatedColumnFormula().getStringValue();
-                    int rFirst = table.getStartCellReference().getRow() + table.getHeaderRowCount();
-                    int rLast = table.getEndCellReference().getRow() - table.getTotalsRowCount();
-                    int c = table.getStartCellReference().getCol() + id - 1;
-                    sheet.getWorkbook().setCellFormulaValidation(false);
-                    for (int r = rFirst; r <= rLast; r++) {
-                        XSSFRow row = sheet.getRow(r);
-                        if (row == null) row = sheet.createRow(r);
-                        XSSFCell cell = row.getCell(c, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
-                        cell.setCellFormula(formula);
-                    }
-                }
-            }
 
-            //is it right that this reverts back to Tabelle1 when it was Tabelle2 for the 'correct all sheet table-reference-formulas' loop?
             assertEquals("Tabelle1[[#This Row],[Total]]/Tabelle1[[#Totals],[Total]]", row4.getCell(5).getCellFormula());
-            //the next formula is still completely wrong (should be the same as the value in the row4 assertion above)
+            //this total formula does get changed
             assertEquals("SUBTOTAL(109,Tabelle1[Percentage])", row7.getCell(5).getCellFormula());
         }
     }