EasyExcel的应用
需求: 由于表格字段是动态的, 所以在做上传数据时需要先根据表的结构生成表格模板, 再根据模板填写内容然后上传数据;
1.下载表头模板
@RestController @RequestMapping("/excel") public class ExcelController { @GetMapping("/exportExcelTemp") public void exportExcelTemp(HttpServletResponse response) { //表头 List<String> headList = new ArrayList<>(); headList.add("lie1"); headList.add("列2"); headList.add("列3"); String fileName = "表明"; //下拉框 List<EasyExcelSelectionVo> selectionVoList = new ArrayList<>(); List<String> selectionList = new ArrayList<>(); selectionList.add("选项1"); selectionList.add("选项2"); selectionList.add("选项3"); EasyExcelSelectionVo selectionVo = EasyExcelSelectionVo.builder() //第一列 .index(0) .list(selectionList) .build(); List<String> selectionList2 = new ArrayList<>(); selectionList2.add("选项11"); selectionList2.add("选项22"); selectionList2.add("选项33"); EasyExcelSelectionVo selectionVo2 = EasyExcelSelectionVo.builder() //第一列 .index(2) .list(selectionList2) .build(); selectionVoList.add(selectionVo); selectionVoList.add(selectionVo2); EasyExcelUtils.downloadHeadExcel(response,headList,fileName,selectionVoList); } }复制代码
2.上传数据
public class EasyExcelUtils { /** * 读取excel 数据 * * @param file 被上传的文件 */ public static void readExcel(MultipartFile file,UploadDao uploadDao) { try { EasyExcel.read(file.getInputStream(), new MyUpLoadListener(uploadDao)).sheet().doRead(); } catch (IOException ex) { ex.printStackTrace(); throw new RuntimeException(file.getOriginalFilename() + "解析失败,请检查!"); } } }复制代码
3.监听类
public class MyUpLoadListener extends AnalysisEventListener<Map<Integer, String>> { /** * 每隔5条存储数据库,实际使用中可以3000条,然后清理list ,方便内存回收 */ private static final int BATCH_COUNT = 5; List<Map<String, String>> dataList = new ArrayList<>(); List<String> headList = new ArrayList<>(); /** * 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来 * * @param uploadDAO */ public MyUpLoadListener(UploadDAO uploadDAO) { this.uploadDAO = uploadDAO; } @Override public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) { Set<Map.Entry<Integer, String>> entries = headMap.entrySet(); log.info("{} > ===================== TABLE HEAD READY ======================", DateUtil.now()); for (Map.Entry<Integer, String> entry : entries) { headList.add(entry.getValue()); } log.info("{} > ===================== TABLE HEAD END ======================", DateUtil.now()); } /** * 这个每一条数据解析都会来调用 * * @param data * one row value. Is is same as {@link AnalysisContext#readRowHolder()} * @param context */ @Override public void invoke(Map<Integer, String> data, AnalysisContext context) { Set<Map.Entry<Integer, String>> entries = data.entrySet(); int i = 0; Map<String, String> headMappingData = new LinkedHashMap<>(); for (Map.Entry<Integer, String> entry : entries) { String headName = this.headList.get(i); headMappingData.put(headName, entry.getValue()); i++; } this.dataList.add(headMappingData); if (this.dataList.size() >= BATCH_COUNT) { saveData(); // 这里必须清理 list , 否则会出现数据重复 this.dataList.clear(); } } /** * 所有数据解析完成了 都会来调用 * * @param context */ @Override public void doAfterAllAnalysed(AnalysisContext context) { // 这里也要保存数据,确保最后遗留的数据也存储到数据库 saveData(); } /** * 加上存储数据库 */ private void saveData() { uploadDAO.save(dataList); } }复制代码
EasyExcel工具类
public class EasyExcelUtils { /** *导出表头 */ public static void downloadHeadExcel(HttpServletResponse resp, List<String> headList, String sheetName, List<EasyExcelSelectionVo> selections) { try { String fileName = sheetName + "(导入模板)"; fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\+", "%20"); EasyExcelUtils.responseDecoration(resp, fileName); ExcelWriterBuilder builder = EasyExcel.write(resp.getOutputStream()).head(EasyExcelUtils.createHead(headList)); if (selections != null && !selections.isEmpty()) { builder.registerWriteHandler(new MultiSelectionSheetWriteHandler(selections)); } builder.sheet(sheetName).doWrite(new ArrayList<>()); } catch (Exception e) { EasyExcelUtils.errorExport(resp, e); } } /** * 导出表数据 */ public static void downloadDataExcel(HttpServletResponse resp, List<String> headList, String sheetName, List<List<String>> dataList) { try { String fileName = URLEncoder.encode(sheetName, "UTF-8").replaceAll("\+", "%20"); EasyExcelUtils.responseDecoration(resp, fileName); EasyExcel.write(resp.getOutputStream()) .head(EasyExcelUtils.createHead(headList)) .sheet(sheetName) .doWrite(dataList); } catch (Exception e) { EasyExcelUtils.errorExport(resp, e); } } /** * 根据模板导出表数据 */ public static <T> void exportByTemp(List<T> data, String tempFileName, Map<String, String> map, HttpServletResponse response) throws IOException { InputStream templateFileName = ExcelUtil.class.getResourceAsStream("/excel" + File.separator + tempFileName); EasyExcelUtils.responseDecoration(response, tempFileName); //读取Excel ExcelWriter excelWriter = EasyExcel .write(response.getOutputStream()) .withTemplate(templateFileName) .build(); WriteSheet writeSheet = EasyExcel.writerSheet().build(); //customerDealerInfo 是我查询并需导出的数据,并且里面的字段和excel需要导出的字段对应 // 直接写入Excel数据 excelWriter.fill(data, writeSheet); if (map != null && !map.isEmpty()) { excelWriter.fill(map, writeSheet); } // 别忘记关闭流 excelWriter.finish(); } /** * 读取excel 数据 * * @param file 被上传的文件 */ public static void readExcel(MultipartFile file, UploadDao uploadDao) { try { EasyExcel.read(file.getInputStream(), new MyUpLoadListener(uploadDao)).sheet().doRead(); } catch (IOException ex) { ex.printStackTrace(); throw new RuntimeException(file.getOriginalFilename() + "解析失败,请检查!"); } } /** * 包装表头 * * @param heads * @return */ private static List<List<String>> createHead(List<String> heads) { List<List<String>> head = new ArrayList<>(heads.size()); List<String> son; for (String name : heads) { son = new ArrayList<>(); son.add(name); head.add(son); } return head; } private static void responseDecoration(HttpServletResponse resp, String fileName) { // 如果后续报错,请针对该代码做放开处理 resp.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); resp.setCharacterEncoding("utf-8"); resp.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx"); // resp.setHeader("Content-disposition", "attachment; filename=" + new String(fileName.getBytes("UTF-8"), "ISO-8859-1")); } /** * 文件下载并且失败的时候返回json(默认失败了会返回一个有部分数据的Excel) */ private static void errorExport(HttpServletResponse resp, Exception e) { // 重置response resp.reset(); resp.setContentType("application/json"); resp.setCharacterEncoding("utf-8"); //自定义异常类 throw new RuntimeException("下载文件失败," + e.getMessage()); } }
作者:不懂不懂丶
链接:https://juejin.cn/post/7025526262600826910