用easyexcel简单导入excel到数据库详细

记录个人初级工作时完整思路

1.建立数据库字段

2.建立实体类

@Data
@EqualsAndHashCode(callSuper = true)
@Accessors(chain = true)
@TableName("info_camper_excel")
@ApiModel(value="CamperExcel对象", description="上传营员名单实体类")
public class CamperExcel extends AbstractEntity {
    private static final long serialVersionUID = 1L;

    @ApiModelProperty(value = "主键")
    @TableId("id")
    private Long id;

    @ApiModelProperty(value = "业务id")
    @TableField("info_id")
    private Long infoId;

    @ApiModelProperty(value = "序号")
    @TableField("serial_no")
    private Long serialNo;


    @ApiModelProperty(value = "姓名")
    @TableField("name")
    private String name;

    @ApiModelProperty(value = "国籍")
    @TableField("nationality")
    private String nationality;

    @ApiModelProperty(value = "性别")
    @TableField("gender")
    private Boolean gender;

    @ApiModelProperty(value = "年龄")
    @TableField("age")
    private Integer age;

    @ApiModelProperty(value = "汉语桥网站注册邮箱")
    @TableField("email")
    private String email;

    @ApiModelProperty(value = "是否孔院学员")
    @TableField("is_ky")
    private Boolean isKy;

    @ApiModelProperty(value = "护照号或本国ID号")
    @TableField("passport_no")
    private Long passportNo;


    @ApiModelProperty(value = "创建时间")
    @TableField(value = "create_time",fill = FieldFill.INSERT)
    private LocalDateTime createTime;

}

 3.建立dto实体类

@Data
@ApiModel(value="CamperExcel对象", description="上传营员名单实体类")
public class CamperExcelDTO implements Serializable  {
    private static final long serialVersionUID = 1L;


    @ApiModelProperty(value = "序号")
    @ExcelProperty(value = "序号",index = 0)
    private Long serialNo;

    @ApiModelProperty(value = "姓名")
    @ExcelProperty(value = "姓名",index = 1)
    private String name;

    @ApiModelProperty(value = "性别")
    @ExcelProperty(value = "性别", index = 2,converter = GenderConverter.class)
    private Boolean gender;

    @ApiModelProperty(value = "年龄")
    @ExcelProperty(value = "年龄",index = 3)
    private Integer age;

    @ApiModelProperty(value = "国籍")
    @ExcelProperty(value = "国籍",index = 4)
    private String nationality;

    @ApiModelProperty(value = "汉语桥网站注册邮箱")
    @ExcelProperty(value = "汉语桥网站注册邮箱",index = 5)
    private String email;


    @ApiModelProperty(value = "护照号或本国ID号")
    @ExcelProperty(value = "护照号或本国ID号",index = 6)
    private Long passportNo;


    @ApiModelProperty(value = "是否孔院学员")
    @ExcelProperty(value = "是否孔院学员",index = 7,converter = IsKyConverter.class)
    private Boolean isKy;



    public CamperExcel convertEntity() {
        CamperExcel entity = new CamperExcel();
        BeanUtils.copyProperties(this, entity);
        return entity;
    }

    public CamperExcelDTO convertVO(CamperExcel entity) {
        BeanUtils.copyProperties(entity, this);
        return this;
    }

}

4.建立前后端交互VO对象

@Data
@Accessors(chain = true)
@ApiModel(value = "CamperExcelVO对象", description = "上传营员名单VO")
@AllArgsConstructor
@NoArgsConstructor
public class CamperExcelVO implements Serializable {

    private static final long serialVersionUID = 1L;

    @ApiModelProperty(value = "infoId")
    @NotNull(message="infoId不能为空")
    private Long infoId;


    @ApiModelProperty(value = "data")
    private List<CamperExcel> camperExcelList;

@Data
public class ResultInfo implements Serializable {
    private Integer code; //编码：200成功，500和其它数字为失败
    private String msg; //错误信息
    private Object data; //数据

    //成功结果
    public static ResultInfo success(Object object) {
        ResultInfo resultInfo = new ResultInfo();
        resultInfo.data = object;
        resultInfo.code = 200;
        return resultInfo;
    }

    //失败结果
    public static ResultInfo error(String msg) {
        ResultInfo resultInfo = new ResultInfo();
        resultInfo.msg = msg;
        resultInfo.code = 500;
        return resultInfo;
    }
}

5.easyExcel监听器

@Slf4j
@Component
public class ExcelListener extends AnalysisEventListener<CamperExcelDTO> {

    public static Long infoId = 0L;

    @Autowired
    private CamperExcelService camperExcelService;

    //删除记录数
    private Integer count  = 0;

    //批量保存
    private List<CamperExcel> camperExcelList = new ArrayList<>();

    @Override
    public void invoke(CamperExcelDTO data, AnalysisContext context) {
        // 每读到一条记录,执行这个方法
        log.info("读取到一条数据{}", JSON.toJSONString(data));

        CamperExcel camperExcel = data.convertEntity();
        camperExcel.setInfoId(infoId);
        // 更新数据到数据库中 单个数据存储
        // camperExcelService.save(camperExcel);

        //放到集合里,进行批量存储
        camperExcelList.add(camperExcel);
    }

    //批量插入
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 读完所有记录后执行这个方法
        log.info("数据读取完成");
        // 这里保存数据，数据存储到数据库
        saveData();

        log.info("所有数据解析完成！");

        //清空list集合
        camperExcelList.clear();

        log.info("删除{}条旧数据",count);
    }

    private void saveData() {
        log.info("{}条数据，开始存储数据库！", camperExcelList.size());

        count = camperExcelService.deleteByInfoId(infoId);

        camperExcelService.saveBatch(camperExcelList);

        log.info("存储数据库成功！");
    }
}

6.Boolean字段前后端转换器

//性别excel和java实体类转换器
public class GenderConverter implements Converter<Boolean> {
    @Override
    public Class supportJavaTypeKey() {
        return Boolean.class;
    }

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

    @Override
    public Boolean convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        return "男".equals(cellData.getStringValue()) ? true : false;
    }

    @Override
    public CellData convertToExcelData(Boolean value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        return new CellData(value.equals(true) ? "男" : "女");
    }
}

//是否孔院excel和java实体类转换器
public class IsKyConverter implements Converter<Boolean> {
    @Override
    public Class supportJavaTypeKey() {
        return Boolean.class;
    }

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

    @Override
    public Boolean convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        return "是".equals(cellData.getStringValue()) ? true : false;
    }

    @Override
    public CellData convertToExcelData(Boolean value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        return new CellData(value.equals(true) ? "是" : "否");
    }
}

7.controller层

@Api(tags = "上传营员名单Excel")
@ResponseResult
@RestController
@RequestMapping("/camperExcel")
public class CamperExcelController {
    @Autowired
    private ExcelListener excelListener;


    @GetMapping("/templateDownLoad")
    public void templateDownLoad(HttpServletRequest request, HttpServletResponse response) throws IOException {
        //下载excel的响应头
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        //读取模板文件，写入到文件中
        String realPath = request.getServletContext().getRealPath("/template/营员名单模板.xlsx");
        File file = new File(realPath);
        byte[] bytes = FileUtils.readFileToByteArray(file);
        //下载文件名是中文，转码，不然会出现乱码
        String fileName = URLEncoder.encode("营员名单模板", "utf-8");
        //告诉浏览器下载文件名
        response.addHeader("Content-Disposition", "attachment;filename="+fileName + ".xlsx");
        // 得到指向浏览器的输出流，向浏览器输出
        ServletOutputStream outputStream = response.getOutputStream();
        outputStream.write(bytes);
        outputStream.close();
    }


    @PostMapping("/import/{infoId}")
    public ResultInfo importExcel(@RequestBody MultipartFile file,@PathVariable Long infoId) throws Exception {
        //避免预检请求报错
        if (file != null) {
            /*
            * 参数1：文件流
            * 参数2： 读取数据封装
            * 参数3： 监听器，每读取一行数据，会调用监听器的invoke方法*/
            ExcelListener.infoId = infoId;
            ExcelReaderBuilder read = EasyExcel.read(file.getInputStream(), CamperExcelDTO.class, excelListener);
            ExcelReaderSheetBuilder sheet = read.sheet();
            sheet.doRead();
            return ResultInfo.success("数据存储完成...");
        }
        return ResultInfo.error("服务器内部异常，请稍后再次尝试...");
    }
}

8.service 和 实现类

public interface CamperExcelService extends MyDoipBaseService<CamperExcel> {
    
    Integer deleteByInfoId(Long infoId)throws BusinessException;

    
    void save(CamperExcelDTO entity)throws BusinessException;

    
    Boolean saveBatch(CamperExcelVO vo) throws BusinessException;
    
}

@Service
@Transactional
public class CamperExcelImpl extends MyDoipBaseServiceImpl<CamperExcelMapper, CamperExcel> implements CamperExcelService {


    @Override
    public Integer deleteByInfoId(Long infoId) throws BusinessException {
        int delete = baseMapper.delete(Wrappers.<CamperExcel>lambdaQuery().eq(CamperExcel::getInfoId, infoId));
        return Integer.valueOf(delete);
    }

    @Override
    public void save(CamperExcelDTO entity) throws BusinessException {
        CamperExcel camperExcel = entity.convertEntity();
        baseMapper.insert(camperExcel);
    }

    @Override
    public Boolean saveBatch(CamperExcelVO vo) throws BusinessException {
        baseMapper.delete(Wrappers.<CamperExcel>lambdaQuery().eq(CamperExcel::getInfoId, vo.getInfoId()));
        this.saveBatch(vo.getCamperExcelList());
        return null;
    }
}

9.mappper

@Repository
public interface CamperExcelMapper extends MyDoipBaseMapper<CamperExcel> {
}

10.模板存储位置

11.模板样式

12.数据库存储效果