easy poi 导入Excel校验

最新推荐文章于 2024-05-08 18:01:50 发布

write-bug

最新推荐文章于 2024-05-08 18:01:50 发布

阅读量1.9k

点赞数 1

文章标签： excel java 开发语言

本文链接：https://blog.csdn.net/qq_36483733/article/details/130641861

版权

一、easy poi

1.easypoi 文档

easypoi官方文档(即将停用): https://easypoi.mydoc.io/
easypoi官方文档（新地址）: http://doc.wupaas.com/docs/easypoi/easypoi-1c0u9g4jihrvq

2.maven 引入

       <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>4.1.0</version>
        </dependency>

二、代码

一、导入验证

1.新建一个错误信息类,实现IExcelModel, IExcelDataModel 接口

@Data
public class ExcelVerifyInfo  implements IExcelModel, IExcelDataModel {

    /**
     * 错误信息
     */
    private String errorMsg;

    /**
     * 错误行
     */
    private int rowNum;


    @Override
    public Integer getRowNum() {
        return rowNum;
    }

    @Override
    public void setRowNum(Integer rowNum) {
        this.rowNum = rowNum;
    }

    @Override
    public String getErrorMsg() {
        return errorMsg;
    }

    @Override
    public void setErrorMsg(String errorMsg) {
        this.errorMsg = errorMsg;
    }
}

2.新建一个导入Dto类继承 ExcelVerifyInfo

@Data
public class SysSchoolImportDto  extends ExcelVerifyInfo {

    @NotBlank(message = "不能为空")
    @Excel(name = "学校名称",width = 30)
    private String name;

    @NotBlank(message = "不能为空")
    @Excel(name = "地市名称",width = 30)
    private String cityName;

    @Excel(name = "联系方式",width = 15)
    private String telephone;

    private Integer regionCode;

    @Excel(name = "学校地址",width = 30)
    private String address;

    @Excel(name = "学校网址",width = 30)
    private String website;

    @Excel(name = "类型")
    private String typeText;

    @Excel(name = "错误信息",width = 40)
    private String errorMsg;

    private Integer type;

}

3. 新建导入接口

/**
     * 导入Excel
     * @param file
     * @param response
     * @return
     * @throws Exception
     */
    Map<String,Object> importExcel(MultipartFile file, HttpServletResponse response) throws Exception;

4.在实现类中新建一个内部类 VerifyResult 继承 IExcelVerifyHandler

@Service
    class VerifyResult implements IExcelVerifyHandler<SysSchoolImportDto> {

        @Autowired
        private SysRegionMapper sysRegionMapper;

        @Autowired
        private SysSchoolMapper sysSchoolMapper;

        private Set<String> hashSet = new HashSet<>();

        @Override
        public ExcelVerifyHandlerResult verifyHandler(SysSchoolImportDto importDto) {
            ExcelVerifyHandlerResult result = new ExcelVerifyHandlerResult(true);
            SysRegionVo sysRegionVo = new SysRegionVo();
            if (StrUtil.isNotEmpty(importDto.getName())) {
                // 根据地区名查询地市
                sysRegionVo = sysRegionMapper.selectSysRegionByCity(importDto.getCityName());
            }
            // 根据学校名查询学校是否存在
            List<SysSchoolVo> sysSchoolVos = sysSchoolMapper.selectSysSchoolList(importDto.getName(), null, null);
            List<String> msg = new ArrayList<>();
                if (StrUtil.isNotEmpty(importDto.getCityName()) && ObjectUtil.isEmpty(sysRegionVo)) {
                    result.setSuccess(false);
                    msg.add("地区不存在");
                } else if (StrUtil.isNotEmpty(importDto.getCityName()) && ObjectUtil.isNotEmpty(sysRegionVo)){
                    importDto.setRegionCode(sysRegionVo.getCode());

            }
                if (CollectionUtil.isNotEmpty(sysSchoolVos)) {
                    result.setSuccess(false);
                    msg.add("学校已存在");
                }

            if (StrUtil.isNotEmpty(importDto.getName()) && !hashSet.add(importDto.getName())) {
                result.setSuccess(false);
                msg.add("导入两个重复的学校");
            }

            if (StrUtil.isNotEmpty(importDto.getTelephone()) && !Validator.isMobile(importDto.getTelephone())) {
                result.setSuccess(false);
                msg.add("手机格式错误");
            }

            if ("小学".equals(importDto.getTypeText())) {
                importDto.setType(1);
            } else if ("中学".equals(importDto.getTypeText())) {
                importDto.setType(2);
            } else {
                importDto.setType(3);
            }
            result.setMsg(CollectionUtil.join(msg,","));
            return result;
        }
    }

5.实现接口方法

注：这里用到了redis，将错误的数据存入到redis 中

    /**
    * 验证方法
    */
    @Autowired
    private VerifyResult verifyResult;


 public Map<String, Object> importExcel(MultipartFile file, HttpServletResponse response) throws Exception {
        Map<String,Object> map = new HashMap<>();
        ImportParams importParams = POIUtil.createImportParams(0, 1);
        importParams.setNeedVerify(true);
        importParams.setVerifyHandler(verifyResult);
        ExcelImportResult<SysSchoolImportDto> result = ExcelImportUtil.importExcelMore(
                file.getInputStream(), SysSchoolImportDto.class, importParams);
        // 判断导入的Excel 是否为空
        if (CollectionUtil.isEmpty(result.getList()) && CollectionUtil.isEmpty(result.getFailList())) {
            throw new TreachException(NdCodeEnum.ERROR_IMPORT_EMPTY);
        }
        // 成功数据
        map.put("list",result.getList());
        // 成功数量
        map.put("success",result.getList().size());
        // 失败信息
        map.put("failList",result.getFailList());
        // 失败数量
        map.put("failed",result.getFailList().size());
        String key = DateUtil.format(new Date(),DateUtil.YMDHMS_FORMAT);
        map.put("key",key);
        if (CollectionUtil.isNotEmpty(result.getList())) {
            List<SysSchool> sysSchools = new ArrayList<>();
            result.getList().stream().forEach(schoolImportDto -> {
                SysSchool sysSchool = new SysSchool();
            });
            // 新增有效的数据
            int insertCount = sysSchoolMapper.insertBatch(sysSchools);
        }
        if (ObjectUtil.isNotEmpty(result.getFailList())) {
            String error = JSON.toJSONString(result.getFailList());
            // 将失败数据存入Redis
            redisUtils.set(key,error);
            // 设置过期时间为120 s
            redisUtils.expire(key,120);

        }

        return map;
    }

6.导入数据如下

在这里插入图片描述

7.导入数据

在这里插入图片描述

二、下载导入的错误文件

1.定义接口

 /**
     * 下载失败文件
     * @param key
     * @param request
     * @param response
     * @throws IllegalAccessException
     * @throws InstantiationException
     * @throws IOException
     * @throws NoSuchFieldException
     */
    void downloadFailExcelFile(String key, HttpServletRequest request,
                               HttpServletResponse response) throws IllegalAccessException, InstantiationException, IOException, NoSuchFieldException;

2.实现接口

@Override
    public void downloadFailExcelFile(String key, HttpServletRequest request, HttpServletResponse response) throws IllegalAccessException, InstantiationException, IOException, NoSuchFieldException {
        ExportParams exportParams = POIUtil.createExportParams("学校列表", "学校导入失败");
        HSSFColor.HSSFColorPredefined.RED.getIndex();
        Object object = redisUtils.get(key);
        List<SysSchoolImportDto> list = JSONArray.parseArray(redisUtils.get(key).toString(), SysSchoolImportDto.class);
        SysSchoolImportDto vo = new SysSchoolImportDto();
        Field[] fields = vo.getClass().getDeclaredFields();
        List<String> stringList = new ArrayList<>();
        for (Field item : fields) {
            stringList.add(item.getName());
        }
        String[] fieldNames = new String[stringList.size()];
        fieldNames = stringList.toArray(fieldNames);
        //String[] fieldNames={"name","account","errorMsg"};
        Workbook workbook = POIUtil.exportExcelCustom(SysSchoolImportDto.class,list,fieldNames,exportParams);

        POIUtil.downloadWorkbook(workbook,request,response);
    }

3.下载错误文件

在这里插入图片描述

4.错误文件数据如图所示

在这里插入图片描述

5.导入所用到的工具类

 /**
     * 导出对象指定属性导出
     * @param clz
     * @param dataList
     * @param fieldNames  所要导出的属性名，导出顺组为数组中的属性名顺序
     * @param exportParams  导出参数配置调用createExportParams方法获取
     * @return
     */
    public static Workbook exportExcelCustom(Class<?> clz, List dataList, String[] fieldNames, ExportParams exportParams) throws NoSuchFieldException, InstantiationException, IllegalAccessException {
        handleEmptyList(clz,dataList);
        //设置要导出的属性
        List<ExcelExportEntity> entityList = setExportField(clz, fieldNames);
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, entityList, dataList);
        return workbook;
    }