SpringBoot整合easypoi之IExcelVerifyHandler自定义校验使用

SpringBoot整合easypoi之IExcelVerifyHandler自定义校验使用

  • Controller

@PostMapping("importCompanyExcel")
@ApiOperation(value = "导入公司信息")
@ApiImplicitParams({ @ApiImplicitParam(name = "文件", value = "file", required = true, dataType = "file") })
public Result  importCompanyExcel(@RequestParam(value="file") MultipartFile file, HttpServletResponse response) throws ControllerException {
   try {
      Result result = companyService.importCompanyExcel(file,response);
return result;
} catch (Exception e) {
      return Result.failed("导入失败");
}
}

 

  • Service

/**
 * 导入
 *
 * @param file
 * @return
 */
Result importCompanyExcel(MultipartFile file, HttpServletResponse response)throws ServiceException;

 

  • ServiceImpl

/**
    * 导入
    *
    * @param file
    * @return
    */
   @Override
@Transactional
public Result importCompanyExcel(MultipartFile file, HttpServletResponse response) throws ServiceException {
      ExcelImportResult<CompanyImport> result = null;
try {
         //一.使用EasyPoi获取文件数据
         ImportParams params = new ImportParams();
//设置表头的行数
         params.setHeadRows(1);
//代表导入这里是需要验证的(根据字段上的注解校验)
         params.setNeedVerify(true);
//设及一个自定义校验 (自定义校验公司名不可重复)
         params.setVerifyHandler(companyExcelHandler);
//二.获取excel中的数据,封装成了一个结果对象(有很多东西)
         //导入成功的数据
         result = ExcelImportUtil.importExcelMore(file.getInputStream(), CompanyImport.class, params);
//三.获到正确的数据,并把它们保存到数据库
         List<CompanyImport> list = result.getList();
list.forEach(CompanyImport->{
            log.info("从Excel导入数据到数据库的详细为 :{}", CompanyImport);
Company company = new Company();
BeanUtil.copyProperties(CompanyImport, company);
company.setEnabled(1);
company.setCreateTime(new Date());
companyDao.save(company);
});
//失败结果集
         List<CompanyImport> failList = result.getFailList();
log.info("失败的详细数据 :{}", failList);
//4.2拿到错误的文件薄
         Workbook failWorkbook = result.getFailWorkbook();
if (null != failWorkbook){
            failWorkbook.write(new FileOutputStream(new File("D:\\error.xlsx")));
}
      } catch (Exception e) {
         e.printStackTrace();
}
      return Result.succeed("导入成功");
}

  • Handler

@Component
public class CompanyExcelHandler implements IExcelVerifyHandler<CompanyImport> {

    @Autowired
private CompanyDao companyDao;

/**
     * 导入校验方法
     *
     * @param companyImport 当前对象
     * @return
     */
    @Override
public ExcelVerifyHandlerResult verifyHandler(CompanyImport companyImport) {
        ExcelVerifyHandlerResult result = new ExcelVerifyHandlerResult();
String companyName =companyImport.getCompanyName();
CompanyImport companyimport = companyDao.findOneByname(companyName);
//查询不为空则说明数据库中用户已存在,此次录入为重复录入
        if (companyimport!=null) {
            result.setMsg("唯一校验失败");
result.setSuccess(false);
return result;
}
            result.setSuccess(true);
return result;
}
}

 

  • 前端(layui)

<div class="layui-upload" align="left" style="margin-top: 10px">
    <form class="layui-form" id="uploadForm">
        <button class='layui-btn layui-btn-normal' type="button" id="importExcel" lay-filte="importExcel" >导入文件</button>
        <button type="button" lay-submit="" class="layui-btn layui-btn-warm" id="exportExcel" name="exportExcel"><i class="layui-icon"></i>导出模板</button>
    </form>
</div>

 

//upload上传实例
var uploadInst = upload.render({
    elem: '#importExcel' //绑定元素
     ,url: config.base_server + 'api-doyen/company/importCompanyExcel?access_token=' + config.getToken().access_token//上传接口(PS:这里不用传递整个 excel)
    , auto: false //选择文件后不自动上传
    , accept: 'file'
    , choose: function (obj) {// 选择文件回调
        debugger
        var files = obj.pushFile();
debugger
        if (files){
            layer.msg("导入成功");
}
      },
error : function(){
        setTimeout(function () {
            layer.msg("上传失败!", {
                icon : 1
});
//关闭所有弹出层
            layer.closeAll(); //疯狂模式,关闭所有层
        },1000);
}
});

 

 

 

 

版权声明:
作者:admin
链接:http://lixiaofang.top/2022/01/10/springboot%e6%95%b4%e5%90%88easypoi%e4%b9%8biexcelverifyhandler%e8%87%aa%e5%ae%9a%e4%b9%89%e6%a0%a1%e9%aa%8c%e4%bd%bf%e7%94%a8/
来源:码农日志
文章版权归作者所有,未经允许请勿转载。

THE END
分享
二维码
< <上一篇
下一篇>>