将 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的工作簿。

请注意，这不会复制任何单元格格式或样式。您必须在复制单元格值的部分中为其添加代码。