需求
前端页面点击文件导入,弹框选择Excel文件,发送表单请求传入后端进行数据解析、批量落库(支持发往多个节点分别落库),并把单条处理结果写入Excel,成功返回Excel文件,异常信息使用http响应返回。
依赖版本
Hutool-poi版本为5.8.9,具体依赖引入可参考Hutool官方文档:概述 (hutool.cn)
代码参考
- Controller
@Autowired
private HttpServletResponse response;
public ResponseDTO batchImport(@RequestParam("file") MultipartFile file) {
ResponseDTO responseDTO = new ResponseDTO();
ServletOutputStream outputStream = null;
List<SingleRequestDTO> batchList;
List<SingleResponseDTO> rows;
try {
//解析写入
outputStream = response.getOutputStream();
String originalFilename = file.getOriginalFilename();
String[] split = originalFilename.split("\\.");
String headerContentDisposition = "attachment;filename=" + URLEncoder.encode(split[0] + "Result.xlsx", "utf-8");
response.addHeader("Content-Disposition", headerContentDisposition);
batchList = batchService.import2List(file);
if (batchList.size() == 0) {
throw new RuntimeException ("无可操作的数据行,请填写数据后再上传!");
}
//多线程批量落库
ResponseDTO<List<SingleResponseDTO>> responseDTO = businessService.batchImport(batchList);
//...结果判断、多节点返回数据条数判定逻辑处理 此处省略
//获取处理结果
rows = responseDTO.getRows();
//封装写出
BigExcelWriter writer = ExcelUtil.getBigWriter();
//写出的Excel字段展示 跟传入的文件字段一一对应
writer.addHeaderAlias("custNo","客户号");
//writer.setColumnWidth(列序号, 列宽); 列宽与Excel表格内列宽一致 单位均为字符
writer.setColumnWidth(0, 20);
//... 省略其他字段
//只写出加了别名的字段
writer.setOnlyAlias(true);
ExportExcelUtil.exportExcel(writer, batchList, rows);
writer.flush(outputStream,true);
} catch (Exception e) {
responseDTO.failed("文件导入失败!" + e);
return responseDTO;
} finally {
if (null != outputStream) outputStream.close();
}
return responseDTO;
}
- Service
@Override
public List<SingleRequestDTO> import2List(MultipartFile file) throws MappingException {
List<SingleRequestDTO> importList = new ArrayList<>();
try{
//Single默认读第一个sheet页
ExcelUtil.readBySax(file.getInputStream(),0,new RowHandler() {
@Override
public void handle(int sheetIndex, long rowIndex, List<Object> rowlist) {
if (rowIndex != 0 && !isRowEmpty(rowlist)){
SingleRequestDTO requestDTO = new SingleRequestDTO();
// 客户号
requestDTO.setCustNo(rowlist.get(0) != null? rowlist.get(0).toString():"");
//... 其他字段映射、多节点赋值逻辑省略
importList.add(requestDTO);
}
}
});
}catch (Exception e){
throw new MappingException("mapping error:"+e);
}
return importList;
}
private boolean isRowEmpty(List list){
Iterator iterator = list.iterator();
while (iterator.hasNext()){
Object next = iterator.next();
if (next != null && StringUtils.isNotBlank(next.toString())){
return false;
}
}
return true;
}
- Utils
/**
* 实体bean+处理结果映射为Map写出Excel 再写入传入的ExcelWriter对象
*/
public static void exportExcel(ExcelWriter writer, List<SingleRequestDTO> batchList , List<SingleResponseDTO> rows) {
String sysNo = null;
//... 获取当前节点信息逻辑省略
//sheet页重命名(当前节点)
writer.renameSheet(sysNo + "节点");
//根据不同节点返回的操作结果写入多个sheet页
Map<String, List<SingleResponseDTO>> sysNoMap = rows.stream().collect(Collectors.groupingBy(SingleResponseDTO::getSysNo));
for (Map.Entry<String, List<SingleResponseDTO>> entity : sysNoMap.entrySet()) {
List<Object> list = new ArrayList<>();
String s = entity.getKey();
//如果当前节点不是第一个sheet页的节点信息 切换新的sheet页
if (!s.equals(sysNo)){
writer.setSheet(s+"节点");
}
List<SingleResponseDTO> value = entity.getValue();
for (int i = 0; i < value.size(); i++) {
try {
//BeanToMap 网上有很多教程 此处不再展开
Map<String, Object> singleMap = BeanToMapUtil.convertBean(batchList.get(i));
singleMap.put("operationMsg", value.get(i).getOperationMsg());
list.add(singleMap);
} catch (Exception e) {
throw new MappingException("operationMsg mapping error!");
}
}
writer.write(list, true);
}
}
笔者曾考虑三种方式:1、直接将处理结果顺序写入一列传入的文件流,再返回给前端;2、请求Bean动态添加操作结果字段,使用Hutool封装返回;3、请求Bean反射为Map,添加操作结果键值对,使用Hutool封装返回。经过效率测试后,目前采用的也正是第三种方式。
有问题欢迎指出,欢迎讨论~