高級打字員的技術雲

根據使用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路徑*/

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的路徑*/

;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 陳述式必須以分號 (;) 結束*/  

;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 陳述式必須以分號 (;) 結束*/