Java代码EasyExcel实现Excel导出多份文件并压缩成zip包访问下载

一、背景

网页请求后端下载excel，但是但是批量下载同一日期的下载文件会过多。所以有需求就是将下载的exl多份文件打包zip下载。
本次项目中使用的是阿里EasyExcel来导出exl.

二、实现

点击查看代码

public void exportAppReport(HttpServletResponse response, AppReportQueryCriteria appReportQueryCriteria) throws IOException {

//        String month = appReportQueryCriteria.getMonths();
        List<String> months = appReportQueryCriteria.getMonths();
        if (ObjectUtil.isEmpty(months)) {
            throw new IllegalArgumentException("时间选择不能为空！");
        }

//        String station = appReportQueryCriteria.getStations();
        List<String> stations = appReportQueryCriteria.getStations();
        if (ObjectUtil.isEmpty(stations)) {
            throw new IllegalArgumentException("场站选择不能为空！");
        }


        response.setContentType("application/zip");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-Disposition", "attachment; filename=\"export.zip\"");

        Resource resource = new ClassPathResource("excel" + File.separator + "生产情况月统计表模板V5.xlsx");

        ServletOutputStream outputStream = response.getOutputStream();
        ZipOutputStream zipOutputStream = new ZipOutputStream(outputStream);

        try{
        for (String month : months) {
            ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
            for (String station : stations) {
                GeognameDetailDto geognameDetailDto = geognameService.findById(Long.valueOf(station));
                stationName = geognameDetailDto.getName();
                Map<String, Object> mapInfo = getsheetInfo(month, station);
                Map<String, Object> mapCover = getSheetCover(month, station);

                // 水质
                List<ReportWaterQualityFormatDto> reportWaterQualityFormatList = getReportWaterQualityListData(month, station);

                // 在线监测
                List<ReportWaterQualityOnlineFormatDto> onlineWaterQualityDtoList = getOnlineWaterQualityListData(month, station);

                //    水处理
                List<ReportWaterMergeDto> reportWaterlist = getReportWaterListData(month, station);

                //      用电
                List<ReportWaterEleDto> waterEleDtoList = getReportWaterEleDtos(month, station);

                //    泥处理
                List<ReportMudStatisticsDto> reportMudStatisticsDtoList = getReportMudStatisticsDtos(month, station);

                // 生成当月指标的数据
                List<Object> currentTargetList = getCurrentTarget(reportWaterQualityFormatList);

                // 生成当月指标汇总
                List<Object> currentTotal = getCurrentTotal(reportWaterlist, waterEleDtoList, reportMudStatisticsDtoList, reportWaterQualityFormatList);

                try (ExcelWriter excelWriter = EasyExcel.write(byteArrayOutputStream).withTemplate(resource.getInputStream()).build()) {
                    WriteSheet sheetInfo = EasyExcel.writerSheet("基础信息").build();
                    excelWriter.fill(mapInfo, sheetInfo);

                    WriteSheet sheetCover = EasyExcel.writerSheet("封面").build();
                    excelWriter.fill(mapCover, sheetCover);

                    WriteSheet sheetWaterQuality = EasyExcel.writerSheet("水质").build();
                    excelWriter.fill(reportWaterQualityFormatList, sheetWaterQuality);

                    WriteSheet sheetCurrentMonth = EasyExcel.writerSheet("当月指标").build();
                    excelWriter.fill(currentTargetList, sheetCurrentMonth);

                    WriteSheet sheetCurrentTotalMonth = EasyExcel.writerSheet("当月指标").build();
                    excelWriter.fill(currentTotal, sheetCurrentTotalMonth);

                    WriteSheet sheetReportWater = EasyExcel.writerSheet("水处理").build();
                    excelWriter.fill(reportWaterlist, sheetReportWater);

                    WriteSheet onlineWaterSheet = EasyExcel.writerSheet("在线监测").build();
                    excelWriter.fill(onlineWaterQualityDtoList, onlineWaterSheet);

                    WriteSheet sheetReportWaterEle = EasyExcel.writerSheet("用电").build();
                    excelWriter.fill(waterEleDtoList, sheetReportWaterEle);

                    WriteSheet sheetReportMud = EasyExcel.writerSheet("泥处理").build();
                    excelWriter.fill(reportMudStatisticsDtoList, sheetReportMud);

                    //设置强制计算公式：不然公式会以字符串的形式显示在excel中
                    Workbook workbook = excelWriter.writeContext().writeWorkbookHolder().getWorkbook();
                    workbook.setForceFormulaRecalculation(true);

                    //创建压缩文件
                    ZipEntry zipEntry = new ZipEntry(month+stationName + ".xlsx");
                    zipOutputStream.putNextEntry(zipEntry);
                    // 添加到zip输出流中
                    workbook.write(zipOutputStream);

                    //记得关闭实体 否则导出的zip包会丢失最后一份数据
                    zipOutputStream.closeEntry();
                    zipOutputStream.flush();
                }
            }

        }
        }catch (Exception e) {
            log.error("导XXX失败,原因" + e.getMessage());
            log.error(e.getMessage(), e);
            //抛出异常结束程序
            throw new RuntimeException("数据导出接口异常");
        } finally {
            //关闭数据流，注意关闭的顺序
            zipOutputStream.close();
            outputStream.close();
        }

    }

三、遇到的报错

报错：不可预料的文件末端
解决：流没有关闭或者关闭的顺序不正确

点击查看代码

finally {
         //关闭数据流，注意关闭的顺序
         zipOutputStream.close();
         outputStream.close();
     }

四、参考博客

https://blog.csdn.net/weixin_44990626/article/details/115307245