SpringBoot整合EasyExcel实现文件导入导出

        <!--easyexcel-->
        <dependency>
            <groupid>com.alibaba</groupid>
            <artifactid>easyexcel</artifactid>
        </dependency>

/**
 * <p>
 * 操作日志信息
 * </p>
 *
 * @author horse
 * @since 2020-09-08
 * 注意: 实体类中如果使用@accessory（chain=true）,那么导入的数据无法填充到实例中,导出数据不受影响
 */
@data
@equalsandhashcode(callsuper = false)
@tablename("tb_operational_log")
@apimodel(value = "operationallog对象", description = "操作日志信息")
public class operationallog implements serializable {

    private static final long serialversionuid = 1l;

    @excelproperty({"操作日志", "日志id"})
    @apimodelproperty(value = "日志id")
    @tableid(value = "id", type = idtype.assign_id)
    private string id;

    @excelproperty({"操作日志", "操作类型"})
    @apimodelproperty(value = "操作类型")
    private string opertype;

    @excelproperty({"操作日志", "操作描述"})
    @apimodelproperty(value = "操作描述")
    private string operdesc;

    @excelproperty({"操作日志", "操作员id"})
    @apimodelproperty(value = "操作员id")
    private string operuserid;

    @excelproperty({"操作日志", "操作员名称"})
    @apimodelproperty(value = "操作员名称")
    private string operusername;

    @excelproperty({"操作日志", "操作方法"})
    @apimodelproperty(value = "操作方法")
    private string opermethod;

    @excelproperty({"操作日志", "请求方法"})
    @apimodelproperty(value = "请求方法")
    private string operrequway;

    @excelproperty(value = {"操作日志", "请求耗时：单位-ms"}, converter = customrequesttimeconverter.class)
    @apimodelproperty(value = "请求耗时：单位-ms")
    private long operrequtime;

    @excelproperty({"操作日志", "请求参数"})
    @apimodelproperty(value = "请求参数")
    private string operrequparams;

    @excelproperty({"操作日志", "请求body"})
    @apimodelproperty(value = "请求body")
    private string operrequbody;

    @excelproperty({"操作日志", "请求ip"})
    @apimodelproperty(value = "请求ip")
    private string operrequip;

    @excelproperty({"操作日志", "请求url"})
    @apimodelproperty(value = "请求url")
    private string operrequurl;

    @excelproperty(value = {"操作日志", "日志标识"}, converter = customlogflagconverter.class)
    @apimodelproperty(value = "日志标识: 1-admin,0-portal")
    private boolean logflag;

    @excelproperty({"操作日志", "操作状态"})
    @apimodelproperty(value = "操作状态:1-成功,0-失败")
    @tablefield(value = "is_success")
    private boolean success;

    @excelignore
    @apimodelproperty(value = "逻辑删除 1-未删除， 0-删除")
    @tablefield(value = "is_deleted")
    @tablelogic(value = "1", delval = "0")
    private boolean deleted;

    @excelproperty(value = {"操作日志", "创建时间"}, converter = customtimeformatconverter.class)
    @apimodelproperty(value = "创建时间")
    private date gmtcreate;
}

    @operatinglog(opertype = blogconstants.export, operdesc = "导出操作日志,写出到响应流中")
    @apioperation(value = "导出操作日志", hidden = true)
    @postmapping("/oper/export")
    public void operlogexport(@requestbody list<string> logids, httpservletresponse response) {
        operationallogservice.operlogexport(logids, response);
    }

    /**
     * 导出操作日志(可以考虑分页导出)
     *
     * @param logids
     * @param response
     */
    @override
    public void operlogexport(list<string> logids, httpservletresponse response) {
        outputstream outputstream = null;
        try {
            list<operationallog> operationallogs;
            lambdaquerywrapper<operationallog> querywrapper = new lambdaquerywrapper<operationallog>()
                    .orderbydesc(operationallog::getgmtcreate);
            // 如果logids不为null,按照id查询信息,否则查询全部
            if (!collectionutils.isempty(logids)) {
                operationallogs = this.listbyids(logids);
            } else {
                operationallogs = this.list(querywrapper);
            }
            outputstream = response.getoutputstream();

            // 获取单元格样式
            horizontalcellstylestrategy strategy = mycellstylestrategy.gethorizontalcellstylestrategy();

            // 写入响应输出流数据
            easyexcel.write(outputstream, operationallog.class).exceltype(exceltypeenum.xlsx).sheet("操作信息日志")
                    // .registerwritehandler(new longestmatchcolumnwidthstylestrategy()) // 自适应列宽(不是很适应,效果并不佳)
                    .registerwritehandler(strategy) // 注册上面设置的格式策略
                    .registerwritehandler(new customcellwritehandler()) // 设置自定义格式策略
                    .dowrite(operationallogs);
        } catch (exception e) {
            log.error(exceptionutils.getmessage(e));
            throw new blogexception(resultcodeenum.excel_data_export_error);
        } finally {
            ioutil.close(outputstream);
        }
    }

/**
 * @author mr.horse
 * @version 1.0
 * @description: 单元格样式策略
 * @date 2021/4/30 8:43
 */

public class mycellstylestrategy {

    /**
     * 设置单元格样式(仅用于测试)
     *
     * @return 样式策略
     */
    public static horizontalcellstylestrategy gethorizontalcellstylestrategy() {
        // 表头策略
        writecellstyle headercellstyle = new writecellstyle();
        // 表头水平对齐居中
        headercellstyle.sethorizontalalignment(horizontalalignment.center);
        // 背景色
        headercellstyle.setfillforegroundcolor(indexedcolors.sky_blue.getindex());
        writefont headerfont = new writefont();
        headerfont.setfontheightinpoints((short) 14);
        headercellstyle.setwritefont(headerfont);
        // 自动换行
        headercellstyle.setwrapped(boolean.false);

        // 内容策略
        writecellstyle contentcellstyle = new writecellstyle();
        // 设置数据允许的数据格式,这里49代表所有可以都允许设置
        contentcellstyle.setdataformat((short) 49);
        // 设置背景色: 需要指定 fillpatterntype 为fillpatterntype.solid_foreground 不然无法显示背景颜色.头默认了 fillpatterntype所以可以不指定
        contentcellstyle.setfillpatterntype(fillpatterntype.solid_foreground);
        contentcellstyle.setfillforegroundcolor(indexedcolors.grey_40_percent.getindex());
        // 设置内容靠左对齐
        contentcellstyle.sethorizontalalignment(horizontalalignment.left);
        // 设置字体
        writefont contentfont = new writefont();
        contentfont.setfontheightinpoints((short) 12);
        contentcellstyle.setwritefont(contentfont);
        // 设置自动换行
        contentcellstyle.setwrapped(boolean.false);
        // 设置边框样式和颜色
        contentcellstyle.setborderleft(medium);
        contentcellstyle.setbordertop(medium);
        contentcellstyle.setborderright(medium);
        contentcellstyle.setborderbottom(medium);
        contentcellstyle.settopbordercolor(indexedcolors.red.getindex());
        contentcellstyle.setbottombordercolor(indexedcolors.green.getindex());
        contentcellstyle.setleftbordercolor(indexedcolors.yellow.getindex());
        contentcellstyle.setrightbordercolor(indexedcolors.orange.getindex());

        // 将格式加入单元格样式策略
        return new horizontalcellstylestrategy(headercellstyle, contentcellstyle);
    }
}

/**
 * @author mr.horse
 * @version 1.0
 * @description 实现cellwritehandler接口, 实现对单元格样式的精确控制
 * @date 2021/4/29 21:11
 */
public class customcellwritehandler implements cellwritehandler {

    private static logger logger = loggerfactory.getlogger(customcellwritehandler.class);

    @override
    public void beforecellcreate(writesheetholder writesheetholder, writetableholder writetableholder, row row,
                                 head head, integer columnindex, integer relativerowindex, boolean ishead) {

    }

    /**
     * 单元格创建之后(没有写入值)
     *
     * @param writesheetholder
     * @param writetableholder
     * @param cell
     * @param head
     * @param relativerowindex
     * @param ishead
     */
    @override
    public void aftercellcreate(writesheetholder writesheetholder, writetableholder writetableholder, cell cell,
                                head head, integer relativerowindex, boolean ishead) {

    }

    @override
    public void aftercelldataconverted(writesheetholder writesheetholder, writetableholder writetableholder,
                                       celldata celldata, cell cell, head head, integer relativerowindex,
                                       boolean ishead) {

    }

    /**
     * 单元格处理后(已写入值): 设置第一行第一列的头超链接到easyexcel的官网(本系统的导出的excel 0,1两行都是头,所以只设置第一行的超链接)
     * 这里再进行拦截的单元格样式设置的话,前面该样式将全部失效
     *
     * @param writesheetholder
     * @param writetableholder
     * @param celldatalist
     * @param cell
     * @param head
     * @param relativerowindex
     * @param ishead
     */
    @override
    public void aftercelldispose(writesheetholder writesheetholder, writetableholder writetableholder,
                                 list<celldata> celldatalist, cell cell, head head, integer relativerowindex,
                                 boolean ishead) {
        // 设置超链接
        if (ishead && cell.getrowindex() == 0 && cell.getcolumnindex() == 0) {
            logger.info(" ==> 第{}行,第{}列超链接设置完成", cell.getrowindex(), cell.getcolumnindex());
            creationhelper helper = writesheetholder.getsheet().getworkbook().getcreationhelper();
            hyperlink hyperlink = helper.createhyperlink(hyperlinktype.url);
            hyperlink.setaddress("https://github.com/alibaba/easyexcel");
            cell.sethyperlink(hyperlink);
        }
        // 精确设置单元格格式
        boolean bool = ishead && cell.getrowindex() == 1 &&
                (cell.getstringcellvalue().equals("请求参数") || cell.getstringcellvalue().equals("请求body"));
        if (bool) {
            logger.info("第{}行，第{}列单元格样式设置完成。", cell.getrowindex(), cell.getcolumnindex());
            // 获取工作簿
            workbook workbook = writesheetholder.getsheet().getworkbook();
            cellstyle cellstyle = workbook.createcellstyle();

            font cellfont = workbook.createfont();
            cellfont.setbold(boolean.true);
            cellfont.setfontheightinpoints((short) 14);
            cellfont.setcolor(indexedcolors.sea_green.getindex());
            cellstyle.setfont(cellfont);
            cell.setcellstyle(cellstyle);
        }
    }
}

// 批量导出
    batchexport() {
      // 遍历获取id集合列表
      const logids = []
      this.multipleselection.foreach(item => {
        logids.push(item.id)
      })
       // 请求后端接口
      axios({
        url: this.base_api + '/admin/blog/log/oper/export',
        method: 'post',
        data: logids,
        responsetype: 'arraybuffer',
        headers: { 'token': gettoken() }
      }).then(response => {
        // type类型可以设置为文本类型，这里是新版excel类型
        const blob = new blob([response.data], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8' })
        const pdfurl = window.url.createobjecturl(blob)
        const filename = 'horseblog操作日志' // 下载文件的名字
        // 对于<a>标签，只有 firefox 和 chrome（内核）支持 download 属性
        if ('download' in document.createelement('a')) {
          const link = document.createelement('a')
          link.href = pdfurl
          link.setattribute('download', filename)
          document.body.appendchild(link)
          link.click()
          window.url.revokeobjecturl(pdfurl) // 释放url 对象
        } else {
          // ie 浏览器兼容方法
          window.navigator.mssaveblob(blob, filename)
        }
      })
    }

/**
 * @author mr.horse
 * @version 1.0
 * @description: easyexcel文件读取配置(不能让spring管理)
 * @date 2021/4/27 13:24
 */

public class myexcelimportconfig<t> extends analysiseventlistener<t> {

    private static logger logger = loggerfactory.getlogger(myexcelimportconfig.class);

    /**
     * 每次读取的最大数据条数
     */
    private static final int max_batch_count = 10;

    /**
     * 泛型bean属性
     */
    private t dynamicservice;

    /**
     * 可接收任何参数的泛型list集合
     */
    list<t> list = new arraylist<>();


    /**
     * 构造函数注入bean(根据传入的bean动态注入)
     *
     * @param dynamicservice
     */
    public myexcelimportconfig(t dynamicservice) {
        this.dynamicservice = dynamicservice;
    }

    /**
     * 解析每条数据都进行调用
     *
     * @param data
     * @param context
     */
    @override
    public void invoke(t data, analysiscontext context) {
        logger.info(" ==> 解析一条数据: {}", jacksonutils.objtostring(data));
        list.add(data);
        if (list.size() > max_batch_count) {
            // 保存数据
            savedata();
            // 清空list
            list.clear();
        }
    }

    /**
     * 所有数据解析完成后,会来调用一次
     * 作用: 避免最后集合中小于 max_batch_count 条的数据没有被保存
     *
     * @param context
     */
    @override
    public void doafterallanalysed(analysiscontext context) {
        savedata();
        logger.info(" ==> 数据解析完成 <==");
    }

    /**
     * 保存数据: 正式应该插入数据库,这里用于测试
     */
    private void savedata() {
        logger.info(" ==> 数据保存开始: {}", list.size());
        list.foreach(system.out::println);
        logger.info(" ==> 数据保存结束 <==");
    }

    /**
     * 在转换异常 获取其他异常下会调用本接口。我们如果捕捉并手动抛出异常则停止读取。如果这里不抛出异常则 继续读取下一行。
     *
     * @param exception
     * @param context
     * @throws exception
     */
    @override
    public void onexception(exception exception, analysiscontext context) throws exception {
        logger.error(" ==> 数据解析失败,但是继续读取下一行:{}", exception.getmessage());
        //  如果是某一个单元格的转换异常 能获取到具体行号
        if (exception instanceof exceldataconvertexception) {
            exceldataconvertexception convertexception = (exceldataconvertexception) exception;
            logger.error("第{}行，第{}列数据解析异常", convertexception.getrowindex(), convertexception.getcolumnindex());
        }
    }

}

    @apioperation(value = "数据导入测试", notes = "操作日志导入测试[operationallog]", hidden = true)
    @postmapping("/import")
    public r excelimport(@requestparam("file") multipartfile file) throws ioexception {
        easyexcel.read(file.getinputstream(), operationallog.class, new myexcelimportconfig<>(operationallogservice))
                .sheet().doread();
        return r.ok().message("文件导入成功");
    }

/**
 * @author mr.horse
 * @version 1.0
 * @description: 自定义excel转换器: 将操作日志的请求耗时加上单位 "ms"
 * @date 2021/4/27 10:25
 */

public class customrequesttimeconverter implements converter<long> {

    /**
     * 读取数据时: 属性对应的java数据类型
     *
     * @return
     */
    @override
    public class<long> supportjavatypekey() {
        return long.class;
    }

    /**
     * 写入数据时: excel内部的数据类型,因为请求耗时是long类型,对应excel是number类型,但是加上"ms后对应的是string类型"
     *
     * @return
     */
    @override
    public celldatatypeenum supportexceltypekey() {
        return celldatatypeenum.string;
    }

    /**
     * 读取回调
     *
     * @param celldata
     * @param contentproperty
     * @param globalconfiguration
     * @return
     * @throws exception
     */
    @override
    public long converttojavadata(celldata celldata, excelcontentproperty contentproperty, globalconfiguration globalconfiguration) throws exception {
        // 截取字符串: "ms",转换为long类型
        string value = celldata.getstringvalue();
        return long.valueof(value.substring(0, value.length() - 2));
    }

    @override
    public celldata<long> converttoexceldata(long value, excelcontentproperty contentproperty, globalconfiguration globalconfiguration) throws exception {
        // 添加字符串: "ms"
        return new celldata<>(string.valueof(value).concat("ms"));
    }
}

/**
 * @author mr.horse
 * @version 1.0
 * @description: {description}
 * @date 2021/4/27 14:01
 */

public class customtimeformatconverter implements converter<date> {

    @override
    public class<date> supportjavatypekey() {
        return date.class;
    }

    @override
    public celldatatypeenum supportexceltypekey() {
        return celldatatypeenum.string;
    }

    @override
    public date converttojavadata(celldata celldata, excelcontentproperty contentproperty, globalconfiguration globalconfiguration) throws exception {
        string value = celldata.getstringvalue();
        return dateutil.parse(value, datepattern.norm_datetime_pattern);
    }

    @override
    public celldata<date> converttoexceldata(date value, excelcontentproperty contentproperty, globalconfiguration globalconfiguration) throws exception {
        return new celldata<>(dateutil.format(value, datepattern.norm_datetime_pattern));
    }
}