將Excel資料匯入SQL Server資料庫

遇到了一個需求，需要將Excel中的資料匯入SQL Server資料庫中，當然，不是使用企業管理器之類的直接匯入資料的那種，而是要做到程式中。本來我最初的考慮是將Excel中的資料讀到dataset中，然後分別插入到資料庫中。

從Excel中讀取資料並不太難，簡單來說就是下面的幾句代碼而已：

 

Code
 public DataSet ExcelToDataset(string path)
        {
            try
            {
string conStringExcel = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=NO;\"";
string sql = "select * from [Sheet1$]";
                DataSet ds = new DataSet();
                OleDbDataAdapter da = new OleDbDataAdapter(sql, conStringExcel);
                da.Fill(ds);
                return ds;
            }
            catch(Exception ex)
            {
                throw ex;
            }
        }

path為Excel的路徑。

 

像其他的情況，比如多個sheet啊，工作區域啊什麼的，在園內查一下就可以了，並不太難。

不過考慮到這樣有些麻煩，而且基本上Excel的格式和sheet是固定的，所以能不能使用別的方法解決呢？

在園內查了一下，園內的以為大神給出了一個預存程序，使用的是串連伺服器的方式，基本代碼如下：

 

Code
ALTER PROCEDURE [dbo].[pro_xls]
@sheetName varchar(50),--Excel中sheet名
@path varchar(2000)--Excel的路徑
as

/**//*
sp_addlinkedserver--建立一個連結的伺服器，使其允許對分布式的、針對 OLE DB 資料來源的異類查詢進行訪問
@server='USER_LIST',--被訪問的伺服器別名
@srvproduct='USER_LIST',--要添加為連結的伺服器的 OLE DB 資料來源的產品名稱
@datasrc='d:\file\Users.xls'   --要訪問的伺服器
*/
EXEC sp_addlinkedserver @server='server', @srvproduct='Excel', @provider='Microsoft.Jet.OLEDB.4.0', @datasrc=@path, @provstr='Excel 8.0;HDR=no;IMEX=1;'
EXEC sp_addlinkedsrvlogin 'server','false'
/**//*
當 SET XACT_ABORT 為 ON 時，如果執行 Transact-SQL 語句產生執行階段錯誤，則整個事務將終止並復原。 
當 SET XACT_ABORT 為 OFF 時，有時只復原產生錯誤的 Transact-SQL 語句，而事務將繼續進行處理。如果錯誤很嚴重，那麼即使 SET XACT_ABORT 為 OFF，也可能復原整個事務。
編譯錯誤（如語法錯誤）不受 SET XACT_ABORT 的影響。
*/
SET XACT_ABORT ON

BEGIN DISTRIBUTED TRAN

    insert into test (testName,testPwd,Istest) select * from [@sheetName$]
IF @@ERROR > 0
    GOTO ChkRollBack

ChkRollBack:
IF @@ERROR > 0
    ROLLBACK TRAN
ELSE
    COMMIT TRAN

SET XACT_ABORT OFF
EXEC sp_dropserver 'server',null

上面的代碼是拿的園內大神的並自己修改的，不過貌似還是有點問題，主要是對sp_addlinkedserver之流的預存程序不太明白，幾個參數不知道是做什麼的。

 

恩，有點頭疼了。

結果又發現了一種方式，貌似叫做即席查詢的東西，代碼寫起來就簡單了：

 

Code
--如果是匯入資料到現有表，則採用

INSERT INTO 表 SELECT * FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0'

,'Excel 5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$)

--如果是匯入資料並新增表，則採用

SELECT * INTO 表 FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0'

,'Excel 5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$)

--以上語句是將 EXCEL檔案裡 SHEET1工作表中所有的列都讀進來，如果只想導部分列，可以

INSERT INTO 表 (a1,a2,a3) SELECT a1,a2,a3 FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0'

,'Excel 5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$)

 

分散式查詢傳送門

 

我這裡只是起到一個拋磚引玉的作用，希望各位園內大神不吝賜教。