如何用C#结合Microsoft.Office.Interop.Excel生成带直连数据库查询的数据表/透视表？

当然可行！这种方式本质是让Excel建立外部数据连接，直接从数据库拉取数据，后续用户打开Excel还能手动刷新获取最新数据，实用性拉满。下面我给你一步步拆解实现方法：

通过Microsoft.Office.Interop.Excel创建Excel的OLEDB/ODBC数据库连接，定义SQL查询语句，然后基于这个连接生成数据表（ListObject）或者数据透视表——全程由Excel自己去连接数据库取数，不需要C#提前把数据捞出来再插入。

首先确保你已经引用了Microsoft.Office.Interop.Excel NuGet包，然后看下面的代码示例：

示例1：创建带直接数据库连接的数据表 #

using Microsoft.Office.Interop.Excel;
using System;
using System.Runtime.InteropServices;

public class ExcelDbConnector
{
    public void CreateDataTableFromDb()
    {
        Application excelApp = null;
        Workbook workbook = null;
        Worksheet dataSheet = null;
        WorkbookConnection connection = null;
        ListObject dataList = null;

        try
        {
            // 初始化Excel应用
            excelApp = new Application();
            excelApp.Visible = true; // 让Excel可见，方便测试

            // 创建新工作簿
            workbook = excelApp.Workbooks.Add();
            dataSheet = workbook.Worksheets[1];
            dataSheet.Name = "实时数据";

            // 替换成你的数据库连接字符串（以SQL Server为例）
            string connectionString = "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=你的数据库名;Data Source=你的服务器名";
            // 替换成你的查询语句
            string sqlQuery = "SELECT 字段1, 字段2, 字段3 FROM 你的表名";

            // 添加数据库连接
            connection = workbook.Connections.Add2(
                name: "DbDirectConnection",
                description: "直接连接数据库的查询",
                connectionString: connectionString,
                commandText: sqlQuery,
                lCmdtype: XlCmdType.xlCmdSql,
                CreateModel: false,
                ImportRelationships: false
            );

            // 将查询结果导入为数据表
            dataList = dataSheet.ListObjects.AddEx(
                SourceType: XlListObjectSourceType.xlSrcExternal,
                Source: connection,
                Destination: dataSheet.Range["A1"]
            );
            dataList.Refresh(); // 触发数据拉取

            Console.WriteLine("数据表已创建，Excel将直接连接数据库获取数据！");
        }
        catch (Exception ex)
        {
            Console.WriteLine($"操作出错：{ex.Message}");
        }
        finally
        {
            // 务必释放所有COM对象，避免内存泄漏
            if (dataList != null) Marshal.ReleaseComObject(dataList);
            if (connection != null) Marshal.ReleaseComObject(connection);
            if (dataSheet != null) Marshal.ReleaseComObject(dataSheet);
            if (workbook != null)
            {
                // 如果不需要保留工作簿，可关闭；需要的话先保存
                // workbook.SaveAs(@"C:\路径\你的文件.xlsx");
                // workbook.Close();
                Marshal.ReleaseComObject(workbook);
            }
            if (excelApp != null)
            {
                // excelApp.Quit();
                Marshal.ReleaseComObject(excelApp);
            }
        }
    }
}

示例2：创建基于直接数据库连接的数据透视表 #

如果需要生成数据透视表，只需在上面的基础上，基于数据库连接创建透视缓存和透视表：

public void CreatePivotTableFromDb()
{
    Application excelApp = null;
    Workbook workbook = null;
    Worksheet pivotSheet = null;
    Worksheet dataSheet = null;
    WorkbookConnection connection = null;
    PivotCache pivotCache = null;
    PivotTable pivotTable = null;

    try
    {
        excelApp = new Application();
        excelApp.Visible = true;

        workbook = excelApp.Workbooks.Add();
        // 新建工作表存放数据透视表
        pivotSheet = workbook.Worksheets.Add();
        pivotSheet.Name = "销售透视";
        dataSheet = workbook.Worksheets[1];
        dataSheet.Name = "数据源";

        // 数据库连接配置
        string connectionString = "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=SalesDB;Data Source=LOCALHOST";
        string sqlQuery = "SELECT 区域, 产品, 销售额 FROM 销售记录表";

        // 添加连接并导入基础数据源
        connection = workbook.Connections.Add2(
            name: "PivotDbConn",
            description: "数据透视表数据源连接",
            connectionString: connectionString,
            commandText: sqlQuery,
            lCmdtype: XlCmdType.xlCmdSql,
            CreateModel: false,
            ImportRelationships: false
        );
        dataSheet.ListObjects.AddEx(
            SourceType: XlListObjectSourceType.xlSrcExternal,
            Source: connection,
            Destination: dataSheet.Range["A1"]
        ).Refresh();

        // 创建透视缓存
        pivotCache = workbook.PivotCaches().Create(
            SourceType: XlPivotTableSourceType.xlExternal,
            SourceData: connection
        );

        // 生成数据透视表
        pivotTable = pivotCache.CreatePivotTable(
            TableDestination: pivotSheet.Range["A3"],
            TableName: "SalesPivot"
        );

        // 配置透视表字段（示例：行=区域，列=产品，值=销售额求和）
        pivotTable.PivotFields("区域").Orientation = XlPivotFieldOrientation.xlRowField;
        pivotTable.PivotFields("产品").Orientation = XlPivotFieldOrientation.xlColumnField;
        pivotTable.AddDataField(pivotTable.PivotFields("销售额"), "总销售额", XlConsolidationFunction.xlSum);

        Console.WriteLine("数据透视表已创建，可直接刷新获取最新数据！");
    }
    catch (Exception ex)
    {
        Console.WriteLine($"操作出错：{ex.Message}");
    }
    finally
    {
        // 释放所有COM对象，避免内存泄漏
        if (pivotTable != null) Marshal.ReleaseComObject(pivotTable);
        if (pivotCache != null) Marshal.ReleaseComObject(pivotCache);
        if (connection != null) Marshal.ReleaseComObject(connection);
        if (dataSheet != null) Marshal.ReleaseComObject(dataSheet);
        if (pivotSheet != null) Marshal.ReleaseComObject(pivotSheet);
        if (workbook != null)
        {
            // workbook.SaveAs(@"C:\路径\透视表文件.xlsx");
            // workbook.Close();
            Marshal.ReleaseComObject(workbook);
        }
        if (excelApp != null)
        {
            // excelApp.Quit();
            Marshal.ReleaseComObject(excelApp);
        }
    }
}

• 连接字符串适配：上面用的是SQL Server的OLEDB连接，如果是MySQL、Oracle等数据库，需要替换对应的Provider。比如MySQL可以用Provider=MySQL ODBC 8.0 Driver;Server=你的服务器;Database=你的库;User=用户名;Password=密码;。
• 权限问题：运行代码的账户需要有访问目标数据库的权限，因为Excel会以当前账户身份去连接数据库。
• 内存泄漏防护：Interop的COM对象不会被.NET自动回收，必须手动用Marshal.ReleaseComObject()逐个释放，否则会导致Excel进程在后台残留。
• 数据刷新：用户打开生成的Excel后，右键点击数据表/透视表，选择「刷新」就能获取最新的数据库数据，完全不用再跑C#代码。
• 版本兼容：如果是旧版本Office，Add2方法可能不支持，可以改用Connections.Add()方法，参数会略有不同，你可以查对应的文档调整。

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