java - 将 Excel 文件合并到单个工作簿中

Question

我需要将所有单独的 excel 文件复制到一个工作簿中，该工作簿由我使用 ASPOSE API 的选项卡分隔。但它是付费的。

我见过另一个 API，它是单元格到单元格的复制，但它很耗时。我找不到任何可直接从工作表复制的 API。

有什么方法可以直接从一张纸复制到另一张纸吗？

Answer 1

这是一个示例，假设一个目录包含扩展名为 .xlsx 的文件，并且每个文件都有一个工作表。

您将需要以下导入:

import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

在示例中，请阅读代码注释:

public static void main(String[] args) {
    // provide a path to a folder containing xlsx-workbooks
    Path folderWithWorkbooks = Paths.get("Y:\\our\\path\\to\\a\\folder\\with\\workbooks");
    // provide a workbook object to be written to
    Workbook resultWorkbook = new XSSFWorkbook();

    try {
        // get the file system objects in that folder
        Files.newDirectoryStream(folderWithWorkbooks).forEach(p -> {
            // and if one is an xlsx-workbook
            if (p.getFileName().toString().endsWith(".xlsx")) {
                // try to read its contents
                try (FileInputStream fis = new FileInputStream(p
                                                              .toAbsolutePath()
                                                              .toString())) {
                    // create the workbook to be parsed
                    Workbook currentWorkbook = new XSSFWorkbook(fis);
                    // get the FIRST sheet (adjust code here if you want more sheets)
                    Sheet sourceSheet = currentWorkbook.getSheetAt(0);
                    // create a new sheet in the result workbook, name pointing to its origin
                    Sheet resultSheet = resultWorkbook.createSheet("from "
                                                           + p.getFileName().toString());

                    // then classicly loop through the rows and cells and copy the contents
                    for (int r = 0; r < sourceSheet.getPhysicalNumberOfRows(); r++) {
                        Row sourceRow = sourceSheet.getRow(r);
                        Row resultRow = resultSheet.createRow(r);

                        for (int c = 0; c < sourceRow.getPhysicalNumberOfCells(); c++) {
                            Cell sourceCell = sourceRow.getCell(c);
                            Cell resultCell = resultRow.createCell(c);

                            // copy contents with respect to their types
                            switch (sourceCell.getCellType()) {
                            case NUMERIC:
                                resultCell.setCellValue(sourceCell.getNumericCellValue());
                                break;
                            case STRING:
                                resultCell.setCellValue(sourceCell.getStringCellValue());
                                break;
                            case FORMULA:
                                resultCell.setCellValue(sourceCell.getCellFormula());
                                break;
                            case BOOLEAN:
                                resultCell.setCellValue(sourceCell.getBooleanCellValue());
                                break;
                            case ERROR:
                                resultCell.setCellValue(sourceCell.getErrorCellValue());
                                break;
                            case BLANK:
                            case _NONE:
                                resultCell.setCellValue(sourceCell.getStringCellValue());
                                break;
                            }
                        }
                    }
                } catch (FileNotFoundException e) {
                    e.printStackTrace();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        });

        // write the result workbook to the same folder
        FileOutputStream fos = new FileOutputStream(folderWithWorkbooks
                .resolve("result.xlsx")
                .toAbsolutePath()
                .toString());
        resultWorkbook.write(fos);
        fos.flush();
        fos.close();
        resultWorkbook.close();
    } catch (IOException e) {
        e.printStackTrace();
    }
}

结果将是同一目录中名为 result.xlsx 的工作簿。

Please note that this does not copy any cell formatting or styles. You would have to add code for it in the section that copies the cell values.