使用Apache POI在工作簿中逐单元复制时保留工作表格式

Question

这是我在使用Apache POI复制工作表时保存样式/格式的第三篇文章(参见第一和第二篇)。

在逐行复制期间未能保留源工作表格式，因此我继续尝试逐单元格复制。虽然我正在从源工作表中获取内容，但所有格式都丢失了。

有什么建议吗?

---

源代码:

public static void copyXSSFSheet2(String srcFilename, String srcSheetname, String destFilename, String destSheetname)
throws IOException, InvalidFormatException {

// Create source and destination workbook objects, given the filenames
XSSFWorkbook srcWorkbook = new XSSFWorkbook(new File(srcFilename));

XSSFWorkbook destWorkbook = new XSSFWorkbook(new FileInputStream(destFilename));
// Destination workbook instantiated differently to get past the following exception:
//        org.apache.poi.ooxml.POIXMLException: java.io.EOFException:
//        Unexpected end of ZLIB input stream
// As per https://stackoverflow.com/a/54695626

// Instantiate the sheet objects
XSSFSheet srcSheet = srcWorkbook.getSheet(srcSheetname);
XSSFSheet destSheet = destWorkbook.createSheet(destSheetname);

// Iterate over the source sheet, row by row, and copy into the destination sheet
// cell by cell
int destRowNum = 0;
for (Row srcRow: srcSheet) {
XSSFRow srcXSSFRow = (XSSFRow) srcRow;
XSSFRow destXSSFRow = destSheet.createRow(destRowNum++);

int srcColNum = srcXSSFRow.getFirstCellNum();
for (Cell srcCell : srcXSSFRow) {
Cell destCell = destXSSFRow.createCell(srcColNum++);

//              CellStyle srcCellStyle = srcCell.getCellStyle();
//              destCell.setCellStyle(srcCellStyle);
// Gives the following exception:
//     java.lang.IllegalArgumentException: This Style does not belong to the supplied
//     Workbook Styles Source. Are you trying to assign a style from one workbook to
//     the cell of a different workbook?

//              CellStyle srcCellStyle = srcCell.getCellStyle();
//              CellStyle destCellStyle = new XSSFCellStyle(new StylesTable());;
//              destCellStyle.cloneStyleFrom(srcCellStyle);
//              destCell.setCellStyle(srcCellStyle);
// Gives the following exception:
//     java.lang.IllegalArgumentException: This Style does not belong to the supplied
//     Workbook Styles Source. Are you trying to assign a style from one workbook to
//     the cell of a different workbook?

switch (srcCell.getCellType()) {
case STRING:
destCell.setCellValue(srcCell.getRichStringCellValue().getString());
break;

case NUMERIC:
if (DateUtil.isCellDateFormatted(srcCell)) {
destCell.setCellValue(srcCell.getDateCellValue());
}
else {
destCell.setCellValue(srcCell.getNumericCellValue());
}
break;

case BOOLEAN:
destCell.setCellValue(srcCell.getBooleanCellValue());
break;

case FORMULA:
destCell.setCellValue(srcCell.getCellFormula());
break;

case BLANK:
destCell.setCellValue("");
break;

default:
destCell.setCellValue("");
}
}
}

// Final cleanup
srcWorkbook.close();

FileOutputStream fos = new FileOutputStream(new File(destFilename));
destWorkbook.write(fos);
destWorkbook.close();
fos.close();
}

Answer 1

您需要在目标工作簿中创建样式，如下所示:

CellStyle newCellStyle = workbook.createCellStyle(); newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); newCell.setCellStyle(newCellStyle);

但是这会为每个单元格创建一个新的CellStyle，即使它们是相同的。因此，您可能需要尝试检测它们何时相同，并重用它们