Java导出复杂的表格文件（.xls）

@MaSaiCheng

于 2024-03-22 15:15:33 发布

阅读量225

点赞数 8

文章标签： java

本文链接：https://blog.csdn.net/qq_64451313/article/details/136941252

版权

项目环境：基于SpringBoot和JDK1.8

1.导入依赖

<!-- EasyPoi依赖 -->
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-base</artifactId>
    <version>4.1.0</version>
</dependency>

2.根据实际需要导出表格文件进行配置例：

3.编写接口来查询模板中需要的数据，返回一个Map集合

@Override
    public Map<String, Object> exportOneReport(String planId) {
        if(StringUtils.isEmpty(planId)){
            throw new RequestException("计划编号不能为空");
        }
        Map<String, Object> map = new HashMap<>();
        QueryWrapper<InnerExaminePlanReport> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("plan_no",planId);
        InnerExaminePlanReport report = reportMapper.selectOne(queryWrapper);
        if(Objects.isNull(report)){
            throw new RequestException("没有查询到对应计划编号的内审报告");
        }
        map.put("checkGoal",report.getCheckGoal());
        map.put("checkScope",report.getCheckScope());
        map.put("checkBasis",report.getCheckBasis());
        map.put("checkTime",report.getCheckTime());
        map.put("reportSummary",report.getReportSummary());
        map.put("gmtCreated",report.getGmtCreated());
        if(report.getCreatorId() != null){
            String createUser = iUserInfoFacade.queryById(report.getCreatorId()).getData().getHmName();
            map.put("creatorId",createUser);
        }
        if(StringUtils.isNotEmpty(report.getCheckUserIds())){
            StringBuilder stringBuilder = new StringBuilder();
            String[] checkUserIds = report.getCheckUserIds().split(",");
            for (String str: checkUserIds) {
                Response<UserInfoVO> checkResponse = iUserInfoFacade.queryById(Long.valueOf(str));
                stringBuilder.append(checkResponse.getData().getHmName()).append("、");
            }
            stringBuilder.deleteCharAt(stringBuilder.length() - 1);
            map.put("checkUserIds", stringBuilder.toString());
        }
        QueryWrapper<InnerExaminePlan> wrapper = new QueryWrapper<>();
        wrapper.eq("plan_no",planId);
        InnerExaminePlan innerExaminePlan = baseMapper.selectOne(wrapper);
        //审核用户、时间
        FlowTaskVO vo = flowService.getProcessTaskPerformer(innerExaminePlan.getInstanceId(), InnerExaminePlanFlowNodeEnums.REPORT_AUDIT.getNode());
        if(Objects.nonNull(vo)){
            String user = iUserInfoFacade.queryById(vo.getAssigneeId()).getData().getHmName();
            map.put("examineUser",user);
            map.put("examineTime",vo.getFinishTime());
        }
        //审批用户、时间
        FlowTaskVO vo2 = flowService.getProcessTaskPerformer(innerExaminePlan.getInstanceId(),InnerExaminePlanFlowNodeEnums.REPORT_APPROVALS.getNode());
        if(Objects.nonNull(vo2)){
            String user = iUserInfoFacade.queryById(vo2.getAssigneeId()).getData().getHmName();
            map.put("approveUser",user);
            map.put("approveTime",vo2.getFinishTime());
        }
        return map;
    }

4.编写接口供前端调用

@GetMapping("/exportOneReport")
    /*@ResponseBody*/
    @ApiOperation(value = "导出单个内部审核实施方案")
    public void exportOneReport(@ApiParam(required = true, name = "计划编号") String planId, HttpServletResponse response) throws Exception {
        if (StringUtils.isEmpty(planId)) {
            throw new Exception("计划编号不能为空");
        }
        //这里是模板的路径
        TemplateExportParams params = new TemplateExportParams("excel/内部审核报告方案模板.xlsx");
        //将文件所需的参数传入workbook
        Workbook workbook = ExcelExportUtil.exportExcel(params, examinePlanService.exportOneReport(planId));
        String filename = URLEncoder.encode("内部审核报告方案", "UTF-8");
        String suffix = ".xlsx";
        String contentType = "application/vnd.ms-excel";
        response.setContentType(contentType); // 设置响应的内容类型为 Excel 文件
        response.setCharacterEncoding("UTF-8"); // 设置响应的字符编码格式为UTF-8
        response.setHeader("Content-Disposition", "attachment; filename*=UTF-8''" + filename + suffix + "; filename=" + filename + suffix);
        workbook.write(response.getOutputStream());
    }