Java 合并多个 Excel 文件

合并多个 Excel 文件

• 添加依赖

<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
</dependency>
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-core</artifactId>
</dependency>

• 工具类

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;
}
}
}

最后

以上就是娇气鞋垫为你收集整理的Java 合并多个 Excel 文件的全部内容，希望文章能够帮你解决Java 合并多个 Excel 文件所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。