EasyExcel list＜Map＞批量导出多个sheet

本文介绍: Str in g headers = “任务名称,任务周期,任务开始时间,任务结束时间,”+mapColumn.get(key).get(0).get(“TITLES”).t oStr in g()+”,上报人工号,上报人姓名,上报人电话,上报时间,上报人县市,上报人网格“;Str ing fileNa m e = URLEncode r.encode(“人员任务列表数据“+Sy ste m.currentTi m eMilli s(), “UTF-8”).replaceAll(“\+”, “%20”);

1 列表List<Map<Str ing,Object>> list 按类型分模块分别导出到各自sheet

2 首先获取列表

List<Map<String,Object&gt;&gt; list = tzBusiTaskUserListService.getTaskUserList(tzBusiTaskUserList);

3 分组

Map<String,List<Map<String,Object>>> map = list.stream().collect(Collectors.groupingBy(o->o.get("T_TASK_NAME").toString()));

4.获取表头，这里自定义表头我是自己配置到数据库的，List<T> 实体或者map都可以

List<Map<String,Object>> listColumn = tzBusiTaskListService.getTaskUserColumns();
Map<String,List<Map<String,Object>>> mapColumn = listColumn.stream().collect(Collectors.groupingBy(o->o.get("FORM_NAME").toString()));

5接下来就是导出

ExcelWriter excelWriter = null;
//  设置内容格式 以及 编码方式（文件指定可以修改）
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String fileName = URLEncoder.encode("人员任务列表数据"+System.currentTimeMillis(), "UTF-8").replaceAll("\+", "%20");
//attachment指定独立文件下载  不指定则回浏览器中直接打开
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");//输出文件流  调用服务层方法，获取excel对象放入输出流

excelWriter = EasyExcel.write(response.getOutputStream()).build();
int i = 0;

最后：完整代码

   //导出excel
    /**
     * 输出多个sheet,自定义表头
     *
     * @param response 输出多excel表格，让用户下载
     */
    @PostMapping("/exportSheetsWithHeaders")
    public void exportSheetsWithHeaders(HttpServletResponse response) throws Exception{

        ExcelWriter excelWriter = null;
        try{
            //  设置内容格式 以及 编码方式（文件指定可以修改）
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String fileName = URLEncoder.encode("人员任务列表数据"+System.currentTimeMillis(), "UTF-8").replaceAll("\+", "%20");
            //attachment指定独立文件下载  不指定则回浏览器中直接打开
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");//输出文件流  调用服务层方法，获取excel对象放入输出流


            TzBusiTaskUserList tzBusiTaskUserList = new TzBusiTaskUserList();

//            List<TzBusiTaskUserList> list = tzBusiTaskUserListService.selectAll(tzBusiTaskUserList);
            List<Map<String,Object>> list = tzBusiTaskUserListService.getTaskUserList(tzBusiTaskUserList);
            if(list.isEmpty()){
                response.reset();
                response.setContentType("application/json");
                response.setCharacterEncoding("utf-8");
                response.getWriter().println(JSON.toJSONString(ApiResp.fail("无数据")));
                return;
            }
            Map<String,List<Map<String,Object>>> map = list.stream().collect(Collectors.groupingBy(o->o.get("T_TASK_NAME").toString()));
            List<Map<String,Object>> listColumn = tzBusiTaskListService.getTaskUserColumns();
            Map<String,List<Map<String,Object>>> mapColumn = listColumn.stream().collect(Collectors.groupingBy(o->o.get("FORM_NAME").toString()));

            if(listColumn.isEmpty()) {
                response.reset();
                response.setContentType("application/json");
                response.setCharacterEncoding("utf-8");
                response.getWriter().println(JSON.toJSONString(ApiResp.fail("字段为空")));
                return;
            }

            excelWriter = EasyExcel.write(response.getOutputStream()).build();
            int i = 0;
            for (String key:map.keySet()) {
                if(mapColumn.get(key) == null || mapColumn.get(key).get(0).get("ORA_FIELDS") ==null) continue;
                String columns = "T_TASK_NAME,T_TASK_TYPE,T_START_TIME,T_END_TIME,"+mapColumn.get(key).get(0).get("ORA_FIELDS").toString()+",CREATE_USER,CREATE_USER_NAME,CREATE_PHONE,CREATE_TIME,CREATE_COUNTY,CREATE_GRID";
                String headers = "任务名称,任务周期,任务开始时间,任务结束时间,"+mapColumn.get(key).get(0).get("TITLES").toString()+",上报人工号,上报人姓名,上报人电话,上报时间,上报人县市,上报人网格";

                List<Map<String,Object>> val = map.get(key);
                List<List<String>> headList = EasyExcelUtil.headList(headers);
                List<List<Object>> dataList =  EasyExcelUtil.dataList(val,columns);
                if(dataList.isEmpty()) continue;

                WriteTable writeTable = new WriteTable();
                writeTable.setHead(headList);

                WriteSheet writeSheet = EasyExcel.writerSheet(i,key).build();
                excelWriter.write(dataList, writeSheet,writeTable);
                i++;
            }

        }catch (Exception e) {
            // 重置response
            log.error("异常",e);
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            response.getWriter().println(JSON.toJSONString(ApiResp.fail("下载失败",e.getMessage())));
        }finally {
            //关闭流
            if(excelWriter != null){
                excelWriter.finish();
            }
        }
    }