SpringBoot 整合EasyExcel 获取动态Excel列名

导读

　　最近负责消息网关，里面有个短信模板导入功能，因为不同模板编号对应不同参数，导入后的数据定时发送，涉及到Excel中列名不固定问题，于是想根据列名+值，组合成一个大JSON，具体代码如下。

引入依赖

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.6</version>
        </dependency>
        <!--fastjson-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.76</version>
        </dependency>

Excel监听器

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;

import java.util.*;

/**
 * @Description：Excel监听器
 * @Author：chenyanbin
 * @Date：2021/4/28 下午3:36
 * @Versiion：1.0
 */

public class ExcelListener extends AnalysisEventListener<Map<Integer, String>> {
    //Excel数据
    private List<Map<Integer, Map<Integer, String>>> list;
    //Excel列名
    private Map<Integer, String> headTitleMap = new HashMap<>();

    public ExcelListener() {
        list = new ArrayList<>();
    }

    @Override
    public void invoke(Map<Integer, String> data, AnalysisContext context) {
        System.out.println("解析到一条数据：" + JSON.toJSONString(data));
        Map<Integer, Map<Integer, String>> map = new HashMap<>();
        map.put(context.readRowHolder().getRowIndex(), data);
        list.add(map);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        System.out.println("所有数据解析完成");
    }

    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        headTitleMap = headMap;
    }

    public List<Map<Integer, Map<Integer, String>>> getList() {
        return list;
    }

    public void setList(List<Map<Integer, Map<Integer, String>>> list) {
        this.list = list;
    }

    public Map<Integer, String> getHeadTitleMap() {
        return headTitleMap;
    }

    public void setHeadTitleMap(Map<Integer, String> headTitleMap) {
        this.headTitleMap = headTitleMap;
    }
}

控制器

　　注：已经组装好动态列名数组，具体业务逻辑，需自行实现

import com.alibaba.excel.EasyExcel;
import com.alibaba.fastjson.JSON;
import com.ybchen.springbooteasyexcel.listener.ExcelListener;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Description：
 * @Author：chenyanbin
 * @Date：2021/4/28 下午3:36
 * @Versiion：1.0
 */
@RestController
public class DemoController {
    /**
     * 导入
     *
     * @param file
     * @throws IOException
     */
    @RequestMapping(value = "import")
    public List<Object> importStudentInfos(MultipartFile file) throws IOException {
        ExcelListener studentListener = new ExcelListener();
        EasyExcel.read(file.getInputStream(), studentListener).sheet().doRead();
        List<Map<Integer, Map<Integer, String>>> list = studentListener.getList();
        Map<Integer, String> headTitleMap = studentListener.getHeadTitleMap();
        List<Map<String, String>> mapList = new ArrayList<>();
        for (int i = 0; i < list.size(); i++) {
            Map<Integer, Map<Integer, String>> integerMapMap = list.get(i);
            integerMapMap.forEach((k, l) -> {
                Map<String, String> map = new HashMap<>();
                l.forEach((y, z) -> {
                    map.put(headTitleMap.get(y), z);
                });
                mapList.add(map);
            });
        }
        System.out.println(mapList);
        System.out.println("=============================");
        System.out.println(JSON.toJSONString(mapList));
        return null;
    }
}

Excel模板

 

演示