如何使用Interop将复杂C#模型转换为Excel电子表格

核心思路 #

针对包含嵌套类的SampleModel，需先解析模型层级属性生成对应分组表头，再逐行填充数据，同时通过Interop的单元格合并功能实现示例中的分组表头样式。

步骤1：添加Excel Interop引用 #

在项目中安装Microsoft.Office.Interop.Excel NuGet包，或直接引用COM组件中的Microsoft Excel xx.x Object Library。

步骤2：完整实现代码 #

using Microsoft.Office.Interop.Excel;
using System;
using System.Collections.Generic;
using System.Reflection;

public class ExcelExporter
{
    public void ExportSampleModelsToExcel(List<SampleModel> models, string filePath)
    {
        Application excelApp = new Application();
        Workbook workbook = excelApp.Workbooks.Add();
        Worksheet worksheet = workbook.ActiveSheet;
        excelApp.Visible = false;

        try
        {
            // 生成分组表头（第一行）
            worksheet.Cells[1, 1] = "SampleModel";
            worksheet.Cells[1, 4] = "UserDetails";
            
            // 合并SampleModel对应的单元格（A1-C1）
            Range sampleModelHeader = worksheet.Range["A1:C1"];
            sampleModelHeader.Merge();
            sampleModelHeader.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            
            // 合并UserDetails对应的单元格（D1-F1）
            Range userDetailsHeader = worksheet.Range["D1:F1"];
            userDetailsHeader.Merge();
            userDetailsHeader.HorizontalAlignment = XlHAlign.xlHAlignCenter;

            // 生成子表头（第二行）
            worksheet.Cells[2, 1] = "Id";
            worksheet.Cells[2, 2] = "Name";
            worksheet.Cells[2, 3] = "LastName";
            worksheet.Cells[2, 4] = "Id";
            worksheet.Cells[2, 5] = "Email";
            worksheet.Cells[2, 6] = "Location";

            // 设置表头样式
            Range headerRange = worksheet.Range["A1:F2"];
            headerRange.Font.Bold = true;
            headerRange.Interior.Color = System.Drawing.Color.LightGray;

            // 填充数据行
            int rowIndex = 3;
            foreach (var model in models)
            {
                // 填充SampleModel属性
                worksheet.Cells[rowIndex, 1] = model.Id;
                worksheet.Cells[rowIndex, 2] = model.Name ?? string.Empty;
                worksheet.Cells[rowIndex, 3] = model.LastName ?? string.Empty;

                // 填充UserDetails属性，处理空引用
                if (model.UserDetails != null)
                {
                    worksheet.Cells[rowIndex, 4] = model.UserDetails.Id;
                    worksheet.Cells[rowIndex, 5] = model.UserDetails.Email ?? string.Empty;
                    worksheet.Cells[rowIndex, 6] = model.UserDetails.Location;
                }
                else
                {
                    worksheet.Cells[rowIndex, 4] = string.Empty;
                    worksheet.Cells[rowIndex, 5] = string.Empty;
                    worksheet.Cells[rowIndex, 6] = string.Empty;
                }

                rowIndex++;
            }

            // 自动调整列宽
            worksheet.UsedRange.Columns.AutoFit();

            // 保存文件
            workbook.SaveAs(filePath);
        }
        catch (Exception ex)
        {
            throw new Exception("导出Excel失败: " + ex.Message);
        }
        finally
        {
            // 释放COM资源，避免Excel进程残留
            workbook.Close();
            excelApp.Quit();
            ReleaseComObject(worksheet);
            ReleaseComObject(workbook);
            ReleaseComObject(excelApp);
        }
    }

    private void ReleaseComObject(object obj)
    {
        if (obj != null)
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
            obj = null;
        }
    }
}

// 使用示例
public class Program
{
    static void Main()
    {
        var sampleData = new List<SampleModel>
        {
            new SampleModel
            {
                Id = 1,
                Name = "John",
                LastName = "Doe",
                UserDetails = new UserDetails { Id = 101, Email = "john.doe@example.com", Location = 1 }
            },
            new SampleModel
            {
                Id = 2,
                Name = "Jane",
                LastName = "Smith",
                UserDetails = new UserDetails { Id = 102, Email = "jane.smith@example.com", Location = 2 }
            }
        };

        var exporter = new ExcelExporter();
        exporter.ExportSampleModelsToExcel(sampleData, @"C:\temp\SampleData.xlsx");
    }
}

关键注意事项 #

• COM资源释放：必须手动释放Excel相关COM对象，防止后台残留Excel进程。
• 空值处理：嵌套类UserDetails可能为空，需提前判断避免空引用异常。
• 表头合并：通过Range.Merge()实现分组表头的合并效果，匹配示例样式。
• 列宽适配：使用UsedRange.Columns.AutoFit()让列宽自动适配内容。

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