当前位置: 首页 > 工具软件 > ExcelUtil > 使用案例 >

Hutool-ExcelUtil多Sheet页导出

苏乐
2023-12-01
public void exportWritten(HttpServletResponse response, String projectKey) {
        //已填报计划表的组织
        Set<Map<String,Object>> orgList = ccpOrgMapper.getWrittenOrg(projectKey);
        //内容
        Set<Map<String,Object>> list1 = assessProjectMapper.getNrByJhb(projectKey);
        //细则
        Set<Map<String,Object>> list2 = assessProjectMapper.getXzByJhb(projectKey);
        list1.addAll(list2);

        String fileName = URLEncoder.encode("计划表考核总分汇总", StandardCharsets.UTF_8);
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        response.setHeader("content-disposition", "attachment;filename=" + fileName + ".xlsx");

        ExcelWriter writer = ExcelUtil.getWriter();
        //index 计数,excel的sheet页索引
        AtomicInteger index = new AtomicInteger();
        orgList.forEach(org->{
            //获取组织已经填写的计划表列表
            Set<Map<String,Object>> writeInfo = assessWriteMapper.getWrittenByProjectKey(org.get("org_code").toString(),projectKey);
            if (index.get() == 0) {
                //更改默认的sheet页命名
                writer.renameSheet(org.get("org_name").toString());
            } else {
                //创建新的sheet页
                writer.setSheet(org.get("org_name").toString());
            }
            List<Map<String,Object>> tempList = new ArrayList<>(list1);
            //设置表格标题
            writer.merge(8,tempList.get(0).get("jhbContent"));
            //设置表头
            writer.writeHeadRow(ListUtil.of("项目",
                    "内容",
                    "分值",
                    "考核标准",
                    "填报截止时间",
                    "填报内容",
                    "组织填报附件",
                    "打分截止时间",
                    "考核分"));
            //设置列宽
            writer.setColumnWidth(0,45);
            writer.setColumnWidth(1,45);
            writer.setColumnWidth(4,25);
            writer.setColumnWidth(7,25);

            //已填报计划表和计划表信息组装
            writeInfo.forEach(write -> list1.forEach(jhb->{
                if (write.get("nrKey").toString().equals(jhb.get("nrKey").toString())) {
                    List<Object> row = new ArrayList<>();
                    row.add(jhb.get("xmContent"));
                    row.add(jhb.get("nrContent"));
                    row.add(jhb.get("points"));
                    row.add(jhb.get("standard"));
                    row.add(jhb.get("upEndTime"));
                    row.add(write.get("writeContent"));
                    row.add(write.get("files"));
                    row.add(jhb.get("scoringEndTime"));
                    row.add(write.get("scoring"));
                    writer.writeRow(row);
                }
            }));
            index.getAndIncrement();
        });

        ServletOutputStream out = null;
        try {
            out = response.getOutputStream();
        } catch (IOException e) {
            log.error("导出失败!!",e);
            e.printStackTrace();
        }
        writer.flush(out, true);
        // 关闭writer,释放内存
        writer.close();
        //此处记得关闭输出Servlet流
        IoUtil.close(out);
    }
 类似资料: