记录一次NPOI复杂样式报表和模板导出

一、开发环境

• .NET Core 8.0

• NPOI 2.8.0

• Visaul Studio 2022

• 控制台项目

二、需求介绍

• 项目希望导出如下格式报表

• 制作模板格式如下

三、个人思考

• 直接计算数据行+标题行+头部行总行数整体下移，然后写个拷贝区域样式的工具方法拷贝头部到指定位置，随后填充数据。

四、代码实现

• NPOI的帮助类

using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

namespace ExcelReportDemo.Excel
{
    /// <summary>
    /// NPOI 通用工具类（静态方法，无状态）
    /// 仅提供原子操作，不包含业务逻辑
    /// </summary>
    public static class NPOIHelper
    {
        #region Workbook 加载

        /// <summary>
        /// 从文件路径加载工作簿（仅支持 .xlsx）
        /// </summary>
        public static IWorkbook LoadWorkbook(string filePath)
        {
            if (string.IsNullOrEmpty(filePath))
                throw new ArgumentNullException(nameof(filePath));

            if (!File.Exists(filePath))
                throw new FileNotFoundException($"文件不存在: {filePath}");

            using var fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
            return LoadWorkbook(fs);
        }

        /// <summary>
        /// 从流加载工作簿（仅支持 .xlsx）
        /// </summary>
        public static IWorkbook LoadWorkbook(Stream stream)
        {
            if (stream == null)
                throw new ArgumentNullException(nameof(stream));

            stream.Position = 0;
            return new XSSFWorkbook(stream);
        }

        #endregion

        #region 行操作

        /// <summary>
        /// 获取或创建行
        /// </summary>
        public static IRow GetOrCreateRow(ISheet sheet, int rowIndex)
        {
            if (sheet == null)
                throw new ArgumentNullException(nameof(sheet));

            if (rowIndex < 0)
                throw new ArgumentException("行索引不能为负数", nameof(rowIndex));

            var row = sheet.GetRow(rowIndex);
            if (row == null)
            {
                row = sheet.CreateRow(rowIndex);
            }
            return row;
        }
        #endregion

        #region 单元格操作

        /// <summary>
        /// 获取或创建单元格
        /// </summary>
        public static ICell GetOrCreateCell(IRow row, int columnIndex)
        {
            if (row == null)
                throw new ArgumentNullException(nameof(row));

            if (columnIndex < 0)
                throw new ArgumentException("列索引不能为负数", nameof(columnIndex));

            var cell = row.GetCell(columnIndex);
            if (cell == null)
            {
                cell = row.CreateCell(columnIndex);
            }
            return cell;
        }

        /// <summary>
        /// 获取或创建单元格（Sheet + 行列索引）
        /// </summary>
        public static ICell GetOrCreateCell(ISheet sheet, int rowIndex, int columnIndex)
        {
            var row = GetOrCreateRow(sheet, rowIndex);
            return GetOrCreateCell(row, columnIndex);
        }
        #endregion

        #region 单元格写入
        /// <summary>
        /// 智能设置单元格值（自动识别类型）
        /// </summary>
        public static void SetCellValue(ICell cell, object? value)
        {
            if (cell == null)
                throw new ArgumentNullException(nameof(cell));

            if (value == null || value == DBNull.Value)
            {
                cell.SetBlank();
                return;
            }

            switch (value)
            {
                case string str:
                    cell.SetCellValue(str);
                    break;
                case int i:
                    cell.SetCellValue(i);
                    break;
                case long l:
                    cell.SetCellValue(l);
                    break;
                case double d:
                    cell.SetCellValue(d);
                    break;
                case float f:
                    cell.SetCellValue(f);
                    break;
                case decimal d:
                    cell.SetCellValue((double)d);
                    break;
                case bool b:
                    cell.SetCellValue(b);
                    break;
                case DateTime dt:
                    cell.SetCellValue(dt);
                    break;
                case DateTimeOffset dto:
                    cell.SetCellValue(dto.DateTime);
                    break;
                case Enum e:
                    cell.SetCellValue(e.ToString());
                    break;
                default:
                    cell.SetCellValue(value.ToString());
                    break;
            }
        }
        #endregion

        #region 保存

        /// <summary>
        /// 保存工作簿到文件
        /// </summary>
        public static void SaveWorkbook(IWorkbook workbook, string filePath)
        {
            if (workbook == null)
                throw new ArgumentNullException(nameof(workbook));

            if (string.IsNullOrEmpty(filePath))
                throw new ArgumentNullException(nameof(filePath));

            // 自动补全 .xlsx 扩展名
            if (!filePath.EndsWith(".xlsx", StringComparison.OrdinalIgnoreCase) &&
                !filePath.EndsWith(".xlsm", StringComparison.OrdinalIgnoreCase))
            {
                filePath += ".xlsx";
            }

            // 确保目录存在
            string? directory = Path.GetDirectoryName(filePath);
            if (!string.IsNullOrEmpty(directory) && !Directory.Exists(directory))
            {
                Directory.CreateDirectory(directory);
            }

            using var fs = new FileStream(filePath, FileMode.Create, FileAccess.Write);
            workbook.Write(fs);
        }
        #endregion
    }
}

• 区域拷贝帮助类

注意: 这里示例Excel的表头比较简单，帮助类不能覆盖全部需求，如果需要其他特殊复制需自行扩展。

using NPOI.SS.UserModel;
using NPOI.SS.Util;

namespace ExcelReportDemo.Excel
{
    /// <summary>
    /// Excel 行复制器
    /// 支持复制行数据、样式、合并区域
    /// </summary>
    public class ExcelRowCopier
    {
        /// <summary>
        /// 初始化行复制器
        /// </summary>
        public ExcelRowCopier()
        {
        }

        #region 公开方法

        /// <summary>
        /// 复制行范围到指定位置
        /// </summary>
        /// <param name="sourceSheet">源工作表</param>
        /// <param name="targetSheet">目标工作表</param>
        /// <param name="startSourceRow">源起始行索引（从0开始）</param>
        /// <param name="endSourceRow">源结束行索引（从0开始）</param>
        /// <param name="startTargetRow">目标起始行索引（从0开始）</param>
        /// <param name="includeMergedRegions">是否包含合并区域，默认 true</param>
        /// <exception cref="ArgumentNullException">源或目标 Sheet 为 null</exception>
        /// <exception cref="ArgumentException">行索引参数无效</exception>
        public static void CopyRowRange(
            ISheet sourceSheet,
            ISheet targetSheet,
            int startSourceRow,
            int endSourceRow,
            int startTargetRow,
            bool includeMergedRegions = true)
        {
            ValidateParameters(sourceSheet, targetSheet, startSourceRow, endSourceRow, startTargetRow);

            int rowOffset = startTargetRow - startSourceRow;

            // 1. 复制行数据
            CopyRows(sourceSheet, targetSheet, startSourceRow, endSourceRow, rowOffset);

            // 2. 复制合并区域
            if (includeMergedRegions)
            {
                CopyMergedRegions(sourceSheet, targetSheet, startSourceRow, endSourceRow, rowOffset);
            }
        }

        #endregion

        #region 核心复制方法

        /// <summary>
        /// 复制多行数据
        /// </summary>
        private static void CopyRows(ISheet sourceSheet, ISheet targetSheet, int startSourceRow, int endSourceRow, int rowOffset)
        {
            for (int rowIndex = startSourceRow; rowIndex <= endSourceRow; rowIndex++)
            {
                IRow sourceRow = sourceSheet.GetRow(rowIndex);
                if (sourceRow == null)
                {
                    continue;
                }

                int targetRowIndex = rowIndex + rowOffset;
                IRow targetRow = targetSheet.GetRow(targetRowIndex) ?? targetSheet.CreateRow(targetRowIndex);

                CopyRow(sourceRow, targetRow);
            }
        }

        /// <summary>
        /// 复制单行（值 + 样式）
        /// </summary>
        private static void CopyRow(IRow sourceRow, IRow targetRow)
        {
            // 复制行属性
            targetRow.Height = sourceRow.Height;
            targetRow.ZeroHeight = sourceRow.ZeroHeight;

            targetRow.RowStyle = sourceRow.RowStyle;

            // 遍历所有单元格
            for (int columnIndex = 0; columnIndex < sourceRow.LastCellNum; columnIndex++)
            {
                ICell sourceCell = sourceRow.GetCell(columnIndex);
                if (sourceCell == null)
                {
                    continue;
                }

                ICell targetCell = targetRow.GetCell(columnIndex) ?? targetRow.CreateCell(columnIndex);
                CopyCellValue(sourceCell, targetCell);
                CopyCellStyle(sourceCell, targetCell);
            }
        }

        /// <summary>
        /// 复制单元格值（支持常用数据类型，处理 null 值）
        /// </summary>
        private static void CopyCellValue(ICell sourceCell, ICell targetCell)
        {
            try
            {
                switch (sourceCell.CellType)
                {
                    case CellType.String:
                        targetCell.SetCellValue(sourceCell.StringCellValue ?? string.Empty);
                        break;

                    case CellType.Numeric:
                        if (DateUtil.IsCellDateFormatted(sourceCell))
                        {
                            // 处理可空日期类型
                            DateTime? dateValue = sourceCell.DateCellValue;
                            if (dateValue.HasValue && dateValue.Value != DateTime.MinValue)
                            {
                                targetCell.SetCellValue(dateValue.Value);
                            }
                            else
                            {
                                targetCell.SetBlank();
                            }
                        }
                        else
                        {
                            targetCell.SetCellValue(sourceCell.NumericCellValue);
                        }
                        break;

                    case CellType.Boolean:
                        targetCell.SetCellValue(sourceCell.BooleanCellValue);
                        break;

                    case CellType.Formula:
                        // 复制公式（注意：公式的结果不会复制，只复制公式本身）
                        targetCell.SetCellFormula(sourceCell.CellFormula);
                        break;

                    case CellType.Blank:
                        targetCell.SetBlank();
                        break;

                    case CellType.Error:
                        targetCell.SetCellErrorValue(sourceCell.ErrorCellValue);
                        break;

                    default:
                        // 未知类型，设置为空白
                        targetCell.SetBlank();
                        break;
                }
            }
            catch
            {
                // 如果复制失败，设置为空白，避免整个复制流程中断
                targetCell.SetBlank();
            }
        }

        private static void CopyCellStyle(ICell sourceCell, ICell targetCell)
        {
            targetCell.CellStyle = sourceCell.CellStyle;
        }

        #endregion

        #region 合并区域复制

        /// <summary>
        /// 复制合并区域
        /// </summary>
        private static void CopyMergedRegions(
            ISheet sourceSheet,
            ISheet targetSheet,
            int startSourceRow,
            int endSourceRow,
            int rowOffset)
        {
            int mergedRegionCount = sourceSheet.NumMergedRegions;

            for (int regionIndex = 0; regionIndex < mergedRegionCount; regionIndex++)
            {
                CellRangeAddress region = sourceSheet.GetMergedRegion(regionIndex);

                // 判断合并区域是否在复制范围内
                if (!IsRegionInRange(region, startSourceRow, endSourceRow))
                {
                    continue;
                }

                CellRangeAddress newRegion = new(
                    region.FirstRow + rowOffset,
                    region.LastRow + rowOffset,
                    region.FirstColumn,
                    region.LastColumn
                );

                targetSheet.AddMergedRegion(newRegion);
            }
        }

        /// <summary>
        /// 检查合并区域是否在指定行范围内（完全包含）
        /// </summary>
        private static bool IsRegionInRange(CellRangeAddress region, int startRow, int endRow)
        {
            // 完全在范围之上
            if (region.LastRow < startRow)
            {
                return false;
            }

            // 完全在范围之下
            if (region.FirstRow > endRow)
            {
                return false;
            }

            // 只处理完全在范围内的，部分在范围内的跳过
            return region.FirstRow >= startRow && region.LastRow <= endRow;
        }

        #endregion

        #region 参数验证

        /// <summary>
        /// 验证输入参数
        /// </summary>
        private static void ValidateParameters(ISheet sourceSheet, ISheet targetSheet, int startSourceRow, int endSourceRow, int startTargetRow)
        {
            ArgumentNullException.ThrowIfNull(sourceSheet);
            ArgumentNullException.ThrowIfNull(targetSheet);

            if (startSourceRow < 0)
                throw new ArgumentException("起始行索引不能为负数", nameof(startSourceRow));

            if (endSourceRow < startSourceRow)
                throw new ArgumentException("结束行索引不能小于起始行索引", nameof(endSourceRow));

            if (startTargetRow < 0)
                throw new ArgumentException("目标起始行索引不能为负数", nameof(startTargetRow));

            // 检查源行是否存在
            var firstSourceRow = sourceSheet.GetRow(startSourceRow);
            ArgumentNullException.ThrowIfNull(firstSourceRow, nameof(startSourceRow));
        }

        #endregion
    }
}

• 示例实体

namespace ExcelReportDemo.Models
{
    public class ReportEntity
    {
        public int ProductTypeOrder { get; set; }

        public string YearMonth { get; set; } = string.Empty;

        public string ProductTypeNo { get; set; } = string.Empty;

        public string ProductTypeName { get; set; } = string.Empty;

        public string CustTypeNo { get; set; } = string.Empty;

        public string CustTypeName { get; set; } = string.Empty;

        public int CustTypeOrder { get; set; }

        // ========== 总量 ==========
        public int TotalQty { get; set; }
        public int StandardQty { get; set; }
        public decimal StandardRate { get; set; }
        public int CustomizedQty { get; set; }
        public decimal CustomizedRate { get; set; }
        // ========== 线上汇总 ==========
        public int OnlineQty { get; set; }
        public decimal OnlineRate { get; set; }
        // ========== 线上-淘宝 ==========
        public int TBStandardQty { get; set; }
        public decimal TBStandardRate { get; set; }
        public int TBCustomizedQty { get; set; }
        public decimal TBCustomizedRate { get; set; }

        // ========== 线上-拼多多 ==========
        public int PDDStandardQty { get; set; }
        public decimal PDDStandardRate { get; set; }
        public int PDDCustomizedQty { get; set; }
        public decimal PDDCustomizedRate { get; set; }
        // ========== 线下汇总 ==========
        public int OfflineQty { get; set; }
        public decimal OfflineRate { get; set; }
        // ========== 线下-门店 ==========
        public int StoreStandardQty { get; set; }
        public decimal StoreStandardRate { get; set; }
        public int StoreCustomizedQty { get; set; }
        public decimal StoreCustomizedRate { get; set; }

        // ========== 线下-工厂 ==========
        public int FactoryStandardQty { get; set; }
        public decimal FactoryStandardRate { get; set; }
        public int FactoryCustomizedQty { get; set; }
        public decimal FactoryCustomizedRate { get; set; }
    }

    public record ProductTypeGroupKey(string ProductTypeNo, string ProductTypeName, int ProductTypeOrder);

    public record CustomerTypeGroupKey(string CustTypeNo, string CustTypeName, int CustTypeOrder);

    /// <summary>
    /// 产品类型分组
    /// </summary>
    public class ProductTypeGroup
    {
        public string ProductTypeNo { get; set; } = string.Empty;
        public string ProductTypeName { get; set; } = string.Empty;
        public int ProductTypeOrder { get; set; }
        public List<ReportEntity> Records { get; set; } = [];
    }

    public class CustomerTypeGroup
    {
        public string CustTypeNo { get; set; } = string.Empty;
        public string CustTypeName { get; set; } = string.Empty;
        public int CustTypeOrder { get; set; }
        public List<ReportEntity> Records { get; set; } = [];
    }
}

• 属性映射器

using ExcelReportDemo.Models;
using System.Linq.Expressions;
using System.Reflection;
namespace ExcelReportDemo.Excel
{
    /// <summary>
    /// Excel 列映射配置
    /// </summary>
    public static class ReportColumnMapping
    {
        /// <summary>
        /// 列号 → (字段名, 表头名称)
        /// </summary>
    public static readonly SortedDictionary<int, (string Field, string Header)> Columns =
        new()
        {
            [0] = (nameof(ReportEntity.YearMonth), "统计年月"),
            [1] = (nameof(ReportEntity.TotalQty), "订单总数"),
            [2] = (nameof(ReportEntity.StandardQty), "标准订单数"),
            [3] = (nameof(ReportEntity.StandardRate), "标准订单占比(%)"),
            [4] = (nameof(ReportEntity.CustomizedQty), "定制订单数"),
            [5] = (nameof(ReportEntity.CustomizedRate), "定制订单占比(%)"),
            [6] = (nameof(ReportEntity.OnlineQty), "线上订单数"),
            [7] = (nameof(ReportEntity.OnlineRate), "线上订单占比(%)"),
            [8] = (nameof(ReportEntity.TBStandardQty), "淘宝-标准订单数"),
            [9] = (nameof(ReportEntity.TBStandardRate), "淘宝-标准订单占比(%)"),
            [10] = (nameof(ReportEntity.TBCustomizedQty), "淘宝-定制订单数"),
            [11] = (nameof(ReportEntity.TBCustomizedRate), "淘宝-定制订单占比(%)"),
            [12] = (nameof(ReportEntity.PDDStandardQty), "拼多多-标准订单数"),
            [13] = (nameof(ReportEntity.PDDStandardRate), "拼多多-标准订单占比(%)"),
            [14] = (nameof(ReportEntity.PDDCustomizedQty), "拼多多-定制订单数"),
            [15] = (nameof(ReportEntity.PDDCustomizedRate), "拼多多-定制订单占比(%)"),
            [16] = (nameof(ReportEntity.OfflineQty), "线下订单数"),
            [17] = (nameof(ReportEntity.OfflineRate), "线下订单占比(%)"),
            [18] = (nameof(ReportEntity.StoreStandardQty), "门店-标准订单数"),
            [19] = (nameof(ReportEntity.StoreStandardRate), "门店-标准订单占比(%)"),
            [20] = (nameof(ReportEntity.StoreCustomizedQty), "门店-定制订单数"),
            [21] = (nameof(ReportEntity.StoreCustomizedRate), "门店-定制订单占比(%)"),
            [22] = (nameof(ReportEntity.FactoryStandardQty), "工厂-标准订单数"),
            [23] = (nameof(ReportEntity.FactoryStandardRate), "工厂-标准订单占比(%)"),
            [24] = (nameof(ReportEntity.FactoryCustomizedQty), "工厂-定制订单数"),
            [25] = (nameof(ReportEntity.FactoryCustomizedRate), "工厂-定制订单占比(%)"),
        };


        /// <summary>
        /// 字段名 → 编译后的属性访问器缓存
        /// </summary>
        private static readonly Dictionary<string, Func<ReportEntity, object?>> _accessorCache;

        static ReportColumnMapping()
        {
            _accessorCache = typeof(ReportEntity)
                .GetProperties()
                .ToDictionary(p => p.Name, CompileGetter);
        }

        /// <summary>
        /// 根据字段名获取编译后的访问器
        /// </summary>
        public static Func<ReportEntity, object?>? GetAccessor(string fieldName)
        {
            _accessorCache.TryGetValue(fieldName, out var accessor);
            return accessor;
        }

        private static Func<ReportEntity, object?> CompileGetter(PropertyInfo property)
        {
            var param = Expression.Parameter(typeof(ReportEntity), "e");
            var cast = Expression.Convert(Expression.Property(param, property), typeof(object));
            return Expression.Lambda<Func<ReportEntity, object?>>(cast, param).Compile();
        }
    }
}

• 主流程

using ExcelReportDemo.Excel;
using ExcelReportDemo.Models;
using NPOI.SS.UserModel;

try
{
    // 1. 定义路径
    var projectRoot = Path.GetFullPath(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, @"..\..\..\"));
    var sourceTemplate = Path.Combine(projectRoot, "Template", "OrderRatioATemplate.xlsx");
    var targetTemplate = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Template", "OrderRatioATemplate.xlsx");
    var exportDir = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Reports");

    // 2. 准备模板和输出目录
    var targetTemplateDir = Path.GetDirectoryName(targetTemplate);
    if (string.IsNullOrWhiteSpace(targetTemplateDir)) return;

    Directory.CreateDirectory(targetTemplateDir);
    if (!File.Exists(targetTemplate) || File.GetLastWriteTime(sourceTemplate) > File.GetLastWriteTime(targetTemplate))
    {
        File.Copy(sourceTemplate, targetTemplate, true);
        Console.WriteLine($"模板已复制到: {targetTemplate}");
    }
    Directory.CreateDirectory(exportDir);

    // 3. 模拟取数
    List<ReportEntity> data =
    [
        new() { ProductTypeNo = "P001", ProductTypeName = "电子产品", ProductTypeOrder = 1,
                CustTypeNo = "C001", CustTypeName = "企业客户", CustTypeOrder = 1,
                YearMonth = "2024-01", TotalQty = 100 },
        new() { ProductTypeNo = "P001", ProductTypeName = "电子产品", ProductTypeOrder = 1,
                CustTypeNo = "C001", CustTypeName = "企业客户", CustTypeOrder = 1,
                YearMonth = "2024-02", TotalQty = 120 },
        new() { ProductTypeNo = "P001", ProductTypeName = "电子产品", ProductTypeOrder = 1,
                CustTypeNo = "C002", CustTypeName = "个人客户", CustTypeOrder = 2,
                YearMonth = "2024-01", TotalQty = 50 },
        new() { ProductTypeNo = "P002", ProductTypeName = "家居用品", ProductTypeOrder = 2,
                CustTypeNo = "C001", CustTypeName = "企业客户", CustTypeOrder = 1,
                YearMonth = "2024-01", TotalQty = 80 },
    ];

    // 4. 按产品类型分组
    List<ProductTypeGroup> productTypeGroups = [.. data
        .GroupBy(r => new ProductTypeGroupKey(r.ProductTypeNo, r.ProductTypeName, r.ProductTypeOrder))
        .Select(g => new ProductTypeGroup
        {
            ProductTypeNo = g.Key.ProductTypeNo,
            ProductTypeName = g.Key.ProductTypeName,
            ProductTypeOrder = g.Key.ProductTypeOrder,
            Records = [.. g]
        })
        .OrderBy(g => g.ProductTypeOrder)];

    // 5. 常量定义
    const int templateSheetIndex = 0;
    const string titleSuffix = "标准/定制订单比例(根据订单数量统计)";
    const int blankRowOffset = 2;
    const int headerRowCount = 5;
    const int dataStartRow = headerRowCount;

    // 6. 加载模板
    using var workbook = NPOIHelper.LoadWorkbook(targetTemplate);

    var exportUser = "测试";
    var exportDate = DateTime.Now.ToString("yyyy/MM/dd");

    // 7. 遍历产品类型分组
    foreach (var productGroup in productTypeGroups)
    {
        // 7.1 克隆 Sheet 并命名
        ISheet sheet = workbook.CloneSheet(templateSheetIndex);
        workbook.SetSheetName(workbook.GetSheetIndex(sheet), productGroup.ProductTypeName);

        // 7.2 按客户类型分组
        List<CustomerTypeGroup> customerTypeGroups = [.. productGroup.Records
            .GroupBy(r => new CustomerTypeGroupKey(r.CustTypeNo, r.CustTypeName, r.CustTypeOrder))
            .Select(g => new CustomerTypeGroup
            {
                CustTypeNo = g.Key.CustTypeNo,
                CustTypeName = g.Key.CustTypeName,
                CustTypeOrder = g.Key.CustTypeOrder,
                Records = [.. g.OrderBy(r => r.YearMonth)]
            })
            .OrderBy(g => g.CustTypeOrder)];

        // 7.3 计算所需行数
        int totalDataRows = customerTypeGroups.Sum(g => g.Records.Count);
        int totalGroups = customerTypeGroups.Count;
        int totalHeaderRows = totalGroups * headerRowCount;
        int totalBlankRows = (totalGroups - 1) * blankRowOffset;
        int totalRowsNeeded = totalHeaderRows + totalDataRows + totalBlankRows;

        // 7.4 设置导出信息
        var exportUserCell = NPOIHelper.GetOrCreateCell(sheet, 6, 1);
        var exportDateCell = NPOIHelper.GetOrCreateCell(sheet, 7, 1);
        exportUserCell.SetCellValue(exportUser);
        exportDateCell.SetCellValue(exportDate);

        // 7.5 清理原有数据并整体下移行（偏移 = 总所需行数 - 模板已有的表头行数）
        int rowsToShift = totalRowsNeeded - headerRowCount;
        sheet.ShiftRows(dataStartRow, sheet.LastRowNum, rowsToShift, true, false);

        // 7.6 填充各客户类型数据
        int currentRowIndex = 0;
        bool isFirstGroup = true;

        foreach (var customerGroup in customerTypeGroups)
        {
            int recordCount = customerGroup.Records.Count;

            // 非第一个分组时复制标题行
            if (!isFirstGroup)
            {
                ExcelRowCopier.CopyRowRange(sheet, sheet, 0, headerRowCount - 1, currentRowIndex);
            }

            // 设置标题
            var title = $"{customerGroup.CustTypeName}{productGroup.ProductTypeName}{titleSuffix}";
            var titleCell = NPOIHelper.GetOrCreateCell(sheet, currentRowIndex, 0);
            titleCell.SetCellValue(title);

            // 填充数据行
            int dataStartRowIndex = currentRowIndex + headerRowCount;
            for (int recordIndex = 0; recordIndex < recordCount; recordIndex++)
            {
                var dataRecord = customerGroup.Records[recordIndex];
                int targetRowIndex = dataStartRowIndex + recordIndex;
                FillDataRow(targetRowIndex, sheet, dataRecord, recordIndex);
            }

            // 更新下一个标题行的起始位置
            currentRowIndex = currentRowIndex + headerRowCount + recordCount + blankRowOffset;
            isFirstGroup = false;
        }
    }

    // 8. 移除模板 Sheet 并保存
    workbook.RemoveSheetAt(0);
    var exportFilePath = Path.Combine(exportDir, $"Report_{DateTime.Now:yyyyMMdd_HHmmss}.xlsx");
    NPOIHelper.SaveWorkbook(workbook, exportFilePath);
    Console.WriteLine($"报表已导出至: {exportFilePath}");
}
catch (Exception ex)
{
    Console.WriteLine($"导出失败: {ex.Message}");
}

// 数据行填充方法
static void FillDataRow(int rowIndex, ISheet sheet, ReportEntity data, int recordIndex)
{
    var row = NPOIHelper.GetOrCreateRow(sheet, rowIndex);

    foreach (var kvp in ReportColumnMapping.Columns)
    {
        int colIndex = kvp.Key;
        var (field, _) = kvp.Value;
        var value = ReportColumnMapping.GetAccessor(field)!(data);
        NPOIHelper.SetCellValue(NPOIHelper.GetOrCreateCell(row, colIndex), value);
    }
}