快捷導(dǎo)航

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

更新時(shí)間：2025年12月24日 08:57:20 作者：愿你天黑有燈下雨有傘

這篇文章主要為大家詳細(xì)介紹了SpringBoot如何使用FastExcel實(shí)現(xiàn)多級(jí)表頭動(dòng)態(tài)數(shù)據(jù)填充導(dǎo)出功能,文中的示例代碼講解詳細(xì),感興趣的小伙伴可以跟隨小編一起學(xué)習(xí)一下

1：依賴（Maven）

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

2：動(dòng)態(tài)表頭實(shí)體類

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;

    /**
     * 門(mén)店名稱
     */
    @ColumnWidth(20)
    @ExcelProperty({"${indexName}", "門(mén)店名稱"})
    private String storeName;
    /**
     * 年目標(biāo)
     */
    @ColumnWidth(15)
    @ExcelProperty({"${indexName}", "年目標(biāo)"})
    private String totalTarget;
    /**
     * 當(dāng)前進(jìn)度
     */
    @ColumnWidth(20)
    @ExcelProperty({"${indexName}", "當(dāng)前進(jìn)度"})
    private String progress;
    /**
     * 目標(biāo)完成率
     */
    @ColumnWidth(20)
    @ExcelProperty({"${indexName}", "目標(biāo)完成率"})
    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;

    /**
     * 門(mén)店名稱
     */
    @ColumnWidth(25)
    @ExcelProperty({"${indexName}", "門(mén)店名稱"})
    private String storeName;

    /**
     * 月目標(biāo)
     */
    @ColumnWidth(15)
    @ExcelProperty({"${indexName}", "${month}月目標(biāo)"})
    private String totalTarget;

    /**
     * 當(dāng)前進(jìn)度
     */
    @ColumnWidth(20)
    @ExcelProperty({"${indexName}", "當(dāng)前進(jìn)度"})
    private String progress;

    /**
     * 目標(biāo)完成率
     */
    @ColumnWidth(20)
    @ExcelProperty({"${indexName}", "目標(biāo)完成率"})
    private String finishRate;
}

3：動(dòng)態(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())) {
            // 處理一級(jí)表頭
            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())) {
                // 處理一級(jí)表頭
                String indexName = head.getHeadNameList().get(0).replace("${indexName}", this.indexName);
                head.getHeadNameList().set(0, indexName);
                // 處理二級(jí)表頭
                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)出工具類

/**
     * 按單個(gè)指標(biāo)生成年度-月度匯總數(shù)據(jù)
     *
     * @param zipOut               ZIP輸出流
     * @param fileNamePrefix        文件名前綴
     * @param storeTargetYearList  門(mén)店年目標(biāo)集合
     * @param storeMonthTargetList 門(mén)店月目標(biāo)集合
     * @param enums                指標(biāo)
     * @param storeMap             門(mén)店集合
     * @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() + "門(mén)店" + fileNamePrefix + "目標(biāo)匯總.xlsx");
        zipOut.putNextEntry(zipEntry);
        try (ExcelWriter excelWriter = EasyExcel.write(zipOut).autoCloseStream(false).build()) {
            // Sheet1: 門(mén)店指標(biāo)年目標(biāo)匯總
            WriteSheet yearSheet = EasyExcel.writerSheet(0, enums.getName() + "門(mén)店年目標(biāo)")
                    .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: 門(mén)店指標(biāo)月目標(biāo)匯總
            for (int month = 1; month <= 12; month++) {
                WriteSheet monthSheet = EasyExcel.writerSheet(month, "門(mén)店" + month + "月目標(biāo)")
                        .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：樣例

以上就是SpringBoot使用FastExcel實(shí)現(xiàn)多級(jí)表頭動(dòng)態(tài)數(shù)據(jù)填充導(dǎo)出的詳細(xì)內(nèi)容，更多關(guān)于SpringBoot FastExcel數(shù)據(jù)填充導(dǎo)出的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章: