关于easyExcel导入心得

Alibaba easyExcel 导入mysql 数据库

版本（比较旧，新版本待研究）
最新版本号

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>1.1.2-beta5</version>
        </dependency>

官方网站
github地址

话不多说直接上代码
controller

**
 * @Author LLX
 * @Version 1.0
 */
@Controller
@RequestMapping("/importTime")
public class ImportEasTimeDataController {

    @Autowired
    private ImportTimeService importContractFromExcel;

    /**
     * 从excel文件导入时间轴有关更细的信息
     *
     * @param myfile
     * @param request
     * @return
     */
    @LoggSys("导入Excel数据")
    @PostMapping("/importContractFromExcel")
    public void importContractFromExcel(@RequestParam("myfile") MultipartFile myfile, HttpServletRequest request) {
        if (XaUtils.isEmpty(myfile)) {
            throw new RrException("没有找到该文件");
        }
        importContractFromExcel.importTimeExcel(myfile);

    }
}

service 接口：

/**
 * @Author LLX
 * @Version 1.0
 */
public interface ImportTimeService {
    /**
     * 报销类导入数据
     */
    void importTimeExcel(MultipartFile file);
}

serviceImpl 实现类：

package com.ourslook.yuebaoxiao.Import;

import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.ourslook.yuebaoxiao.util.RrException;
import com.ourslook.yuebaoxiao.util.yuebaoxiaoUtil.AddDataServiceAbstract;
import com.ourslook.yuebaoxiao.util.yuebaoxiaoUtil.excelListener.CpBcBillDataListener;
import com.ourslook.yuebaoxiao.vo.eas.EasCpBcBillDataVo;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.BufferedInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;

/**
 * @Author LLX
 * @Version 1.0
 */
@Service("ImportTimeService")
@Slf4j
public class ImportTimeServiceImpl implements ImportTimeService {

    @Autowired
    private AddTimeServiceAbstract addTimeServiceAbstract;


    @Override
    public void importTimeExcel(MultipartFile file) {

        //报销的时间轴相关
        readExcelMoreData1(file,new TimeDataListener(addTimeServiceAbstract),
                new EasTimeDataVo(), 1, 2);
    }


    public static List<EasTimeDataVo> readExcelMoreData1(MultipartFile excel,
                                                             TimeDataListener timeDataListener,
                                                             BaseRowModel rowModel, int sheetNo,
                                                             int headLineNum)
    {
        ExcelReader reader = getReader(excel, timeDataListener);

        if (reader == null) {
            return null;
        }

        reader.read(new Sheet(sheetNo, headLineNum, rowModel.getClass()));

        return timeDataListener.getDatas();
    }


    /**
     * 返回 ExcelReader
     *
     * @param excel         需要解析的 Excel 文件
     * @param excelListener new excelListener()
     */
    private static ExcelReader getReader(MultipartFile excel, AnalysisEventListener excelListener) {
        String filename = excel.getOriginalFilename();

        if (filename == null || (!filename.toLowerCase().endsWith(".xls") && !filename.toLowerCase().endsWith(".xlsx"))) {
            throw new RrException("文件格式错误！");
        }
        InputStream inputStream;

        try {
            inputStream = new BufferedInputStream(excel.getInputStream());

            return new ExcelReader(inputStream, null, excelListener, false);
        } catch (IOException e) {
            e.printStackTrace();
        }

        return null;
    }
}

监听：此监听非常重要，主要是读取excel中的数据，速度非常块

package com.ourslook.yuebaoxiao.Import;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import com.ourslook.yuebaoxiao.util.yuebaoxiaoUtil.AddDataServiceAbstract;
import com.ourslook.yuebaoxiao.vo.eas.EasCpBcBillDataVo;
import lombok.extern.slf4j.Slf4j;

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

/**
 * @author llx
 * @version 1.0.0
 */
@Slf4j
public class TimeDataListener extends AnalysisEventListener<EasTimeDataVo> {

    private AddTimeServiceAbstract addTimeServiceAbstract;
    private List<EasTimeDataVo> datas = new ArrayList<>();
    private static final int BATCH_COUNT = 3000;

    @Override
    public void invoke(EasTimeDataVo object, AnalysisContext analysisContext) {
        log.info("解析到一条数据:{}", JSON.toJSONString(object));
        //数据存储到datas，供批量处理，或后续自己业务逻辑处理。
        datas.add(object);
        //根据业务自行 do something

        //达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (datas.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理datas
            datas.clear();
        }
    }

    public TimeDataListener() {
    }

    public TimeDataListener(AddTimeServiceAbstract addTimeServiceAbstract) {
        this.addTimeServiceAbstract = addTimeServiceAbstract;
    }

    /**
     * 根据业务自行实现该方法
     */
    private void saveData() {
        log.info("{}条数据，开始存储数据库！", datas.size());
        //此处业务逻辑的实现方法，根据自己的业务逻辑
        addTimeServiceAbstract.timeData(datas);
        log.info("存储数据库成功！");
    }

    public List<EasTimeDataVo> getDatas() {
        return datas;
    }

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

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        saveData();//确保所有数据都能入库
        log.info("所有数据解析完成！");
    }
}

实体类（旧版本需要继承BaseRowModel ，而新版本不需要继承只需要加入@Data注解）

package com.ourslook.yuebaoxiao.Import;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import com.fasterxml.jackson.annotation.JsonFormat;
import com.fasterxml.jackson.databind.annotation.JsonDeserialize;
import com.ourslook.yuebaoxiao.util.jackson.CustomSuperDateDeserializer;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Getter;
import lombok.Setter;

import java.io.Serializable;
import java.util.Date;

/**
 * @author llx
 * @version 1.0.0
 */
@Getter
@Setter
@ApiModel(description = "时间轴Vo")
public class EasTimeDataVo extends BaseRowModel implements Serializable {

    @ExcelProperty(value = "单据ID",index = 0)
    @ApiModelProperty("单据ID")
    private String billId;

    @ExcelProperty(value = "是否发起报销",index = 1)
    @ApiModelProperty("是否发起报销")
    private String isPassSendBiz;


    @ExcelProperty(value = "发起报销时间",index = 2)
    @ApiModelProperty("发起报销时间")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "Asia/Shanghai")
    @JsonDeserialize(using = CustomSuperDateDeserializer.class)
    private Date passSendBizDate;

    @ExcelProperty(value = "是否业务审批",index = 3)
    @ApiModelProperty("是否业务审批")
    private String isBusineAudit;


    @ExcelProperty(value = "业务审批时间",index = 4)
    @ApiModelProperty("业务审批时间")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "Asia/Shanghai")
    @JsonDeserialize(using = CustomSuperDateDeserializer.class)
    private Date passBusineAuditDate;


    @ExcelProperty(value = "是否共享审批",index = 5)
    @ApiModelProperty("是否财务审批")
    private String isShareAudit;

    @ExcelProperty(value = "共享审批时间",index = 6)
    @ApiModelProperty("财务审批时间")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "Asia/Shanghai")
    @JsonDeserialize(using = CustomSuperDateDeserializer.class)
    private Date passShareAuditDate;

    @ExcelProperty(value = "是否等待支付",index = 7)
    @ApiModelProperty("是否等待支付")
    private String isWaitPay;


    @ExcelProperty(value = "等待支付时间",index = 8)
    @ApiModelProperty("等待支付时间")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "Asia/Shanghai")
    @JsonDeserialize(using = CustomSuperDateDeserializer.class)
    private Date waitPayDate;

    @ExcelProperty(value = "是否支付成功",index = 9)
    private String isFinalPay;

    @ExcelProperty(value = "支付成功时间",index = 10)
    @ApiModelProperty("支付成功时间")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "Asia/Shanghai")
    @JsonDeserialize(using = CustomSuperDateDeserializer.class)
    private Date finalPayDate;

}