Author: fanningpj
Date: Tue Aug 16 17:37:35 2022
New Revision: 1903464
URL: http://svn.apache.org/viewvc?rev=1903464&view=rev
Log:
[bug-66215] try to fix formulas in tables after row/column shifting
Modified:
poi/trunk/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFSheet.java
poi/trunk/poi-ooxml/src/test/java/org/apache/poi/ss/tests/formula/TestStructuredReferences.java
Modified:
poi/trunk/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFSheet.java
URL:
http://svn.apache.org/viewvc/poi/trunk/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFSheet.java?rev=1903464&r1=1903463&r2=1903464&view=diff
==============================================================================
---
poi/trunk/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFSheet.java
(original)
+++
poi/trunk/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFSheet.java
Tue Aug 16 17:37:35 2022
@@ -3042,6 +3042,12 @@ public class XSSFSheet extends POIXMLDoc
*/
@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 POIXMLDoc
rowShifter.updateHyperlinks(formulaShifter);
rebuildRows();
+
+ for (XSSFTable table : overlappingTables) {
+ rebuildTableFormulas(table);
+ }
}
/**
@@ -3070,6 +3080,12 @@ public class XSSFSheet extends POIXMLDoc
*/
@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 POIXMLDoc
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() {
Modified:
poi/trunk/poi-ooxml/src/test/java/org/apache/poi/ss/tests/formula/TestStructuredReferences.java
URL:
http://svn.apache.org/viewvc/poi/trunk/poi-ooxml/src/test/java/org/apache/poi/ss/tests/formula/TestStructuredReferences.java?rev=1903464&r1=1903463&r2=1903464&view=diff
==============================================================================
---
poi/trunk/poi-ooxml/src/test/java/org/apache/poi/ss/tests/formula/TestStructuredReferences.java
(original)
+++
poi/trunk/poi-ooxml/src/test/java/org/apache/poi/ss/tests/formula/TestStructuredReferences.java
Tue Aug 16 17:37:35 2022
@@ -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());
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
