[ASP.net WebForm] 把Excel資料匯入資料庫的懶人Code分享 - SQL Server篇

[ASP.net WebForm] 把Excel資料匯入資料庫的懶人Code分享 - SQL Server篇

 

根據使用OpenRowSet操作Excel - breezee - 博客园,提供從SQL Server端操作Excel的做法

 

要先做設定

SQL Server端的電腦

1. 灌Office Driver(32bit或64bit要灌對)或灌Microsoft Office Excel軟體

並確認連結的伺服器>提供者有

Microsoft.ACE.OLEDB.12.0(或Microsoft.Jet.OLEDB.4.0)

 

2. 還要做以下設定,否則會出現錯誤

訊息 15281,層級 16,狀態 1,行 1
SQL Server 已封鎖元件 'Ad Hoc Distributed Queries' 的 STATEMENT 'OpenRowset/OpenDatasource' 之存取,因為此元件已經由此伺服器的安全性組態關閉。系統管理員可以使用 sp_configure 來啟用 'Ad Hoc Distributed Queries' 的使用。如需有關啟用 'Ad Hoc Distributed Queries' 的詳細資訊,請參閱《SQL Server 線上叢書》中的<介面區組態>(Surface Area Configuration)。

 

伺服器>Facet

 

介面區組態>AdHocRemoteQueriesEnabled>True

 

如果是Windows Server 2008 R2(64位元)上的SQL Server 2008 (64位元),會發現無論怎麼灌Office Excel,連結伺服器的提供者都不會出現Excel相關Driver

那是因為目前的Office軟體大都是32位元,SQL Server 2008 (64位元)不支援的關係

解決方法:

Microsoft下載中心 下載Microsoft Access Database Engine 2010 可轉散發套件(挑最新版就好)

因為環境都是64位元,所以要下載AccessDatabaseEngine_X64.exe,這個檔案

然後安裝前,如果有先灌好Office 32位元軟體的話,程式會要求你先解除安裝,就先解除Office軟體後,再安裝AccessDatabaseEngine_X64.exe這個檔案

連結伺服器的提供者就會出現一個Microsoft.ACE.OLEDB.12.0可以撈Excel資料(而且SQL Server上不用灌Office Excel軟體)

MSDN的討論:如何新增連結伺服器的提供者

 

接著回到ASP.net,對檔案上傳儲存所在的資料夾(如:upload)做共用設定外

如果OS都是Win2008以上的話,還要確認DB Server和AP Server都擁有共同的Windows帳密,還有SQL Server Service也得是該共同的Windows帳密

參考MSDN論壇:Win2008 R2+SQL2008 R2 使用sa帳戶下OPENROWSET撈不到另一台機器上的資料

 

環境設定完畢後,ASP.net就可以下SQL語法,DB去WebServer撈Excel讀取了


Insert into [UserTable]
Select *  from --撈出WorkBook裡Sheet1的資料集
OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=\\192.168.1.110\upload\Book1.xls', [Sheet1$])   
/*此unc路徑為WebServer路徑*/

其他更詳細的語法:[sql server] 整理 openrowset 与 opendatasource 函数连接 Excel 的用法及问题 - xys_777的专栏 - CSDN博客

 

2011/06/14 追記:

使用T-SQL OPENROWSET方式,可能客戶Excel檔的Sheet名稱不會那麼剛好為Sheet1,可以參考此篇[ASP.net] 取得Excel檔的Sheet名稱

把 Sheet1$ 字串換成 變數$ 即可,但請注意數字開頭的Sheet名稱用以上寫法[Sheet1$]會造成T-SQL錯誤:

訊息 7314,層級 16,狀態 1,行 1
連結伺服器 "(null)" 的 OLE DB 提供者 "Microsoft.ACE.OLEDB.12.0" 並未包含資料表 "數字開頭名稱$"。該資料表不存在,或是目前的使用者沒有使用該資料表的權限。

所以上面的T-SQL語法建議改成以下最穩:


Insert into [UserTable]
Select a.*  from --撈出WorkBook裡Sheet1的資料集
OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=\\192.168.1.110\upload\Book1.xls', 'Select * from [Sheet1$]')   a --[Sheet1$]仍然要有[]括號
--另外資料集最好給別名
/*unc路徑仍是由DB到WebServer的路徑*/
 
2011.6.17 追記:

主管要求,重覆的資料要蓋掉(or略過)

雖然我Table的PK都是開Identity(int,1,1),資料列不會重覆,但以使用者角度來看,除了PK欄位以外資料都重覆了就算重覆

所以承接上面,如果要用OPENROWSET語法操作的話,就要搭配SQL 2008的新語法MERGE

為了讓程式碼看起來簡潔,所以OPENROWSET撈出來的資料集,我把它包成一個CTE(SQL 2005新語法)物件


;with ExcelData
As
(
	Select Distinct a.*  from 
	OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=\\192.168.1.110\分享資料夾\Book1.xls', 'Select * from [Sheet1$]') a 
	Where [ColumnName1] IS NOT NULL
)

MERGE 合併的目標Table AS [Target]
USING ExcelData 
on     [Target].[ColumnName1]=ExcelData.[ColumnName1]
   And [Target].[ColumnName2]=ExcelData.[ColumnName2]
   And [Target].[ColumnName3]=ExcelData.[ColumnName3]
When Not Matched Then /*沒有在目標Table裡的資料才做Insert*/
 Insert ([ColumnName1],[ColumnName2],[ColumnName3])
  Values (ExcelData.[ColumnName1],ExcelData.[ColumnName2],ExcelData.[ColumnName3])
;/*MERGE 陳述式必須以分號 (;) 結束*/  
 
2011.6.26 補上MERGE有相同資料時,做Update Target表的動作

;with ExcelData
As
(
	Select Distinct a.*  from 
	OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=\\192.168.1.110\分享資料夾\Book1.xls', 'Select * from [Sheet1$]') a 
	Where [ColumnName1] IS NOT NULL
)

MERGE 合併的目標Table AS [Target]
USING ExcelData 
on     [Target].[ColumnName1]=ExcelData.[ColumnName1]
   And [Target].[ColumnName2]=ExcelData.[ColumnName2]
   
When Matched Then /*Target表有Source表的資料時,把Target表資料Update為Source的資料*/
 Update Set
         Target.ColumnName1 = Source.ColumnName1,
         Target.ColumnName2 = Source.ColumnName2,
         Target.ColumnName3 = Source.ColumnName3
When Not Matched Then /*Target表沒有Source的資料時,做Insert資料到Target*/
 Insert ([ColumnName1],[ColumnName2],[ColumnName3])
  Values (ExcelData.[ColumnName1],ExcelData.[ColumnName2],ExcelData.[ColumnName3])
;/*MERGE 陳述式必須以分號 (;) 結束*/  

須注意MERGE時,只能異動Target資料

When Matched Then 有三個選擇:忽略(不寫Code)、做Update、Delete Target資料

When Not Matched Then 有兩個選擇:忽略(不寫Code)、做Insert (無法Update和Delete Target資料,因為對應不到Target資料)

參考:MERGE (Transact-SQL) (找merge_not_matched關鍵字)

 

2011.7.7追記

因為When Matched Then 的Insert動作過不了compiler,所以可以利用條件逆轉的Merge小技巧:

比對更新的t-sql語法問題