EasyExcel模板导出与公式计算（下）

目录

环境要求

功能预览

需求分析

导入依赖

制作模板

编写代码

格式优化

最终效果

总结

---

在上一篇 EasyExcel模板导出与公式计算（上）-CSDN博客 文章中我们知道了在若依中使用自带的@Excel注解来实现表格数据的导出，并且通过重写相关接口来支持导出公式的自动计算功能，而在本文中，我们将探究如何在带有自定义表头的定制化场景下，如何实现表格数据的导出和公式的自动计算。

环境要求

若依Cloud 3.6.3

EasyExcel  3.3.2

功能预览

与上一篇文章相同，这里继续以若依用户管理的导出功能为示例，做一个简单的公式计算的验证，大家可根据自己需求修改相应代码。

首先本地启动若依微服务版本，登录后台，依次选择系统管理 —> 用户管理菜单，进入如下页面

 点击该页面的导出按钮

获取如下导出文件

现在如果我们想要在第四行计算第二和第三行用户编号的和，可以直接在B4单元格输入"=A2+A3"这样的公式来实现，可以注意到此时B4单元格中的公式颜色已经发生变化，并且自动选中了A2和A3单元格，但实际上我们的需求为从数据库表中导出的单元格实现自动计算结果，接下来将通过若依前端页面来实现该功能验证。

现在需求变为除了能够正确计算单元格数据外，还要变成像如下图的导出样式:

不难看出，我们需要导出的结果有自定义的表头，以及时间，并且需要特别注意的是，原本的计算公式由=A2+A3，变为了=A5+A6，这是因为新增了表头导致行号发生了变化，接下来将针对这种需求进行实现。

需求分析

由于有了自定义标头的要求，显然我们不能按照上一篇文章中直进行数据的导出，因此我们将使用EasyExcel实现该功能

导入依赖

首先在ruoyi-common-core的pom.xml添加EaysExcel的依赖，之所以添加到这里是因为该模块为通用核心模块，会在项目中被其他的模块引用，可以实现“一处添加，多处使用”的效果。

<!-- easyexcel --><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.3.2</version></dependency>

制作模板

为了实现上述中的导出效果，我们需要使用Excel制作一个用于导出的模板，最终效果如下所示:

可以看到图中红色框所标识的是新增的表头内容，其中时间后面的{dataDate} 则为填充对象，而第5行对应的内容中除了花括号外，还有一个点，这其实是代表的是将使用对象集合填充，与时间的dataDate区别为， dataDate仅为一个对象。

编写代码

由于我们使用了EasyExcel之后，原有的导出功能需要做调整， @Excel注解将被替换为@ExcelProperty，如下图所示，其中value属性对应了我们的模板中的表头

对应Controller层的代码也需要做如下调整:

@Log(title = "用户管理", businessType = BusinessType.EXPORT)@RequiresPermissions("system:user:export")@PostMapping("/export")public void export(HttpServletResponse response, SysUser user){List<SysUser> list = userService.selectUserList(user);//ExcelUtil<SysUser> util = new ExcelUtil<SysUser>(SysUser.class);//util.exportExcel(response, list, "用户数据");try(InputStream resourceAsStream =  SysUserController.class.getClassLoader().getResourceAsStream("UserExportTemplate.xlsx");ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(resourceAsStream).inMemory(true).build()) {response.setContentType("application/vnd.ms-excel");String filename=  URLEncoder.encode("用户管理数据导出","UTF-8");response.setHeader("Content-disposition","attachment;filename="+filename+".xlsx");WriteSheet writeSheet = EasyExcel.writerSheet().build();FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();excelWriter.fill(list, fillConfig, writeSheet);Map<String, Object> map = MapUtils.newHashMap();// 自定义日期map.put("dataDate", new Date());excelWriter.fill(map, writeSheet);// 拿到原始的Workbook对象Workbook workbook = excelWriter.writeContext().writeWorkbookHolder().getWorkbook();// 获取已经填充的sheet对象Sheet userSheet = workbook.getSheetAt(0);for(Row row : userSheet){for (Cell cell : row){if(cell.getCellType() == CellType.STRING &&StringUtils.isNotBlank(cell.getStringCellValue())&& cell.getStringCellValue().contains("=")){cell.setCellFormula(cell.getStringCellValue().substring(1));}}}// 关闭excelWriter.finish();} catch (IOException e) {e.printStackTrace();}}

 我们首先获取到了用户信息的list对象， 随后加载自定义的模板 UserExportTemplate.xlsx ，使用EasyExcel 构建一个 WriteSheet 对象，其作用为填充自定义的日期对象以及我们的查询的list对象，待填充完成后。通过excelWriter再次获取到初始的WorkBook对象，在这里就是如何解决公式自动计算的关键，通过workBookt获取到Sheet对象后对其进行遍历，依次获取到Row和Cell对象， 这里的Cell对象就是我们的单元格，而我们填值的单元格为String类型，因此需要进行过滤，并且此处需要特别注意，去掉公式中原有的 = 符号，否则会引发异常。最后需要使用finish方法关闭流。该方案来源于官方文档easyexcel模板填充后，有公式的单元格不会自动计算，而需要我手动算，如何处理？ | Easy Excel 官网

以及github上的 issue https://github.com/alibaba/easyexcel/issues/3242 

格式优化

虽然上述内容已经解决了公式导出后无法自动计算的问题，但是如果仔细观察就会发现有个问题可以进一步优化，由于最开始填入的单元格公式为A2+A3，而当增加了自定义表头后，此处内容应变为A5+A6，在Excel中这种情况会自动处理，但在当前场景下，只能通过手动方式去修正最终的单元格，而如果带有公式的单元格非常多时就会变得非常麻烦，因此可以通过如下代码进行调整:

 @Log(title = "用户管理", businessType = BusinessType.EXPORT)@RequiresPermissions("system:user:export")@PostMapping("/export")public void export(HttpServletResponse response, SysUser user){List<SysUser> list = userService.selectUserList(user);//ExcelUtil<SysUser> util = new ExcelUtil<SysUser>(SysUser.class);//util.exportExcel(response, list, "用户数据");try(InputStream resourceAsStream =  SysUserController.class.getClassLoader().getResourceAsStream("UserExportTemplate.xlsx");ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(resourceAsStream).inMemory(true).build()) {response.setContentType("application/vnd.ms-excel");String filename=  URLEncoder.encode("用户管理数据导出","UTF-8");response.setHeader("Content-disposition","attachment;filename="+filename+".xlsx");WriteSheet writeSheet = EasyExcel.writerSheet().build();FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();excelWriter.fill(list, fillConfig, writeSheet);Map<String, Object> map = MapUtils.newHashMap();// 自定义日期map.put("dataDate", new Date());excelWriter.fill(map, writeSheet);// 根据自定义表头调整公式int rowOffset = 3;// 拿到原始的Workbook对象Workbook workbook = excelWriter.writeContext().writeWorkbookHolder().getWorkbook();// 获取已经填充的sheet对象Sheet userSheet = workbook.getSheetAt(0);for(Row row : userSheet){for (Cell cell : row){if(cell.getCellType() == CellType.STRING &&StringUtils.isNotBlank(cell.getStringCellValue())&& cell.getStringCellValue().contains("=")){// 获取当前单元格内容String formula = cell.getStringCellValue();// 正则表达式匹配单元格引用 (例如 A1, B2 等)Pattern pattern = Pattern.compile("([A-Z]+)(\\d+)");Matcher matcher = pattern.matcher(formula);StringBuffer adjustedFormula = new StringBuffer();while(matcher.find()) {// 列名 (A, B, ...)String column = matcher.group(1);// 行号 (1, 2, ...)int rowNum = Integer.parseInt(matcher.group(2));// 调整行号int adjustedRow = rowNum + rowOffset;// 替换原始引用为调整后的引用matcher.appendReplacement(adjustedFormula, column + adjustedRow);}matcher.appendTail(adjustedFormula);// 重新设置公式cell.setCellFormula(adjustedFormula.substring(1));}}}// 关闭excelWriter.finish();} catch (IOException e) {e.printStackTrace();}}

此处通过在验证公式时使用正则匹配原有的公式，并根据设置的 rowOffset 动态进行调整（rowOffset为3是因为当前填入的公式从A2为基准开始，因此偏移量为3而并非4），使其能够正确计算结果。(此处代码也可抽取为方法在查询时list对象时直接使用)

最终效果

可以看到前端页面中填入的内容为=A2+A3，而导出文件内容为=A5+A6，并且打开导出的文件后自动计算了结果。

总结

对于具有自定义表头内容的导出需求，需要使用EasyExcel工具实现功能增强，并且需要先将内容写入到内容中之后，再读取将公式进行进一步过滤转换（去除等号，调整行号），进而实现导出文文件的计算功能，根据官方文档所述，该方案适用于文件小的场景，当文件过大时将可能造成内存溢出的问题，因此官方建议在Java层实现对公式的计算后再进行导出，如果各位读者有更好的想法欢迎评论区留言探讨。