Bug 66215 - Versions greater than 5.1 damages structured references while XSSFsheet.shiftRows
Summary: Versions greater than 5.1 damages structured references while XSSFsheet.shift...
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 5.2.2-FINAL
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2022-08-15 15:13 UTC by Axel Richter
Modified: 2022-08-18 23:16 UTC (History)
0 users

Sample Java code and sample *.xlsx file (8.92 KB, application/x-zip-compressed)
2022-08-15 15:13 UTC, Axel Richter

Note You need to log in before you can comment on or make changes to this bug.
Description Axel Richter 2022-08-15 15:13:17 UTC
Created attachment 38366 [details]
Sample Java code and sample *.xlsx file

Take the code of BugShiftRowsInXSSFTables.java from attachment jaba.zip together with the also attached SAMPLE.xlsx. Run the code.

After that you see the formulas in table column Percentage of SAMPLE_NEW.xlsx are damaged. 

This is the same problem as in https://bz.apache.org/bugzilla/show_bug.cgi?id=66039 but in this case usage of XSSFWorkbook.setCellFormulaValidation(false) does not help.
Comment 1 PJ Fanning 2022-08-15 18:43:51 UTC
I've added r1903440 - basically the provided test case without mods to main code.

I may be wrong but I think the problems exhibited here relate to XSSF tables and the formulas in those tables are dealt with differently from cells outside tables (when cells are shifted).

The formula shifter is not logic I am in a hurry to change - it is complicated enough already.

If anyone wants to try to fix the formula shifter, feel free to take this up.
Comment 2 Axel Richter 2022-08-16 06:04:09 UTC
Formula shifter seems to work on Ptg-level. Me not able fully understand this though. All I can contribute is a work around. See new code in https://stackoverflow.com/questions/52877212/expanding-an-existing-table-in-excel-using-apache-poi/52904452#52904452.

   if (totalsRowCount > 0) {
    //if we have totals rows, shift totals rows down
    sheet.shiftRows(lastTableDataRow, lastTableRow, 1);
    //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 = (int)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;
      for (int r = rFirst; r <= rLast; r++) {
       XSSFRow row = sheet.getRow(r); if (row == null) row = sheet.createRow(r);
       XSSFCell cell = row.getCell(c); if (cell == null) cell = row.createCell(c);

Maybe this can be implemented after shiftRows shiftted rows of a XSSFTable, if that can be detected somehow.
Comment 3 PJ Fanning 2022-08-16 13:19:44 UTC
I added your code just in the test case for now - r1903458. The total row in the table does not seem to get corrected. The table name gets changed back to Tabelle1 in the other formulas when it had been changed to Tabelle2 after initial formula shifting - which of these is correct?
Comment 4 Axel Richter 2022-08-16 14:42:40 UTC
In my workbook "Tabelle2" is the sheet name and "Tabelle1" is the table name. The formula shifter damages the structured column formula Tabelle1[[#This Row],[Total]]/Tabelle1[[#Totals],[Total]]. It sets the sheet formula Tabelle2!E5:E5/Tabelle2!E8:E8 in row 5 of the sheet. My workaround repairs  this using the also stored column formula from table definition: <calculatedColumnFormula>Tabelle1[[#This Row],[Total]]/Tabelle1[[#Totals],[Total]]</calculatedColumnFormula>. 

One cannot detect such problems by JUnit tests only. One needs testing using real Excel files opened in real Excel applications.
Comment 5 PJ Fanning 2022-08-16 17:39:56 UTC
I've applied some changes via r1903464

This code tries to work out which tables are have overlapping areas with the areas  being moved when row or column shifting happens and applies Alex's code to correct the formulas for the area inside the overlapping tables.