[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
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 8efbfcf..0dee546 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 @@
*/
@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 @@
rowShifter.updateHyperlinks(formulaShifter);
rebuildRows();
+
+ for (XSSFTable table : overlappingTables) {
+ rebuildTableFormulas(table);
+ }
}
/**
@@ -3070,6 +3080,12 @@
*/
@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 @@
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 03510d5..17b7186 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 @@
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());
}
}
