使用C# EPPlus将嵌套模型数据导出为指定格式Excel报表

实现思路 #

每个ECUPartHistory对象包含多条Carlines数据，导出时需要将主从结构扁平化：每条Carlines对应一行完整数据，主表字段重复填充到对应子表行中，适配Excel的行式存储逻辑。

完整代码示例 #

先确保已安装EPPlus NuGet包：Install-Package EPPlus

using OfficeOpenXml;
using System.IO;
using System.Collections.Generic;
using System.Linq;

public class ExcelExporter
{
    public void ExportECUPartHistory(List<ECUPartHistory> dataList, string outputPath)
    {
        // EPPlus 5+版本需指定许可证上下文，商用场景请切换为Commercial
        ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

        using (var package = new ExcelPackage())
        {
            var worksheet = package.Workbook.Worksheets.Add("ECU部件历史");

            // 写入表头
            var headers = new[]
            {
                "部件历史ID", "ECU名称", "迭代周期", "ECU版本", "ECU变体", "LU部件号",
                "车型ID", "车型名称", "起始年份", "结束年份", "变更年份"
            };

            for (int col = 0; col < headers.Length; col++)
            {
                worksheet.Cells[1, col + 1].Value = headers[col];
                // 表头样式优化（可选）
                worksheet.Cells[1, col + 1].Style.Font.Bold = true;
                worksheet.Cells[1, col + 1].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                worksheet.Cells[1, col + 1].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightGray);
            }

            // 写入数据行
            int currentRow = 2;
            foreach (var ecuPart in dataList)
            {
                // 处理无车型数据的情况
                if (ecuPart.Carlines == null || !ecuPart.Carlines.Any())
                {
                    FillECUMainData(worksheet, currentRow, ecuPart);
                    currentRow++;
                    continue;
                }

                // 遍历每个车型，填充完整行数据
                foreach (var carline in ecuPart.Carlines)
                {
                    FillECUMainData(worksheet, currentRow, ecuPart);
                    FillCarlineData(worksheet, currentRow, carline);
                    currentRow++;
                }
            }

            // 自动调整列宽
            worksheet.Cells.AutoFitColumns();

            // 保存Excel文件
            package.SaveAs(new FileInfo(outputPath));
        }
    }

    // 提取主表数据填充逻辑，避免重复代码
    private void FillECUMainData(ExcelWorksheet worksheet, int row, ECUPartHistory ecuPart)
    {
        worksheet.Cells[row, 1].Value = ecuPart.PartHistID;
        worksheet.Cells[row, 2].Value = ecuPart.ECUName;
        worksheet.Cells[row, 3].Value = ecuPart.Sprint;
        worksheet.Cells[row, 4].Value = ecuPart.ECURelease;
        worksheet.Cells[row, 5].Value = ecuPart.ECUVariant;
        worksheet.Cells[row, 6].Value = ecuPart.LUPartNumber;
    }

    // 提取子表数据填充逻辑
    private void FillCarlineData(ExcelWorksheet worksheet, int row, Carlines carline)
    {
        worksheet.Cells[row, 7].Value = carline.CarlineID;
        worksheet.Cells[row, 8].Value = carline.CarLineName;
        worksheet.Cells[row, 9].Value = carline.From;
        worksheet.Cells[row, 10].Value = carline.To;
        worksheet.Cells[row, 11].Value = carline.ChangeYear;
    }
}

// 调用示例
// var exporter = new ExcelExporter();
// var ecuData = GetECUPartHistoryData(); // 替换为你的数据获取逻辑
// exporter.ExportECUPartHistory(ecuData, @"D:\ECU部件历史.xlsx");

关键说明 #

• 扁平化处理：将嵌套的主从结构转换为Excel支持的二维表格，确保数据展示清晰
• 许可证设置：EPPlus 5及以上版本必须指定许可证上下文，商用场景需购买官方授权
• 空值兼容：Nullable<int>类型会自动导出为空单元格，无需额外处理
• 代码复用：通过提取填充方法，减少重复代码，提升可维护性

内容的提问来源于stack exchange，提问作者Prasanth Aman