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("导入成功");
    }
发布了2 篇原创文章 · 获赞 0 · 访问量 10
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 游动-白 设计师: 上身试试

分享到微信朋友圈

×

扫一扫,手机浏览