多个excel文件合并工具类

获取需要合并文件

 public File getExcelsFile(List<Object> os) {
        // 创建一个保持插入顺序的 LinkedHashMap
        Map<String, String> orderedMap = new LinkedHashMap<>();
        for (Object o: os) {
            //获取file文件集合
            File excelFile = this.getFile(os);
            orderedMap.put(os.getExcelName(),excelFile.getAbsolutePath());
        }
        // 指定生成的Excel文件路径
        String filePath = "path";
        File file = new File(filePath);
        ExcelMerger.mergeExcelFiles(orderedMap,filePath);
        return file;
    }

合并工具类

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Map;

/**
 * @Author wpf
 * @Description excel合并工具类
 * @Date 2024/1/17
 */
public class ExcelMerger{

    //源sheet页
    private static Sheet sourceSheet;
    //目标sheet页
    private static Sheet targetSheet;

    /**
     * @Author wpf
     * @Description 合并excel
     * @Date 2024/1/17
     * @param orderedMap key： sheet页名称  value：单个excel路径
     * @param outputExcelFile 目标excel路径
     */
    public static void mergeExcelFiles(Map<String, String> orderedMap, String outputExcelFile) {
        Workbook mergedWorkbook = new HSSFWorkbook(); // 创建一个新的工作簿（.xls 格式）

        // 遍历 LinkedHashMap，元素的顺序是插入的顺序
        for (Map.Entry<String, String> entry : orderedMap.entrySet()) {
            //获取单个excel流
            try (InputStream is = new FileInputStream(entry.getValue())) {
                Workbook inputWorkbook;
                if (entry.getValue().toLowerCase().endsWith(".xls")) {
                    inputWorkbook = new HSSFWorkbook(is); // 使用 HSSFWorkbook 处理 .xls 文件
                } else if (entry.getValue().toLowerCase().endsWith(".xlsx")) {
                    inputWorkbook = WorkbookFactory.create(is); // 使用通用的 WorkbookFactory 处理 .xlsx 文件
                } else {
                    throw new IllegalArgumentException("不支持的文件格式: " + entry.getValue());
                }
                //将单个excel工作簿复制到新工作簿
                copySheetsWithStyles(inputWorkbook, mergedWorkbook,entry.getKey());
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

        //生成合并的工作簿写入到指定路径
        try (FileOutputStream fos = new FileOutputStream(outputExcelFile)) {
            mergedWorkbook.write(fos);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * @Author wpf
     * @Description 将单个excel工作簿复制到新工作簿
     * @Date 2024/1/17
     * @param sourceWorkbook 单个工作簿
     * @param targetWorkbook 目标工作簿
     * @param sheetName 目标工作簿sheet名称
     */
    private static void copySheetsWithStyles(Workbook sourceWorkbook, Workbook targetWorkbook,String sheetName) {
            //获取源工作簿第一个sheet页
            sourceSheet = sourceWorkbook.getSheetAt(0);
            //创建目标工作簿sheet页 名称为sheetName
            targetSheet = targetWorkbook.createSheet(sheetName);

            copySheetWithStyles(targetWorkbook);
    }

    /**
     * @Author wpf
     * @Description 复制工作簿
     * @Date 2024/1/17
     * @param targetWorkbook 目标工作簿
     */
    private static void copySheetWithStyles(Workbook targetWorkbook) {
        //遍历源工作簿行
        for (int i = 0; i < sourceSheet.getPhysicalNumberOfRows(); i++) {
            //获取源工作簿行
            Row sourceRow = sourceSheet.getRow(i);
            //创建目标工作簿行
            Row targetRow = targetSheet.createRow(i);

            if (sourceRow != null) {
                copyRowWithStyles(sourceRow, targetRow, targetWorkbook);
            }
        }
    }

    /**
     * @Author wpf
     * @Description 复制行内单元格
     * @Date 2024/1/17
     * @param sourceRow 源行
     * @param targetRow 目标行
     * @param targetWorkbook 目标工作簿
     */
    private static void copyRowWithStyles(Row sourceRow, Row targetRow, Workbook targetWorkbook) {
        //遍历单元格cell
        for (int j = 0; j < sourceRow.getPhysicalNumberOfCells(); j++) {
            //获取源cell
            Cell sourceCell = sourceRow.getCell(j);
            //创建目标cell
            Cell targetCell = targetRow.createCell(j);

            if (sourceCell != null) {
                copyCellWithStyles(sourceCell, targetCell, targetWorkbook);
            }
        }
    }

    /**
     * @Author wpf
     * @Description 复制具体单元格
     * @Date 2024/1/17
     * @param sourceCell 源单元格
     * @param targetCell 目标单元格
     * @param targetWorkbook 目标工作簿
     */
    private static void copyCellWithStyles(Cell sourceCell, Cell targetCell, Workbook targetWorkbook) {
        //创建单元格样式 并将源单元格样式复制到目标单元格
        CellStyle newCellStyle = targetWorkbook.createCellStyle();
        newCellStyle.cloneStyleFrom(sourceCell.getCellStyle());
        targetCell.setCellStyle(newCellStyle);
        if (sourceCell.getCellType() == CellType.STRING) {
            //文本类型
            targetCell.setCellValue(sourceCell.getStringCellValue());
        } else if (sourceCell.getCellType() == CellType.NUMERIC) {
            //数字类型
            targetCell.setCellValue(sourceCell.getNumericCellValue());
        } else if (sourceCell.getCellType() == CellType.BOOLEAN) {
            //布尔类型
            targetCell.setCellValue(sourceCell.getBooleanCellValue());
        } else if (sourceCell.getCellType() == CellType.FORMULA) {
            //公式类型
            targetCell.setCellFormula(sourceCell.getCellFormula());
        }

        // 处理合并单元格
        CellRangeAddress mergedRegion = getMergedRegion(sourceSheet, sourceCell.getRowIndex(), sourceCell.getColumnIndex());
        if (mergedRegion != null && !isMergedRegionAlreadyExist(targetSheet, mergedRegion)) {
            targetSheet.addMergedRegion(mergedRegion);
        }
    }

    /**
     * @Author wpf
     * @Description 合并区域是否存在
     * @Date 2024/1/17
     * @param sheet sheet页
     * @param newMergedRegion
     * @return boolean
     */
    private static boolean isMergedRegionAlreadyExist(Sheet sheet, CellRangeAddress newMergedRegion) {
        for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
            CellRangeAddress existingMergedRegion = sheet.getMergedRegion(i);
            if (existingMergedRegion.getFirstRow() == newMergedRegion.getFirstRow()
                    && existingMergedRegion.getLastRow() == newMergedRegion.getLastRow()
                    && existingMergedRegion.getFirstColumn() == newMergedRegion.getFirstColumn()
                    && existingMergedRegion.getLastColumn() == newMergedRegion.getLastColumn()) {
                return true; // 已经存在相同区域的合并单元格
            }
        }
        return false; // 不存在相同区域的合并单元格
    }

    /**
     * @Author wpf
     * @Description 获取合并区域
     * @Date 2024/1/17
     * @param sheet 源目标sheet页
     * @param rowNum 行数
     * @param colNum 列数
     * @return CellRangeAddress
     */
    private static CellRangeAddress getMergedRegion(Sheet sheet, int rowNum, int colNum) {
        for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
            CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
            if (mergedRegion.isInRange(rowNum, colNum)) {
                return mergedRegion;
            }
        }
        return null;
    }

}

样式完整版

jar包需自己下载

		<dependency>
            <groupId>com.aspose</groupId>
            <artifactId>aspose-cells</artifactId>
            <version>8.1.1.0</version>
        </dependency>

import com.aspose.cells.Workbook;
import com.aspose.cells.Worksheet;
import lombok.extern.slf4j.Slf4j;
import java.util.Map;

@Slf4j
public class ExcelCloneUtil {
    /**
     * @Author wpf
     * @Description 合并excel
     * @Date 2024/1/24
     * @param orderedMap key： sheet页名称  value：单个excel路径
     * @param outputExcelFile 目标excel路径
     */
    public static void mergeExcelFiles(Map<String, String> orderedMap, String outputExcelFile) {
        try {
            Workbook newWorkbook = new Workbook();
            // 遍历 LinkedHashMap，元素的顺序是插入的顺序
            for (Map.Entry<String, String> entry : orderedMap.entrySet()) {
                // 加载现有的工作簿
                Workbook workbook = new Workbook(entry.getValue());

                // 获取要复制的工作表
                Worksheet sourceSheet = workbook.getWorksheets().get(0);
                // 获取新添加的目标工作表
                int destSheetIndex  = newWorkbook.getWorksheets().addCopy(0);
                Worksheet destSheet = newWorkbook.getWorksheets().get(destSheetIndex);
                destSheet.setName(entry.getKey());
                // 复制源工作表的内容到目标工作表
                destSheet.copy(sourceSheet);
            }
            // 移除多余的 Sheet1 页
            int sheet1Index = newWorkbook.getWorksheets().get("Sheet1").getIndex();
            newWorkbook.getWorksheets().removeAt(sheet1Index);
            // 保存工作簿
            newWorkbook.save(outputExcelFile);
        }catch (Exception e){
            log.error("合并excel报错：" + e.getMessage());
        }
    }

}