Java 合并多个 Excel 文件

1
2
3
4
5
6
7
8
9
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
</dependency>
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-core</artifactId>
</dependency>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
package net.saisimon.util;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class Utils {
private static final int XLS_MAX_ROWS = 65535;
private static final int XLSX_MAX_ROWS = 1048575;
/**
* 合并多个 Excel 文件
*
* @param mergedFile 合并后的文件
* @param files
待合并的文件
* @param isXlsx
合并文件类型是否是 xlsx
* @throws IOException
合并异常
*/
public static void mergeExcel(File mergedFile, List<File> files, boolean isXlsx) throws IOException {
if (mergedFile == null || files == null) {
return;
}
try (Workbook mergedWorkbook = isXlsx ? new SXSSFWorkbook() : new HSSFWorkbook();
FileOutputStream out = new FileOutputStream(mergedFile)) {
Sheet newSheet = mergedWorkbook.createSheet();
int start = 0;
for (File file : files) {
if (file == null) {
continue;
}
try (Workbook oldWorkbook = isXlsx ? new XSSFWorkbook(new FileInputStream(file)) : new HSSFWorkbook(new FileInputStream(file))) {
int oldSheetSize = oldWorkbook.getNumberOfSheets();
for (int i = 0; i < oldSheetSize; i++) {
Sheet oldSheet = oldWorkbook.getSheetAt(i);
int oldRowSize = oldSheet.getLastRowNum();
for (int j = 0; j <= oldRowSize; j++) {
if (start == (isXlsx ? XLSX_MAX_ROWS : XLS_MAX_ROWS)) {
newSheet = mergedWorkbook.createSheet();
start = newSheet.getLastRowNum();
}
Row oldRow = oldSheet.getRow(j);
Row newRow = newSheet.createRow(start);
copyRow(oldRow, newRow);
start++;
}
}
}
}
mergedWorkbook.write(out);
}
}
private static void copyRow(Row oldRow, Row newRow) {
newRow.setHeight(oldRow.getHeight());
for (int i = oldRow.getFirstCellNum(); i <= oldRow.getLastCellNum(); i++) {
Cell oldCell = oldRow.getCell(i);
if (null != oldCell) {
copyCell(oldCell, newRow.createCell(i));
}
}
}
private static void copyCell(Cell oldCell, Cell newCell) {
switch (oldCell.getCellTypeEnum()) {
case FORMULA:
newCell.setCellFormula(oldCell.getCellFormula());
break;
case NUMERIC:
if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(oldCell)) {
newCell.setCellValue(cn.hutool.core.date.DateUtil.formatDate(oldCell.getDateCellValue()));
} else {
newCell.setCellValue(oldCell.getNumericCellValue());
}
break;
case BLANK:
newCell.setCellValue(oldCell.getStringCellValue());
break;
case BOOLEAN:
newCell.setCellValue(oldCell.getBooleanCellValue());
break;
case STRING:
newCell.setCellValue(oldCell.getStringCellValue());
break;
default:
break;
}
}
}