Controller层代码
@Value("${jeecg.path.upload}") private String multipartPre; private static List<List<String>> head = new ArrayList<>(); static { head = new ArrayList<>(Arrays.asList( Collections.singletonList("编号"), Collections.singletonList("所属层级"), Collections.singletonList("原料编号"), Collections.singletonList("原料名称"), Collections.singletonList("原料状态"), Collections.singletonList("创建时间"), Collections.singletonList("更新时间"), Collections.singletonList("录入人"))); }
@ApiOperation(value = "模板导出") @GetMapping("downloadTemp") public void downloadTemp(HttpServletResponse response) throws Exception { response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setCharacterEncoding("utf-8"); String fileName = URLEncoder.encode("数据模板", "UTF-8").replaceAll("\\+", "%20"); response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx"); List<List<Object>> list = ListUtils.newArrayList(); EasyExcel.write(response.getOutputStream()).head(head).sheet("data").doWrite(list); } @ApiOperation(value = "导出Excel数据") @PostMapping("export") public void exportAllStructData(@RequestBody MaterialInfoRequestDTO materialInfoRequestDTO, HttpServletResponse response){ try (OutputStream out = response.getOutputStream()) { List<MaterialInfoPO> materialInfoPOS = iMaterialInfoService.queryList(materialInfoRequestDTO); //组装Excel内容 List<List<String>> content = new ArrayList<>(); materialInfoPOS.stream().forEach(m->{ List<String> data = new ArrayList<String>(); data.add(String.valueOf(m.getOrderNo())); data.add(m.getLevelName()); data.add(m.getMaterialNo()); data.add(m.getMaterialName()); data.add(String.valueOf(m.getMaterialStatus())); DateFormat dateformat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss"); data.add(dateformat.format(m.getCreateTime())); data.add(dateformat.format(m.getUpdateTime())); data.add(m.getRecorder()); content.add(data); }); response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setCharacterEncoding("utf-8"); String fileName = URLEncoder.encode("catalog", "UTF-8").replaceAll("\\+", "%20"); response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx"); ExcelWriter excelWriter = EasyExcelFactory.write(out).build(); WriteSheet data = EasyExcel.writerSheet(0, "data").head(head).build(); excelWriter.write(content, data); excelWriter.finish(); out.flush(); } catch (Exception e) { throw new JeecgBootException("下载Excel失败!"+e.getMessage()); } } @ApiOperation(value = "上传Excel数据") @PostMapping("upload") public void uploadData(@RequestParam("file") MultipartFile file, @RequestParam("ids") List<Integer> ids) { if (file.isEmpty()) { throw new JeecgBootException("请上传Excel!"); } File inputFile = null; try { String fileName = file.getOriginalFilename(); String filePath = multipartPre + "/" + fileName.replace("'", ""); File localFile = new File(filePath); if (!localFile.getParentFile().exists()) { localFile.getParentFile().mkdirs(); } file.transferTo(localFile); inputFile = new File(filePath); CatalogueReadListener catalogueReadListener = new CatalogueReadListener(); List<CatalogueExcelVO> data = EasyExcel.read(inputFile, catalogueReadListener).head(CatalogueExcelVO.class).sheet().doReadSync(); iMaterialInfoService.importData(data); if(localFile.exists()){ localFile.delete(); } } catch (Exception e) { throw new JeecgBootException("导入Excel失败!"+e.getMessage()); } finally { if (!ObjectUtils.isEmpty(inputFile)) { inputFile.delete(); } } }
读取Excel 重写listener
package com.zhanwan.modules.catalogue.listener; import com.alibaba.excel.context.AnalysisContext; import com.alibaba.excel.event.AnalysisEventListener; import com.zhanwan.modules.catalogue.entity.CatalogueExcelVO; import lombok.extern.slf4j.Slf4j; import java.util.Map; /** * @Copyright 上海展湾信息科技有限公司 * @Author LZ * @Date 2022/8/8 16:14 */ @Slf4j public class CatalogueReadListener extends AnalysisEventListener<CatalogueExcelVO> { /** * 每解析一条数据,都会来调用该方法 * 对所有数据进行校验,在此增加校验逻辑 * * @param excelDTO * @param analysisContext */ @Override public void invoke(CatalogueExcelVO excelDTO, AnalysisContext analysisContext) { } /** * 每解析一行表头,会调用该方法 * * @param headMap * @param context */ @Override public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) { if (!headMap.containsKey(0) || !headMap.containsKey(1) || !headMap.containsKey(2) || !headMap.containsKey(3) || !headMap.get(0).equals("所属维度(必须)") || !headMap.get(1).equals("对象名称(必须)") || !headMap.get(2).equals("所属层级(必须)") || !headMap.get(3).equals("上一层级对象") ) { throw new RuntimeException("表头校验失败"); } } /** * 所有数据都解析完成后,会调用该方法 * * @param analysisContext */ @Override public void doAfterAllAnalysed(AnalysisContext analysisContext) { System.out.println("数据处理完成后会调用该方法"); } }
@Data @ApiModel(value = "CatalogueExcelVO", description = "Excel导入VO") public class CatalogueExcelVO implements Serializable { private static final long serialVersionUID = 1L; @ExcelProperty(value = "所属维度(必须)") private String dimensionName; @ExcelProperty(value = "对象名称(必须)") private String catalogueName; @ExcelProperty(value = "所属层级(必须)") private String levelName; @ExcelProperty(value = "上一层级对象") private String parentName; }