Bug 66213 - XSSFWorkbook.cloneSheet does not clone XSSFTables linked from the sheet
Summary: XSSFWorkbook.cloneSheet does not clone XSSFTables linked from the sheet
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-13 14:16 UTC by Axel Richter
Modified: 2022-08-19 12:55 UTC (History)
0 users


Note You need to log in before you can comment on or make changes to this bug.
Description Axel Richter 2022-08-13 14:16:38 UTC
XSSFWorkbook.cloneSheet does not clone XSSFTables linked from the sheet.
It only clones the references. So after that two different sheet ranges refer to the same table. But that leads to a corrupt workbook.

See https://stackoverflow.com/questions/73339524/xssfworkbook-clonesheet-corrupts-workbook-if-sheet-contains-a-table/73344100#73344100.
Comment 1 PJ Fanning 2022-08-13 16:35:31 UTC
I added r1903396
Comment 2 PJ Fanning 2022-08-13 17:55:34 UTC
poi-integration tests fail for 5 xlsx files when the new clone tables support is enabled.

There are at least 2 issues.

I will debug them and try to fix them.

TestAllFiles. #1719 spreadsheet/56170.xlsx XSSF
TestAllFiles. #1901 spreadsheet/61281.xlsx XSSF
TestAllFiles. #1972 spreadsheet/ConditionalFormattingSamples.xlsx XSSF
TestAllFiles. #2209 spreadsheet/SingleCellTable.xlsx XSSF
TestAllFiles. #2618 spreadsheet/xxe_in_schema.xlsx XSSF
Comment 3 PJ Fanning 2022-08-13 18:13:44 UTC
Axel - I added r1903398 to try to fix at least some of the test failures.

Looks like the original code couldn't handle some edge cases. In particular, SingleCellTable.xlsx in POI test-data may require a better solution than the one I added.
Comment 4 Axel Richter 2022-08-14 07:13:02 UTC
Not clear what you have changed and why.
I also changed my code of answer in https://stackoverflow.com/questions/73339524/xssfworkbook-clonesheet-corrupts-workbook-if-sheet-contains-a-table/73344100#73344100 to cover some edge cases:

    // clone calculated column formulas
       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);

To avoid NPEs when there is no row and/or cell present in sheet for data row in table.


   // clone table; XSSFTable.setArea fails and throws exception for too small tables
   XSSFTable clonedTable = null;
   int rowCount = (table.getArea().getLastCell().getRow() - table.getArea().getFirstCell().getRow()) + 1;
   int headerRowCount = table.getHeaderRowCount(); if (headerRowCount == 0) headerRowCount = 1;
   int minimumRowCount = 1 + headerRowCount + table.getTotalsRowCount();
   if (rowCount >= minimumRowCount) {
    clonedTable = sheet.createTable(table.getArea());
   if (clonedTable != null) {

To cover the case of single-cell-tables. XSSFTable.setArea fails for this case as it expects at least one header row and one data row per table.