原文链接:https://www.cnblogs.com/Donnnnnn/p/15412128.html
官方教程:
https://www.yuque.com/easyexcel/doc/fill
一、填充模板里单个sheet页
模板
feeDate:要填充的单个的值
.name:是个集合
封装的公共方法
![复制代码](https://common.cnblogs.com/images/copycode.gif)
/** * * EasyExcel 填充报表 * * @param response * @param list 填充集合 * @param map 填充单个的值 * @param sheetNo 填充到哪个Sheet页 Index of sheet, 0 base. * @param filename 文件名 * @param inputStream 文件流. */ public void fillReportWithEasyExcel(HttpServletResponse response, Integer sheetNo, List<?> list, Map<String, String> map, String filename, InputStream inputStream){ ExcelWriter excelWriter = null; try { OutputStream outputStream = response.getOutputStream(); response.setHeader("Content-disposition", "attachment; filename=" + filename); response.setContentType("application/msexcel;charset=UTF-8");//设置类型 response.setHeader("Pragma", "No-cache");//设置头 response.setHeader("Cache-Control", "no-cache");//设置头 response.setDateHeader("Expires", 0);//设置日期头 excelWriter = EasyExcel.write(outputStream).withTemplate(inputStream).build(); WriteSheet writeSheet = EasyExcel.writerSheet(sheetNo).build(); FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build(); excelWriter.fill(list, fillConfig, writeSheet); excelWriter.fill(map, writeSheet); }catch (Exception e){ e.printStackTrace(); }finally { excelWriter.finish(); try { inputStream.close(); } catch (IOException e) { e.printStackTrace(); } } }
![复制代码](https://common.cnblogs.com/images/copycode.gif)
业务代码调用例子:
![复制代码](https://common.cnblogs.com/images/copycode.gif)
//数据库里查询得到的list集合 List<XXX> list = mapper.select(); //要填充的单个的值 Map<String, String> map = new HashMap<>(); map.put("feeDate", feeDate); map.put("name", name); String filename = "filename"; //工程路径下有个模板文件 String modelPath = "classpath:static/XXX/xxxxxx.xlsx"; //读取模板文件 InputStream inputStream = ResourceUtils.getURL(modelPath).openStream(); //调用方法即可 fillReportWithEasyExcel(response, 0, list, map, filename, inputStream);
![复制代码](https://common.cnblogs.com/images/copycode.gif)
二、填充一个模板里多个sheet页
对上面的封装的公共方法进行改进
![复制代码](https://common.cnblogs.com/images/copycode.gif)
/** * * EasyExcel 填充报表 * * @param response * @param sheetAndDataMap key:sheet页,value:填充的list集合 * @param map 填充单个的值 * @param filename 文件名 * @param inputStream 文件流. */ public static void fillReportWithEasyExcel(HttpServletResponse response, Map<String, List<?>> sheetAndDataMap, Map<String, String> map, String filename, InputStream inputStream){ ExcelWriter excelWriter = null; try { OutputStream outputStream = response.getOutputStream(); response.setHeader("Content-disposition", "attachment; filename=" + filename); response.setContentType("application/msexcel;charset=UTF-8");//设置类型 response.setHeader("Pragma", "No-cache");//设置头 response.setHeader("Cache-Control", "no-cache");//设置头 response.setDateHeader("Expires", 0);//设置日期头 excelWriter = EasyExcel.write(outputStream).withTemplate(inputStream).build(); for(Map.Entry<String, List<?>> entry : sheetAndDataMap.entrySet()){ List<?> value = entry.getValue(); WriteSheet writeSheet = EasyExcel.writerSheet(Integer.valueOf(entry.getKey())).build(); FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build(); excelWriter.fill(value, fillConfig, writeSheet); excelWriter.fill(map, writeSheet); } }catch (Exception e){ e.printStackTrace(); }finally { excelWriter.finish(); try { inputStream.close(); } catch (IOException e) { e.printStackTrace(); } } }
![复制代码](https://common.cnblogs.com/images/copycode.gif)
说明:
(1)List<?>:泛型,支持填充不同类型的List
(2)sheetAndDataMap key:sheet页,value:填充的list集合;for循环往不同的sheet页里填充不同的list
业务代码调用例子:
![复制代码](https://common.cnblogs.com/images/copycode.gif)
public void exportAllReport(HttpServletResponse response, String feeDate) throws Exception { String filename = "test.xlsx"; String modelPath = "classpath:static/test.xlsx";//模板所在路径 InputStream inputStream = ResourceUtils.getURL(modelPath).openStream();//获取输入流 //填充单个的值 Map<String, String> map = new HashMap<>(); map.put("feeDate", feeDate); //待填充的多个sheet页的list,都加入此map Map<String, List<?>> dataMap = new HashMap<>(); //填充sheet1 List<Model1> list1 = dao.select1(feeDate); dataMap.put("0", list1); //填充sheet2 List<Model2> list2 = dao.select2(feeDate); dataMap.put("1", list2); //填充sheet3 List<Model3> list3 = dao.select3(feeDate); dataMap.put("2", list3); //填充报表,并下载 ExcelUtil.fillReportWithEasyExcel(response, dataMap, map, filename, inputStream); }
![复制代码](https://common.cnblogs.com/images/copycode.gif)