EasyPoi 导出同文件多Sheet/多文件zip

同文件多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，统计图表；
            break;
        default: throw new RuntimeException("报表类型异常");
    }
});
try {
    FileOutputStream out = new FileOutputStream(exportVo.getFileName() + ".xlsx");
    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) {
                        strVal = row.getCell(j).getStringCellValue();
                }
                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));
		}
	}

多文件zip

主要代码块
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，统计图表；
             break;
         default: throw new RuntimeException("报表类型异常");
     }
 });
 ExcelUtil.downFileByStream(response, excels, exportVo.getFileName());
 
/**
 * 直接下载zip包
 * @param response response
 * @param excels wb集合
 */
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());
        }
    }

相关依赖

<!-- 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>