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

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

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

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

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

　　--下面是個(gè)查詢的示例，它通過(guò)用于 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=;的后面，加個(gè) HDR=NO 的選項(xiàng), 表示第1行是數(shù)據(jù), 默認(rèn)為YES, 表示第1行是字段名

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

　　下面是實(shí)例說(shuō)明：

　　/**//*1、插入Excel中的資料到現(xiàn)存的sql數(shù)據(jù)庫(kù)表中(假設(shè)C盤(pán)有excel表book2.xls，book2.xls中有個(gè)工作表sheet1，sheet1中有兩列id和FName;而同時(shí)sql數(shù)據(jù)庫(kù)中也有一個(gè)表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 * ，則列的次序會(huì)亂，資料內(nèi)容也會(huì)亂，無(wú)法插入成功，所以指定列名

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

　　/**//*2、插入excel表中資料到sql數(shù)據(jù)庫(kù)并新建一個(gè)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)換，否則資料類型會(huì)不如預(yù)期。

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

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

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

　　上面這個(gè)錯(cuò)誤是因?yàn)槟愕腅XECL 文件被打開(kāi)著,關(guān)掉那個(gè)EXCEL文件再試試.

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

　　id name age

　　1 tomclus 35

　　。。。

　　如果沒(méi)有列名僅僅

　　1 tomclus 35

　　。。。

　　可能會(huì)出錯(cuò)

　　如果上面的例子中沒(méi)有制定所有列，或select*,都會(huì)出錯(cuò)，如列不完全，或數(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'+

　　' 姓名,性別,身份證號(hào)'+

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

　　ADOQ1.Parameters.Clear;

　　ADOQ1.ParamCheck:=false;

　　ADOQ1.SQL.Text := sqltrs;

　　ADOQ1.Execsql;

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

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

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

　　一、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ù)源)對(duì)話框中選擇Microsoft Access as the Source，然后鍵入你的.mdb數(shù)據(jù)庫(kù)(.mdb文件擴(kuò)展名)的文件名或通過(guò)瀏覽尋找該文件。

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

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

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

　　Transact-SQL語(yǔ)句進(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 里運(yùn)行:

　　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 里運(yùn)行：

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

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

　　(列名1,列名2)

　　select 列名1,列名2 from sql表

　　實(shí)例：

　　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$]

　　下面是個(gè)查詢的示例，它通過(guò)用于 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$]

　　實(shí)例:

　　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)成一個(gè)Excel文件

　　T-SQL代碼：

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

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

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

　　實(shí)例: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 庫(kù)名.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é)：利用以上語(yǔ)句，我們可以方便地將SQL SERVER、ACCESS和EXCEL電子表格軟件中的數(shù)據(jù)進(jìn)行轉(zhuǎn)換，為我們提供了極大方便!

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

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

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

　　實(shí)際例子與說(shuō)明如下：

　　/**//*如果要將表整個(gè)導(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ù)庫(kù)名+擁有者+表名

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

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

　　/**//*如果要利用查詢來(lái)導(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后面加了一個(gè)查詢語(yǔ)句，并用雙引號(hào)括起來(lái)

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

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

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

　　如：

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

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

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

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

　　declare @a float

　　set @a=8.63089e+006

　　select cast(@a as decimal(38))

　　--結(jié)果：8630890

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

　　file1=request("file")

　　sql="insert into student(studyid,yourname,yourpass,yourclass,courseid) SELECT cast(學(xué)號(hào) as decimal(18)),姓名,cast(密碼 as decimal(18)),班級(jí),cast(選課班號(hào) 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

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

最新評(píng)論