C#实现Excel表数据导入Sql Server数据库中的方法_C#

C#实现Excel表数据导入Sql Server数据库中的方法

2022-01-05 13:10aparche C#

这篇文章主要介绍了C#实现Excel表数据导入Sql Server数据库中的方法,结合实例形式详细分析了C#读取Excel表数据及导入Sql Server数据库的具体操作步骤与相关操作技巧,需要的朋友可以参考下

本文实例讲述了C#实现Excel表数据导入Sql Server数据库中的方法。分享给大家供大家参考，具体如下：

Excel表数据导入Sql Server数据库的方法很多，这里只是介绍了其中一种：

1、首先，我们要先在test数据库中新建一个my_test表，该表具有三个字段tid int类型， tname nvarchar类型, tt nvarchar类型
（注意：my_test表中的数据类型必须与Excel中相应字段的类型一致）

2、我们用SELECT * FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0 ', 'Excel 5.0;DatabASE=[Excel表.xsl文件的路径];HDR=YES;IMEX=1 ', Sheet1来读取Excel表中的数据，读出来的数据跟从数据库中的表读出的数据是一样，也包括字段名和数据。当然我们也可以用字段名列表来获取 Excel表中的部门数据。SELECT 字段1, 字段2，字段3 [...] FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0 ', 'Excel 5.0;DatabASE=[Excel表.xsl文件的路径];HDR=YES;IMEX=1 ', Sheet1

注意：HDR=Yes，这代表第一行是标题，不做为数据使用；IMEX ( IMport EXport mode )设置
　　IMEX 有三种模式：
　　0 is Export mode
　　1 is Import mode
　　2 is Linked mode (full update capabilities)
　　我这里特别要说明的就是 IMEX 参数了，因为不同的模式代表著不同的读写行为：
　　当 IMEX=0 时为“汇出模式”，这个模式开启的 Excel 档案只能用来做“写入”用途。
　　当 IMEX=1 时为“汇入模式”，这个模式开启的 Excel 档案只能用来做“读取”用途。
　　当 IMEX=2 时为“连结模式”，这个模式开启的 Excel 档案可同时支援“读取”与“写入”用途。
意义如下:
0 ---输出模式;
1---输入模式;
2----链接模式(完全更新能力)

3、Excel中第一行是定义的是列名，从第2行开始才是数据。通过Sql语句从Excel中读取到的数据也是从第二行开始的，二列名变成了字段名。如果你的第一行有定义列名，那么从Excel中获取的数据的各个字段的名称就是Excel中的列名。如：从test.xls sheet表中获取的数据的字段名分别是编号姓名备注。如果你定义的Excel表的第一行没有定义列名，那么获取后数据的字段名分别是F1、F2、F3...以此类推。如果你只是要获取Excel表中部分列的数据，那么你可以就要用到上面的内容。

4、在VS中新建一个web窗体(test.aspx,注:winform窗体也可以)，往其中添加一个Button控件，一点击该按钮就执行导入。双击该按钮，定义事件处理函数。test.aspx.cs中的代码如下：

				?

									using System;

									using System.Data;

									using System.Configuration;

									using System.Collections;

									using System.Web;

									using System.Web.Security;

									using System.Web.UI;

									using System.Web.UI.WebControls;

									using System.Web.UI.WebControls.WebParts;

									using System.Web.UI.HtmlControls;

									using System.Data.SqlClient;

									public partial class admin_test : System.Web.UI.Page

									{

									  protected void Page_Load(object sender, EventArgs e)

									  {

									  }

									  public SqlConnection con()

									  {

									    return new SqlConnection("server=localhost;uid=test;pwd=test;database=test");

									    //这里的uid=test中的test必须是System Administrtor， 否则会出错

									  }

									  protected void Button1_Click1(object sender, EventArgs e)

									  {

									    SqlConnection mycon = con();

									    string sqlstr = "insert into my_test select 编号, 姓名, 备注 from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=e:\\test.xls',sheet1$)";

									/*这里可以用 * 代替 编号, 姓名, 备注,这些表示excel中的列名  */

									    SqlCommand cmd = new SqlCommand(sqlstr, mycon);

									    mycon.Open();

									    cmd.ExecuteNonQuery();

									    mycon.Close();

									  }

									}

执行上面的代码可能会出现下面的问题：

SQL Server 阻止了对组件 'Ad Hoc Distributed Queries' 的 STATEMENT'OpenRowset/OpenDatasource' 的访问，因为此组件已作为此服务器安全配置的一部分而被关闭。系统管理员可以通过使用 sp_configure 启用 'Ad Hoc Distributed Queries'。有关启用 'Ad Hoc Distributed Queries' 的详细信息，请参阅 SQL Server 联机丛书中的 "外围应用配置器"。

解决办法：

				?

									/*启用Ad Hoc Distributed Queries：*/

									exec sp_configure 'show advanced options',1

									reconfigure

									exec sp_configure 'Ad Hoc Distributed Queries',1

									reconfigure

									/*使用完成后，关闭Ad Hoc Distributed Queries：*/

									exec sp_configure 'Ad Hoc Distributed Queries',0

									reconfigure

									exec sp_configure 'show advanced options',0

									reconfigure