1、SQL Server導(dǎo)出為Excel：

　　要用T-SQL語句直接導(dǎo)出至Excel工作薄，就不得不用借用SQL Server管理器的一個擴展存儲過程：xp_cmdshell，此過程的作用為“以操作系統(tǒng)命令行解釋器的方式執(zhí)行給定的命令字符串，并以文本行方式返 回任何輸出。”下面為定義示例：

　　2、Excel導(dǎo)入SQL Server表：

　　在SQL Server中，有定義一個OpenDateSource函數(shù)，用于引用那些不經(jīng)常訪問的 OLE DB 數(shù)據(jù)源，而我們的數(shù)據(jù)互導(dǎo)操作，就是建立在這個函數(shù)之上。

　　首先看一個T-SQL幫助中的示例，描述如下：

　　--下面是個查詢的示例，它通過用于 Jet 的 OLE DB 提供程序查詢 Excel 電子表格。

　　SELECT *

　　FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

　　'Data Source="c:Financeaccount.xls";User ID=Admin;PassWord=;Extended PRoperties=Excel 5.0')xactions

　　注：--在password=;的后面，加個 HDR=NO 的選項, 表示第1行是數(shù)據(jù), 默認(rèn)為YES, 表示第1行是字段名

　　如果你直接引用這個示例進(jìn)行查詢，那么肯定是通不過的。關(guān)鍵在于語句中的兩個地方需要修改，一處在于Data Source處，雙引號內(nèi)為Excel表格的實際存放位置，要修改為你想查詢的Excel表實際完整路徑;二為最后的...xactions，其實這里代 表的是要進(jìn)行的某些動作，下面會講，這里修改成用中括號包圍的Excel表中工作表名字(加上一個$)就可以了，如[Sheet1$]。當(dāng)然，還可以將 Excel 5.0改為Excel 8.0，因為5.0是以前的老版本了。

　　下面是實例說明：

　　/**//*1、插入Excel中的資料到現(xiàn)存的sql數(shù)據(jù)庫表中(假設(shè)C盤有excel表book2.xls，book2.xls中有個工作表sheet1，sheet1中有兩列id和FName;而同時sql數(shù)據(jù)庫中也有一個表test)：*/

　　insert into test SELECT id,FName

　　FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="c:ook2.xls";User ID=Admin;Password=;Extended properties=Excel 8.0')[sheet1$]

　　--如果用select * ，則列的次序會亂，資料內(nèi)容也會亂，無法插入成功，所以指定列名

　　-----------------------

　　/**//*2、插入excel表中資料到sql數(shù)據(jù)庫并新建一個sql表(excel的定義和內(nèi)容同上)：*/

　　select convert(int,id)as id,FName into test7

　　FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="c:ook2.xls";User ID=Admin;Password=;Extended properties=Excel 8.0')[sheet1$]

　　--在select 列中最好用convert進(jìn)行顯示類型轉(zhuǎn)換，否則資料類型會不如預(yù)期。

　　特別注意!!!：1)如果是從數(shù)據(jù)庫中導(dǎo)出的exel表，例如從jobs表導(dǎo)出的exel文件mytest.xls工作表默認(rèn)是jobs上面例子中的[sheet1$] 應(yīng)改為[jobs$]

　　2)如果出現(xiàn)“服務(wù)器: 消息 7399，級別 16，狀態(tài) 1，行 1

　　OLE DB 提供程序 'MICROSOFT.JET.OLEDB.4.0' 報錯。提供程序未給出有關(guān)錯誤的任何信息。”

　　上面這個錯誤是因為你的EXECL 文件被打開著,關(guān)掉那個EXCEL文件再試試.

　　3)被導(dǎo)入的exel表第一行要有各列的列名如

　　id name age

　　1 tomclus 35

　　。。。

　　如果沒有列名僅僅

　　1 tomclus 35

　　。。。

　　可能會出錯

　　如果上面的例子中沒有制定所有列，或select*,都會出錯，如列不完全，或數(shù)據(jù)類型布匹

　　SQL Server與Excel的數(shù)據(jù)互導(dǎo)講解完了，你明白了嗎?而access和Excel的基本一樣，只是要去掉Extended properties聲明。

　　=======================

　　Delphi示例(導(dǎo)出為excel表)：

　　ADOQ1.Close;

　　ADOQ1.SQL.Clear;

　　sqltrs :=

　　'INSERT INTO CTable (Name1,Sex,ID)'+

　　' SELECT'+

　　' 姓名,性別,身份證號'+

　　' FROM [excel 8.0;database=' + XlsName + '].[sheet1$]';

　　ADOQ1.Parameters.Clear;

　　ADOQ1.ParamCheck:=false;

　　ADOQ1.SQL.Text := sqltrs;

　　ADOQ1.Execsql;

　　//中文字段兩邊不能有空格

　　另附：(下面的部分內(nèi)容沒有親自實踐)

　　熟悉SQL SERVER 2000的數(shù)據(jù)庫管理員都知道，其DTS可以進(jìn)行數(shù)據(jù)的導(dǎo)入導(dǎo)出，其實，我們也可以使用Transact-SQL語句進(jìn)行導(dǎo)入導(dǎo)出操作。在 Transact-SQL語句中，我們主要使用OpenDataSource函數(shù)、OPENROWSET 函數(shù)，關(guān)于函數(shù)的詳細(xì)說明，請參考SQL聯(lián)機幫助。利用下述方法，可以十分容易地實現(xiàn)SQL SERVER、ACCESS、EXCEL數(shù)據(jù)轉(zhuǎn)換，詳細(xì)說明如下：

　　一、SQL SERVER 和ACCESS的數(shù)據(jù)導(dǎo)入導(dǎo)出

　　常規(guī)的數(shù)據(jù)導(dǎo)入導(dǎo)出：

　　使用DTS向?qū)нw移你的Access數(shù)據(jù)到SQL Server，你可以使用這些步驟:

　　○1在SQL SERVER企業(yè)管理器中的Tools(工具)菜單上，選擇Data Transformation

　　○2Services(數(shù)據(jù)轉(zhuǎn)換服務(wù))，然后選擇 czdImport Data(導(dǎo)入數(shù)據(jù))。

　　○3在Choose a Data Source(選擇數(shù)據(jù)源)對話框中選擇Microsoft Access as the Source，然后鍵入你的.mdb數(shù)據(jù)庫(.mdb文件擴展名)的文件名或通過瀏覽尋找該文件。

　　○4在Choose a Destination(選擇目標(biāo))對話框中，選擇Microsoft OLE　DB Prov ider for SQL　Server，選擇數(shù)據(jù)庫服務(wù)器，然后單擊必要的驗證方式。

　　○5在Specify Table Copy(指定表格復(fù)制)或Query(查詢)對話框中，單擊Copy tables(復(fù)制表格)。

　　○6在Select Source Tables(選擇源表格)對話框中，單擊Select All(全部選定)。下一步，完成。

　　Transact-SQL語句進(jìn)行導(dǎo)入導(dǎo)出：

　　1.在SQL SERVER里查詢access數(shù)據(jù):

　　SELECT *

　　FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

　　'Data Source="c:DB.mdb";User ID=Admin;Password=')...表名

　　2.將access導(dǎo)入SQL server

　　在SQL SERVER 里運行:

　　SELECT *

　　INTO newtable

　　FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0',

　　'Data Source="c:DB.mdb";User ID=Admin;Password=' )...表名

　　3.將SQL SERVER表里的數(shù)據(jù)插入到Access表中

　　在SQL SERVER 里運行：

　　insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

　　'Data Source=" c:DB.mdb";User ID=Admin;Password=')...表名

　　(列名1,列名2)

　　select 列名1,列名2 from sql表

　　實例：

　　insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',

　　'C:db.mdb';'admin';'', Test)

　　select id,name from Test

　　INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'c: rade.mdb'; 'admin'; '', 表名)

　　SELECT *

　　FROM sqltablename

　　二、SQL SERVER 和EXCEL的數(shù)據(jù)導(dǎo)入導(dǎo)出

　　1、在SQL SERVER里查詢Excel數(shù)據(jù):

　　SELECT *

　　FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

　　'Data Source="c:ook1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]

　　下面是個查詢的示例，它通過用于 Jet 的 OLE DB 提供程序查詢 Excel 電子表格。

　　SELECT *

　　FROM OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0',

　　'Data Source="c:Financeaccount.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions

　　2、將Excel的數(shù)據(jù)導(dǎo)入SQL server :

　　SELECT * into newtable

　　FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

　　'Data Source="c:ook1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]

　　實例:

　　SELECT * into newtable

　　FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

　　'Data Source="c:Financeaccount.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions

　　3、將SQL SERVER中查詢到的數(shù)據(jù)導(dǎo)成一個Excel文件

　　T-SQL代碼：

　　EXEC master..xp_cmdshell 'bcp 庫名.dbo.表名out c:Temp.xls -c -q -S"servername" -U"sa" -P""'

　　參數(shù)：S 是SQL服務(wù)器名;U是用戶;P是密碼

　　說明：還可以導(dǎo)出文本文件等多種格式

　　實例:EXEC master..xp_cmdshell 'bcp saletesttmp.dbo.CusAccount out c: emp1.xls -c -q -S"pmserver" -U"sa" -P"sa"'

　　EXEC master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout C: authors.xls -c -Sservername -Usa -Ppassword'

　　在VB6中應(yīng)用ADO導(dǎo)出EXCEL文件代碼：

　　Dim cn As New ADODB.Connection

　　cn.open "Driver={SQL Server};Server=WEBSVR;DataBase=WebMis;UID=sa;WD=123;"

　　cn.execute "master..xp_cmdshell 'bcp "SELECT col1, col2 FROM 庫名.dbo.表名" queryout E:DT.xls -c -Sservername -Usa -Ppassword'"

　　4、在SQL SERVER里往Excel插入數(shù)據(jù):

　　insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

　　'Data Source="c:Temp.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...table1 (A1,A2,A3) values (1,2,3)

　　T-SQL代碼：

　　INSERT INTO

　　OPENDATASOURCE('Microsoft.JET.OLEDB.4.0',

　　'Extended Properties=Excel 8.0;Data source=C: raininginventur.xls')...[Filiale1$]

　　(bestand, produkt) VALUES (20, 'Test')

　　總結(jié)：利用以上語句，我們可以方便地將SQL SERVER、ACCESS和EXCEL電子表格軟件中的數(shù)據(jù)進(jìn)行轉(zhuǎn)換，為我們提供了極大方便!

　　EXEC master..xp_cmdshell 'bcp 庫名.dbo.表名out c:Book3.xls -c -q -S"servername" -U"sa" -P""'

　　--參數(shù)：S 是SQL服務(wù)器名;U是用戶名;P是密碼,沒有就空著

　　--說明：其實用這個過程導(dǎo)出的格式實質(zhì)上就是文本格式的，不信的話在導(dǎo)出的Excel表中改動一下再保存看看。

　　實際例子與說明如下：

　　/**//*如果要將表整個導(dǎo)出至Excel的話*/

　　EXEC master..xp_cmdshell 'bcp northwind.dbo.orders out c:Book1.xls -c -q -S"(local)" -U"sa" -P""'

　　--注意句中的northwind.dbo.orders，為數(shù)據(jù)庫名+擁有者+表名

　　--直接導(dǎo)出用“out”關(guān)健字

　　-------------------------------------------

　　/**//*如果要利用查詢來導(dǎo)出部分字段至Excel的話*/

　　EXEC master..xp_cmdshell 'bcp "SELECT orderid,cutomerid,freight FROM northwind..orders ORDER BY orderid" queryout C: Book2.xls -c -S"(local)" -U"sa" -P""'

　　--這里在bcp后面加了一個查詢語句，并用雙引號括起來

　　--利用查詢要用“queryout”關(guān)鍵字

　　關(guān)于SQL Server與Excel、Access數(shù)據(jù)互導(dǎo)問題的補充：

　　1、將excel中的數(shù)據(jù)導(dǎo)入sql中時，數(shù)字變?yōu)榭茖W(xué)計數(shù)法的解決辦法：

　　如：

　　excel中的數(shù)據(jù)為：8630890

　　導(dǎo)入sql后變?yōu)椋?.63089e+006

　　注：sql中該字段數(shù)據(jù)類型為nvchar。

　　可以參考下面的方法轉(zhuǎn)換已經(jīng)導(dǎo)入的數(shù)據(jù),但因精度問題導(dǎo)致的數(shù)據(jù)不準(zhǔn)確不能被處理,另外,excel數(shù)據(jù)中,如果有前導(dǎo)的0,那么導(dǎo)入后的數(shù)據(jù)由于是float數(shù)字,所以會丟失前導(dǎo)0

　　declare @a float

　　set @a=8.63089e+006

　　select cast(@a as decimal(38))

　　--結(jié)果：8630890

　　結(jié)合自己的實例，給大家一段代碼：

　　file1=request("file")

　　sql="insert into student(studyid,yourname,yourpass,yourclass,courseid) SELECT cast(學(xué)號 as decimal(18)),姓名,cast(密碼 as decimal(18)),班級,cast(選課班號 as decimal(18)) FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="&file1&";User ID=Admin;Password=;Extended properties=Excel 8.0')...[sheet1$]"

　　conn.Execute sql

　　2、如何得到EXCEL的表名(asp中)：

　　set app=server.CreateObject("Excel.application")

　　app.Workbooks.Open(""&file1&"")

　　for i =1 to app.worksheets.count

　　response.write app.worksheets(i).name

　　next

　　測試的時候，不知什么原因，時好時不好的，有待進(jìn)一步解決!

最新評論