Excel表資料匯入Sql Server資料庫中

標籤：style   blog   color   os   io   使用   ar   檔案   資料   

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、在VS2005中建立一個web表單(test.aspx)，往其中添加一個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‘,1reconfigureexec sp_configure ‘Ad Hoc Distributed Queries‘,1reconfigure /*使用完成後，關閉Ad Hoc Distributed Queries：*/exec sp_configure ‘Ad Hoc Distributed Queries‘,0reconfigureexec sp_configure ‘show advanced options‘,0reconfigure

 

Excel表資料匯入Sql Server資料庫中