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文件,这边接收处理数据后返回,效果如下:
如果文章对你有帮助还请点个赞收藏关注一波,谢谢!