easyexcel拷贝模板导出&zip打包多文件导出

 0.背景

导出的报表需要以同一个模板，根据数据的不同，导出在一个workbook中的不同sheet中，同时，需要对该模板数据进行一个单个workbook导出。总的来说，一个n+1个workbook，n个单个的，1个汇总所有sheet页的，这都需要共用一个模板

 1.导出主体

    @Override
    public void exportRXZCFZ(String period, BigDecimal exchangeRate) {
        ServletRequestAttributes servletRequestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
        HttpServletResponse response = servletRequestAttributes.getResponse();
        ZipOutputStream zipOut = null;
        ByteArrayOutputStream outputStream = null;
        String templatePath = "/excel/salesdept/SDReptRXZCFZTemp.xlsx";
        String foreignTemplatePath = "/excel/salesdept/SDReptRXZCFZ_foreignTemp.xlsx";
        String rmbSingleTemplatePath = "/excel/salesdept/SDReptRXZCFZTemp_single.xlsx";
        String foreignSingleTemplatePath = "/excel/salesdept/SDReptRXZCFZ_foreignTemp_single.xlsx";
        FillConfig fillCfgHor = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).forceNewRow(Boolean.TRUE).build();
        try{
            String fileName = "人行资产负债报表"+ DateTimeFormatter.ofPattern("yyyyMMddHHmmssSSS").format(LocalDateTime.now())+".zip";
            response.setCharacterEncoding("UTF-8");
            response.setContentType("application/octet-stream");
            response.setHeader("content-Disposition", "attachment;filename=" +
                    URLEncoder.encode(new String(fileName.getBytes(), "UTF-8"), "UTF-8"));

            ClassPathResource rmbTempResource = new ClassPathResource(templatePath);
            ClassPathResource rmbSingleResource = new ClassPathResource(rmbSingleTemplatePath);
            ClassPathResource foreignTempResource = new ClassPathResource(foreignTemplatePath);
            ClassPathResource foreignSingleTempResource = new ClassPathResource(foreignSingleTemplatePath);

            //处理导出的zip 文件名称，避免中文乱码
            zipOut = new ZipOutputStream(response.getOutputStream());

            //本币
            //准备数据
            List<ExcelFillModel4SDReptRXZCFZ> rmbMdatas = rxzcfzService.getSDDataSumRXZCFZBaseData(period,true,exchangeRate);
            //准备模板
            XSSFWorkbook rmbWorkbook = new XSSFWorkbook(rmbTempResource.getInputStream());
            //分支需要调整列数
            XSSFSheet sheet0 = rmbWorkbook.cloneSheet(0, "RMB-"+rmbMdatas.get(0).getCompanyName());
            for(int startIndex = 41; startIndex >= 6 ;startIndex--){
                ExcelUtil.deleteColumn(sheet0,startIndex);
            }
            for(int i = 1; i < rmbMdatas.size(); i++){
                //复制模板，得到第i个sheet
                rmbWorkbook.cloneSheet(1, "RMB-"+rmbMdatas.get(i).getCompanyName());
            }
            rmbWorkbook.cloneSheet(0, "RMB-分支汇总");
            rmbWorkbook.removeSheetAt(0);
            // 刷新公式
            rmbWorkbook.setForceFormulaRecalculation(true);
            //使用evaluateFormulaCell对函数单元格进行强行更新计算
            rmbWorkbook.getCreationHelper().createFormulaEvaluator().evaluateAll();
            ByteArrayOutputStream rmbBos = new ByteArrayOutputStream();
            rmbWorkbook.write(rmbBos);
            //设置 zip 中文件名称
            zipOut.putNextEntry(new ZipEntry("本币-人行资产负债报表.xlsx"));
            InputStream rmbCopyInputStream = new ByteArrayInputStream(rmbBos.toByteArray());
            outputStream = new ByteArrayOutputStream();
            ExcelWriter rmbWriter = EasyExcel.write(outputStream).withTemplate(rmbCopyInputStream).excelType(ExcelTypeEnum.XLSX).build();
            //单个
            int sheetNo = 0;
            for(ExcelFillModel4SDReptRXZCFZ mdata : rmbMdatas){
                WriteSheet rmbSheet = EasyExcel.writerSheet(sheetNo).build();
                List<ExcelFillModel4SDReptRXZCFZ> tempList = new ArrayList<>();
                tempList.add(mdata);
                rmbWriter.fill(tempList,fillCfgHor,rmbSheet);
                sheetNo++;
            }
            //汇总
            WriteSheet rmbSheet = EasyExcel.writerSheet(sheetNo).build();
            rmbWriter.fill(rmbMdatas,fillCfgHor,rmbSheet);

            //刷到输出流中
            rmbWriter.finish();
            outputStream.writeTo(zipOut);
            outputStream.flush();
            outputStream.close();
            zipOut.closeEntry();

            //单省份workbook
            for(ExcelFillModel4SDReptRXZCFZ rmbModel : rmbMdatas){
                //准备模板
                XSSFWorkbook rmbWbk = new XSSFWorkbook(rmbSingleResource.getInputStream());
                // 刷新公式
                rmbWbk.setForceFormulaRecalculation(true);
                //使用evaluateFormulaCell对函数单元格进行强行更新计算
                rmbWbk.getCreationHelper().createFormulaEvaluator().evaluateAll();
                ByteArrayOutputStream bos= new ByteArrayOutputStream();
                rmbWbk.write(bos);
                //设置 zip 中文件名称
                zipOut.putNextEntry(new ZipEntry("本币-人行资产负债报表-"+rmbModel.getCompanyName()+".xlsx"));
                InputStream is = new ByteArrayInputStream(bos.toByteArray());
                outputStream = new ByteArrayOutputStream();
                ExcelWriter writer = EasyExcel.write(outputStream).withTemplate(is).excelType(ExcelTypeEnum.XLSX).build();
                WriteSheet sheet = EasyExcel.writerSheet().build();
                writer.fill(rmbModel,fillCfgHor,sheet);
                writer.finish();
                outputStream.writeTo(zipOut);
                outputStream.flush();
                outputStream.close();
                zipOut.closeEntry();
            }

            //外币
            //准备数据
            List<ExcelFillModel4SDReptRXZCFZ> foreignMdatas = rxzcfzService.getSDDataSumRXZCFZBaseData(period,false,exchangeRate);
            //准备模板
            XSSFWorkbook foreignWorkbook = new XSSFWorkbook(foreignTempResource.getInputStream());
            //分支需要调整列数
            XSSFSheet foreignSheet0 = foreignWorkbook.cloneSheet(0, "外币-"+foreignMdatas.get(0).getCompanyName());
            for(int startIndex = 41; startIndex >= 6 ;startIndex--){
                ExcelUtil.deleteColumn(foreignSheet0,startIndex);
            }
            for(int i = 1; i < foreignMdatas.size(); i++){
                //复制模板，得到第i个sheet
                foreignWorkbook.cloneSheet(1, "外币-"+foreignMdatas.get(i).getCompanyName());
            }
            foreignWorkbook.cloneSheet(0, "外币-分支汇总");
            foreignWorkbook.removeSheetAt(0);
            // 刷新公式
            foreignWorkbook.setForceFormulaRecalculation(true);
            //使用evaluateFormulaCell对函数单元格进行强行更新计算
            foreignWorkbook.getCreationHelper().createFormulaEvaluator().evaluateAll();
            ByteArrayOutputStream foreignBos = new ByteArrayOutputStream();
            foreignWorkbook.write(foreignBos);
            //设置 zip 中文件名称
            zipOut.putNextEntry(new ZipEntry("外币-人行资产负债报表.xlsx"));
            InputStream foreignCopyInputStream = new ByteArrayInputStream(foreignBos.toByteArray());
            outputStream = new ByteArrayOutputStream();
            ExcelWriter foreignWriter = EasyExcel.write(outputStream).withTemplate(foreignCopyInputStream).excelType(ExcelTypeEnum.XLSX).build();
            //单个
            int foreignSheetNo = 0;
            for(ExcelFillModel4SDReptRXZCFZ mdata : foreignMdatas){
                WriteSheet foreignSheet = EasyExcel.writerSheet(foreignSheetNo).build();
                List<ExcelFillModel4SDReptRXZCFZ> tempList = new ArrayList<>();
                tempList.add(mdata);
                foreignWriter.fill(tempList,fillCfgHor,foreignSheet);
                foreignSheetNo++;
            }
            //汇总
            WriteSheet foreignSheet = EasyExcel.writerSheet(foreignSheetNo).build();
            foreignWriter.fill(foreignMdatas,fillCfgHor,foreignSheet);

            //刷到输出流中
            foreignWriter.finish();
            outputStream.writeTo(zipOut);
            outputStream.flush();
            outputStream.close();
            zipOut.closeEntry();

            //单省份workbook
            for(ExcelFillModel4SDReptRXZCFZ foreignModel : foreignMdatas){
                //准备模板
                XSSFWorkbook foreignWbk = new XSSFWorkbook(foreignSingleTempResource.getInputStream());
                // 刷新公式
                foreignWbk.setForceFormulaRecalculation(true);
                //使用evaluateFormulaCell对函数单元格进行强行更新计算
                foreignWbk.getCreationHelper().createFormulaEvaluator().evaluateAll();
                ByteArrayOutputStream bos= new ByteArrayOutputStream();
                foreignWbk.write(bos);
                //设置 zip 中文件名称
                zipOut.putNextEntry(new ZipEntry("外币-人行资产负债报表-"+foreignModel.getCompanyName()+".xlsx"));
                InputStream is = new ByteArrayInputStream(bos.toByteArray());
                outputStream = new ByteArrayOutputStream();
                ExcelWriter writer = EasyExcel.write(outputStream).withTemplate(is).excelType(ExcelTypeEnum.XLSX).build();
                WriteSheet sheet = EasyExcel.writerSheet().build();
                writer.fill(foreignModel,fillCfgHor,sheet);
                writer.finish();
                outputStream.writeTo(zipOut);
                outputStream.flush();
                outputStream.close();
                zipOut.closeEntry();
            }


        }catch (Exception ex){
            log.error("下载失败:period:{},错误:{}", period, ExceptionUtils.getStackTrace(ex));
            ExcelUtil.exportFail("下载失败");
        } finally {
            if (null != outputStream) {
                try {
                    outputStream.flush();
                    outputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (null != zipOut) {
                try {
                    zipOut.flush();
                    zipOut.finish();
                    zipOut.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

2.删除多余列

/**
     * 删除列
     * @param sheet
     * @param columnToDelete
     */
    public static void deleteColumn(XSSFSheet sheet, int columnToDelete) {
        for (int rId = 0; rId <= sheet.getLastRowNum(); rId++) {
            Row row = sheet.getRow(rId);
            for (int cID = columnToDelete; cID <= row.getLastCellNum(); cID++) {
                Cell cOld = row.getCell(cID);
                if (cOld != null) {
                    row.removeCell(cOld);
                }
                Cell cNext = row.getCell(cID + 1);
                if (cNext != null) {
                    Cell cNew = row.createCell(cID, cNext.getCellTypeEnum());
                    cloneCell(cNew, cNext);
                    //Set the column width only on the first row.
                    //Other wise the second row will overwrite the original column width set previously.
                    if (rId == 0) {
                        sheet.setColumnWidth(cID, sheet.getColumnWidth(cID + 1));

                    }
                }
            }
        }
    }