From a38a5c3a0bfbed99f93de62a6da3c335c7a4b297 Mon Sep 17 00:00:00 2001 From: PJ Fanning Date: Sat, 13 Aug 2022 16:35:05 +0000 Subject: [PATCH] [bug-66213] clone tables while cloning sheet. Thanks to Axel Richter. git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1903396 13f79535-47bb-0310-9956-ffa450edef68 --- .../apache/poi/xssf/usermodel/XSSFSheet.java | 125 ++++++++++++++++++ .../poi/xssf/usermodel/XSSFWorkbook.java | 1 + .../poi/xssf/usermodel/TestXSSFTable.java | 26 +++- 3 files changed, 146 insertions(+), 6 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 3e19fe2416..6cb0a79096 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 @@ -4872,4 +4872,129 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet, OoxmlSheetEx public void setDimensionOverride(CellRangeAddress dimension) { this.dimensionOverride = dimension; } + + static void cloneTables(XSSFSheet sheet) { + for (XSSFTable table : sheet.getTables()) { + // clone table + XSSFTable clonedTable = sheet.createTable(table.getArea()); + clonedTable.updateHeaders(); + + // clone style + clonedTable.setStyleName(table.getStyleName()); + XSSFTableStyleInfo style = (XSSFTableStyleInfo)table.getStyle(); + XSSFTableStyleInfo clonedStyle = (XSSFTableStyleInfo)clonedTable.getStyle(); + if (style != null && clonedStyle != null) { + clonedStyle.setShowColumnStripes(style.isShowColumnStripes()); + clonedStyle.setShowRowStripes(style.isShowRowStripes()); + clonedStyle.setFirstColumn(style.isShowFirstColumn()); + clonedStyle.setLastColumn(style.isShowLastColumn()); + } + + //clone autofilter + clonedTable.getCTTable().setAutoFilter(table.getCTTable().getAutoFilter()); + + //clone totalsrow + int totalsRowCount = table.getTotalsRowCount(); + if (totalsRowCount == 1) { // never seen more than one totals row + XSSFRow totalsRow = sheet.getRow(clonedTable.getEndCellReference().getRow()); + if (clonedTable.getCTTable().getTableColumns().getTableColumnList().size() > 0) { + clonedTable.getCTTable().setTotalsRowCount(totalsRowCount); + for (int i = 0; i < clonedTable.getCTTable().getTableColumns().getTableColumnList().size(); i++) { + CTTableColumn tableCol = table.getCTTable().getTableColumns().getTableColumnList().get(i); + CTTableColumn clonedTableCol = clonedTable.getCTTable().getTableColumns().getTableColumnList().get(i); + clonedTableCol.setTotalsRowFunction(tableCol.getTotalsRowFunction()); + int intTotalsRowFunction = clonedTableCol.getTotalsRowFunction().intValue(); + sheet.getWorkbook().setCellFormulaValidation(false); + if (intTotalsRowFunction == 10) { //custom + CTTableFormula totalsRowFormula = tableCol.getTotalsRowFormula(); + clonedTableCol.setTotalsRowFormula(totalsRowFormula); + totalsRow.getCell(clonedTable.getStartCellReference().getCol()+i).setCellFormula(totalsRowFormula.getStringValue()); + } else if (intTotalsRowFunction == 1) { //none + //totalsRow.getCell(clonedTable.getStartCellReference().getCol()+i).setBlank(); + } else { + String subtotalFormulaStart = getSubtotalFormulaStartFromTotalsRowFunction(intTotalsRowFunction); + if (subtotalFormulaStart != null) + totalsRow.getCell(clonedTable.getStartCellReference().getCol()+i).setCellFormula(subtotalFormulaStart + "," + clonedTable.getName() +"[" + clonedTableCol.getName()+ "])"); + } + } + } + } + + // clone calculated column formulas + if (clonedTable.getCTTable().getTableColumns().getTableColumnList().size() > 0) { + clonedTable.getCTTable().setTotalsRowCount(totalsRowCount); + for (int i = 0; i < clonedTable.getCTTable().getTableColumns().getTableColumnList().size(); i++) { + CTTableColumn tableCol = table.getCTTable().getTableColumns().getTableColumnList().get(i); + CTTableColumn clonedTableCol = clonedTable.getCTTable().getTableColumns().getTableColumnList().get(i); + if (tableCol.getCalculatedColumnFormula() != null) { + clonedTableCol.setCalculatedColumnFormula(tableCol.getCalculatedColumnFormula()); + CTTableFormula calculatedColumnFormula = clonedTableCol.getCalculatedColumnFormula(); + String formula = tableCol.getCalculatedColumnFormula().getStringValue(); + String clonedFormula = formula.replace(table.getName(), clonedTable.getName()); + calculatedColumnFormula.setStringValue(clonedFormula); + int rFirst = clonedTable.getStartCellReference().getRow() + clonedTable.getHeaderRowCount(); + int rLast = clonedTable.getEndCellReference().getRow() - clonedTable.getTotalsRowCount(); + int c = clonedTable.getStartCellReference().getCol() + i; + sheet.getWorkbook().setCellFormulaValidation(false); + for (int r = rFirst; r <= rLast; r++) { + sheet.getRow(r).getCell(c).setCellFormula(clonedFormula); + } + } + } + } + + // remove old table + String rId = sheet.getRelationId(table); + sheet.removeTable(table); + } + } + + private static String getSubtotalFormulaStartFromTotalsRowFunction(int intTotalsRowFunction) { + final int INT_NONE = 1; + final int INT_SUM = 2; + final int INT_MIN = 3; + final int INT_MAX = 4; + final int INT_AVERAGE = 5; + final int INT_COUNT = 6; + final int INT_COUNT_NUMS = 7; + final int INT_STD_DEV = 8; + final int INT_VAR = 9; + final int INT_CUSTOM = 10; + String subtotalFormulaStart = null; + switch (intTotalsRowFunction) { + case INT_NONE: + subtotalFormulaStart = null; + break; + case INT_SUM: + subtotalFormulaStart = "SUBTOTAL(109"; + break; + case INT_MIN: + subtotalFormulaStart = "SUBTOTAL(105"; + break; + case INT_MAX: + subtotalFormulaStart = "SUBTOTAL(104"; + break; + case INT_AVERAGE: + subtotalFormulaStart = "SUBTOTAL(101"; + break; + case INT_COUNT: + subtotalFormulaStart = "SUBTOTAL(103"; + break; + case INT_COUNT_NUMS: + subtotalFormulaStart = "SUBTOTAL(102"; + break; + case INT_STD_DEV: + subtotalFormulaStart = "SUBTOTAL(107"; + break; + case INT_VAR: + subtotalFormulaStart = "SUBTOTAL(110"; + break; + case INT_CUSTOM: + subtotalFormulaStart = null; + break; + default: + subtotalFormulaStart = null; + } + return subtotalFormulaStart; + } } diff --git a/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java b/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java index 38ade18345..7a588ecb0f 100644 --- a/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java +++ b/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java @@ -714,6 +714,7 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook, Date1904Su } } } + XSSFSheet.cloneTables(clonedSheet); return clonedSheet; } diff --git a/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFTable.java b/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFTable.java index 254f476399..e7f6f57173 100644 --- a/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFTable.java +++ b/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFTable.java @@ -17,12 +17,6 @@ package org.apache.poi.xssf.usermodel; -import static org.junit.jupiter.api.Assertions.assertEquals; -import static org.junit.jupiter.api.Assertions.assertFalse; -import static org.junit.jupiter.api.Assertions.assertNotNull; -import static org.junit.jupiter.api.Assertions.assertThrows; -import static org.junit.jupiter.api.Assertions.assertTrue; - import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; @@ -47,6 +41,8 @@ import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableColumn; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableStyleInfo; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet; +import static org.junit.jupiter.api.Assertions.*; + public final class TestXSSFTable { @Test @@ -738,4 +734,22 @@ public final class TestXSSFTable { assertEquals(0, sheet.getCTWorksheet().getTableParts().sizeOfTablePartArray()); } } + + @Test + void bug66213() throws IOException { + try (XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("table-sample.xlsx")) { + wb.cloneSheet(0, "Test"); + try (UnsynchronizedByteArrayOutputStream bos = new UnsynchronizedByteArrayOutputStream()) { + wb.write(bos); + try (XSSFWorkbook wb2 = new XSSFWorkbook(bos.toInputStream())) { + XSSFSheet sheet0 = wb2.getSheetAt(0); + XSSFSheet sheet1 = wb2.getSheetAt(1); + assertEquals(1, sheet0.getTables().size()); + assertEquals(1, sheet1.getTables().size()); + assertEquals("Tabelle1", sheet0.getTables().get(0).getName()); + assertEquals("Table2", sheet1.getTables().get(0).getName()); + } + } + } + } } -- 2.39.5