POI实现一个excel 生成多个sheet

1. 要点

1.1 outPutStream 最后才能close

1.2 使用同一个 workbook

示例代码如下：

    @ApiOperation("buttonDownload")
    @LogApi("button and download")
    @PostMapping("/button/download")
    public void buttonDownload(@RequestBody ValidateQuery query, HttpServletResponse response) throws IOException {

        String dateStr = DateUtils.format(new Date(), DateUtils.PATTERN_MM_DD_YYYY_HH_MM_SS);
        String fileName = "PSS Validation_" + dateStr;

        // 设置下载文件格式
        response.addHeader( "Content-Type", "application/octet-stream");
        response.addHeader( "Content-Disposition", "attachment; filename=\"" + fileName + "\".xlsx;filename*=utf-8''" + fileName);

        Workbook workbook = new XSSFWorkbook();
        query.setWorkbook(workbook);


        List<String> names = query.getValidationName();

        try (OutputStream out = response.getOutputStream()) {

            for (int i = 0; i < names.size(); i++) {
                String o = names.get(i);

                query.setCurrentName(o);
                query.setIndex(i);

                if (ValidationConstant.DOS_ARR.contains(o)) {
                    dosService.pssValidationDownload(query, response);
                }

                if (ValidationConstant.VALIDATION_ARR.contains(o)) {
                    validationService.pssValidationDownload(query, response);
                }
            }


            query.getWorkbook().write(out);
        }
    }

1.3 每个sheet 设置一个index和name

    /**
     * 一个 excel 文件，同时 生成多个sheet;
     * 解析动态表头，使用此方法
     *
     * @param query     sheet 属性 封装
     * @param response  响应
     * @param excelHead 表头
     * @param excelData 数据
     * @author: leiming5
     */
    public void download(ValidateQuery query, HttpServletResponse response, List<List<String>> excelHead, List<List<Object>> excelData) {

        String sheetName = query.getCurrentName();
        String header = response.getHeader("content-disposition");

        if (StringUtils.isEmpty(header)) {
            String dateStr = DateUtils.format(new Date(), DateUtils.PATTERN_MM_DD_YYYY_HH_MM_SS);
            response.setHeader("content-disposition", "attachment;filename=" + sheetName + "_" + dateStr + ".xlsx");
        }

        try {
            Workbook workbook = query.getWorkbook();
            if (workbook == null) {
                workbook = new XSSFWorkbook();
            }

            // 生成一个表格
            Sheet sheet = workbook.createSheet(sheetName);

            // 生成表格
            createExcel(sheet, excelHead, excelData, workbook, sheetName);

            // 设置多sheet表格名称
            Integer index = query.getIndex();
            if (index != null) {
                workbook.setSheetName(index, sheetName);
            }

            if (StringUtils.isEmpty(header)) {
                OutputStream out = response.getOutputStream();
                workbook.write(out);
                out.close();
            }

        } catch (IOException e) {
            e.printStackTrace();
        }
    }