poi多Excel文件拼接合并为单文件需求：导出全院排班明细表
现状：目前只能导出单科室排班明细
目标：全院所有科室拼接在同一个sheet中
Excel多文件合并工具类（将多个Excel合并为单Excel文件的多个Sheet）
package org.example;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import java.io.*;
import java.util.Iterator;
import java.util.List;
public class ExcelFileMergeUtils {
/**
* #合并多个excel文件
* @param fileLists excel文件路径
* @param path 目标文件保存目录
* @param fileName 目标文件名称
*/
public static void mergeExcel(List<String> fileLists, String path, String fileName) {
// 创建新的excel工作簿
HSSFWorkbook newExcelWorkBook = new HSSFWorkbook();
// 遍历需要合并的excel文件
for (String excelName : fileLists) {
try (InputStream in = new FileInputStream(excelName)) {
// 创建工作簿
HSSFWorkbook tmpWorkBook = new HSSFWorkbook(in);
// 获取工作簿中的Sheet个数
int len = tmpWorkBook.getNumberOfSheets();
if (len <= 1) {
HSSFSheet tmpSheet = tmpWorkBook.getSheetAt(0);
HSSFSheet newExcelSheet = newExcelWorkBook.createSheet(tmpSheet.getSheetName());
// 复制sheet内容
copyExcelSheet(newExcelWorkBook, tmpSheet, newExcelSheet);
} else {
for (int i = 0; i < len; i++) {
HSSFSheet tmpSheet = tmpWorkBook.getSheetAt(i);
HSSFSheet newExcelSheet = newExcelWorkBook.createSheet(tmpSheet.getSheetName());
// 复制sheet内容
copyExcelSheet(newExcelWorkBook, tmpSheet, newExcelSheet);
}
}
// 关闭tmpWorkBook工作簿
tmpWorkBook.close();
} catch (IOException e) {
e.printStackTrace();
}
}
// 新生成的excel文件
if (!fileName.endsWith(".xlsx") && !fileName.endsWith(".xls")) {
fileName += ".xlsx";
}
String excelFileName = path + File.separator + fileName;
// 判断文件是否存在
File excelFile = new File(excelFileName);
if (excelFile.exists()) {
// 存在则删除
excelFile.delete();
}
// 使用输出流写出
try (FileOutputStream fos = new FileOutputStream(excelFileName)) {
newExcelWorkBook.write(fos);
fos.flush();
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
newExcelWorkBook.close();
} catch (IOException e) {
e.printStackTrace();
}
}
System.out.println("excel文件合并成功，合并后文件路径：" + excelFileName);
}
/**
* #复制sheet到新的excel文件中
* @param workbook excel工作簿
* @param tmpSheet 来源sheet
* @param newExcelSheet 新生成的sheet
*/
public static void copyExcelSheet(HSSFWorkbook workbook, HSSFSheet tmpSheet, HSSFSheet newExcelSheet) {
// 合并单元格
mergeSheetAllRegion(tmpSheet, newExcelSheet);
// 设置单元格列宽度
// 获取最后一个单元格位置
int len = tmpSheet.getRow(tmpSheet.getFirstRowNum()).getLastCellNum();
for (int i = 0; i < len; i++) {
newExcelSheet.setColumnWidth(i, tmpSheet.getColumnWidth(i));
}
// 复制每行内容
Iterator<Row> it = tmpSheet.iterator();
while (it.hasNext()) {
HSSFRow tmpRow = (HSSFRow) it.next();
// 创建新行
HSSFRow newExcelRow = newExcelSheet.createRow(tmpRow.getRowNum());
// 复制行
copyExcelRow(workbook, tmpRow, newExcelRow);
}
}
/**
* #合并单元格
* @param tmpSheet 来源sheet
* @param newExcelSheet 目标sheet
*/
private static void mergeSheetAllRegion(HSSFSheet tmpSheet, HSSFSheet newExcelSheet) {
int num = tmpSheet.getNumMergedRegions();
CellRangeAddress cellRange = null;
for (int i = 0; i < num; i++) {
cellRange = tmpSheet.getMergedRegion(i);
newExcelSheet.addMergedRegion(cellRange);
}
}
/**
* #复制excel中的行到新的sheet中
* @param workbook 目标工作簿
* @param tmpRow 来源excel行
* @param newExcelRow 目标excel行
*/
public static void copyExcelRow(HSSFWorkbook workbook, HSSFRow tmpRow, HSSFRow newExcelRow) {
// 设置行高
newExcelRow.setHeight(tmpRow.getHeight());
// 获取所有列
Iterator<Cell> it = tmpRow.cellIterator();
while (it.hasNext()) {
HSSFCell tmpCell = (HSSFCell) it.next();
// 创建单元格
HSSFCell newExcelCell = newExcelRow.createCell(tmpCell.getColumnIndex());
// 复制单元格
copyExcelCell(workbook, tmpCell, newExcelCell);
}
}
/**
* #复制单元格
* @param workbook 目标工作簿
* @param tmpCell 来源excel单元格
* @param newExcelCell 目标excel单元格
*/
public static void copyExcelCell(HSSFWorkbook workbook, HSSFCell tmpCell, HSSFCell newExcelCell) {
HSSFCellStyle newExcelStyle = workbook.createCellStyle();
// 复制单元格样式
newExcelStyle.cloneStyleFrom(tmpCell.getCellStyle());
// 单元格样式
newExcelCell.setCellStyle(newExcelStyle);
if (tmpCell.getCellComment() != null) {
newExcelCell.setCellComment(tmpCell.getCellComment());
}
// 不同数据类型处理
CellType tmpCellType = tmpCell.getCellType();
newExcelCell.setCellType(tmpCellType);
if (tmpCellType == CellType.NUMERIC) {
if (DateUtil.isCellDateFormatted(tmpCell)) {
newExcelCell.setCellValue(tmpCell.getDateCellValue());
} else {
newExcelCell.setCellValue(tmpCell.getNumericCellValue());
}
} else if (tmpCellType == CellType.STRING) {
newExcelCell.setCellValue(tmpCell.getRichStringCellValue());
} else if (tmpCellType == CellType.BLANK) {
} else if (tmpCellType == CellType.BOOLEAN) {
newExcelCell.setCellValue(tmpCell.getBooleanCellValue());
} else if (tmpCellType == CellType.ERROR) {
newExcelCell.setCellErrorValue(tmpCell.getErrorCellValue());
} else if (tmpCellType == CellType.FORMULA) {
newExcelCell.setCellFormula(tmpCell.getCellFormula());
} else {
}
}
}
多sheet合并器（注：单文件多个sheet名称不能重复）
package com.bozhong.nursebase.common.utils.excel; import com.bozhong.nurse.common.utils.MinioClientUtil; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddress; import java.io.*; import java.util.Iterator; /** * 多sheet合并器 * @author 徐顺洁 * @date 2024年03月18日 21:04:42 **/ public class ExcelSheetMerger { // 工作簿游标 private int destNum = 0; // 工作簿首行游标 private int sheetFirstRowCursor = 0; // 是否第一个sheet标志 private boolean firstSheel = true; // 是否已经复制过列宽（列宽只复制一次就行） private boolean copyedColumnWith = false; // 单文件多sheet的Excel合并到同一个sheet的最终Excel文件 public static byte[] getMergerExcel(String multiSheetExcelName, String targetSheetName) { try { ExcelSheetMerger sheetMerger = new ExcelSheetMerger(); InputStream is = MinioClientUtil.download(MinioClientUtil.getTemporaryBucket(), multiSheetExcelName); HSSFWorkbook workbook; workbook = new HSSFWorkbook(is); is.close(); HSSFWorkbook targetWorkbook = new HSSFWorkbook(); targetWorkbook.createSheet(targetSheetName); // 目标sheet Sheet targetSheet = targetWorkbook.getSheetAt(0); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { Sheet sourceSheet = workbook.getSheetAt(i); sheetMerger.copySheet(sourceSheet, targetSheet, targetWorkbook); sheetMerger.copyMerge(sourceSheet, targetSheet, targetWorkbook); } ByteArrayOutputStream out = new ByteArrayOutputStream(); targetWorkbook.write(out); out.close(); targetWorkbook.close(); return out.toByteArray(); } catch (IOException e) { throw new RuntimeException(e); } } private void copyMerge(Sheet source, Sheet target, Workbook workbook) { // 复制合并单元格信息 for (int i = 0; i < source.getNumMergedRegions(); i++) { CellRangeAddress cellRangeAddress = source.getMergedRegion(i); int firstRow = cellRangeAddress.getFirstRow(); int lastRow = cellRangeAddress.getLastRow(); int firstCol = cellRangeAddress.getFirstColumn(); int lastCol = cellRangeAddress.getLastColumn(); target.addMergedRegion(new CellRangeAddress(firstRow+sheetFirstRowCursor, lastRow+sheetFirstRowCursor, firstCol, lastCol)); } } private void copySheet(Sheet source, Sheet target, Workbook workbook) { int rowNum = 0; while (source.getRow(rowNum) != null) { Row sourceRow = source.getRow(rowNum); Row destRow = target.createRow(destNum++); copyRow(sourceRow, destRow, workbook); rowNum++; } if (!copyedColumnWith) { // 列宽复制 for (Iterator rowIt = source.rowIterator(); rowIt.hasNext();) { HSSFRow sourceRow = (HSSFRow) rowIt.next(); // 设置列宽 for (int i = 0; i < sourceRow.getLastCellNum(); i++) { target.setColumnWidth(i, source.getColumnWidth(i)); } } copyedColumnWith = true; } if (!firstSheel) { sheetFirstRowCursor = destNum - rowNum; } else { firstSheel = false; } } private void copyRow(Row source, Row target, Workbook workbook) { target.setHeight(source.getHeight()); for (int i = 0; i < source.getLastCellNum(); i++) { Cell sourceCell = source.getCell(i); Cell destCell = target.createCell(i); if (sourceCell != null) { copyCell(sourceCell, destCell, workbook); } } } private void copyCell(Cell source, Cell target, Workbook workbook) { CellStyle style = workbook.createCellStyle(); style.cloneStyleFrom(source.getCellStyle()); target.setCellStyle(style); switch (source.getCellType()) { case STRING: target.setCellValue(source.getStringCellValue()); break; case NUMERIC: target.setCellValue(source.getNumericCellValue()); break; case BOOLEAN: target.setCellValue(source.getBooleanCellValue()); break; case FORMULA: target.setCellFormula(source.getCellFormula()); break; case BLANK: // target.setCellType(CellType.BLANK); break; case ERROR: target.setCellErrorValue(source.getErrorCellValue()); break; } } }
使用方式：
1、将多个Excel合并为单Excel文件的多个Sheet
public static void main(String[] args) {
String dir = "src\\main\\java\\org\\example\\";
//这里是xls文件
String[] filePaths = {"2022.08.22~2022.08.28(第35周)全院排班表-1.xls",
"2022.08.22~2022.08.28(第35周)全院排班表-2.xls", "2022.08.22~2022.08.28(第35周)全院排班表-3.xls", "2022.08.22~2022.08.28(第35周)全院排班表-4.xls"};
ArrayList<String> list = new ArrayList<>();
for (String path : filePaths) {
list.add(dir+path);
}
String path = "文件合并后存放路径";
String fileName = "test.xls";
ExcelFileMergeUtils.mergeExcel(list, dir, fileName);
}2、多sheet合并器（注：单文件多个sheet名称不能重复）
public static void main(String[] args) throws IOException {
ExcelSheetMerger sheetMerger = new ExcelSheetMerger();
String dir = "src\\main\\java\\org\\example\\";
InputStream is = new FileInputStream(dir+"test.xls"); // 源xls文件
HSSFWorkbook workbook = new HSSFWorkbook(is);
is.close();
HSSFWorkbook targetWorkbook = new HSSFWorkbook();
targetWorkbook.createSheet("全院"); // 目标sheet
Sheet targetSheet = targetWorkbook.getSheetAt(0);
for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
Sheet sourceSheet = workbook.getSheetAt(i);
sheetMerger.copySheet(sourceSheet, targetSheet, targetWorkbook);
sheetMerger.copyMerge(sourceSheet, targetSheet, targetWorkbook);
}
FileOutputStream fileOut = new FileOutputStream(dir+"merged.xls");
targetWorkbook.write(fileOut);
fileOut.close();
}
* 使用线程池优化导出效率
// 全院多线程导出设置锁保证线程安全
private final ReentrantLock scheduleExportLock = new ReentrantLock();
public void exportMergeExcel() {
// 多个单科室导出Excel文件列表，线程安全集合
List<String> deptFileNames = Collections.synchronizedList(new ArrayList<>());
CountDownLatch processCountDownLatch = new CountDownLatch(nurseUnitInfoList.size());
// 定义线程数为科室总数，每当调用processCountDownLatch.countDown();一次就会减去1，减到0后执行主线程
for (NurseUnitInfoRespDTO nurseUnitInfo : nurseUnitInfoList) {
ThreadUtil.runForProcessScheduleExport(() -> {
// 【非公共资源变量等在这个代码块里】
try {
// 设置锁超时时间2分钟，超时后自动释放锁防止死锁
if (scheduleExportLock.tryLock(120, TimeUnit.SECONDS)) {
try {
【公共资源变量等在这个代码块里】
// 导出逻辑省略...
} catch (IOException e) {
throw new RuntimeException(e);
} finally {
scheduleExportLock.unlock();
}
}
} catch (Exception e) {
Thread.currentThread().interrupt();
log.error("scheduleExportLock ---> Interrupted while trying to acquire lock. 导出发生错误：", e);
} finally {
processCountDownLatch.countDown();
}
deptFileNames.add(deptFileName);
}
}
try {
processCountDownLatch.await();
log.info(">>>>>>>>>>>>>>>>>>>>>主线程等待所有科室排班明细导出结束<<<<<<<<<<<<<<<<<<<<<<<");
} catch (InterruptedException e) {
e.printStackTrace();
}
// 多文件首sheet合并为单文件多sheet
byte[] mergeBuffer = ExcelFileMergeUtils.mergeExcel(deptFileNames);
// 单文件多sheet合并为单文件首sheet并且命名为“全院排班明细”
byte[] mergedBuffer = ExcelSheetMerger.getMergerExcel(fileName, "全院排班明细");
}
恭喜注册成功，快来登录吧！