如果工作表包含表，XSSFWorkbook cloneSheet会损坏工作簿。
如果工作表包含表，XSSFWorkbook cloneSheet会损坏工作簿。
提问于 2022-08-12 20:33:50
我想克隆一个Excel表格及其所有内容。我尝试了XSSFWorkbook cloneSheet方法，但是如果我的工作表包含Excel表，工作簿似乎已经损坏。请参阅下面带有简单表的试题簿：
当我试图打开输出工作簿时，会收到一个提示，告诉我该文件已损坏，需要修复。如果恢复工作簿，显然表没有正确复制；原始总计行现在是数据行。
try (InputStream is = Table.class.getResourceAsStream("table.xlsx")) {
XSSFWorkbook workbook = new XSSFWorkbook(is);
workbook.cloneSheet(0, "Test");
try (OutputStream fileOut = new FileOutputStream("table-2.xlsx")) {
workbook.write(fileOut);
}
} catch (IOException e) {
e.printStackTrace();
}我会怎么复印这张纸？任何帮助都是非常感谢的！
Stack Overflow用户
回答已采纳
发布于 2022-08-13 12:05:02
XSSFWorkbook.cloneSheet克隆一张纸。但它没有考虑其中可能定义的表。它只是复制表引用。但是工作表中的两个表范围不能引用相同的表引用。表本身需要克隆。这就是结果导致工作簿损坏的原因。
我试图通过编写一个方法cloneTables(XSSFSheet sheet)来解决这个问题，该方法简单地在一个表中创建每个表的克隆，然后每个表引用各自的表引用。我考虑表样式、自动筛选器、总计行和计算列公式.我希望我没有忽略一些东西，但我对此表示怀疑。
该代码经过测试，并使用当前的apache poi 5.2.2工作。
它还包含用于以下bug的修复：
在使用当前Excel版本创建的Excel工作簿中，XSSFTable.updateHeaders失败。这是因为测试row.getCTRow().validate()，因为新的名称空间的使用，它总是会是假的。见用Apache Poi重命名XSSFTable的头文件会导致XLSX文件损坏。。
XSSFSheet.removeTable不会从工作表中删除到表部件引用的链接。
要测试的完整示例：
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.util.*;
import org.apache.poi.ss.SpreadsheetVersion;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableColumn;
class ExcelCloneSheetHavingTable {
static void updateHeaders(XSSFTable table) {
XSSFSheet sheet = (XSSFSheet)table.getParent();
CellReference ref = table.getStartCellReference();
if (ref == null) return;
int headerRow = ref.getRow();
int firstHeaderColumn = ref.getCol();
XSSFRow row = sheet.getRow(headerRow);
DataFormatter formatter = new DataFormatter();
if (row != null /*&& row.getCTRow().validate()*/) { // see bug: https://stackoverflow.com/questions/55532006/renaming-headers-of-xssftable-with-apache-poi-leads-to-corrupt-xlsx-file/55539181#55539181
int cellnum = firstHeaderColumn;
org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableColumns ctTableColumns = table.getCTTable().getTableColumns();
if(ctTableColumns != null) {
for (org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableColumn col : ctTableColumns.getTableColumnList()) {
XSSFCell cell = row.getCell(cellnum);
if (cell != null) {
String colName = formatter.formatCellValue(cell);
colName = colName.replace("\n", "_x000a_");
colName = colName.replace("\r", "_x000d_");
col.setName(colName);
}
cellnum++;
}
}
}
//tableColumns = null;
//columnMap = null;
//xmlColumnPrs = null;
//commonXPath = null;
try {
java.lang.reflect.Field tableColumns = XSSFTable.class.getDeclaredField("tableColumns");
tableColumns.setAccessible(true);
tableColumns.set(table, null);
java.lang.reflect.Field columnMap = XSSFTable.class.getDeclaredField("columnMap");
columnMap.setAccessible(true);
columnMap.set(table, null);
java.lang.reflect.Field xmlColumnPrs = XSSFTable.class.getDeclaredField("xmlColumnPrs");
xmlColumnPrs.setAccessible(true);
xmlColumnPrs.set(table, null);
java.lang.reflect.Field commonXPath = XSSFTable.class.getDeclaredField("commonXPath");
commonXPath.setAccessible(true);
commonXPath.set(table, null);
} catch (Exception ex) {
ex.printStackTrace();
}
}
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;
}
static void cloneTables(XSSFSheet sheet) {
for (XSSFTable table : sheet.getTables()) {
// 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) {
//clonedTable.updateHeaders(); // don't work, see bug: https://stackoverflow.com/questions/55532006/renaming-headers-of-xssftable-with-apache-poi-leads-to-corrupt-xlsx-file/55539181#55539181
updateHeaders(clonedTable);
// 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++) {
org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableColumn tableCol = table.getCTTable().getTableColumns().getTableColumnList().get(i);
org.openxmlformats.schemas.spreadsheetml.x2006.main.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
org.openxmlformats.schemas.spreadsheetml.x2006.main.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++) {
org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableColumn tableCol = table.getCTTable().getTableColumns().getTableColumnList().get(i);
org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableColumn clonedTableCol = clonedTable.getCTTable().getTableColumns().getTableColumnList().get(i);
if (tableCol.getCalculatedColumnFormula() != null) {
clonedTableCol.setCalculatedColumnFormula(tableCol.getCalculatedColumnFormula());
org.openxmlformats.schemas.spreadsheetml.x2006.main.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++) {
XSSFRow row = sheet.getRow(r); if (row == null) row = sheet.createRow(r);
XSSFCell cell = row.getCell(c); if (cell == null) cell = row.createCell(c);
cell.setCellFormula(clonedFormula);
}
}
}
}
}
// remove old table; do that even if XSSFsheet.createTable failed, because a one-cell-table doesn't make any sense
String rId = sheet.getRelationId(table);
sheet.removeTable(table);
// remove links to the table part reference
org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableParts tblParts = sheet.getCTWorksheet().getTableParts();
if (tblParts != null && tblParts.getTablePartList().size() > 0) {
for (int i = 0; i < tblParts.getTablePartList().size(); i++) {
org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTablePart tblPart = tblParts.getTablePartArray(i);
if(tblPart.getId().equals(rId)) {
tblParts.removeTablePart(i);
}
}
}
}
}
public static void main(String[] args) throws Exception {
try (Workbook workbook = WorkbookFactory.create(new FileInputStream("SAMPLE.xlsx"));
FileOutputStream out = new FileOutputStream("SAMPLE_NEW.xlsx")) {
XSSFSheet sheet = ((XSSFWorkbook)workbook).cloneSheet(0, "Test");
cloneTables(sheet);
workbook.write(out);
}
}
}这段代码需要所有模式的完整jar，这是poi-ooxml-full-5.2.2.jar for apache poi 5.2.2，如常见问题中提到的。注意，因为apache poi 5.*以前使用的ooxml-schemas-*.jar不能再使用了。当使用时，类路径中必须有而不是ooxml-schemas-*.jar。
注意，你需要这个额外的。文件名poi-ooxml-5.2.2.jar和poi-ooxml-lite-5.2.2.jar各自的poi-ooxml-full-5.2.2.jar具有误导性。*-lite*.jar各自的*-full*.jar不替换，而是对poi-ooxml-5.2.2.jar进行补充。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/73339524
复制相关文章
相似问题
腾讯云开发者
