springboot使用HSSFWorkbook做多个sheet任务导出excel

1.上干货

  @GetMapping(value = "/exportExcel")
    @ApiOperation(value = "查询统计(看板显示)，导出")
    public RestResult<String> exportExcel(@RequestParam(value = "startTime", required = false) String startTime,
                                          @RequestParam(value = "endTime", required = false) String endTime,
                                          @RequestParam(value = "departmentId", required = false) String departmentId,
                                          HttpServletResponse response
    ) {
        try {
            List<DatagoActivity> strList = activityService.findCount(departmentId, startTime, endTime);
            HSSFWorkbook wb = new HSSFWorkbook();

            //整理分组(例如5个工会又数据)
            List<DatagoActivity> strListTotal = new ArrayList<>();
            List<String> departmentIdsList = new ArrayList<>();
            for (int i = 0; i < strList.size(); i++) {
                DatagoActivity datagoActivity = strList.get(i);
                DatagoActivity newItem = new DatagoActivity();
                if (Utils.isEmpty(departmentIdsList) || !departmentIdsList.contains(datagoActivity.getDepartmentId())) {
                    List<DatagoActivity> subList = new ArrayList<>();
                    newItem.setDepartmentName(datagoActivity.getDepartmentName());
                    newItem.setActivityNum(1);
                    newItem.setConutNum(datagoActivity.getApplynumber());
                    newItem.setCurrStartyear(datagoActivity.getCurrStartyear());
                    newItem.setCurrEndyear(datagoActivity.getCurrEndyear());
                    subList.add(datagoActivity);
                    newItem.setActivities(subList);
                    departmentIdsList.add(datagoActivity.getDepartmentId());
                    strListTotal.add(newItem);
                } else {
                    int idx = departmentIdsList.indexOf(datagoActivity.getDepartmentId());
                    DatagoActivity datagoActivitytemp = strListTotal.get(idx);
                    datagoActivitytemp.setActivityNum(datagoActivitytemp.getActivityNum() + 1);
                    datagoActivitytemp.setConutNum(datagoActivitytemp.getConutNum() + datagoActivity.getApplynumber());
                    List<DatagoActivity> subList2 = datagoActivitytemp.getActivities();
                    subList2.add(datagoActivity);
                    datagoActivitytemp.setActivities(subList2);
                }
            }
            if (strListTotal.size() > 0) {
                HSSFSheet sheetTotal  = wb.createSheet("各工会活动开展情况");
                CellRangeAddress region1 = new CellRangeAddress(0, 0, 0, 3);
                //参数1：起始行 参数2：终止行 参数3：起始列 参数4：终止列
                sheetTotal.addMergedRegion(region1);
                HSSFRow row1 = sheetTotal.createRow(0);
                row1.createCell(0).setCellValue("统计时间:"+startTime+"-"+endTime);
                //标题
                HSSFRow rowTotalTitle = sheetTotal.createRow(1);
                rowTotalTitle.createCell(0).setCellValue("工会名称");
                rowTotalTitle.createCell(1).setCellValue("开展活动次数");
                rowTotalTitle.createCell(2).setCellValue("活动参与人数");
                rowTotalTitle.createCell(3).setCellValue("当前疗养轮次");
                for (int i = 0; i < strListTotal.size(); i++) {
                    HSSFRow row = sheetTotal.createRow(i + 2);
                    DatagoActivity datagoActivityTotal = strListTotal.get(i);
                    row.createCell(0).setCellValue(datagoActivityTotal.getDepartmentName());
                    row.createCell(1).setCellValue(datagoActivityTotal.getActivityNum());
                    row.createCell(2).setCellValue(datagoActivityTotal.getConutNum());
                    row.createCell(3).setCellValue(datagoActivityTotal.getCurrStartyear()
                            + "-" + datagoActivityTotal.getCurrEndyear()
                    );
                    //导出当前工会的明细
                    List<DatagoActivity> activitySubList = datagoActivityTotal.getActivities();
                    if (activitySubList.size() > 0) {
                        HSSFSheet  sheetSub = wb.createSheet(datagoActivityTotal.getDepartmentName());
                        //标题
                        CellRangeAddress region2 = new CellRangeAddress(0, 0, 0, 4);
                        //参数1：起始行 参数2：终止行 参数3：起始列 参数4：终止列
                        sheetSub.addMergedRegion(region2);
                        HSSFRow row2 = sheetSub.createRow(0);
                        row2.createCell(0).setCellValue("统计时间:"+startTime+"-"+endTime);
                        HSSFRow rowTitle = sheetSub.createRow(1);
                        rowTitle.createCell(0).setCellValue("活动名称");
                        rowTitle.createCell(1).setCellValue("活动类型");
                        rowTitle.createCell(2).setCellValue("活动时间");
                        rowTitle.createCell(3).setCellValue("报名人数");
                        rowTitle.createCell(4).setCellValue("活动所处疗养轮次");
                        for (int k = 0; k < activitySubList.size(); k++) {
                            HSSFRow rowSub = sheetSub.createRow(k + 2);
                            DatagoActivity datagoActivitySub = activitySubList.get(k);
                            rowSub.createCell(0).setCellValue(datagoActivitySub.getTitle());
                            rowSub.createCell(1).setCellValue(datagoActivitySub.getType().equals("1") ? "奖励型活动" : "常规性活动");
                            rowSub.createCell(2).setCellValue(datagoActivitySub.getTime());
                            rowSub.createCell(3).setCellValue(datagoActivitySub.getApplynumber());
                            rowSub.createCell(4).setCellValue(datagoActivitySub.getCycle());
                        }
                        //列宽自适应
                        for (int l = 0; l < 3; l++) {
                            sheetSub.autoSizeColumn(l);
                        }
                    }
                }
                //列宽自适应
                for (int l = 0; l < 2; l++) {
                    sheetTotal.autoSizeColumn(l);
                }
            }


            Date date = new Date();
            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd-hh-mm-ss");
            String format = dateFormat.format(new Date());
            FileOutputStream outputStream = new FileOutputStream(excelPath + "工会活动报名情况单" + format + ".xlsx");
            String httpUrl = httpPath + excelPath.replaceAll(excelPath.split("/")[0], "") + "工会活动报名情况单" + format + ".xlsx";
            wb.write(outputStream);
            outputStream.flush();
            outputStream.close();
            wb.close();
            return RestResultUtil.genSuccessResult(httpUrl);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return RestResultUtil.failed();
    }

2.成功实例