EasyExcel自己封装的工具类及其使用

1.工具类部分

package net.lesscoding.utils;

import cn.hutool.core.collection.CollUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.google.common.collect.Lists;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;

/**
 * @author eleven
 * @date 2022-11-10 19:55:27
 * @description 转义和反转义工具类
 * Generated By: lesscoding.net basic service
 * Link to: <a href="https://lesscoding.net">https://lesscoding.net</a>
 * mail to:2496290990@qq.com zjh292411@gmail.com admin@lesscoding.net
 */
@Slf4j
public class EasyExcelUtil<T> {

    private static final int MAX_SIZE = 900;

    /**
     * 下载excel
     *
     * @param response 响应
     * @param cls      cls
     * @param fileName 文件名称
     * @param data     数据
     * @throws IOException ioexception
     */
    public <T> void downloadExcel(HttpServletResponse response, Class cls, String fileName, List<T> data) throws IOException {
        // 如果传入的data数据是空的话就让data成为一个空集合 变成下载导入模板
        if (CollUtil.isEmpty(data)) {
            data = new ArrayList<>();
        }
        try (OutputStream os = response.getOutputStream()) {
            setResponseHeader(response, fileName);
            EasyExcel.write(os, cls)
                    .sheet("导入模板")
                    .doWrite(data);
        } catch (IOException e) {
            log.error("下载导入模板异常{}", e);
            throw new IOException("下载导入模板异常");
        }
    }


    /**
     * 设置响应头
     *
     * @param response 响应
     * @param fileName 文件名称
     */
    public static void setResponseHeader(HttpServletResponse response, String fileName) throws UnsupportedEncodingException {
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
        response.setHeader("Connection", "close");
        response.setHeader("Content-Type", "application/octet-stream");
    }

    /**
     * 解析excel和批量插入 调用ServiceImpl的默认方法进行插入操作
     *
     * @param file excel文件
     * @param cls  cls
     * @param impl impl
     */
    public int parseExcelAndDoInsertBatch(MultipartFile file, Class cls, ServiceImpl impl) throws IOException {
        List<T> resultList = new ArrayList<>();
        try (InputStream inputStream = file.getInputStream()) {
            EasyExcel.read(inputStream, cls, new ReadListener<T>() {
                @Override
                public void invoke(T t, AnalysisContext analysisContext) {
                    resultList.add(t);
                }

                @Override
                public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                }
            }).sheet().doRead();
            if (CollUtil.isNotEmpty(resultList)) {
                List<List<T>> partition = Lists.partition(resultList, MAX_SIZE);
                partition.forEach(impl::saveBatch);
            }
            return resultList.size();
        }
    }

    /**
     * 解析excel返回解析数据 解析数据之后如果有相关的操作建议使用此方法 如使用mapper的自动填充字段等
     *
     * @param file 文件                       前端传入文件
     * @param cls  cls                       需要解析的类的对象
     * @return {@link List}<{@link T}>       返回解析之后的泛型集合
     * @throws IOException ioexception       获取MultipartFile输入流有可能会导致IO异常
     */
    public List<T> parseExcel(MultipartFile file, Class cls) throws IOException {
        try (InputStream inputStream = file.getInputStream()) {
            List<T> resultList = new ArrayList<>();
            EasyExcel.read(inputStream, cls, new ReadListener<T>() {
                @Override
                public void invoke(T t, AnalysisContext analysisContext) {
                    resultList.add(t);
                }

                @Override
                public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                    log.info("解析数据完成-{}\n", resultList);
                }
            }).sheet().doRead();
            return resultList;
        }
    }

    public List<T> parseExcel(InputStream inputStream, Class cls) throws IOException {
        List<T> resultList = new ArrayList<>();
        EasyExcel.read(inputStream, cls, new ReadListener<T>() {
            @Override
            public void invoke(T t, AnalysisContext analysisContext) {
                resultList.add(t);
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                log.info("解析数据完成-{}\n", resultList);
            }
        }).sheet().doRead();
        return resultList;
    }
}

2. 使用

 /**
     * 下载导入模板
     *
     * @param response
     */
    @Override
    public void downloadTemplate(HttpServletResponse response) throws IOException {
        new EasyExcelUtil<InterviewQuestion>()
                .downloadExcel(response,
                        InterviewQuestion.class,
                        "面试问题表导入模板.xlsx",
                        null);
    }

    /**
     * 从excel批量导入数据
     *
     * @return
     */
    @Override
    public Result import4Excel(MultipartFile file) throws IOException {
        /**
         *   如果需要解析数据做其他操作可以使用
         *   List<SysInterviewQuestion> sysInterviewQuestionList = new EasyExcelUtil<SysInterviewQuestion>()
         *                 .parseExcel(file, SysInterviewQuestion.class);
         */
        int insertBatch = new EasyExcelUtil<InterviewQuestion>()
                .parseExcelAndDoInsertBatch(file, InterviewQuestion.class, this);
        return ResultFactory.buildByResult(insertBatch);
    }

    /**
     * 导出为excel
     *
     * @param response
     */
    @Override
    public void export2Excel(InterviewQuestion interviewQuestion, HttpServletResponse response) throws IOException {
        new EasyExcelUtil<InterviewQuestion>()
                .downloadExcel(response,
                        InterviewQuestion.class,
                        "面试问题表.xlsx",
                        questionMapper.getPageByLike(interviewQuestion));
    }