快捷導(dǎo)航

Spring Boot 使用FastExcel實現(xiàn)多級表頭動態(tài)數(shù)據(jù)填充導(dǎo)出

更新時間：2025年12月24日 11:15:09 作者：愿你天黑有燈下雨有傘

文章介紹了如何使用Maven管理依賴,創(chuàng)建動態(tài)表頭實體類,實現(xiàn)動態(tài)表頭數(shù)據(jù)填充策略,并提供了一個導(dǎo)出工具類的示例,感興趣的朋友跟隨小編一起看看吧

1：依賴（Maven）

<!-- FastExcel 官網(wǎng)最新版 -->
<dependency>
    <groupId>cn.idev.excel</groupId>
    <artifactId>fastexcel</artifactId>
    <version>1.0.0</version>
</dependency>

2：動態(tài)表頭實體類

package com.fantaibao.module.vo.target;
import cn.idev.excel.annotation.ExcelProperty;
import cn.idev.excel.annotation.write.style.ColumnWidth;
import cn.idev.excel.annotation.write.style.ContentStyle;
import cn.idev.excel.annotation.write.style.HeadFontStyle;
import cn.idev.excel.annotation.write.style.HeadStyle;
import cn.idev.excel.enums.poi.FillPatternTypeEnum;
import cn.idev.excel.enums.poi.HorizontalAlignmentEnum;
import cn.idev.excel.enums.poi.VerticalAlignmentEnum;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@HeadFontStyle(fontName = "黑體", fontHeightInPoints = 12)
@HeadStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER,
        fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 44)
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER)
public class TargetIndexExportStoreStaVo {
    /**
     * 年份(2025)
     */
    @ColumnWidth(15)
    @ExcelProperty({"${indexName}", "年度"})
    private Integer year;
    /**
     * 門店名稱
     */
    @ColumnWidth(20)
    @ExcelProperty({"${indexName}", "門店名稱"})
    private String storeName;
    /**
     * 年目標
     */
    @ColumnWidth(15)
    @ExcelProperty({"${indexName}", "年目標"})
    private String totalTarget;
    /**
     * 當前進度
     */
    @ColumnWidth(20)
    @ExcelProperty({"${indexName}", "當前進度"})
    private String progress;
    /**
     * 目標完成率
     */
    @ColumnWidth(20)
    @ExcelProperty({"${indexName}", "目標完成率"})
    private String finishRate;
}

package com.fantaibao.module.vo.target;
import cn.idev.excel.annotation.ExcelProperty;
import cn.idev.excel.annotation.write.style.*;
import cn.idev.excel.enums.poi.FillPatternTypeEnum;
import cn.idev.excel.enums.poi.HorizontalAlignmentEnum;
import cn.idev.excel.enums.poi.VerticalAlignmentEnum;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@HeadFontStyle(fontName = "黑體", fontHeightInPoints = 12)
@HeadStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER,
        fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 44)
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER)
@ContentFontStyle(fontName = "黑體", fontHeightInPoints = 10)
public class TargetIndexExportStoreMonthStaVo {
    /**
     * 年份(2025)
     */
    @ColumnWidth(15)
    @ExcelProperty({"${indexName}", "年度"})
    private Integer year;
    /**
     * 門店名稱
     */
    @ColumnWidth(25)
    @ExcelProperty({"${indexName}", "門店名稱"})
    private String storeName;
    /**
     * 月目標
     */
    @ColumnWidth(15)
    @ExcelProperty({"${indexName}", "${month}月目標"})
    private String totalTarget;
    /**
     * 當前進度
     */
    @ColumnWidth(20)
    @ExcelProperty({"${indexName}", "當前進度"})
    private String progress;
    /**
     * 目標完成率
     */
    @ColumnWidth(20)
    @ExcelProperty({"${indexName}", "目標完成率"})
    private String finishRate;
}

3：動態(tài)表頭數(shù)據(jù)填充策略

package com.fantaibao.handler;
import cn.hutool.core.collection.CollUtil;
import cn.idev.excel.metadata.Head;
import cn.idev.excel.write.handler.CellWriteHandler;
import cn.idev.excel.write.metadata.holder.WriteSheetHolder;
import cn.idev.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.Cell;
public class CustomHeaderWriteHandler implements CellWriteHandler {
    private final String indexName;
    public CustomHeaderWriteHandler(String indexName) {
        this.indexName = indexName;
    }
    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
                                Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        // 處理表頭
        if (CollUtil.isNotEmpty(head.getHeadNameList())) {
            // 處理一級表頭
            if (CollUtil.isNotEmpty(head.getHeadNameList())) {
                String replace = head.getHeadNameList().get(0).replace("${indexName}", indexName);
                head.getHeadNameList().set(0, replace);
            }
        }
    }
}
public class CustomMonthHeaderWriteHandler implements CellWriteHandler {
    private final String indexName;
    private final int month;
    public CustomMonthHeaderWriteHandler(String indexName, int month) {
        this.indexName = indexName;
        this.month = month;
    }
    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
                                Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        if (isHead && head != null) {
            // 處理表頭
            if (CollUtil.isNotEmpty(head.getHeadNameList())) {
                // 處理一級表頭
                String indexName = head.getHeadNameList().get(0).replace("${indexName}", this.indexName);
                head.getHeadNameList().set(0, indexName);
                // 處理二級表頭
                String monthStr = head.getHeadNameList().get(1);
                if (!monthStr.contains("${month}")) {
                    return;
                }
                String month = monthStr.replace("${month}", String.valueOf(this.month));
                head.getHeadNameList().set(1, month);
            }
        }
    }
}

4：導(dǎo)出工具類

/**
     * 按單個指標生成年度-月度匯總數(shù)據(jù)
     *
     * @param zipOut               ZIP輸出流
     * @param fileNamePrefix        文件名前綴
     * @param storeTargetYearList  門店年目標集合
     * @param storeMonthTargetList 門店月目標集合
     * @param enums                指標
     * @param storeMap             門店集合
     * @throws IOException 拋出的異常
     */
    public static void generateSingleIndexStaExcelFile(ZipOutputStream zipOut, String fileNamePrefix, List<StoreIndexYearStaVo> storeTargetYearList,
                                                       List<StoreIndexMonthStaVo> storeMonthTargetList, IndicatorsTypeEnums enums,
                                                       Map<String, StoreInfoListVO> storeMap) throws IOException {
        ZipEntry zipEntry = new ZipEntry(enums.getName() + "門店" + fileNamePrefix + "目標匯總.xlsx");
        zipOut.putNextEntry(zipEntry);
        try (ExcelWriter excelWriter = EasyExcel.write(zipOut).autoCloseStream(false).build()) {
            // Sheet1: 門店指標年目標匯總
            WriteSheet yearSheet = EasyExcel.writerSheet(0, enums.getName() + "門店年目標")
                    .head(TargetIndexExportStoreStaVo.class)
                    .registerWriteHandler(new CustomHeaderWriteHandler(StrUtil.isNotBlank(enums.getUnit()) ? enums.getName() + "(" + enums.getUnit() + ")" : enums.getName()))
                    .build();
            excelWriter.write(getYearSheetData(enums, storeTargetYearList, storeMap), yearSheet);
            // Sheet2: 門店指標月目標匯總
            for (int month = 1; month <= 12; month++) {
                WriteSheet monthSheet = EasyExcel.writerSheet(month, "門店" + month + "月目標")
                        .head(TargetIndexExportStoreMonthStaVo.class)
                        .registerWriteHandler(new CustomMonthHeaderWriteHandler(StrUtil.isNotBlank(enums.getUnit()) ? enums.getName() + "(" + enums.getUnit() + ")" : enums.getName(), month))
                        .build();
                excelWriter.write(getMonthSheetDataByMonth(enums, storeMonthTargetList, month, storeMap), monthSheet);
            }
            excelWriter.finish();
        } catch (Exception e) {
            try {
                zipOut.closeEntry();
            } catch (IOException ignored) {
            }
            throw e;
        }
        zipOut.closeEntry();
    }

5：樣例

到此這篇關(guān)于Spring Boot 使用FastExcel實現(xiàn)多級表頭動態(tài)數(shù)據(jù)填充導(dǎo)出的文章就介紹到這了,更多相關(guān)Spring Boot FastExcel多級表頭數(shù)據(jù)填充導(dǎo)出內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: