时间戳

首页 > 解决方案 > 如何使用 POI Apache 将工作表从 Excel 文件单元弹出式文档复制到另一个工作表？

java - 如何使用 POI Apache 将工作表从 Excel 文件单元弹出式文档复制到另一个工作表？

问题描述

我正在尝试从两个不同的 XSSF 工作簿中复制一张工作表。我编写了一个运行良好的代码（见下文），但它没有考虑单元格的弹出文档并且没有复制它（这是合乎逻辑的，因为我没有编写那个功能）。我已经搜索了 Apache POI 文档，看起来没有办法做到这一点。关于如何复制粘贴该信息的任何想法？

public class CopySheets {


    public static void copySheet(String sheetName, XSSFWorkbook wbSrc,XSSFWorkbook wbDest) throws IOException {
        XSSFSheet srcSheet = null;
        XSSFRow srcRow = null;
        XSSFCell srcCell = null;
        XSSFSheet destSheet = null;
        XSSFRow destRow = null;
        XSSFCell destCell = null;
        int fCell = 0;
        int lCell = 0;
        int fRow = 0;
        int lRow = 0;
        System.out.println(sheetName);
        srcSheet = wbSrc.getSheet(sheetName);
        System.out.println(srcSheet);
            if (srcSheet != null) {
                destSheet = wbDest.createSheet(sheetName);
                fRow = srcSheet.getFirstRowNum();
                lRow = srcSheet.getLastRowNum();
                for (int iRow = fRow; iRow <= lRow; iRow++) {
                    srcRow = srcSheet.getRow(iRow);
                    destRow = destSheet.createRow(iRow);
                    if (srcRow != null) {
                        fCell = srcRow.getFirstCellNum();
                        lCell = srcRow.getLastCellNum();
                        for (int iCell = fCell; iCell < lCell; iCell++) {
                            srcCell = srcRow.getCell(iCell);
                            destCell = destRow.createCell(iCell);
                            if (srcCell != null) {
                                switch (srcCell.getCellTypeEnum()) {
                                case BLANK:
                                    destCell.setCellValue("");
                                    break;

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

                                case ERROR:
                                    destCell.setCellErrorValue(srcCell.getErrorCellValue());
                                    break;

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

                                case NUMERIC:
                                    destCell.setCellValue(srcCell.getNumericCellValue());
                                    break;

                                case STRING:
                                    destCell.setCellValue(srcCell.getStringCellValue());
                                    break;
                                default:
                                    destCell.setCellFormula(srcCell.getCellFormula());
                                }
                                CellStyle origStyle = srcCell.getCellStyle(); // Or from a cell
                                CellStyle newStyle = wbDest.createCellStyle();
                                newStyle.cloneStyleFrom(origStyle);
                                destCell.setCellStyle(newStyle);
                                
                                Comment origComment=srcCell.getCellComment();
                                destCell.setCellComment(origComment);
                                
                                
                                
                            }
                        }
                    }
                }
            }
        BufferedOutputStream bos = new BufferedOutputStream(
        new FileOutputStream("workbook.xls", true));
        wbDest.write(bos);
        bos.close();
    }
}

非常感谢！

标签： javaapache-poi

解决方案

您应该使用 RangeCopier。

XSSFWorkbook workbookFrom = new XSSFWorkbook(new File("/path/to/workbookFrom.xlsx"));
XSSFSheet sheetFrom = workbookFrom.getSheetAt(0);

XSSFWorkbook workbookTo = new XSSFWorkbook(new File("/path/to/workbookTo.xlsx"));
XSSFSheet sheetTo = workbookTo.createSheet("sheet1");
workbookTo.setSheetOrder("sheet1", 0);

XSSFRangeCopier xssfRangeCopier = new XSSFRangeCopier(sheetFrom, sheetTo);
//copy the row height and column width, and find the max column num
int lastRow = sheetFrom.getLastRowNum();
int lastCol = 0;
for (int i = 0; i < lastRow; i++) {
    Row row = sheetFrom.getRow(i);
    if (row != null) {
        if (row.getLastCellNum() > lastCol) {
            lastCol = row.getLastCellNum();
        }
        sheetTo.setDefaultRowHeight(sheetFrom.getDefaultRowHeight());
    }
}

for (int j = 0; j < lastCol; j++) {
    sheetTo.setColumnWidth(j, sheetFrom.getColumnWidth(j));
}

//copy contents from source sheet to destination sheet
CellRangeAddress cellAddresses = new CellRangeAddress(0, lastRow, 0, lastCol);
xssfRangeCopier.copyRange(cellAddresses, cellAddresses, true, true);

workbookTo.write(new FileOutputStream(new File("/path/to/worksheetTo.xlsx")));

推荐阅读