SpringBoot集成easyexcel实现动态模板导出

模板导出"/>

SpringBoot集成easyexcel实现动态模板导出

添加依赖

        <dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.3.2</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>4.1.2</version></dependency>

查看easyexcel 开发文档，定义excel动态模板

根据自己需求：

单属性用{} + 属性名填充，列表属性用{.} + 属性名填充

代码实战

导入请求，Controller层：

    @GetMapping("/export")@ApiOperation("导出盘库范围")public void export(@ApiParam("盘库任务id") String id, HttpServletResponse response) {storeInventoryTaskService.export(id, response);}

业务实现，主要是查询需导出的数据比较简单：主要，使用map设置属性，map键值与excel中属性名对应上

    public void export(String id, HttpServletResponse response) {StoreInventoryTaskDetailVo taskInfo = taskInfo(id);List<InventoryListVo> vos = rangeInfo(id);Map<String, Object> map = new HashMap<>();map.put("inventoryNo", taskInfo.getInventoryNo());map.put("storeName", taskInfo.getStoreName());map.put("startTime", DateUtils.parseDateToStr(DateUtils.YYYY_MM_DD_HH_MM_SS, taskInfo.getStartTime()));map.put("endTime", DateUtils.parseDateToStr(DateUtils.YYYY_MM_DD_HH_MM_SS, taskInfo.getEndTime()));// 自增序号AtomicInteger idx = new AtomicInteger();vos.forEach(el -> el.setIdx(idx.incrementAndGet()));map.put("list", vos);try {TemplateExcelUtil.downLoadExcel("盘库工单" + taskInfo.getInventoryNo(), "盘库工单模板.xlsx", map, response);} catch (Exception e) {throw new ServiceException("导出盘库工单报错");}}

核心类

TemplateExcelUtil.downLoadExcel

根据模板名称获取输入流，输出流也为固定套路，同时设置导出excel文件名，其余代码基本和官方文档保持一致即可

@Slf4j
public class TemplateExcelUtil {/*** 根据模板导出数据** @param fileName   导出文件名* @param sourcePath resource/template文件夹下路径*/public static void downLoadExcel(String fileName, String sourcePath, Map<String, Object> beanParams, HttpServletResponse response)throws Exception {try (OutputStream os = getOutputStream(fileName, response);InputStream is = TemplateExcelUtil.class.getClassLoader().getResourceAsStream("template/" + sourcePath);ExcelWriter excelWriter = EasyExcel.write(os).withTemplate(is).build()) {WriteSheet writeSheet = EasyExcel.writerSheet().build();FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();excelWriter.fill(beanParams.get("list"), fillConfig, writeSheet);excelWriter.fill(beanParams, writeSheet);// 关闭流excelWriter.finish();} catch (Exception e) {e.printStackTrace();throw e;}}/*** 导出文件时为Writer生成OutputStream.** @param fileName 文件名* @param response response*/private static OutputStream getOutputStream(String fileName,HttpServletResponse response) throws Exception {try {fileName = URLEncoder.encode(fileName, "UTF-8");response.setContentType("application/vnd.ms-excel");response.setCharacterEncoding("utf8");response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx");response.setHeader("Pragma", "public");response.setHeader("Cache-Control", "no-store");response.addHeader("Cache-Control", "max-age=0");return response.getOutputStream();} catch (IOException e) {throw new Exception("导出excel表格失败!", e);}}
}

经测试，导出效果如下：