easyExcel合并单元格导出

效果图

一、导入maven依赖

（很多旧项目自定义了一套Excel导出工具，poi版本可能不兼容，一般poi新旧版本不兼容分界线在3.17，选择3.17版本不会发生代码不兼容情况）

        <dependency>
            <groupId>com.alibabagroupId>
            <artifactId>easyexcelartifactId>
            <version>2.2.6version>
            <exclusions>
 
                <exclusion>
                    <groupId>org.apache.poigroupId>
                    <artifactId>poiartifactId>
                exclusion>
                <exclusion>
                    <groupId>org.apache.poigroupId>
                    <artifactId>poi-ooxmlartifactId>
                exclusion>
                <exclusion>
                    <groupId>org.apache.poigroupId>
                    <artifactId>poi-ooxml-schemasartifactId>
                exclusion>
            exclusions>
        dependency>
        <dependency>
            <groupId>org.apache.poigroupId>
            <artifactId>poiartifactId>
            <version>3.17version>
        dependency>
        <dependency>
            <groupId>org.apache.poigroupId>
            <artifactId>poi-ooxmlartifactId>
            <version>3.17version>
        dependency>
        <dependency>
            <groupId>org.apache.poigroupId>
            <artifactId>poi-ooxmlartifactId>
            <version>3.17version>
            <classifier>sourcesclassifier>
        dependency>
 
        <dependency>
            <groupId>org.apache.commonsgroupId>
            <artifactId>commons-collections4artifactId>
            <version>4.1version>
        dependency>

二、重写easyExcel-自定义写入处理器

 
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.merge.AbstractMergeStrategy;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
 
import java.util.ArrayList;
import java.util.List;
 
/**
 * 自定义合并策略 该类继承了AbstractMergeStrategy抽象合并策略，需要重写merge()方法
 * @author reshui
 * @date 2023/9/4
 **/
public class CustomMergeStrategy extends AbstractMergeStrategy {
 
    /**
     * 分组，每几行合并一次
     */
    private List exportFieldGroupCountList;
 
    /**
     * 目标合并列index
     */
    private Integer targetColumnIndex;
 
    /**
     * 需要开始合并单元格的首行index
     */
    private Integer rowIndex;
 
    /**
     * @param exportDataList exportDataList为待合并目标列的值
     * @param targetColumnIndex 需要合并的列
     * @return {@code  }
     * @author reshui
     * @date 2023/09/05
     */
    public CustomMergeStrategy(List exportDataList, Integer targetColumnIndex) {
        this.exportFieldGroupCountList = getGroupCountList(exportDataList);
        this.targetColumnIndex = targetColumnIndex;
    }
 
 
    @Override
    protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
        if (null == rowIndex) {
            rowIndex = cell.getRowIndex();
        }
        // 仅从首行以及目标列的单元格开始合并，忽略其他
        if (cell.getRowIndex() == rowIndex && cell.getColumnIndex() == targetColumnIndex) {
            mergeGroupColumn(sheet);
        }
    }
 
    private void mergeGroupColumn(Sheet sheet) {
        int rowCount = rowIndex;
        for (Integer count : exportFieldGroupCountList) {
            if (count == 1) {
                rowCount += count;
                continue;
            }
            // 合并单元格
            CellRangeAddress cellRangeAddress = new CellRangeAddress(rowCount, rowCount + count - 1, targetColumnIndex, targetColumnIndex);
            sheet.addMergedRegionUnsafe(cellRangeAddress);
            rowCount += count;
        }
    }
 
    /**
     * 该方法将目标列根据值是否相同连续可合并，存储可合并的行数
     * @param exportDataList
     * @return {@code List }
     * @author reshui
     * @date 2023/09/05
     */
    private List getGroupCountList(List exportDataList) {
        if (CollectionUtils.isEmpty(exportDataList)) {
            return new ArrayList<>();
        }
 
        List groupCountList = new ArrayList<>();
        int count = 1;
 
        for (int i = 1; i < exportDataList.size(); i++) {
            if (exportDataList.get(i).equals(exportDataList.get(i - 1))) {
                count++;
            } else {
                groupCountList.add(count);
                count = 1;
            }
        }
        // 处理完最后一条后
        groupCountList.add(count);
        return groupCountList;
    }
 
 
}

三、导出工具类封装

 
 
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.handler.WriteHandler;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
 
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
 
 
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.*;
 
 
 
/**
 * 下载excel文件工具
 *
 * @author reshui
 * @date 2023/09/05
 */
public class DownloadExcelUtil {
 
    private static final Logger log = LoggerFactory.getLogger(DownloadExcelUtil.class);
 
    private final static String separatorChar = "-";
 
    public static  void downloadFile(HttpServletResponse response, Class clazz, String data) throws IOException {
        String timeStamp = DateUtil.format(DateUtil.date(), "yyyyMMddHHmmss");
        String fileName = timeStamp + "-log";
        downloadFile(response, clazz, data, fileName, "数据", null);
    }
 
    /**
     * @param response         响应请求
     * @param clazz            导出数据类型
     * @param data             数据源
     * @param customFileName   文件名
     * @param sheetName        页名
     * @param writeHandlerList 自定义写入处理器
     * @return {@code T }
     * @author reshui
     * @date 2023/09/05
     */
    public static  T downloadFile(HttpServletResponse response, Class clazz
            , String data
            , String customFileName
            , String sheetName
            , List writeHandlerList) throws IOException {
        // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
        try {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easy-excel没有关系
            String fileName = URLEncoder.encode(customFileName, "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
 
            // 这里需要设置不关闭流
            ExcelWriterSheetBuilder writerSheetBuilder = EasyExcel.write(response.getOutputStream(), clazz).autoCloseStream(Boolean.FALSE).sheet(sheetName);
            if (CollUtil.isNotEmpty(writeHandlerList)) {
                for (WriteHandler writeHandler : writeHandlerList) {
                    writerSheetBuilder.registerWriteHandler(writeHandler);
                }
            }
            writerSheetBuilder.doWrite(JSONObject.parseArray(data, clazz));
        } catch (Exception e) {
            // 重置response
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            Map map = new HashMap<>(2);
            map.put("status", "failure");
            map.put("message", "下载文件失败" + e.getMessage());
            response.getWriter().println(JSON.toJSONString(map));
        }
        return null;
    }
 
 
 
 
    /**
     * 出把excel
     *
     * @param timeStamp       时间戳
     * @param excelFileName   excel文件名字
     * @param headClassType   头类类型
     * @param resultExcelList 结果excel表
     * @param filePath        文件路径
     * @author reshui
     * @date 2023/02/15
     */
    public static void outputExcelToLocal(String timeStamp, String excelFileName, Class headClassType, List resultExcelList, String filePath) {
        //文件时间戳
        timeStamp = Objects.nonNull(timeStamp) ? timeStamp : StrUtil.EMPTY;
        String partFileName = filePath + File.separator + excelFileName + separatorChar + timeStamp + "-log.xlsx";
        FileUtil.touch(partFileName);
        EasyExcel.write(partFileName, headClassType).sheet("源数据").doWrite(resultExcelList);
    }
 
    /**
     * 简单把excel
     *
     * @param excelFileName   excel文件名字
     * @param headClassType   头类类型
     * @param resultExcelList 结果excel表
     * @param filePath        文件路径
     * @author reshui
     * @date 2023/02/15
     */
    public static void easyOutputExcelToLocal(String excelFileName, Class headClassType, List resultExcelList, String filePath) {
        String timeStamp = DateUtil.format(DateUtil.date(), "yyyyMMddHHmmss");
        outputExcelToLocal(timeStamp, excelFileName, headClassType, resultExcelList, filePath);
    }
 
    public static void main(String[] args) {
 
        String timeStamp = DateUtil.format(DateUtil.date(), "yyyyMMddHHmmss");
        String titleTmpDirPath = FileUtil.getTmpDirPath() + File.separator + "test" + File.separator + timeStamp + File.separator;
        try {
            System.out.println("日志存储地址-[" + titleTmpDirPath + "]");
            log.info("日志存储[" + titleTmpDirPath + "]");
            String partFileName = titleTmpDirPath + timeStamp + "-log.xlsx";
            FileUtil.touch(partFileName);
            EasyExcel.write(partFileName, String.class).sheet("源数据").doWrite(null);
        } catch (Exception e) {
            log.error("日志存储[" + titleTmpDirPath + "]" + "-error:", e);
        }
    }
}

四、运行

@RestController
@RequestMapping("/check")
public class TestController {
 
 
    @RequestMapping(value = "/run1",method = {RequestMethod.GET})
    public void run1(HttpServletResponse response) throws IOException {
        List demoDataList = data1();
        List customMergeStrategies = Arrays.asList(
                new CustomMergeStrategy(demoDataList.stream().map(DemoData::getName).collect(Collectors.toList()), 1));
        DownloadExcelUtil.downloadFile(response,DemoData.class, JSONObject.toJSONString(demoDataList),"测试","页1"
                , customMergeStrategies);
    }
 
    public static List data1(){
        List demoDataList = new ArrayList<>();
        DemoData demoData0 = new DemoData();
        demoData0.setId("0");
        demoData0.setName("hello0");
        demoDataList.add(demoData0);
 
        DemoData demoData1 = new DemoData();
        demoData1.setId("1");
        demoData1.setName("hello1");
        demoDataList.add(demoData1);
 
        DemoData demoData11 = new DemoData();
        demoData11.setId("1");
        demoData11.setName("hello1");
        demoDataList.add(demoData11);
 
        DemoData demoData2 = new DemoData();
        demoData2.setId("2");
        demoData2.setName("hello2");
        demoDataList.add(demoData2);
        return demoDataList;
    }
}