Java 多个 Excel 文件合并指南
在 Java 中合并多个 Excel 文件是一个常见的需求，可以使用多种库来实现,以下是几种常用的方法：
（图片来源网络，侵删）
使用 Apache POI
Apache POI 是处理 Office 文件（包括 Excel）最流行的 Java 库。
基本合并方法
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
public class ExcelMerger {
public static void main(String[] args) {
List<String> inputFiles = new ArrayList<>();
inputFiles.add("file1.xlsx");
inputFiles.add("file2.xlsx");
inputFiles.add("file3.xlsx");
String outputFile = "merged_output.xlsx";
mergeExcelFiles(inputFiles, outputFile);
System.out.println("Excel files merged successfully!");
}
public static void mergeExcelFiles(List<String> inputFiles, String outputFile) {
try (Workbook mergedWorkbook = new XSSFWorkbook()) {
Sheet mergedSheet = mergedWorkbook.createSheet("Merged Data");
int currentRow = 0;
for (String file : inputFiles) {
try (Workbook workbook = WorkbookFactory.create(new FileInputStream(file))) {
Sheet sheet = workbook.getSheetAt(0); // 获取第一个工作表
// 复制表头（如果是第一个文件）
if (currentRow == 0) {
Row headerRow = sheet.getRow(0);
if (headerRow != null) {
Row newHeaderRow = mergedSheet.createRow(currentRow++);
copyRow(headerRow, newHeaderRow);
}
}
// 复制数据行
for (int i = (currentRow == 0 ? 1 : 0); i <= sheet.getLastRowNum(); i++) {
Row sourceRow = sheet.getRow(i);
if (sourceRow != null) {
Row newRow = mergedSheet.createRow(currentRow++);
copyRow(sourceRow, newRow);
}
}
}
}
// 写入输出文件
try (FileOutputStream out = new FileOutputStream(outputFile)) {
mergedWorkbook.write(out);
}
} catch (IOException e) {
e.printStackTrace();
}
}
private static void copyRow(Row sourceRow, Row targetRow) {
for (Cell sourceCell : sourceRow) {
Cell targetCell = targetRow.createCell(sourceCell.getColumnIndex());
switch (sourceCell.getCellType()) {
case STRING:
targetCell.setCellValue(sourceCell.getStringCellValue());
break;
case NUMERIC:
targetCell.setCellValue(sourceCell.getNumericCellValue());
break;
case BOOLEAN:
targetCell.setCellValue(sourceCell.getBooleanCellValue());
break;
case FORMULA:
targetCell.setCellFormula(sourceCell.getCellFormula());
break;
default:
targetCell.setCellValue("");
}
}
}
}
高级合并选项
如果需要更复杂的合并（如合并多个工作表、处理不同格式等）,可以扩展上述方法：
public static void mergeMultipleSheets(List<String> inputFiles, String outputFile) {
try (Workbook mergedWorkbook = new XSSFWorkbook()) {
int sheetIndex = 0;
for (String file : inputFiles) {
try (Workbook workbook = WorkbookFactory.create(new FileInputStream(file))) {
for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
Sheet sourceSheet = workbook.getSheetAt(i);
Sheet targetSheet = mergedWorkbook.createSheet(sourceSheet.getSheetName() + "_" + (sheetIndex++));
copySheet(sourceSheet, targetSheet);
}
}
}
try (FileOutputStream out = new FileOutputStream(outputFile)) {
mergedWorkbook.write(out);
}
} catch (IOException e) {
e.printStackTrace();
}
}
private static void copySheet(Sheet sourceSheet, Sheet targetSheet) {
for (int i = 0; i <= sourceSheet.getLastRowNum(); i++) {
Row sourceRow = sourceSheet.getRow(i);
if (sourceRow != null) {
Row targetRow = targetSheet.createRow(i);
copyRow(sourceRow, targetRow);
}
}
}
使用 EasyExcel
EasyExcel 是阿里巴巴开源的 Excel 处理库，性能更好,内存占用更低。
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.WriteTable;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.List;
public class EasyExcelMerger {
public static void main(String[] args) {
List<String> inputFiles = new ArrayList<>();
inputFiles.add("file1.xlsx");
inputFiles.add("file2.xlsx");
String outputFile = "merged_output_easyexcel.xlsx";
mergeExcelFiles(inputFiles, outputFile);
System.out.println("Excel files merged successfully with EasyExcel!");
}
public static void mergeExcelFiles(List<String> inputFiles, String outputFile) {
try (ExcelWriter excelWriter = EasyExcel.write(outputFile).build()) {
WriteSheet writeSheet = EasyExcel.writerSheet("Merged Data").build();
boolean firstFile = true;
for (String file : inputFiles) {
WriteTable writeTable = EasyExcel.writerTable(firstFile ? 0 : excelWriter.writeContext().writeSheetHolder().getSheetNo() + 1)
.relativeHeadRowIndex(firstFile ? 0 : 1)
.build();
EasyExcel.read(file).sheet().doRead(excelWriter, writeSheet, writeTable);
firstFile = false;
}
}
}
}
使用 JExcelApi
另一个选择是 JExcelApi：
（图片来源网络，侵删）
import jxl.Workbook;
import jxl.write.WritableWorkbook;
import jxl.write.WriteData;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableCell;
import java.io.File;
import java.io.IOException;
import java.util.List;
public class JExcelMerger {
public static void mergeExcelFiles(List<String> inputFiles, String outputFile) throws Exception {
WritableWorkbook mergedWorkbook = Workbook.createWorkbook(new File(outputFile));
WritableSheet mergedSheet = mergedWorkbook.createSheet("Merged Data", 0);
int currentRow = 0;
for (String file : inputFiles) {
Workbook workbook = Workbook.getWorkbook(new File(file));
jxl.Sheet sheet = workbook.getSheet(0);
// 复制表头
if (currentRow == 0) {
for (int j = 0; j < sheet.getColumns(); j++) {
Label label = new Label(j, currentRow, sheet.getCell(j, 0).getContents());
mergedSheet.addCell(label);
}
currentRow++;
}
// 复制数据
for (int i = (currentRow == 0 ? 1 : 0); i < sheet.getRows(); i++) {
for (int j = 0; j < sheet.getColumns(); j++) {
Label label = new Label(j, currentRow, sheet.getCell(j, i).getContents());
mergedSheet.addCell(label);
}
currentRow++;
}
workbook.close();
}
mergedWorkbook.write();
mergedWorkbook.close();
}
}
注意事项
- 内存管理：对于大文件，Apache POI 可能会消耗大量内存，考虑使用 SXSSFWorkbook（流式 API）
- 格式保留：合并时可能需要保留原始格式，POI 提供了更多格式控制选项
- 性能考虑：EasyExcel 在处理大数据量时性能更好
- 错误处理：添加适当的异常处理机制
- 文件类型：确保所有输入文件格式一致（都是 .xlsx 或都是 .xls）
性能优化建议
对于非常大的 Excel 文件,可以使用以下优化方法：
// 使用 SXSSFWorkbook 处理大文件 Workbook workbook = new SXSSFWorkbook(100); // 保持100行在内存中 // 其余代码与普通 POI 类似
选择哪种方法取决于你的具体需求、文件大小和性能要求，对于大多数应用场景，Apache POI 提供了最全面的解决方案，而 EasyExcel 在处理大数据量时表现更好。
（图片来源网络，侵删）
