POI实现一个excel 生成多个sheet

迷南。 2022-11-16 10:38 414阅读 0赞

### 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();
            }
        }