EasyExcle学习笔记

EasyExcle 学习笔记

1、相关依赖

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

2、继承和重写AnalysisEventListener

整体采用抽象类和泛型为的是后续扩展方便

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import lombok.extern.slf4j.Slf4j;

import java.util.ArrayList;
import java.util.List;

/**
 * EasyExcel 导入监听
 */
@Slf4j
public abstract class ExcelListener<T> extends AnalysisEventListener<T> {
    // 可以通过实例获取该值
    private List<T> datas = new ArrayList<>();

    public List<T> getDatas() {
        return this.datas;
    }

    public void setDatas(List<T> datas) {
        this.datas = datas;
    }

    @Override
    public void invoke(T data, AnalysisContext context) {
        //数据存储到list，供批量处理，或后续自己业务逻辑处理。
        this.datas.add(data);
        log.info("数据域范围内{}",this.datas);
        //根据自己业务做处理
        doSomething(datas);
    }


    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
         //解析结束销毁不用的资源
         datas.clear();
    }

    public abstract void doSomething(List<T> object);
}

此时如果我们想要进行特殊逻辑处理包括批量写入数据库就可以重写doSomething 方法

import lombok.extern.slf4j.Slf4j;
import java.util.List;

@Slf4j
public class SysLogListener<T> extends ExcelListener<T> {
    @Override
    public void doSomething(List<T> object) {
    		// 写数据库，类型转换,生成id等操作
        log.info("{}",object);
    }
}

这样每次出现导入文件的情况我们便可以继承ExcelListener 重写doSomething 方法

2.1、操作封装工具类

import com.alibaba.excel.EasyExcel;
import com.js.listener.ExcelListener;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.List;

public class EasyExcleUtil {
    private static final String USER_AGENT = "User-Agent";
    
    private static final String MSIE = "MSIE";
    
    private static final String TRIDENT = "Trident";
    
    private static final String UTF_8 = "UTF-8";
    
    private static final String ISO_8859_1 = "ISO-8859-1";
    
    private static final String XLSX = ".xlsx";

    /**
     * @return
     * @Description: 导出文件
     * @Param [request, response, filenames, list, clazz]
     * @Author: 渡劫 dujie
     * @Date: 2021/5/14 1:49 PM
     */
    public static <T> void exportExcle(HttpServletRequest request, HttpServletResponse response, String filenames, List<T> list, Class clazz) throws Exception {
        String userAgent = request.getHeader(USER_AGENT);
        if (userAgent.contains(MSIE) || userAgent.contains(TRIDENT)) {
            filenames = URLEncoder.encode(filenames, UTF_8);
        } else {
            filenames = new String(filenames.getBytes(UTF_8), ISO_8859_1);
        }
        response.setContentType("application/vnd.ms-exce");
        response.setCharacterEncoding("utf-8");
        response.addHeader("Content-Disposition", "filename=" + filenames + XLSX);
        EasyExcel.write(response.getOutputStream(), clazz).sheet("sheet").doWrite(list);
    }

    /**
     * @return
     * @Description: 导入文件解析
     * @Param [multipartFile, clazz, excelListener]
     * @Author: 渡劫 dujie
     * @Date: 2021/5/14 1:50 PM
     */
    public static  <T extends ExcelListener> void importExcle(File multipartFile, Class clazz, T listener) throws Exception {
        InputStream inputStream = new FileInputStream(multipartFile);
        //传入参数
        EasyExcel.read(inputStream, clazz, listener).sheet().doRead();
    }
}

访问者调用模拟代码

String strUrl = "/Users/a2021/Downloads/查询日志.xlsx";
File multipartFile = new File(strUrl);
//实例化实现了AnalysisEventListener接口的类
SysLogListener<SysLogDto> listener = new SysLogListener();
EasyExcleUtil.importExcle(multipartFile,SysLogDto.class,listener);

项目地址 ：  https://github.com/my-study-project/easy-excle-util.git

个人理解可能理解有误，希望大家帮忙指正