easyExcel批量导入数据

E在这里插入代码片批量导入Excel数据
加粗样式
实体类（数据库对应字段即可）
package gov.pbc.xjcloud.provider.information.entity;

import cn.afterturn.easypoi.excel.annotation.Excel;
import com.baomidou.mybatisplus.enums.IdType;
import java.util.Date;
import com.baomidou.mybatisplus.annotations.TableId;
import com.baomidou.mybatisplus.activerecord.Model;
import java.io.Serializable;

import lombok.*;
import lombok.experimental.Accessors;

/**

• 信息登记

• @author panpengbo

• @since 2020-01-14
  */
  @Data
  @AllArgsConstructor
  @NoArgsConstructor
  @Getter
  @Accessors(chain = true)
  public class AbroadInformation extends Model{

  private static final long serialVersionUID = 1L;

  @TableId(value = “abroad_id”, type = IdType.AUTO)
  private Long abroadId;
  /**

  • 姓名
    /
    private String abroadUsername;
    /*
  • 身份证号
    /
    private String idcard;
    /*
  • 所在处室
    /
    private String perDeptName;
    /*
  • 职务
    /
    private String pos;
    /*
  • 证件类型
    /
    private String documentType;
    /*
  • 证件号
    /
    private String certificateNumber;
    /*
  • 签发日期
    /
    private Date dateOfIssue;
    /*
  • 有效期
    /
    private Date termOfValidity;
    /*
  • 当前保管状态
    /
    private String currentCustodyStatus;
    /*
  • 新办上交时间
    /
    private Date newUpperTime;
    /*
  • 领用时间
    /
    private Date leadTime;
    /*
  • 归还时间
    /
    private Date returnTime;
    /*
  • 领用记录
    /
    private Integer collarRecord;
    /*
  • 操作类型
    /
    private String operationType;
    /*
  • 创建时间
    /
    private Date createTime;
    /*
  • 修改时间
    /
    private Date updateTime;
    /*
  • 删除标志
    */
    private String deleteFlag;

  @Override
  protected Serializable pkVal() {
  return this.abroadId;
  }
  }

实体类对象模型（Excel表格）

package gov.pbc.xjcloud.provider.information.model;

import com.alibaba.excel.annotation.ExcelProperty;
import gov.pbc.xjcloud.provider.information.controller.relove.StringToDateRelove;
import lombok.Data;

import java.util.Date;

/**
 * @Author: sky
 * @Date 2020/1/14 21:17
 */
@Data
public class InformationModel {
    /**
     * 姓名
     */
    @ExcelProperty(value = "姓名",index = 0)
    private String abroadUsername;
    /**
     * 身份证号
     */
    @ExcelProperty(value = "身份证号",index = 1)
    private String idcard;
    /**
     * 所在处室
     */
    @ExcelProperty(value = "所在处室",index = 2)
    private String perDeptName;
    /**
     * 职务
     */
    @ExcelProperty(value = "职务",index = 3)
    private String pos;
    /**
     * 证件类型
     */
    @ExcelProperty(value = "证件类型",index = 4)
    private String documentType;
    /**
     * 证件号
     */
    @ExcelProperty(value = "证件号",index = 5)
    private String certificateNumber;
    /**
     * 签发日期
     */
    @ExcelProperty(value = "签发日期",index = 6,converter = StringToDateRelove.class)
    private Date dateOfIssue;
    /**
     * 有效期
     */
    @ExcelProperty(value = "有效期",index = 7,converter = StringToDateRelove.class)
    private Date termOfValidity;
    /**
     * 当前保管状态
     */
    @ExcelProperty(value = "当前保管状态",index = 8)
    private String currentCustodyStatus;
    /**
     * 新办上交时间
     */
    @ExcelProperty(value = "新办上交时间",index = 9,converter = StringToDateRelove.class)
    private Date newUpperTime;
    /**
     * 领用时间
     */
    @ExcelProperty(value = "领用时间",index = 10,converter = StringToDateRelove.class)
    private Date leadTime;
    /**
     * 归还时间
     */
    @ExcelProperty(value = "归还时间",index = 11,converter = StringToDateRelove.class)
    private Date returnTime;
    /**
     * 领用记录
     */
    @ExcelProperty(value = "领用记录",index = 12)
    private Integer collarRecord;
}

监听器

package gov.pbc.xjcloud.provider.information.controller.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.exception.ExcelDataConvertException;
import gov.pbc.xjcloud.feamework.common.ennums.ResultStatusEnum;
import gov.pbc.xjcloud.feamework.exception.ServiceException;
import gov.pbc.xjcloud.provider.information.entity.AbroadInformation;
import gov.pbc.xjcloud.provider.information.model.InformationModel;
import gov.pbc.xjcloud.provider.information.service.AbroadInformationService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

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

/**
 * @Author: sky
 * @Date 2020/1/14 19:04
 * 证件信息导入监听器
 */
public class InformationListener extends AnalysisEventListener<InformationModel> {
    private static final Logger LOGGER =
            LoggerFactory.getLogger(InformationListener.class);
    /**
     * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 1000;
    List<InformationModel> list = new ArrayList<>();
    private AbroadInformationService abroadInformationService;

    public InformationListener(AbroadInformationService abroadInformationService) {
        this.abroadInformationService = abroadInformationService;
    }

    /**
     * 异常处理
     *
     * @param exception
     * @param context
     */
    @Override
    public void onException(Exception exception, AnalysisContext context) {
        LOGGER.error("解析失败{}", exception.getMessage());
        // 如果是某一个单元格的转换异常 能获取到具体行号
        // 如果要获取头的信息 配合invokeHeadMap使用
        if (exception instanceof ExcelDataConvertException) {

            ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException) exception;
            LOGGER.error("第{}行，第{}列解析异常", excelDataConvertException.getRowIndex(),
                    excelDataConvertException.getColumnIndex()
                            + ",异常数据为:" + excelDataConvertException.getCellData()
                            + ",异常列属性为:" + excelDataConvertException.getExcelContentProperty().getHead());

            throw new ServiceException(300
                    , "第" + excelDataConvertException.getRowIndex() + "行，第{" + excelDataConvertException.getColumnIndex() + "}列解析异常" +
                    exception.getMessage());

        }
    }

    @Override
    public void invoke(InformationModel data, AnalysisContext analysisContext) {
        LOGGER.info("解析到一条数据:{}", data.toString());
        if (null == data) {
            throw new ServiceException(300, ResultStatusEnum.BATCH_INSERT_ERROR.getMsg() + "存在空行!");
        }
        list.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            list.clear();
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        LOGGER.info("所有数据解析完成！");
    }

    /**
     * 保存导入数据至数据库
     */
    private void saveData() {
        LOGGER.info("{}条数据，开始存储数据库！", list.size());
        abroadInformationService.insertBatch(modelToInformation(list));
        LOGGER.info("存储数据库成功！");
    }

    private List<AbroadInformation> modelToInformation(List<InformationModel> models) {
        List<AbroadInformation> abroadInformations = new ArrayList<>();
        for (InformationModel model : list) {
            AbroadInformation abroadInformation = new AbroadInformation();
            abroadInformation.setAbroadUsername(model.getAbroadUsername());
            //判断重复插入数据
            abroadInformation.setIdcard(model.getIdcard());
            abroadInformation.setPerDeptName(model.getPerDeptName());
            abroadInformation.setPos(model.getPos());
            abroadInformation.setDocumentType(model.getDocumentType());
            abroadInformation.setCertificateNumber(model.getCertificateNumber());
            abroadInformation.setDateOfIssue(model.getDateOfIssue());
            abroadInformation.setTermOfValidity(model.getTermOfValidity());
            abroadInformation.setCurrentCustodyStatus(model.getCurrentCustodyStatus());
            abroadInformation.setNewUpperTime(model.getNewUpperTime());
            abroadInformation.setLeadTime(model.getLeadTime());
            abroadInformation.setReturnTime(model.getReturnTime());
            abroadInformation.setCollarRecord(model.getCollarRecord());
            abroadInformations.add(abroadInformation);
        }
        return abroadInformations;
    }
}

日期格式转换

package gov.pbc.xjcloud.provider.information.controller.relove;

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;

import java.text.SimpleDateFormat;
import java.util.Date;

/**
 * @Author: sky StringToDate
 * @Date 2020/1/14 22:43
 */
public class StringToDateRelove implements Converter<Date> {
    SimpleDateFormat sdf =   new SimpleDateFormat( "yyyy-MM-dd HH:mm:ss" );
    @Override
    public Class supportJavaTypeKey() {
        return Date.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.NUMBER;
    }

    @Override
    public Date convertToJavaData(CellData cellData, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        System.out.println(excelContentProperty.getHead().getFieldName()+"列名："+excelContentProperty.getField().getName());

        if (CellDataTypeEnum.NUMBER.equals(cellData.getType())){
            Double dv = cellData.getNumberValue().doubleValue();
            String dateV = String.valueOf(dv);
            StringBuilder dstr = new StringBuilder();
            String[] split = dateV.split("\\.");
            for (int i = 0; i < split.length; i++) {
                if (i==split.length-1){
                    dstr.append(split[i]);
                }else {
                    dstr.append(split[i]);
                    dstr.append("-");
                }

            }
            return new SimpleDateFormat("yyyy-MM-dd").parse(dstr.toString());
        }
        else if (CellDataTypeEnum.STRING.equals(cellData.getType())){
            String data = cellData.getStringValue();
            String[] split = data.split("\\.");
            StringBuilder dstr = new StringBuilder();
            for (int i = 0; i < split.length; i++) {
                if (i==split.length-1){
                    dstr.append(split[i]);
                }else {
                    dstr.append(split[i]);
                    dstr.append("-");
                }
            }
            return new SimpleDateFormat("yyyy-MM-dd").parse(dstr.toString());
        }
        return null;
    }
    @Override
    public CellData convertToExcelData(Date date, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        return new CellData(date);
    }
}

控制层

 @RequestMapping("/information/import")
    @ResponseBody
    public R<String> importInformation(@RequestParam(name = "file") MultipartFile file) throws Exception {
        try {
            InputStream inputStream = file.getInputStream();
            EasyExcel.read(inputStream, InformationModel.class, new InformationListener(abroadInformationService)).sheet()
                    .headRowNumber(1).doRead();
        } catch (IOException e) {
            e.printStackTrace();
            throw new ServiceException(300, "导入失败，请重新导入");
        }
        return new R("导入成功");
    }