easyexcel 简单使用

一，导包

<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.0.5</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>4.1.2</version>
        </dependency>

 

二，创建listener

创建了2种，方便使用。

①，json类型listener

package com.leadtrans.report.common;
 
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
 
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
 
/**
 * @author: Tyler
 * @createDate: 2021/11/17
 */
 
 
public class ExcelJsonListener extends AnalysisEventListener<Object> {
    private static final Logger LOGGER = LoggerFactory.getLogger(ExcelJsonListener.class);
    /**
     * 自定义用于暂时存储data
     */
    private List<JSONObject> dataList = new ArrayList<>();
 
    /**
     * 导入表头
     */
    private Map<String, Integer> importHeads = new HashMap<>(16);
 
    public List<JSONObject> getDataList() {
        return dataList;
    }
 
    public void setDataList(List<JSONObject> dataList) {
        this.dataList = dataList;
    }
 
    public Map<String, Integer> getImportHeads() {
        return importHeads;
    }
 
    public void setImportHeads(Map<String, Integer> importHeads) {
        this.importHeads = importHeads;
    }
 
    /**
     * 这个每一条数据解析都会来调用
     */
    @Override
    public void invoke(Object data, AnalysisContext context) {
        String headStr = JSON.toJSONString(data);
        dataList.add(JSONObject.parseObject(headStr));
    }
 
    /**
     * 这里会一行行的返回头
     */
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        for (Integer key : headMap.keySet()) {
            if (importHeads.containsKey(headMap.get(key))) {
                continue;
            }
            importHeads.put(headMap.get(key), key);
        }
    }
 
    /**
     * 所有数据解析完成了 都会来调用
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        LOGGER.info("Excel解析完毕");
    }
}

 

②，泛型listener

package com.leadtrans.report.common;
 
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import org.apache.commons.compress.utils.Lists;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
 
import java.lang.reflect.InvocationTargetException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
 
/**
 * @author: Tyler
 * @createDate: 2021/11/18
 */
 
 
public class ExcelModelListener<T> extends AnalysisEventListener<T> {
    private static final Logger LOGGER = LoggerFactory.getLogger(ExcelModelListenner.class);
 
    //数据
    List<T> dataList = new ArrayList<T>();
    //List<Map> 列名作为key
    List<Map<String,String>> dataListMap=new ArrayList<>();
    //表头
    private Map<String, Integer> importHeads = new HashMap<>(16);
 
    private List<String> headsList = Lists.newArrayList();
 
    public List<T> getDataList() {
        return dataList;
    }
 
    public List<Map<String,String>> getDataListMap() {
        return dataListMap;
    }
 
    public void setDataList(List<T> dataList) {
        this.dataList = dataList;
    }
 
    public Map<String, Integer> getImportHeads() {
        return importHeads;
    }
 
    public void setImportHeads(Map<String, Integer> importHeads) {
        this.importHeads = importHeads;
    }
 
    public List<String> getHeadsList() {
        return headsList;
    }
 
    public void setHeadsList(List<String> headsList) {
        this.headsList = headsList;
    }
 
    /**
     * 这里会一行行的返回头
     */
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        for (Integer key : headMap.keySet()) {
            if (importHeads.containsKey(headMap.get(key))) {
                continue;
            }
            importHeads.put(headMap.get(key), key);
            if(null != headMap.get(key)){
                headsList.add(headMap.get(key));
            }
        }
    }
 
    /**
     * 这个每一条数据解析都会来调用
     *
     * @param t   one row value. Is is same as {@link AnalysisContext#readRowHolder()}
     * @param context
     */
    @Override
    public void invoke(T t, AnalysisContext context) {
        dataList.add(t);
 
        if(t instanceof Map) {
            Map<String, String> map = new HashMap<>();
            Map<String, String> tMap=(Map<String, String>)t;
            getImportHeads().forEach((k,v)->{
                map.put(k,tMap.get(v));
            });
            dataListMap.add(map);
        }
 
    }
 
    /**
     * 所有数据解析完成了 都会来调用
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
 
        LOGGER.info("所有数据解析完成！");
    }
 
}

 

三，使用

@SpringBootTest
public class ReportApplicationTests {
 
    @Autowired
    ExcelUtil excelUtil;
 
    /**
     * EasyExcel 写入xls
     */
    @Test
    public void writeXls() {
        List<List<String>> headList = new ArrayList<>();
        List<String> head0 = new ArrayList<>();
        head0.add("姓名");
        List<String> head1 = new ArrayList<>();
        head1.add("年龄");
        List<String> head2 = new ArrayList<>();
        head2.add("生日");
        headList.add(head0);
        headList.add(head1);
        headList.add(head2);
 
        List<List<Object>> list = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            List<Object> data = new ArrayList<>();
            data.add("张三");
            data.add(25);
            data.add(new Date());
            list.add(data);
        }
 
        String fileName="C:\\Users\\pc\\Desktop\\test.xls";
        List<String> myList=new ArrayList<>();
        myList.add("t1");
        myList.add("t2");
        myList.add("t3");
        excelUtil.write(fileName,myList,list);
//        EasyExcel.write(fileName).head(headList).sheet("模板").doWrite(list);
    }
 
    /**
     * EasyExcel 读取xlsx（ExcelJsonListener:Json类型监听器）
     */
    @Test
    public void readXlsx() throws FileNotFoundException {
        String fileName="C:\\Users\\pc\\Desktop\\test.xlsx";
        ExcelJsonListener excelListener = new ExcelJsonListener();
        EasyExcel.read(fileName, excelListener).sheet().doRead();
 
        //read xls head
        Map<String, Integer> importHeads = excelListener.getImportHeads();
        //read xls data
        List<JSONObject> dataList = excelListener.getDataList();
 
    }
 
    /**
     * EasyExcel 读取xlsx（ExcelModelListenner: 泛型监听器）
     */
    @Test
    public void simpleRead() {
        // 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
        // 写法1：
        String fileName = "C:\\Users\\pc\\Desktop\\demo.xlsx";
        // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
        ExcelModelListenner<DemoData> excelListener = new ExcelModelListenner<DemoData>();
        EasyExcel.read(fileName, DemoData.class, excelListener).sheet().doRead();
        //read xls head
        Map<String, Integer> importHeads = excelListener.getImportHeads();
        //read xls data
        List<DemoData> dataList = excelListener.getDataList();
    }
 
    /**
     * EasyExcel 读取
     */
    @Test
    public void simpleWrite() {
        List<DemoData> list = new ArrayList<DemoData>();
        for (int i = 0; i < 10; i++) {
            DemoData data = new DemoData();
            data.setString("字符串" + i);
            data.setDate(new Date());
            data.setDoubleData(0.56);
            list.add(data);
        }
        // 写法1
        String fileName = "C:\\Users\\pc\\Desktop\\demo.xlsx";
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        // 如果这里想使用03 则 传入excelType参数即可
        EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(list);
    }
 
    @Test
    public void readMap()
    {
        String fileName="";
        // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
        ExcelModelListenner<Map<String,String>> excelListener = new ExcelModelListenner<>();
        EasyExcel.read(fileName, ReportShipmentDto.class, excelListener).sheet().doRead();
        //read xls head
        Map<String, Integer> dataHeads = excelListener.getImportHeads();
        //read xls data
        List<Map<String,String>> dataList = excelListener.getDataList();
    }