Java 使用POI批量生成Excel，并打包成压缩包

我这里引入的是poi-3.17.jar

效果图

完整代码

public void enrollExportBatch(HttpServletRequest request, HttpServletResponse response) throws IOException {
    String examId = request.getParameter("examId");
    String cityRegionCode = request.getParameter("cityRegionCode");
    String regionId = request.getParameter("regionId");
    String optionId = request.getParameter("optionId");

    List<EmRegion> emRegionList = emExamRegionService.findByExamIdAndLikeCode(examId, cityRegionCode, getLoginInfo().getUnitId());
    // 获取考点列表
    List<EmOption> emOptionList = emOptionService.findByExamIdAndOptionIdAndExamRegionIdIn(examId,
            regionId, EntityUtils.getSet(emRegionList, "id").toArray(new String[0]), optionId);

    // 获取考试名称
    EmExamInfo examInfo = emExamInfoService.findOne(examId);
    String examName = examInfo.getExamName();

    // 输出流
    ServletOutputStream out = response.getOutputStream();
    //压缩输出流
    ZipOutputStream zipOutputStream = new ZipOutputStream(out);
    String fileName = examName + ".zip";
    int i = 0;
    
    // 设置题头
    String[] headers = {"考生姓名", "准考证号", "身份证号", "考区", "座位号", "作弊/缺考", "备注"};

    try {
        for (EmOption emOption : emOptionList) {
            // 设置regionName
            setRegionName(emRegionList, emOptionList);
            // 查询考场列表
            List<EmPlace> emPlaces = emPlaceService.findByOptionIdAndExamId(emOption.getId(), emOption.getExamId());

            for (EmPlace emPlace : emPlaces) {
                String sheetName = "sheet" + i++;
                //新建一个Excel 并设置下sheet头
                HSSFWorkbook workbook = createExcelAndSetHeaders(headers, sheetName, emOption.getOptionName());

                // 获取考场学生列表
                List<EmPlaceStudent> emPlaceStudentList = emPlaceStudentService.findByExamPlaceIdAndGroupId(null, emPlace.getId());
                //向sheet中 继续填充对象的数据
                setSheetCellValue(workbook.getSheet(sheetName), emPlaceStudentList, emOption.getRegionName());

                response.setContentType("application/octet-stream; charset=utf-8");
                response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "utf-8"));
                //重点开始,创建压缩文件
                // 这里添加 "/" 可以把文件分目录进行打包；去掉 "/" 则打包到一个文件夹中
                ZipEntry z = new ZipEntry(emOption.getOptionName() + "/" + emPlace.getExamPlaceCode() + "考场.xls");
                zipOutputStream.putNextEntry(z);
                //写入一个压缩文件
                workbook.write(zipOutputStream);
            }
        }

        zipOutputStream.flush();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        //注意关闭顺序，否则可能文件错误
        if (zipOutputStream != null) {
            zipOutputStream.close();
        }
        if (out != null) {
            out.close();
        }
    }
}

/**
 * 定制化表格创建头部
 *
 * @param headers 头部信息
 * @param sheetName sheet名称
 * @param optionName 考点名称
 * @return
 */
private HSSFWorkbook createExcelAndSetHeaders(String[] headers, String sheetName, String optionName) {
    HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
    HSSFSheet hssfSheet = hssfWorkbook.createSheet(sheetName);
    // 设置头第一行信息
    HSSFRow row0 = hssfSheet.createRow(0);
    HSSFCell cellHeader0 = row0.createCell(0);
    cellHeader0.setCellValue("考点");
    HSSFCell cellHeader6 = row0.createCell(2);
    cellHeader6.setCellValue(optionName);

    // 设置头第二行信息
    HSSFRow row1 = hssfSheet.createRow(1);
    HSSFCell cellHeader1 = row1.createCell(0);
    cellHeader1.setCellValue("考场号及日期");
    HSSFCell cellHeader2 = row1.createCell(3);
    cellHeader2.setCellValue("科目以及时间");

    HSSFRow row3 = hssfSheet.createRow(2);
    for (int i = 0; i < headers.length; i++) {
        HSSFCell cellHeader3 = row3.createCell(i);
        cellHeader3.setCellValue(headers[i]);
    }
    return hssfWorkbook;
}

/**
 * 表格填充数据
 *
 * @param hssfSheet
 * @param emPlaceStudents 学生数据
 * @param regionName 考区名称
 */
private void setSheetCellValue(HSSFSheet hssfSheet, List<EmPlaceStudent> emPlaceStudents, String regionName) {
    for (EmPlaceStudent emPlaceStudent : emPlaceStudents) {
        //从当前sheet页的最后一行后新增一行，开始填充数据
        HSSFRow row = hssfSheet.createRow(hssfSheet.getLastRowNum() + 1);
        int count = -1;
        row.createCell(++count).setCellValue(emPlaceStudent.getStudentName());
        row.createCell(++count).setCellValue(emPlaceStudent.getExamNumber());
        row.createCell(++count).setCellValue(emPlaceStudent.getIdentityCard());
        row.createCell(++count).setCellValue(regionName);
        row.createCell(++count).setCellValue(emPlaceStudent.getSeatNum());
    }

    int rowNum = hssfSheet.getLastRowNum();// 获得总行数
    Row newRow=hssfSheet.createRow(rowNum+2);//新建一行
    Cell cell = newRow.createCell(0);
    cell.setCellValue("主监考员：");
    Cell cell1 = newRow.createCell(3);
    cell1.setCellValue("副监考员：");

    int rowNum1 = hssfSheet.getLastRowNum();// 获得总行数
    Row newRow1=hssfSheet.createRow(rowNum1+1);//新建一行
    Cell cell2 = newRow1.createCell(0);
    cell2.setCellValue("备注说明：缺考——“1” 作弊——“2”");
}