EasyPOI 多Sheet，多文件压缩导出

依赖

        <!-- easypoi相关依赖-->
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>4.1.0</version>
        </dependency>

多Sheet

先根据poi生成对应的workbook 然后把sheet复制到新的文件中

XSSFWorkbook resultWorkbook = new XSSFWorkbook();
exportVo.getReportTypeList().forEach(type -> {
    switch (type) {
        case 1://1，日报；
            XSSFWorkbook workbook = this.getDayWorkbook(exportVo);
            copySheet(resultWorkbook, workbook.getSheetAt(0));
            break;
        case 2://2，月报；
            workbook = this.getMonthWorkbook(exportVo);
            copySheet(resultWorkbook, workbook.getSheetAt(0));
            break;
        case 3://3，打卡记录 纵向记录；
            workbook = this.getCheckInRecordWorkbook(exportVo);
            copySheet(resultWorkbook, workbook.getSheetAt(0));
            break;
        case 4://4，考勤异常；
            workbook = this.getAttendanceErrorWorkbook(exportVo);
            copySheet(resultWorkbook, workbook.getSheetAt(0));
            break;
        case 5://5，统计图表；
            try {
                getAttendanceStatisticWorkbook(exportVo, user, resultWorkbook);
            } catch (Exception e) {
                log.error("attendanceComprehensiveExport:error:", e);
                throw new RuntimeException("图表导出异常");
            }
            break;
        default: throw new RuntimeException("报表类型异常");
    }
});
try {
    FileOutputStream out = new FileOutputStream(exportVo.getFileName() + ".xlsx");
//                OutputStream out = new BufferedOutputStream(response.getOutputStream());
    response.setContentType("application/octet-stream");
    response.setHeader("content-disposition", "attachment;filename=" + URLEncoder.encode(exportVo.getFileName(), "UTF-8"));
    response.setHeader("filename", URLEncoder.encode(exportVo.getFileName() + ".xlsx", "UTF-8"));
    resultWorkbook.write(out);
    out.close();
} catch (Exception e) {
    e.getStackTrace();
    throw new RuntimeException(e);
}

/**
* 复制sheet
**/
private void copySheet(XSSFWorkbook wbCreat, XSSFSheet sheet) {
        XSSFSheet sheetCreat = wbCreat.createSheet(sheet.getSheetName());

        // 复制源表中的合并单元格
        MergerRegion(sheetCreat, sheet);
        int firstRow = sheet.getFirstRowNum();
        int lastRow = sheet.getLastRowNum();
        for (int i = firstRow; i <= lastRow; i++) {
            // 创建新建excel Sheet的行
            XSSFRow rowCreat = sheetCreat.createRow(i);
            // 取得源有excel Sheet的行
            XSSFRow row = sheet.getRow(i);
            //行所以单元格与行首单元格样式一致。
            final XSSFCellStyle style = rowCreat.getSheet().getWorkbook().createCellStyle();
            // 克隆出一个 style
            style.cloneStyleFrom(row.getCell(0).getCellStyle());
            // 单元格式样
            int firstCell = row.getFirstCellNum();
            int lastCell = row.getLastCellNum();
            for (int j = firstCell; j < lastCell; j++) {
                final XSSFCell cell = rowCreat.createCell(j);

                cell.setCellStyle(style);
                String strVal = "";
                if (row.getCell(j) != null) {
//                    try {
                        strVal = row.getCell(j).getStringCellValue();
//                    } catch (Exception e) {
//                        strVal = removeInternalBlank(String.valueOf(row.getCell(j).getNumericCellValue()));
//                    }
                }
                cell.setCellValue(strVal);
                // 设置最大列宽
                final int width = sheetCreat.getColumnWidth(j) / 256;
                final int length = strVal.getBytes().length;
                if (width < length) {
                    sheetCreat.setColumnWidth(j, length * 256);
                }

            }
        }
    }
/**
	 * 复制原有sheet的合并单元格到新创建的sheet
	 *
	 * @param sheetCreat 新创建sheet
	 * @param sheet      原有的sheet
	 */
	private static void MergerRegion(XSSFSheet sheetCreat, XSSFSheet sheet) {
		int sheetMergerCount = sheet.getNumMergedRegions();
		for (int i = 0; i < sheetMergerCount; i++) {
			sheetCreat.addMergedRegion(sheet.getMergedRegion(i));
		}
	}

多文件压缩导出

List<XSSFWorkbook> excels = new ArrayList<>();
exportVo.getReportTypeList().forEach(type -> {
    switch (type) {
        case 1://1，日报；
            excels.add(this.getDayWorkbook(exportVo));
            break;
        case 2://2，月报；
            excels.add(this.getMonthWorkbook(exportVo));
            break;
        case 3://3，打卡记录 纵向记录；
            excels.add(this.getCheckInRecordWorkbook(exportVo));
            break;
        case 4://4，考勤异常；
            excels.add(this.getAttendanceErrorWorkbook(exportVo));
            break;
        case 5://5，统计图表；
            try {
                XSSFWorkbook workbook = new XSSFWorkbook();
                getAttendanceStatisticWorkbook(exportVo, user, workbook);
                excels.add(workbook);
            } catch (Exception e) {
                log.error("attendanceComprehensiveExport:error:", e);
                throw new RuntimeException("图表导出异常");
            }
            break;
        default: throw new RuntimeException("报表类型异常");
    }
});
ExcelUtil.downFileByStream(response, excels, exportVo.getFileName());
/**
* 把文件列表 传入压缩
**/
public static void downFileByStream(HttpServletResponse response, List<XSSFWorkbook> excels, String fileName){
        try {
//            FileOutputStream toClient = new FileOutputStream(fileName +".zip");
            OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
            response.setContentType("application/octet-stream");
            response.setHeader("content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            response.setHeader("filename", URLEncoder.encode(fileName + ".zip", "UTF-8"));
            ZipOutputStream zipOutputStream = new ZipOutputStream(toClient);
            for (XSSFWorkbook excel : excels) {
                ByteArrayOutputStream baos = new ByteArrayOutputStream();
                // 将Workbook写入内存流
                excel.write(baos);
                ZipEntry zipEntry = new ZipEntry(excel.getSheetAt(0).getSheetName() + ".xlsx");
                zipOutputStream.putNextEntry(zipEntry);
                // 将内存流写入Zip文件
                zipOutputStream.write(baos.toByteArray());
            }
            zipOutputStream.closeEntry();
            zipOutputStream.flush();
            zipOutputStream.close();
            toClient.close();
        }catch (Exception e){
            LOGGER.error("downFileByStream==========fail:{}", e.getMessage());
        }
    }