EasyExcel根据报表模板来填充报表（多sheet页填充、封装工具方法）

官方教程：

https://www.yuque.com/easyexcel/doc/fill

 

 

 

一、填充模板里单个sheet页

 

模板

 

 

 

feeDate：要填充的单个的值

.name：是个集合

 

 

封装的公共方法

/**
     *
     * 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();
            }
        }
    }

 

业务代码调用例子：

//数据库里查询得到的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);

 

 

 

二、填充一个模板里多个sheet页

 

对上面的封装的公共方法进行改进

/**
 *
 * 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();
        }
    }
}

 

 说明：

（1）List<?>：泛型，支持填充不同类型的List

（2）sheetAndDataMap  key:sheet页，value:填充的list集合；for循环往不同的sheet页里填充不同的list

 

业务代码调用例子：

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);

}