ADO.NET操作Excel文件指南

在软件开发中,开发者经常需要将数据导出到其他应用程序中。这可能是因为客户需要将数据从其他系统迁移过来,或者数据量不大(例如几千条记录),迁移起来相对容易。有时,需要在Excel文件中修改这些数据,例如合并两个数据表,或者从其他来源提取数据并添加到电子表格中。

要使用ADO.NET访问Excel工作簿,可以使用Microsoft Jet数据库引擎通过可安装的Indexed Sequential Access Method (ISAM)驱动程序来实现。通过Jet OLE DB提供程序,可以使用OledbConnection对象来连接Excel文件。

以下是使用Jet OLE DB提供程序连接Excel工作簿的连接字符串语法:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\WorkBook1.xls;Extended Properties="Excel 8.0;HDR=YES;"

如果在连接字符串中指定HDR=NO,Jet OLE DB提供程序将自动为命名字段(F1代表第一个字段,F2代表第二个字段,依此类推)。在连接字符串中,需要在Data Source参数中指定工作簿的完整路径和文件名。Extended Properties参数可能包含两个属性:一个是ISAM版本的属性,另一个是指示表是否包含标题的属性。

与数据库不同,Excel表中没有直接指定列数据类型的方法。相反,OLE DB提供程序会扫描列中的八行来猜测字段的数据类型。可以通过在连接字符串的扩展属性中指定MAXSCANROWS设置的值(1到16之间)来更改扫描行数。

以下是在Excel中引用数据的几种方式:

  • 使用工作表名称后跟$符号,例如:Sheet1$
  • 使用已定义名称的范围,例如:[MyNamedRange]
  • 使用特定地址的范围,例如:Sheet1$A1:B10

注意:$符号意味着表存在,因此在创建新的Excel表时不需要包含$符号。

要在Excel工作簿中创建表,可以使用以下SQL语句:

CREATE TABLE Sheet1 (F1 char(255), F2 char(255))

使用ADO.NET,可以通过以下三种方式之一在工作簿中插入和更新记录:

  • 使用OLEDbCommand并设置其CommandText,然后调用ExecuteNonQuery方法。例如:INSERT INTO [Sheet1$] (F1, F2) values ('111', 'ABC')。
  • UPDATE [Sheet1$] SET F2 = 'F2Value' WHERE F1 = 'F1Value'。
  • 对已从Excel工作簿中填充的DataSet进行更改,然后调用DataAdapter的Update方法将更改从DataSet回写到工作簿。

例如:

OleDbDataAdapter adapter = new OleDbDataAdapter(); adapter.SelectCommand = cmd; adapter.FillSchema(ds, SchemaType.Source); adapter.Fill(dtItems); ds.Tables.Add(dtItems);

请注意,要使用Update方法进行更改解析,必须为DataAdapter的InsertCommand设置参数化命令:

INSERT INTO [Sheet1$] (F1, F2) values (?,?) UPDATE [Sheet1$] SET F2 = ? WHERE F1 =?

这些命令是必需的,因为OleDbDataAdapter不提供Excel工作簿的键/索引信息;没有键/索引字段,CommandBuilder无法为自动生成命令。

可以使用单个插入命令将其他文件中的数据导入Excel,这些文件支持Jet OLE DB提供程序。例如:文本文件、Microsoft Access数据库以及Excel工作簿本身。

INSERT INTO [Sheet1$] IN 'C:\Book1.xls' 'Excel 8.0;' SELECT * FROM MyTable

需要注意的是,虽然Jet OLE DB提供程序允许在Excel工作簿中插入和更新记录,但它不允许DELETE操作。

如果尝试在64位机器上连接Excel,可能会遇到以下错误:

The 'Microsoft.Jet.OLEDB.12.0' provider is not registered on the local machine.

没有64位驱动程序,因此需要将其作为32位进程运行。因此,需要在项目属性中使用x86,并使用Microsoft.ACE.OLEDB.12.0。

OleDbConnection excelConnection = null; OleDbDataAdapter adapter = null; try { excelConnection = new OleDbConnection(); excelConnection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\\DbBackup.xlsx;Extended Properties='Excel 8.0;HDR=YES;'"; excelConnection.Open(); DataTable dtTables = new DataTable(); dtTables = excelConnection.GetSchema(); dtTables = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); String[] excelSheets = null; if ((dtTables != null)) { excelSheets = new String[dtTables.Rows.Count]; int i = 0; foreach (DataRow row in dtTables.Rows) { excelSheets[i] = row["TABLE_NAME"].ToString(); i++; } } DataSet ds = new DataSet(); foreach (string sheet in excelSheets) { OleDbCommand cmd = new OleDbCommand(); cmd.Connection = excelConnection; cmd.CommandText = "Select * from [" + sheet + "]"; DataTable dtItems = new DataTable(); dtItems.TableName = sheet; adapter = new OleDbDataAdapter(); adapter.SelectCommand = cmd; adapter.FillSchema(ds, SchemaType.Source); adapter.Fill(dtItems); ds.Tables.Add(dtItems); } } finally { adapter.Dispose(); excelConnection.Dispose(); }
沪ICP备2024098111号-1
上海秋旦网络科技中心:上海市奉贤区金大公路8218号1幢 联系电话:17898875485