springboot 使用 EasyExcel 通过模板导出EXCEL 带多个动态列表
1.模板定义如下:
导出效果如下
源码如下:
@GetMapping(value = "/exportDynamicExcel") @ApiOperation(value = "导出动态表格") public void exportDynamicExcel(HttpServletResponse response,@RequestParam Long id) throws IOException { // 查询数据库信息 ConstraintValueOrderInfoDTL dtl = constraintValueOrderInfoService.getById(id); String dynamicFieldValue = dtl.getDynamicFieldValue(); if (Objects.isNull(dynamicFieldValue)) { throw new CustomException("没有动态表单数据", getClass().toString()); } List<DynamicFiledVO> dynamicFiledVOS = JSONObject.parseArray(dynamicFieldValue, DynamicFiledVO.class); List<DynamicFiledVO> textVos = dynamicFiledVOS.stream() .filter(e -> DynamicFiledTypeDict.TEXT.getCode().equals(e.getFiledType())).collect(Collectors.toList()); List<DynamicFiledVO> tableVos = dynamicFiledVOS.stream() .filter(e -> DynamicFiledTypeDict.TABLE.getCode().equals(e.getFiledType())).collect(Collectors.toList()); Map<String, String> textMap = new HashMap<>(); for (DynamicFiledVO textVo : textVos) { textMap.put(textVo.getFiledName(), textVo.getFiledValue().toString()); } //我把模板文件放到了resource下面了,所以读取resource下的模板文件 && 这里也可以是从服务器获取到的文件 InputStream inputStream = ResourceUtil.getResourceObj("execlTemplate/导出excel示例模板.xlsx").getStream(); ServletOutputStream outputStream = response.getOutputStream(); // 设置创建行的方式 FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build(); FillConfig fillConfig2 = FillConfig.builder().forceNewRow(Boolean.FALSE).build(); ExcelWriter excelWriter = EasyExcel.write(outputStream).withTemplate(inputStream).excelType(ExcelTypeEnum.XLSX).build(); // 写入到sheet WriteSheet oneSheet = EasyExcel.writerSheet(0).build(); // 填充 excelWriter.fill(textMap, oneSheet); for (int i = 0; i < tableVos.size(); i++) { List<DynamicFiledVO> subFiledInfo = tableVos.get(i).getSubFiledInfo(); List<Map> filedValue = JSONObject.parseArray(JSONObject.toJSONString(tableVos.get(i).getFiledValue()), Map.class); // 只有两个属性 就一行重复显示一次 // 例如: 序号 定值项名称 定值 序号 定值项名称 定值 if (subFiledInfo.size() == 2) { // 拆分成两类列 还要带序号 List<Map> list1 = new ArrayList<>(); List<Map> list2 = new ArrayList<>(); for (int i1 = 1; i1 <= filedValue.size(); i1++) { Map map = filedValue.get(i1-1); map.put("num", i1); if (i1 % 2 != 0) { list1.add(map); } else { // 第二行 列增加标识 避免填充出错 Map map2 = new HashMap(); Set set = map.keySet(); for (Object o : set) { map2.put(o + "2", map.get(o)); } list2.add(map2); } } excelWriter.fill(list1, fillConfig, oneSheet); excelWriter.fill(list2, fillConfig2, oneSheet); } } // 设置响应头信息 String fileName = "导出excel示例.xlsx"; response.setHeader("Content-Disposition", "attachment;filename*=utf-8''" + URLEncoder.encode(fileName, StandardCharsets.UTF_8)); response.setContentType("application/octet-stream"); //关流 excelWriter.finish(); IOUtils.closeQuietly(inputStream); IOUtils.closeQuietly(outputStream); }
上述用到的数据格式如下:
[ { "filedDesc": "高压侧差动CT变比", "filedName": "gaoyacechadongCTbianhua", "filedType": "text", "filedOrder": 1, "filedStruct": null, "filedValue": "高压侧差动CT变比-值" }, { "filedDesc": "中压侧差动CT变比", "filedName": "zhongyacechadongCTbianhua", "filedType": "text", "filedOrder": 2, "filedStruct": null, "filedValue": "中压侧差动CT变比-值" }, { "filedDesc": "差动定值清单", "filedName": "chadongdingzhiqingdan", "filedType": "table", "filedOrder": 3, "subFiledInfo": [{ "filedDesc": "定值项名称", "filedName": "constraintValueName", "filedType": "tableHeader", "filedOrder": 1, "filedValue": "" },{ "filedDesc": "定值", "filedName": "constraintValueValue", "filedType": "tableHeader", "filedOrder": 2, "filedValue": "" }], "filedValue": [{"constraintValueName": "表单示例数据1", "constraintValueValue": 50},{"constraintValueName": "表单示例数据2", "constraintValueValue": 20},{"constraintValueName": "表单示例数据3", "constraintValueValue": 30}] } ]
主要是有拆分动态列表为列的需求 再继续增加列表应该也可以
优化 由于填充时第二行开始,easyexcel不会自动合并单元格,所以需要自定义handler根据上一行的合并信息自行合并 解决方案如下:
1.创建handler
public class MyExcelHandler extends AbstractMergeStrategy { @Override protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) { if(relativeRowIndex==null ||relativeRowIndex==0){ return; } int rowIndex = cell.getRowIndex(); int colIndex = cell.getColumnIndex(); sheet=cell.getSheet(); Row preRow = sheet.getRow(rowIndex - 1); Cell preCell = preRow.getCell(colIndex);//获取上一行的该格 List<CellRangeAddress> list = sheet.getMergedRegions(); CellStyle cs = cell.getCellStyle(); cell.setCellStyle(cs); for (int i = 0; i < list.size(); i++) { CellRangeAddress cellRangeAddress = list.get(i); if (cellRangeAddress.containsRow(preCell.getRowIndex()) && cellRangeAddress.containsColumn(preCell.getColumnIndex())) { int lastColIndex = cellRangeAddress.getLastColumn(); int firstColIndex = cellRangeAddress.getFirstColumn(); CellRangeAddress cra = new CellRangeAddress(cell.getRowIndex(), cell.getRowIndex(), firstColIndex, lastColIndex); sheet.addMergedRegion(cra); RegionUtil.setBorderBottom(BorderStyle.THIN, cra, sheet); RegionUtil.setBorderLeft(BorderStyle.THIN, cra, sheet); RegionUtil.setBorderRight(BorderStyle.THIN, cra, sheet); RegionUtil.setBorderTop(BorderStyle.THIN, cra, sheet); return; } } } }
写入到sheet的时候指定handler
WriteSheet oneSheet = EasyExcel.writerSheet(0).registerWriteHandler(new MyExcelHandler()).build();
优化后:
填充多个列表的时候指定列表对象
excelWriter.fill(new FillWrapper("data1", list), fillConfig, writeSheet1); excelWriter.fill(new FillWrapper("data2", list1), fillConfig, writeSheet1); excelWriter.fill(new FillWrapper("data3", list2), fillConfig, writeSheet1); excelWriter.fill(new FillWrapper("data4", list3), fillConfig, writeSheet1);
⎛⎝官萧何⎠⎞一只快乐的爪哇程序猿;邮箱:1570608034@qq.com