java excel表格导出

/**
     * 导出学习过程统计报表
     *
     * @param startTime       开始时间
     * @param endTime         结束时间
     * @param categoryId      培训类型编号
     * @param isCategoryGroup 是否按培训项目分组
     * @param isMonthGroup    是否按月分组
     * @param isQuarterGroup  是否按季度分组
     * @return
     */
    @RequestMapping("/exportLearnProcessReport")
    @ResponseBody
    public ResultData exportLearnProcessReport(@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss") Date startTime, @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss") Date endTime,
                                               String categoryId, Integer isCategoryGroup, Integer isMonthGroup, Integer isQuarterGroup,
                                               HttpServletRequest request, HttpServletResponse response) throws UnsupportedEncodingException {
        SecurityUtils.getSubject().getSession().setAttribute("exportedFlag", "false");
        ResultData resultData = new ResultData();
        resultData.setCode(StatusCode.YES);
        //数据校验
        ShiroUser shiroUser = (ShiroUser) SecurityUtils.getSubject().getPrincipal();
        if (shiroUser == null) {
            resultData.setCode(StatusCode.NO);
            resultData.setMessage("当前登录用户信息异常！");
            return resultData;
        }
        //设置默认数据
        if (isCategoryGroup == null) {
            isCategoryGroup = 0;
        }
        if (isMonthGroup == null) {
            isMonthGroup = 0;
        }
        if (isQuarterGroup == null) {
            isQuarterGroup = 0;
        }
        //获取数据
        List<LearnProcessReportResponse> list = learnProcessReportService.getLearnProcessReport(startTime, endTime, categoryId, isCategoryGroup, isMonthGroup, isQuarterGroup);

        //处理数据
        if (list != null && list.size() > 0) {
            for (LearnProcessReportResponse learnProcessReportResponse : list) {
                if (learnProcessReportResponse.getLearnProcessReportByTimeList() != null && learnProcessReportResponse.getLearnProcessReportByTimeList().size() > 0) {
                    learnProcessReportResponse.setCount(learnProcessReportResponse.getLearnProcessReportByTimeList().size());
                }
            }
        }
        //数据转成excel
        request.setCharacterEncoding("UTF-8");
        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/x-download");
        String fileName = DateUtils.getTime(new Date()) + "-学习过程统计报表.xlsx";
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
        // 定义一个新的工作簿
        XSSFWorkbook wb = new XSSFWorkbook();
        // 创建一个Sheet页
        XSSFSheet sheet = null;
        sheet = wb.createSheet("学习过程统计报表");

        sheet.setDefaultRowHeight((short) (2 * 256));//设置行高
        sheet.setColumnWidth(0, 4000);//设置列宽
        sheet.setColumnWidth(1, 5500);
        sheet.setColumnWidth(2, 5500);
        sheet.setColumnWidth(3, 5500);
        sheet.setColumnWidth(11, 3000);
        sheet.setColumnWidth(12, 3000);
        sheet.setColumnWidth(13, 3000);
        XSSFFont font = wb.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 16);

        XSSFRow row = sheet.createRow(0);
        XSSFCell cell = null;

        int i = 1;
        if (isCategoryGroup == 1) {
            i--;
            cell = row.createCell(0);
            cell.setCellValue("培训类型");
        }
        cell = row.createCell(1 - i);
        cell.setCellValue("时间");
        cell = row.createCell(2 - i);
        cell.setCellValue("学习人数");
        cell = row.createCell(3 - i);
        cell.setCellValue("发送短信次数");
        cell = row.createCell(4 - i);
        cell.setCellValue("活体验证次数");

        XSSFRow rows;
        XSSFCell cells;

        int j = 0;
        for (int k = 0; k < list.size(); k++) {
            if (list.get(k).getLearnProcessReportByTimeList() != null && list.get(k).getLearnProcessReportByTimeList().size() > 0) {
                for (int m = 0; m < list.get(k).getLearnProcessReportByTimeList().size(); m++) {
                    ++j;
                    // 在这个sheet页里创建一行
                    rows = sheet.createRow(j);
                    int n = 1;
                    if (isCategoryGroup == 1) {
                        --n;
                        // 在该行创建一个单元格
                        cells = rows.createCell(0);
                        // 在该单元格里设置值
                        cells.setCellValue(list.get(k).getCategoryName());
                        if (list.get(k).getCount() > 1) {
                            sheet.addMergedRegion(new CellRangeAddress(j, j + list.get(k).getCount() - 1, 0, 0));
                            list.get(k).setCount(0);
                        }
                    }
                    cells = rows.createCell(1 - n);
                    cells.setCellValue(list.get(k).getLearnProcessReportByTimeList().get(m).getDateTime());
                    cells = rows.createCell(2 - n);
                    cells.setCellValue(list.get(k).getLearnProcessReportByTimeList().get(m).getStudentNum());
                    cells = rows.createCell(3 - n);
                    cells.setCellValue(list.get(k).getLearnProcessReportByTimeList().get(m).getSmsNum());
                    cells = rows.createCell(4 - n);
                    cells.setCellValue(list.get(k).getLearnProcessReportByTimeList().get(m).getLiveVerificationNum());
                }
            }
        }
        try {
            OutputStream out = response.getOutputStream();
            wb.write(out);
            out.close();
            wb.close();
            SecurityUtils.getSubject().getSession().removeAttribute("exportedFlag");
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }