近期在負(fù)責(zé)一個(gè)小項(xiàng)目，每月定期將一些Excel報(bào)表中的數(shù)據(jù)導(dǎo)入到SQL Server中，方便公司系統(tǒng)與第三方系統(tǒng)的交互。

中間服務(wù)器將我方客戶的服務(wù)器和第三方公司的服務(wù)器銜接起來，我的任務(wù)就是將我方客戶的Excel報(bào)表數(shù)據(jù)導(dǎo)入到中間服務(wù)器上的SQL Server 2000中。

我的處理思路是這樣的，由于中間服務(wù)器的操作系統(tǒng)是Windows Server 2003，故需要?jiǎng)?chuàng)建一個(gè)計(jì)劃任務(wù)，這個(gè)計(jì)劃任務(wù)每月定期執(zhí)行一個(gè)PowerShell腳本，執(zhí)行數(shù)據(jù)導(dǎo)入等相關(guān)操作。

測試代碼下載

本系列所有測試腳本均在Windows Server 2008 R2 DataCenter (PowerShell 2.0) + PowerGUI Script Editor Free Edition x64中測試通過。

注：本次在VMWare Workstation 7.1.3(Windows Server 2003 R2 x86)中測試通過。

轉(zhuǎn)載請注明出處：http://www.rzrgm.cn/brooks-dotnet/archive/2010/12/15/1907199.html

1、準(zhǔn)備工作

為了模擬中間服務(wù)器的環(huán)境，我準(zhǔn)備了一個(gè)VMWare的虛擬機(jī)，操作系統(tǒng)是Windows Server 2003 R2 x86：

2、創(chuàng)建任務(wù)計(jì)劃

Windows Server 2003 中有個(gè)任務(wù)計(jì)劃功能，可以在設(shè)定的時(shí)間段執(zhí)行任務(wù)，非常靈活，可以自定義執(zhí)行的時(shí)間、次數(shù)、間隔等。

可以看到，搜狗拼音已經(jīng)創(chuàng)建了一個(gè)任務(wù)計(jì)劃：

使用GUI創(chuàng)建任務(wù)計(jì)劃是比較簡單的，但是對于項(xiàng)目部署來說就比較麻煩了，最好能自動(dòng)化部署，所以考慮使用腳本來操作。

Windows提供了一個(gè)命令行工具：Schtasks.exe，可以在DOS下對任務(wù)計(jì)劃進(jìn)行創(chuàng)建、修改、刪除等高級(jí)操作。可以在這里查看其詳細(xì)說明。

我創(chuàng)建的任務(wù)計(jì)劃批處理腳本如下：

@echo off

REM 創(chuàng)建任務(wù)計(jì)劃

schtasks /create /tn import /tr "powershell -File \"C:\Excel to SQL Server Demo.ps1\"" /sc monthly /mo 1 /d 15 /st 22:00 /ru ""

echo 創(chuàng)建計(jì)劃任務(wù)成功！

Pause

其中核心的一句命令為：powershell -File "C:\Excel to SQL Server Demo.ps1"，注意由于雙引號(hào)出現(xiàn)了嵌套，故需要用\進(jìn)行轉(zhuǎn)義。

創(chuàng)建的任務(wù)計(jì)劃是：在每月的15號(hào)晚上10點(diǎn)鐘，以System賬戶執(zhí)行腳本C:\Excel to SQL Server Demo.ps1

任務(wù)名為：import。Excel to SQL Server Demo.ps1為另一個(gè)PowerShell腳本，負(fù)責(zé)導(dǎo)入數(shù)據(jù)的。

執(zhí)行該批處理，運(yùn)行結(jié)果如下：

在控制面板里的任務(wù)計(jì)劃里也可以看到創(chuàng)建的任務(wù)計(jì)劃：

3、準(zhǔn)備工作

任務(wù)計(jì)劃創(chuàng)建好后，下面我們就要編寫將Excel數(shù)據(jù)導(dǎo)入到SQL Server中的PowerShell腳本了。

安裝SQL Server 2008 或 R2后會(huì)安裝兩個(gè)PowerShell擴(kuò)展：SqlServerCmdletSnapin100、SqlServerProviderSnapin100，提供了本地及遠(yuǎn)程執(zhí)行SQL語句的能力。

在這一次的測試環(huán)境中，中間服務(wù)器安裝的是SQL Server 2000，顯然不能隨便將其升級(jí)，故需要手動(dòng)來安裝這兩個(gè)PowerShell擴(kuò)展。那么該怎樣手動(dòng)安裝呢？

我在國外的一篇博客中找到了解決方案：

3.1、安裝.NET Framework 3.5 With SP1，下載地址

3.2、安裝Office 2010 PIA，下載地址，下載地址

注：我在虛擬機(jī)中安裝Office 2010 PIA時(shí)出了些問題，始終安裝不上，故暫時(shí)安裝Office 2010 x86進(jìn)行測試（Office 2010包含PIA）。

3.3、安裝Windows 管理框架(包含PowerShell 2.0)，下載地址

3.4、安裝Microsoft? SQL Server? 2008 R2 功能包，下載地址

功能包中的組件有很多，只需要安裝如下幾個(gè)，安裝也按照下面列出的順序(這里僅給出 x86架構(gòu)的下載地址，如果你需要其他架構(gòu)，請到微軟網(wǎng)站下載)：

Microsoft? System CLR Types for SQL Server? 2008 R2，下載地址

Microsoft? SQL Server? 2008 R2 Native Client，下載地址

Microsoft? Core XML Services (MSXML) 6.0 SP 1，下載地址

Microsoft? SQL Server? 2008 R2 命令行實(shí)用工具，下載地址

Microsoft? SQL Server? 2008 R2 共享管理對象，下載地址

Microsoft? Windows PowerShell Extensions for SQL Server? 2008 R2，下載地址

3.5、更改PowerShell的執(zhí)行策略

為了安全起見，PowerShell的默認(rèn)執(zhí)行策略為不允許執(zhí)行任何腳本，詳情參見：get-help about_signing 命令。

所以我們要將執(zhí)行策略更改為：允許運(yùn)行未簽名腳本和來來自其他用戶的簽名腳本。

Set-ExecutionPolicy Remotesigned -Force

3.6、手動(dòng)安裝 SQL Server 2008 R2的PowerShell擴(kuò)展

Set-Alias installutil $env:windir\microsoft.net\framework\v2.0.50727\installutil

installutil -i "C:\Microsoft.SqlServer.Management.PSProvider.dll"

installutil -i "C:\Microsoft.SqlServer.Management.PSSnapins.dll"

使用Set-Alias命令為installutil創(chuàng)建一個(gè)別名，其中installutil.exe是.NET框架自帶的安裝輔助工具，這里將注冊SQL Server 的兩個(gè)程序集。

這兩個(gè)程序集的位置位于：C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn （x64）

C:\Program Files\Microsoft SQL Server\100\Tools\Binn（x86）

可以在SQL Server 2008 或 SQL Server 2008 R2的安裝目錄中找到：

當(dāng)然，中間服務(wù)器是不一定安裝有SQL Server 2008的，所以要從別的機(jī)器上復(fù)制出來。

使用Get-PSSnapin –Registered命令查看程序集是否安裝成功：

3.7添加PSSnapin

注意PSSnapin不能重復(fù)添加

3.8、安裝Microsoft Access Database Engine 2010 Redistributable

MADE 2010包含一系列的組件，使得非 Microsoft Office 應(yīng)用程序可以使用這些組件從 Microsoft Office 2010 文件中讀取數(shù)據(jù)，當(dāng)然也是向后兼容的。

我準(zhǔn)備在SQL語句中用OPENDATASOURCE函數(shù)讀取Excel，故需要下載MADE 2010，下載地址。

3.9、在中間服務(wù)器上創(chuàng)建數(shù)據(jù)庫Middle

Invoke-Sqlcmd -Query "IF NOT EXISTS(SELECT * FROM dbo.sysdatabases WHERE name = 'Middle')

BEGIN

    CREATE DATABASE [Middle] ON PRIMARY

    ( NAME = N'Middle', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Middle.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

     LOG ON

    ( NAME = N'Middle_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Middle_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

END

GO" -ServerInstance "." -Username sa -Password ******

3.10、在Middle數(shù)據(jù)庫中創(chuàng)建表Users

這里假定向中間服務(wù)器中的數(shù)據(jù)庫Middle的Users表中導(dǎo)入數(shù)據(jù)，創(chuàng)建表的腳本如下：

Invoke-Sqlcmd -Query "

USE Middle

GO

IF NOT EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('Users') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

BEGIN

    CREATE TABLE dbo.Users

    (

        UserID INT IDENTITY(1, 1) NOT NULL,

        UserName VARCHAR(20) NULL,

        UserPass VARCHAR(20) NULL,

        CONSTRAINT PK_Users_UserID PRIMARY KEY(UserID)

    )

END

GO" -ServerInstance "." -Database Middle -Username sa -Password ******

我準(zhǔn)備了5個(gè)Excel文件做測試，簡單起見，僅包含一個(gè)工作表，兩列數(shù)據(jù)，第一列作為UserName，第二列作為UserPass：

3.12、創(chuàng)建存儲(chǔ)過程

因?yàn)镺PENDATASOURCE函數(shù)不支持直接傳遞參數(shù)，故需要使用動(dòng)態(tài)SQL語句。

Invoke-Sqlcmd -Query "

USE Middle

GO

IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'sp_導(dǎo)入Users表數(shù)據(jù)') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)

BEGIN

    DROP PROCEDURE dbo.sp_導(dǎo)入Users表數(shù)據(jù)

END

GO

CREATE PROCEDURE dbo.sp_導(dǎo)入Users表數(shù)據(jù)

(

    @ACEVersion VARCHAR(50),

    @ExcelPath NVARCHAR(1000),

    @ExcelVersion VARCHAR(50),

    @SheetName VARCHAR(50)

)

AS

DECLARE @SQL VARCHAR(8000)

SET @SQL = '

    INSERT INTO Users(UserName, UserPass)

    SELECT * FROM OPENDATASOURCE(''' + @ACEVersion + ''', ''Data Source = ' + @ExcelPath + ';Extended Properties = ' + @ExcelVersion + ''')...[' + @SheetName + '$]'

EXECUTE(@SQL)

GO" -ServerInstance "." -Database Middle -Username sa -Password ******

4、將Excel數(shù)據(jù)導(dǎo)入SQL Server

調(diào)用存儲(chǔ)過程

Add-Type -AssemblyName Microsoft.Office.Interop.Excel

$app = New-Object -TypeName Microsoft.Office.Interop.Excel.ApplicationClass

$dir = New-Object -TypeName System.IO.DirectoryInfo -ArgumentList C:\tmp

foreach($file in $dir.GetFiles("*.xlsx", [System.IO.SearchOption]::AllDirectories))

{

    $ExcelPath = $file.FullName

    Invoke-Sqlcmd -Query "EXECUTE dbo.sp_導(dǎo)入Users表數(shù)據(jù) 'Microsoft.ACE.OLEDB.12.0', '$ExcelPath', 'Excel 12.0', 'User'" -Database Middle -Username sa -Password ******

}

$app.Quit()

[System.Runtime.InteropServices.Marshal]::ReleaseComObject($app)

$app = $null

這里有點(diǎn)奇怪，我安裝的是Microsoft.ACE.OLEDB.14.0，但是卻只能使用Microsoft.ACE.OLEDB.12.0，使用14.0會(huì)提示未注冊：

關(guān)于連接字符串的寫法參見ConnectionString網(wǎng)站。

最終運(yùn)行結(jié)果：

小結(jié)：

將Excel導(dǎo)入到SQL Server中有很多種解決方案，具體應(yīng)取決于業(yè)務(wù)。我這里采用純腳本的方式來處理，優(yōu)點(diǎn)是非常靈活，速度較快，可擴(kuò)展性很強(qiáng)。且PowerShell建立在.NET之上，可以調(diào)用其他類庫，進(jìn)一步擴(kuò)展了其適用范圍。如調(diào)用存儲(chǔ)過程時(shí)使用了Office 2010 PIA、System.IO等，和一般的C#代碼很相像。缺點(diǎn)是需要安裝很多依賴組件，如.NET 3.5 SP1，SQL Server 2008 R2 功能包等，部署準(zhǔn)備工作復(fù)雜，且難以對待導(dǎo)入的Excel進(jìn)行更細(xì)粒度的控制，不能精確到單元格。若Excel中的數(shù)據(jù)格式很不規(guī)整、數(shù)據(jù)類型混亂，則會(huì)帶來一些麻煩，實(shí)際項(xiàng)目中應(yīng)根據(jù)需求決定使用哪種方案，合適的才是最好的。