EasyExcel实战 自定义动态化导出excel

1.Java操作excel表格，除了运用POI技术，阿里开发一个工具简易操作EasyExcel，接下来我们来实战操作下自定义动态化导出excel，自定义动态化为自定义标题，合并单元格

引入 pom

<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.7</version>
        </dependency>

实现代码逻辑

package com.example.excel;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.merge.LoopMergeStrategy;
import jdk.nashorn.internal.runtime.JSONFunctions;
import lombok.extern.slf4j.Slf4j;
import org.springframework.util.CollectionUtils;
import org.springframework.util.NumberUtils;

import java.util.*;
import java.util.stream.Collectors;

/**
 * describe: 导出Excel 表格
 *
 * @author 
 * @date 
 */
@Slf4j
public class WriteExcelUtils {

    public static void main(String[] args) {
        List<String> titleList = new ArrayList<>();
        titleList.add("客户名称");
        titleList.add("客户编号");
        titleList.add("供应商名称");
        titleList.add("合同名称");
        titleList.add("合同编号");
        titleList.add("要合并");

        List<String> mergeTitles = new ArrayList<>();
        mergeTitles.add("客户名称");
        mergeTitles.add("供应商名称");
        mergeTitles.add("合同编号");

        List<List<Object>> dataList = new ArrayList<>();
        for (int i = 0; i< 10; i++) {
            List<Object> objList = new ArrayList<>();
            objList.add("客户名称");
            objList.add("客户编号"+i);
            objList.add("供应商名称"+i);
            objList.add("合同名称"+i);
            objList.add("合同编号");
            objList.add("要合并"+i);
            dataList.add(objList);
        }

        WriteExcelUtils writeExcelUtils = new WriteExcelUtils();
        writeExcelUtils.customDynamicExport("测试导出excel" + Math.random(),titleList, mergeTitles, dataList);
    }


    /**
     * 自定义动态导出excel
     *
     * @param fileName 文件名称 不带后缀
     * @param titleList excel标题名称
     * @param mergeTitles 需要合并的标题列
     * @param dataList 导出的数据 按顺序
     */
    public void customDynamicExport(String fileName, List<String> titleList,
                                    List<String> mergeTitles, List<List<Object>> dataList) {
        fileName = fileName + ".xlsx";

        // 导出的数据转换为Map数据结构， k -> 标题，v -> 此标题下的数据按顺序
        Map<String, List<Object>> dataMap = new HashMap<>(titleList.size());
        for (int i = 0 ; i < titleList.size(); i++) {
            // 当前列数据
            List<Object> currectTitleDatas = new ArrayList<>();
            for (int j = 0; j < dataList.size(); j++) {
                currectTitleDatas.add(dataList.get(j).get(i));
            }
            dataMap.put(titleList.get(i), currectTitleDatas);
        }

        // 获取标题信息
        List<List<String>> headTitleInfo = excelTitle(titleList);
        // 获取数据信息
        List<List<Object>> excelDataInfo = excelData(dataList);

        // 获取合并单元格信息
        List<LoopMergeStrategy> mergeStrategies = mergeCells(titleList, mergeTitles, dataMap);
        ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(fileName);
        if (!CollectionUtils.isEmpty(mergeStrategies)) {
            log.info("开始进行合并单元格操作 mergeStrategies ===> {}", mergeStrategies);
            mergeStrategies.forEach(m -> {
                    excelWriterBuilder.registerWriteHandler(m);
            });
        }

        excelWriterBuilder.head(headTitleInfo).sheet("模板")
                // 当然这里数据也可以用 List<List<String>> 去传入
                .doWrite(excelDataInfo);
    }

    /**
     * 获取excel标题
     *
     * @param titleList
     * @return
     */
    private List<List<String>> excelTitle(List<String> titleList) {
        if (CollectionUtils.isEmpty(titleList)) {
            log.error("====> 导出excel标题为空");
            throw new RuntimeException();
        }

        List<List<String>> excelTitleList = new ArrayList<>(titleList.size());
        titleList.forEach(k -> {
            List<String> titles = new ArrayList<>(1);
            titles.add(k);
            excelTitleList.add(titles);
        });
        return excelTitleList;
    }

    private List<List<Object>> excelData(List<List<Object>> dataList) {
        if (CollectionUtils.isEmpty(dataList)) {
            log.error("===> 导出excel数据为空");
            throw new RuntimeException();
        }

        return dataList;
    }

    private List<LoopMergeStrategy> mergeCells(List<String> titleList, List<String> mergeTitles, Map<String, List<Object>> dataMap) {
        if (CollectionUtils.isEmpty(titleList)) {
            log.error("====> 导出excel标题为空");
            throw new RuntimeException();
        }
        if (CollectionUtils.isEmpty(mergeTitles)) {
            log.warn("该次导出无合并单元格操作");
            return new ArrayList<>();
        }

        // 将需要合并的项转换为Map，以便获取合并位置
        Map<String, String> mergeTitleMap = mergeTitles.stream().collect(Collectors.toMap(k -> k, k -> k));

        // 获取合并位置
        Map<String, Integer> mergeLocationMap = new HashMap<>(mergeTitles.size());
        for (int i = 0; i < titleList.size(); i++) {
            if (Objects.nonNull(mergeTitleMap.get(titleList.get(i)))) {
                mergeLocationMap.put(titleList.get(i), i);
            }
        }

        List<LoopMergeStrategy> mergeStrategyList = new ArrayList<>();

        mergeTitles.forEach(k -> {
            // 合并数据所在的列数
            Integer columnIndex = mergeLocationMap.get(k);

            List<Object> mergeDatas = dataMap.get(k);

            // 需要合并的数量
            Integer currectMergeNum = 1;
            for (int m = 0; m < mergeDatas.size(); m++) {
                // 是否存在合并环节
                Boolean isMerge = false;
                if (m != 0 && Objects.equals(mergeDatas.get(m), mergeDatas.get(m - 1))) {
                    isMerge = true;
                    currectMergeNum = currectMergeNum + 1;

                    if (m == mergeDatas.size() - 1) {
                        isMerge = false;
                    }
                }
                if (!isMerge && currectMergeNum > 1) {
                    // 合并的行数
                    Integer eachRow = currectMergeNum;
                    mergeStrategyList.add(new LoopMergeStrategy(eachRow, columnIndex));
                    currectMergeNum = 1;
                }
            }
        });
        return mergeStrategyList;
    }
}