Java使用easyExcel輕松實現(xiàn)Excel文件合并

更新時間：2026年04月03日 09:07:07 作者：諸葛大鋼鐵

這篇文章主要為大家詳細介紹了Java如何使用easyExcel輕松實現(xiàn)Excel文件合并,文中的示例代碼講解詳細,感興趣的小伙伴可以跟隨小編一起學(xué)習一下

使用easyExcel工具實現(xiàn)Excel文件的合并，包含多文件合并、多sheet合并以及按照Sheet文件名字合并。

Excel文件合并，提供三種類型需求實現(xiàn)Excel合并。

1、合并多個excel,文件中沒有包含sheet

2、Excel的文件sheet順序合并sheet

3、合并多個sheet,按照兩個文件的sheet名字合并

package org.example.engine;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.read.listener.*;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.write.*;
import com.alibaba.excel.write.metadata.WriteSheet;
import java.util.*;
public class AppendMergeFlow {
    //合并多個excel,文件中沒有包含sheet
    public static void merge(List<String> inputFiles, String outputFile,Boolean deduplicate) {
        // ?? 創(chuàng)建 listener（核心）
        AppendMergeListener listener = new AppendMergeListener(outputFile,deduplicate);
        for (String file : inputFiles) {
            EasyExcel.read(file, listener)
                    .sheet()
                    .doRead();
        }
        // ?? 最后必須手動關(guān)閉 writer
        listener.finish();
    }
    //根據(jù)excel的文件sheet順序合并sheet
    public static void mergeAllSheet(List<String> inputFiles, String outputFile,Boolean deduplicate){
//        ExcelWriter writer = EasyExcel.write(outputFile).build();
//        WriteSheet writeSheet = EasyExcel.writerSheet(0, "merged").build();
//        AppendMergeListener listener = new AppendMergeListener(writer, writeSheet, deduplicate);
        AppendMergeListener listener = new AppendMergeListener(outputFile,deduplicate);
        for (String file : inputFiles) {
            ExcelReader reader = EasyExcel.read(file).build();
            List<ReadSheet> sheets = reader.excelExecutor().sheetList();
            for (ReadSheet sheet : sheets) {
                // ? 順序讀取每個sheet
                EasyExcel.read(file, listener)
                        .sheet(sheet.getSheetName())
                        .doRead();
            }
            reader.finish();
        }
        listener.finish();
    }
    //合并多個sheet,按照兩個文件的sheet名字合并
    public static void mergeSheet(List<String> inputFiles, String outputFile,Boolean deduplicate){
        // 1?? 收集所有 sheet
        Map<String, List<String>> sheetMap = collectSheets(inputFiles);
        System.out.println("sheetMap"+ " " +sheetMap.values());
        // 2?? 創(chuàng)建 writer（多sheet輸出）
        ExcelWriter writer = EasyExcel.write(outputFile).build();
//        AppendMergeListener listener = new AppendMergeListener(writer, writeSheet,deduplicate);
        int sheetIndex = 0;
        // 3?? 每個sheet分別合并
        for (String sheetName : sheetMap.keySet()) {
            WriteSheet writeSheet = EasyExcel.writerSheet(sheetIndex++, sheetName).build();
            System.out.println("sheeIndex："+sheetIndex+";"+ " " +"sheetName："+sheetName);
            AppendMergeListener listener = new AppendMergeListener(writer, writeSheet,deduplicate);
            for (String file : sheetMap.get(sheetName)) {
                System.out.println("sheetName:"+ " " +sheetName);
                EasyExcel.read(file, listener)
                        .sheet(sheetName) // ? 指定sheet
                        .doRead();
            }
//            listener.finish(); // 可選
        }
        writer.finish();
    }
    private static Map<String, List<String>> collectSheets(List<String> files) {
        Map<String, List<String>> map = new LinkedHashMap<>();
        for (String file : files) {
            ExcelReader reader = EasyExcel.read(file).build();
            List<ReadSheet> sheets = reader.excelExecutor().sheetList();
            for (ReadSheet sheet : sheets) {
                String name = sheet.getSheetName();
                map.computeIfAbsent(name, k -> new ArrayList<>())
                        .add(file);
            }
            reader.finish();
        }
        return map;
    }
}

監(jiān)聽器AppendMergeListener，基于 AnalysisEventListener，使用 Map<Integer,String> 流式處理對Excel每一行的的內(nèi)容處理。

package org.example.engine;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.write.metadata.WriteSheet;

import java.util.*;


/**
 * AppendMergeListener：基于 AnalysisEventListener，使用 Map<Integer,String> 流式處理
 */

public class AppendMergeListener extends AnalysisEventListener<Map<Integer, String>> {

    private ExcelWriter writer;
    private WriteSheet sheet;
    private boolean deduplicate;


    private Set<String> seen = new HashSet<>();

    //全局表頭
    private List<String> globalheader = null;

    // ? 當前文件表頭
    private List<String> currentHeader = null;

    // ? 是否已經(jīng)寫過“統(tǒng)一表頭”
    private boolean headerWritten = false;

    private int fileIndex = 1;
    private int rowCount = 0;

    // ? Excel最大行（建議留余量）
    private static final int MAX_ROW = 1_000_000;

    private  String outputFile;


    //合并多個文件，并且去重
    public AppendMergeListener(String outputFile,Boolean deduplicate) {
//        this.writer = EasyExcel.write(outputFile).build();
        this.outputFile= outputFile;                  //添加
        initWriter();                                  //添加
        this.sheet = EasyExcel.writerSheet("Merged").build();
        this.deduplicate = deduplicate;
    }

    private void initWriter() {
        String fileName = outputFile + "_" + fileIndex + ".xlsx";
        writer = EasyExcel.write(fileName).build();
        rowCount = 0;
    }

    //合并多個文件
    public AppendMergeListener(String outputFile) {
        this.writer = EasyExcel.write(outputFile).build();
        this.outputFile= outputFile;                  //添加
        initWriter();                                  //添加
        this.sheet = EasyExcel.writerSheet("Merged").build();
    }

    //合并多個文件中的sheet
    public AppendMergeListener(ExcelWriter writer, WriteSheet sheet, Boolean deduplicate) {
        this.writer = writer;
        this.sheet = sheet;
        this.deduplicate = deduplicate;
    }




    // ? 正確處理表頭
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {

        List<String> currentHeader = mapToList(headMap);

        if (globalheader == null){

            globalheader = new ArrayList<>(currentHeader);

            writer.write(Collections.singletonList(globalheader), sheet);

            headerWritten = true;
            rowCount++;

            return;
        }

        if (globalheader.equals(currentHeader)) {
            System.out.println("1:兩者相同");
            System.out.println(currentHeader);

            System.out.println(globalheader);

        }else{
            System.out.println("2:兩者不相同");
            System.out.println(currentHeader);

            System.out.println(globalheader);

            writer.write(Collections.singletonList(currentHeader), sheet);
            rowCount++;
        }
    }
    @Override
    public void invoke(Map<Integer, String> row, AnalysisContext context) {

        List<String> list = mapToList(row);

        if(rowCount >= MAX_ROW){
            writer.finish();
            fileIndex++;
            initWriter();
        }

        if (deduplicate) {
            String key = String.join("|", list);
            if (seen.contains(key)) return;
            seen.add(key);
        }

        writer.write(Collections.singletonList(list), sheet);

        rowCount++;

    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 不關(guān)閉！多個文件共用
    }


    public void finish() {
        if(writer!=null){      //添加的
            writer.finish();
        }

    }

    public List<String> getGlobalheader() {
        return globalheader;
    }

    private List<String> mapToList(Map<Integer, String> row) {
        List<String> list = new ArrayList<>();
        int maxIndex = row.keySet().stream().max(Integer::compareTo).orElse(0);

        for (int i = 0; i <= maxIndex; i++) {
            list.add(row.getOrDefault(i, ""));
        }

        return list;
    }

    public String getOutputFile() {
        return outputFile;
    }
}

測試文件：文件路徑替換為你自己的文件，

package org.example;

import org.example.engine.AppendMergeFlow;
import org.example.engine.JoinMergeFlow;

import java.io.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

public class TestMain {

    public static void main(String[] args) throws FileNotFoundException {

        String file1="C:\\Users\\XXX\\Downloads\\excelmerge\\excel1.xlsx";
        String file2="C:\\Users\\XXX\\Downloads\\excelmerge\\excel2.xlsx";

        String file3="C:\\Users\\XXX\\Downloads\\excelmerge\\hm1.xlsx";
        String file4="C:\\Users\\XXX\\Downloads\\excelmerge\\hm2.xlsx";

        String file5="C:\\Users\\XXX\\Downloads\\excelmerge\\檢測數(shù)據(jù)13-21-1222.xlsx";
        String file6="C:\\Users\\XXX\\Downloads\\excelmerge\\檢測數(shù)據(jù)22-29-1222.xlsx";



//        // ? 1?? Append（去重）
        //合并多Excel文件
        AppendMergeFlow.merge( Arrays.asList(file5, file6),
                "C:\\Users\\XXX\\Downloads\\excelmerge\\merged_append19.xlsx",true);
        //按照文件的sheet順序合并，支持多文件合并
        AppendMergeFlow.mergeAllSheet( Arrays.asList(file5,file6),
                "C:\\Users\\XXX\\Downloads\\excelmerge\\merged_append_all1.xlsx",true);
        //按照文件名合并Sheet，支持多文件合并
        AppendMergeFlow.mergeSheet( Arrays.asList(file5,file6),
                "C:\\Users\\XXX\\Downloads\\excelmerge\\merged_append_all1.xlsx",true);


//         ? 2?? Join（按第0列）
//        JoinMergeFlow.join(
//                file1,
//                file2,
//                "C:\\Users\\XXX\\Downloads\\excelmerge\\merged_join1.xlsx",
//                0
//        );

        System.out.println("全部完成！");
    }
}

導(dǎo)入對應(yīng)的依賴：

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>org.example</groupId>
    <artifactId>mergeExcel</artifactId>
    <version>1.0-SNAPSHOT</version>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>3.2.5</version>
        <relativePath/>
    </parent>
    <properties>
        <maven.compiler.source>11</maven.compiler.source>
        <maven.compiler.target>11</maven.compiler.target>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>
    <dependencies>
        <!-- Spring Boot Web -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!-- EasyExcel -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.3.2</version>
        </dependency>
        <!-- Lombok（可選） -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

合并效果：