邮件发送easyexcel附件

最新推荐文章于 2024-05-06 23:26:43 发布

加油干饭人

最新推荐文章于 2024-05-06 23:26:43 发布

阅读量91

点赞数

文章标签： json

本文链接：https://blog.csdn.net/after_17/article/details/134942467

版权

  //流的形式
  public void sendEmail(){
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        // 创建合并策略对象
        OnceAbsoluteMergeStrategy strategy = new OnceAbsoluteMergeStrategy(67, 67, 0, 1);// 合并A65:B65区域的单元格
        OnceAbsoluteMergeStrategy strategy2 = new OnceAbsoluteMergeStrategy(68, 68, 0, 1);// 合并A65:B65区域的单元格
        List<ExcelHiddenCellVo> excelHiddenCellVos = this.calculateHiddenRow(financeDeptKpiStatisticsExportVos); // 计算分组的数据
        EasyExcel.write(outputStream)
                .head(this.emailExcelHead(year, deptName)) // 设置表头
                .inMemory(true)
                .registerWriteHandler(this.getHorizontalCellStyleStrategy()) // 整体单元格的样式设置
                .registerWriteHandler(new SimpleColumnWidthStyleStrategy(11)) // 简单的列宽策略，列宽20
                .registerWriteHandler(strategy) // 合并合计单元格
                .registerWriteHandler(strategy2) // 合并收入达成率单元格
                .sheet(deptName)
               .registerWriteHandler(this.cellStyleHandler(financeDeptKpiStatisticsExportVos, excelHiddenCellVos)) // 自定义单元格样式
                .registerWriteHandler(this.rowWriteHandler(this.calculateHiddenColumn())) // 列分组
                .doWrite(financeDeptKpiStatisticsExportVos);
                }

        InputStream inputStream = new ByteArrayInputStream(outputStream.toByteArray());
        // 再用流作为附件信息发送

    // 计算出需要折叠的行
    private List<ExcelHiddenCellVo> calculateHiddenRow(List<FinanceDeptKpiStatisticsExportVo> vos) {
        List<ExcelHiddenCellVo> excelHiddenCellVos = new ArrayList<>();
        int realStartHidden = 6;
        int endHidden = 14;
        for (int i = 9; i < vos.size() - 1; i++) {
            if ((vos.get(i).getActualAggregateValue() != 0 || vos.get(i).getBudgetAggregateValue() != 0 || i == vos.size() - 1)) {
                if (realStartHidden == endHidden){
                    ++realStartHidden;
                }
                ++endHidden;
            }
            if (vos.get(i).getActualAggregateValue() == 0 && vos.get(i).getBudgetAggregateValue() == 0) {
                if ( (vos.get(i + 1).getActualAggregateValue() != 0 || vos.get(i + 1).getBudgetAggregateValue() != 0 || i == vos.size() - 1)) {
                  excelHiddenCellVo.setStartHidden(realStartHidden);
                    excelHiddenCellVo.setEndHidden(endHidden);
                    excelHiddenCellVos.add(excelHiddenCellVo);
                    realStartHidden = endHidden = excelHiddenCellVo.getEndHidden() + 1;
                }else{
                    ++endHidden;
                }
            }
        }
        return excelHiddenCellVos;
    }
// 计算出需要折叠的列
    private List<ExcelHiddenCellVo> calculateHiddenColumn() {
        List<ExcelHiddenCellVo> excelHiddenCellVos = new ArrayList<>();
        int monthValue = LocalDateTime.now().getMonthValue() - 1;
        int startHidden = 3;
        for (int i = 1; i < 18; i++) {
            ExcelHiddenCellVo excelHiddenCellVo = new ExcelHiddenCellVo();
            excelHiddenCellVo.setStartHidden(startHidden);
            excelHiddenCellVo.setEndHidden(startHidden + 1);
            excelHiddenCellVo.setIfHidden(false);
            excelHiddenCellVo.setIfCollapse(true);
            if (i >= 13) {
                excelHiddenCellVo.setIfCollapse(false);
            } else if (i > monthValue) {
                excelHiddenCellVo.setIfHidden(true);
            } else if (monthValue == i) {
                excelHiddenCellVo.setIfCollapse(false);
            }

            excelHiddenCellVos.add(excelHiddenCellVo);
            startHidden += 3;
        }
        return excelHiddenCellVos
        }

    private RowWriteHandler rowWriteHandler(List<ExcelHiddenCellVo> excelHiddenCellVos) {
        return new RowWriteHandler() {
            @Override
            public void afterRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {
                // 列分组
                if (relativeRowIndex == 53) {
                    excelHiddenCellVos.forEach(item -> {
                        Sheet sheet = writeSheetHolder.getSheet();
                        sheet.groupColumn(item.getStartHidden(), item.getEndHidden());
                        sheet.setColumnGroupCollapsed(item.getStartHidden(), item.getIfCollapse());
                        if (item.getIfHidden()) {
//                            sheet.setColumnHidden(item.getStartHidden(), true);
//                            sheet.setColumnHidden(item.getStartHidden()+1,true);
                            sheet.setColumnHidden(item.getStartHidden() + 2, true);
                        }
                    });
                }
            }
        };
    }

//