C#利用OLEDB实现将DataTable写入Excel文件中

下面我将详细讲解“C#利用OLEDB实现将DataTable写入Excel文件中”的完整攻略。

1. OLEDB介绍

OLE DB 是一种标准的连接方式，支持多种数据库和文件格式，可以用于访问关系数据库、普通数据文件、Excel 和文本文件等。OLE DB 的本质是一种支持 COM 的面向 ROWSET 的标准，应用可以通过 OLE DB 访问所有遵守本标准的提供程序。OLE DB 提供了一个很好的统一接口，使得应用可以无需了解不同的数据库或文件格式就能访问并操作它们。

2. 利用OLEDB实现将DataTable写入Excel文件中的步骤

1. 引入命名空间

using System.Data;
using System.Data.OleDb;

1. 定义连接字符串

连接字符串格式如下：

Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=YES;IMEX=1';

其中，Data Source 表示 Excel 文件路径，HDR=YES 表示第一行是标题行，IMEX=1 表示值为文本类型（不自动转换数据类型）。

举个例子：

string path = @"D:\test.xlsx";
string connstr = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=YES;IMEX=1';", path);

1. 创建连接对象并打开连接

OleDbConnection conn = new OleDbConnection(connstr);
conn.Open();

1. 创建命令对象

OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;

1. 创建插入语句

string insertSql = "INSERT INTO [Sheet1$]({0}) VALUES({1})";

1. 循环遍历 DataTable，将数据插入到 Excel 文件中

foreach(DataRow row in dt.Rows)
{
string field = "";
string values = "";
foreach(DataColumn col in dt.Columns)
{
field += "["+col.ColumnName+"]" + ",";
values += "'" + row[col.ColumnName].ToString().Replace("'","''") + "',";
}
field = field.TrimEnd(',');
values = values.TrimEnd(',');
string sql = string.Format(insertSql, field, values);
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}

1. 关闭连接

conn.Close();

3. 示例说明

下面给出两条示例说明：

示例 1：导出一个 DataTable 到 Excel 文件

假设有一个名为 dt 的 DataTable，包含了三列数据：Name、Age 和 Gender，数据如下：

Name	Age	Gender
Tom	20	Male
Jack	25	Male
Lily	18	Female

现在要将这个 DataTable 导出到名为 test.xlsx 的 Excel 文件中，代码如下：

DataTable dt = new DataTable();
// 假设 dt 有三列 Name、Age、Gender，数据如上所示

string path = @"D:\test.xlsx";
string connstr = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=YES;IMEX=1';", path);

OleDbConnection conn = new OleDbConnection(connstr);
conn.Open();

OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;

string insertSql = "INSERT INTO [Sheet1$]({0}) VALUES({1})";

foreach(DataRow row in dt.Rows)
{
    string field = "";
    string values = "";
    foreach(DataColumn col in dt.Columns)
    {
        field += "["+col.ColumnName+"]" + ",";
        values += "'" + row[col.ColumnName].ToString().Replace("'","''") + "',";
    }
    field = field.TrimEnd(',');
    values = values.TrimEnd(',');
    string sql = string.Format(insertSql, field, values);
    cmd.CommandText = sql;
    cmd.ExecuteNonQuery();
}

conn.Close();

示例 2：导出一个 SqlDataReader 到 Excel 文件

假设有一个名为 dr 的 SqlDataReader，包含了三列数据：Name、Age 和 Gender，数据如下：

Name	Age	Gender
Tom	20	Male
Jack	25	Male
Lily	18	Female

现在要将这个 SqlDataReader 导出到名为 test.xlsx 的 Excel 文件中，代码如下：

string path = @"D:\test.xlsx";
string connstr = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=YES;IMEX=1';", path);

OleDbConnection conn = new OleDbConnection(connstr);
conn.Open();

OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;

string insertSql = "INSERT INTO [Sheet1$]({0}) VALUES({1})";

while(dr.Read())
{
    string field = "";
    string values = "";
    for(int i=0; i<dr.FieldCount; i++)
    {
        field += "["+dr.GetName(i)+"]" + ",";
        values += "'" + dr.GetValue(i).ToString().Replace("'","''") + "',";
    }
    field = field.TrimEnd(',');
    values = values.TrimEnd(',');
    string sql = string.Format(insertSql, field, values);
    cmd.CommandText = sql;
    cmd.ExecuteNonQuery();
}

conn.Close();

以上就是利用 OLEDB 实现将 DataTable 写入 Excel 文件的完整攻略了。