C#操作Excel的多种方法对比与应用场景

向北走

发布时间：2026-10-16 | 7人浏览过

原创

在C#开发中，处理Excel文件通常涉及读取、写入及格式生成等需求。常见的解决方案包括基于OLEDB的快速读取、利用SqlBulkCopy的大数据入库，以及通过Excel COM组件或第三方库（如NPOI）进行精细化的表格渲染。本文将分析不同方法的适用场景、操作步骤及潜在的性能陷阱。

数据导入：OLEDB与SqlBulkCopy的高效路径

当需要将Excel数据导入数据库，或仅做简单的网格展示时，OLEDB是轻量级的选择；而面对百万级数据入库，SqlBulkCopy则是性能首选。

第1步：使用OLEDB读取Excel并绑定展示

目的：无需安装Excel即可快速将工作表内容加载至DataSet，适用于简单数据的读取与页面控件绑定。

入口：System.Data.OleDb命名空间。

动作：构建连接字符串，指定Provider为Microsoft.Jet.OLEDB.4.0（针对.xls）或Microsoft.ACE.OLEDB.12.0（针对.xlsx），执行Select查询。

public static string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\08.xls;Extended Properties=Excel 8.0";
public static DataSet ds;
protected void Page_Load(object sender, EventArgs e)
{
    OleDbConnection conn = new OleDbConnection(strCon);
    string sql = "SELECT * FROM [Sheet1$]";
    conn.Open();
    OleDbDataAdapter myCommand = new OleDbDataAdapter(sql, strCon);
    ds = new DataSet();
    myCommand.Fill(ds, "[Sheet1$"]);
    conn.Close();
    // 假设 datagrid1 为前端DataGrid控件
    datagrid1.DataMember = "[Sheet1$]";
    datagrid1.DataSource = ds;
    datagrid1.DataBind();
}

第2步：利用SqlBulkCopy实现海量数据批量入库

目的：克服传统循环插入的性能瓶颈，将DataTable或DataReader中的数据极速写入SQL Server数据库。

重要参数：DestinationTableName（目标表名）、ColumnMappings（列映射）。需注意混合类型列（如数字与文本混合）可能导致NULL值问题，建议预处理。

// 检查数据源有效性
if (dataTable != null && dataTable.Rows.Count != 0)
{
    sqlBulkCopy.WriteToServer(dataTable);
}

第3步：结合OLEDB读取与SqlBulkCopy导出的完整流程

目的：解决Excel中日期/数字混合列导致的类型丢失问题，先转为DataTable清洗后再入库。

关键配置：连接字符串中加入 HDR=YES（第一行为标题）和 IMEX=1（混合模式，强制作为文本读取以避免类型截断）。

string ExcelFile = @"C:\_Hub_Report.xls";
// IMEX=1 确保混合数据类型被当作文本读取，减少NULL值bug
string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelFile + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";

using (OleDbConnection excelConnection = new OleDbConnection(excelConnectionString))
{
    excelConnection.Open();
    OleDbCommand command = new OleDbCommand(
        "SELECT [Region],[CustomerPN],[RMA],[Date],[QTY] FROM [_Hub_Report$]", excelConnection);

    using (OleDbDataReader dr = command.ExecuteReader())
    {
        string sqlConnectionString = @"Data Source=SERVER\SQLEXPRESS;Initial Catalog=DBName;Integrated Security=True";
        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
        {
            bulkCopy.DestinationTableName = "TargetTable";
            // 必须严格映射源列名与目标列名
            bulkCopy.ColumnMappings.Add("Region", "region");
            bulkCopy.ColumnMappings.Add("CustomerPN", "customer_item_number");
            bulkCopy.ColumnMappings.Add("Date", "date");
            bulkCopy.BatchSize = 1000; // 设置批次大小提升性能
            bulkCopy.WriteToServer(dr);
        }
    }
}

复杂报表生成：Excel COM组件的高级用法

当需求涉及复杂的单元格合并、公式计算、字体样式调整及宏操作时，直接操作Excel进程（COM Interop）是最灵活的方式。此方法依赖服务器已安装Microsoft Office Excel，且需注意线程资源释放。

第1步：初始化Excel应用与工作簿

目的：创建Excel实例，打开或新建工作簿，准备数据填充环境。

Excel.Application m_objExcel = new Excel.Application();
m_objExcel.Visible = false; // 后台运行不显示界面
Excel.Workbooks m_objBooks = m_objExcel.Workbooks;
// Open 打开已有文件或 Add 新建
Excel.Workbook m_objBook = m_objBooks.Open(sourpath, Type.Missing, Type.Missing); 
Excel.Worksheet m_objSheet = (Excel.Worksheet)m_objBook.Worksheets.get_Item(1);

第2步：区域设置、合并与样式定制

目的：通过Range对象精确控制单元格的格式、颜色及边框，满足报表美观需求。

获取区域：m_objSheet.get_Range("A1", "E1")
合并单元格：range.Merge(0);
文本格式：range.NumberFormatLocal = "@";（设置为文本以保留前导零）
字体样式：设置 Font.Name, Font.Size, Font.Color
对齐与背景：HorizontalAlignment = xlCenter, Interior.ColorIndex
自动适应：EntireColumn.AutoFit();

第3步：插入公式、宏运算与数据填充

目的：实现动态求和、跨表引用及自动填充功能，模拟人工Excel操作逻辑。

// 赋值与插入求和公式
m_objSheet.Cells[row, col] = value;
Excel.Range range = m_objSheet.get_Range("B25", Type.Missing);
range.Formula = "=SUM(B2:B24)"; // 支持公式语法
range.Calculate(); // 立即重算

// 跨Sheet引用
range.Formula = "=Sheet3!B8+Sheet3!B12";

// 自动填充序列（如递增数列）
Excel.Range rangeStart = m_objSheet.get_Range("B25");
Excel.Range rangeEnd = m_objSheet.get_Range("E25");
rangeStart.AutoFill(rangeEnd, Excel.XlAutoFillType.xlFillDefault);

// 删除行
range.EntireRow.Delete(Excel.XlDirection.xlUp); // 向上删除

第4步：QueryTable反向导入数据库数据

目的：利用Excel内置的查询表功能，直接从SQL Server拉取数据并刷新，适合需要用户手动刷新报表的场景。

string connString = @"Provider=SQLOLEDB.1;Data Source=Server;uid=user;Password=pass;";
string sql = "SELECT [day01],[day02] FROM [dbo].[Report_Base]";

Excel.QueryTable qTable = m_objSheet.QueryTables.Add(
    "OLEDB;" + connString,
    m_objSheet.get_Range("A2"),
    sql
);
qTable.RefreshStyle = Excel.XlCellInsertionMode.xlInsertEntireRows;
qTable.Refresh(false);
m_objBook.SaveAs(destPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive);
m_objBook.Close(false);
m_objExcel.Quit();
GC.Collect(); // 强制回收COM对象内存

注意：在使用COM组件结束后，必须调用 Quit() 并显式释放ComObject，否则Excel进程可能驻留在内存中导致服务器内存泄漏。