使用ExcelImportUtil实现Excel模板数据导入

1.业务场景：导入excel中的号码集合，实现短信通知（提供模板供用户下载填写导入，也就是起到我们开发人员对模板格式固定解析的作用）

 

 2引入maven依赖

maven依赖

<!-- AutoPoi Excel工具类-->
		<dependency>
			<groupId>org.jeecgframework</groupId>
			<artifactId>autopoi-web</artifactId>
			<version>${autopoi-web.version}</version>
			<exclusions>
				<exclusion>
					<groupId>commons-codec</groupId>
					<artifactId>commons-codec</artifactId>
				</exclusion>
			</exclusions>
		</dependency>

 3.这里没再细分controller和service，所有处理都放在controller

controller层

@ApiOperation(value = "-导入号码", notes = "-导入号码")
    @PostMapping(value = "/importPhone")
    public Result importPhone(HttpServletRequest request, HttpServletResponse response) {
        MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
        Map<String, MultipartFile> fileMap = multipartRequest.getFileMap();
        for (Map.Entry<String, MultipartFile> entity : fileMap.entrySet()) {
            MultipartFile file = entity.getValue();// 获取上传文件对象
            ImportParams params = new ImportParams();
            // 表格标题占用行数
            params.setTitleRows(1);
            // 表头占用行数
            params.setHeadRows(0);
            // 是否需要保存上传的Excel
            params.setNeedSave(true);
            try {
                List<PhoneNumberParam> list = ExcelImportUtil.importExcel(file.getInputStream(), PhoneNumberParam.class, params);
//对解析的数据做你需要的业务处理，比如：
                //去除null值
                list.removeAll(Collections.singleton(new PhoneNumberParam()));
                if (list.size() == 0) {
                    return Result.error("文件导入失败:" + "号码为空");
                }
                //去除phone为空的对象
                list.removeIf(element -> StringUtils.isEmpty(element.getPhone()));
                //重复号码去除
                Set<PhoneNumberParam> set = new TreeSet<>(Comparator.comparing(PhoneNumberParam::getPhone));
                set.addAll(list);
                List<PhoneNumberParam> distinctList = new ArrayList<>(set);
                //号码位数校验
                distinctList.removeIf(element -> element.getPhone().length()!= 11);
                //序号字段排序
                int number = 1;
                for (PhoneNumberParam record : distinctList) {
                    record.setNo(String.valueOf(number));
                    number++;
                }
                long start = System.currentTimeMillis();
                log.info("消耗时间" + (System.currentTimeMillis() - start) + "毫秒");
                return Result.OK("文件导入成功！数据行数：" + distinctList.size(), distinctList);
            } catch (Exception e) {
                log.error(e.getMessage(), e);
                return Result.error("文件导入失败:" + e.getMessage());
            } finally {
                try {
                    file.getInputStream().close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return Result.error("文件导入失败！");
    }

4.根据你的业务字段定义entity，这里我模板只有序号、手机号码，所以只定义这两字段就好

entity

@Data
public class PhoneNumberParam {

    @Excel(name="序号")
    private String no;

    @Excel(name="手机号码")
    private String phone;
}

5.前端传入二进制excel文件，这边接收处理数据后返回，效果如下：

 如果文章对你有帮助还请点个赞收藏关注一波，谢谢！