java - 在使用 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();
    }

标签： javaapache-poi

Answer 1

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

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

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