-- 從Excel文件中,導(dǎo)入數(shù)據(jù)到SQL數(shù)據(jù)庫中,很簡(jiǎn)單,直接用下面的語句:
/**/ /**/ /**/ /* =================================================================== */
-- 如果接受數(shù)據(jù)導(dǎo)入的表已經(jīng)存在
insert into 表 select * from
OPENROWSET ( ' MICROSOFT.JET.OLEDB.4.0 '
, ' Excel5.0;HDR=YES;DATABASE=c: est.xls ' ,sheet1$)

-- 如果導(dǎo)入數(shù)據(jù)并生成表
select * into 表 from
OPENROWSET ( ' MICROSOFT.JET.OLEDB.4.0 '
, ' Excel5.0;HDR=YES;DATABASE=c: est.xls ' ,sheet1$)

-- 補(bǔ)充幾句如果系統(tǒng)顯示
/**/ /*

SQLServer阻止了對(duì)組件'AdHocDistributedQueries'的STATEMENT'OpenRowset/OpenDatasource'的訪問，因?yàn)榇私M件已作為此服務(wù)器安全配置的一部分而被關(guān)閉。系統(tǒng)管理員可以通過使用sp_configure啟用'AdHocDistributedQueries'。有關(guān)啟用'AdHocDistributedQueries'的詳細(xì)信息，請(qǐng)參閱SQLServer聯(lián)機(jī)叢書中的"外圍應(yīng)用配置器"。
因?yàn)镾QL2005默認(rèn)是沒有開啟'AdHocDistributedQueries'組件，開啟方法如下 */


EXEC sp_configure ' showadvancedoptions ' , 1
GO
RECONFIGURE
GO
EXEC sp_configure ' AdHocDistributedQueries ' , 1
GO
RECONFIGURE
GO


/**/ /**/ /**/ /* =================================================================== */
-- 如果從SQL數(shù)據(jù)庫中,導(dǎo)出數(shù)據(jù)到Excel,如果Excel文件已經(jīng)存在,而且已經(jīng)按照要接收的數(shù)據(jù)創(chuàng)建好表頭,就可以簡(jiǎn)單的用:
insert into OPENROWSET ( ' MICROSOFT.JET.OLEDB.4.0 '
, ' Excel5.0;HDR=YES;DATABASE=c: est.xls ' ,sheet1$)
select * from 表


-- 如果Excel文件不存在,也可以用BCP來導(dǎo)成類Excel的文件,注意大小寫:
-- 導(dǎo)出表的情況
EXEC master..xp_cmdshell ' bcp數(shù)據(jù)庫名.dbo.表名out"c: est.xls"/c-/S"服務(wù)器名"/U"用戶名"-P"密碼" '

-- 導(dǎo)出查詢的情況
EXEC master..xp_cmdshell ' bcp"SELECTau_fname,au_lnameFROMpubs..authorsORDERBYau_lname"queryout"c: est.xls"/c-/S"服務(wù)器名"/U"用戶名"-P"密碼" '

/**/ /**/ /**/ /* --說明:
c: est.xls為導(dǎo)入/導(dǎo)出的Excel文件名.
sheet1$為Excel文件的工作表名,一般要加上$才能正常使用.
-- */


-- 下面是導(dǎo)出真正Excel文件的方法:

if exists ( select * from dbo.sysobjects where id = object_id (N ' [dbo].[p_exporttb] ' ) and OBJECTPROPERTY (id,N ' IsProcedure ' ) = 1 )
drop procedure [ dbo ] . [ p_exporttb ]
GO

/**/ /**/ /**/ /* --數(shù)據(jù)導(dǎo)出EXCEL

導(dǎo)出表中的數(shù)據(jù)到Excel,包含字段名,文件為真正的Excel文件
,如果文件不存在,將自動(dòng)創(chuàng)建文件
,如果表不存在,將自動(dòng)創(chuàng)建表
基于通用性考慮,僅支持導(dǎo)出標(biāo)準(zhǔn)數(shù)據(jù)類型

--鄒建2003.10(引用請(qǐng)保留此信息)-- */

/**/ /**/ /**/ /* --調(diào)用示例

p_exporttb@tbname='地區(qū)資料',@path='c:',@fname='aa.xls'
-- */
create proc p_exporttb
@tbname sysname, -- 要導(dǎo)出的表名
@path nvarchar ( 1000 ), -- 文件存放目錄
@fname nvarchar ( 250 ) = '' -- 文件名,默認(rèn)為表名
as
declare @err int , @src nvarchar ( 255 ), @desc nvarchar ( 255 ), @out int
declare @obj int , @constr nvarchar ( 1000 ), @sql varchar ( 8000 ), @fdlist varchar ( 8000 )

-- 參數(shù)檢測(cè)
if isnull ( @fname , '' ) = '' set @fname = @tbname + ' .xls '

-- 檢查文件是否已經(jīng)存在
if right ( @path , 1 ) <> ' ' set @path = @path + ' '
create table #tb(a bit ,b bit ,c bit )
set @sql = @path + @fname
insert into #tb exec master..xp_fileexist @sql

-- 數(shù)據(jù)庫創(chuàng)建語句
set @sql = @path + @fname
if exists ( select 1 from #tb where a = 1 )
set @constr = ' DRIVER={MicrosoftExcelDriver(*.xls)};DSN= '''' ;READONLY=FALSE '
+ ' ;CREATE_DB=" ' + @sql + ' ";DBQ= ' + @sql
else
set @constr = ' Provider=Microsoft.Jet.OLEDB.4.0;ExtendedProperties="Excel8.0;HDR=YES '
+ ' ;DATABASE= ' + @sql + ' " '


-- 連接數(shù)據(jù)庫
exec @err = sp_oacreate ' adodb.connection ' , @obj out
if @err <> 0 goto lberr

exec @err = sp_oamethod @obj , ' open ' , null , @constr
if @err <> 0 goto lberr

/**/ /**/ /**/ /* --如果覆蓋已經(jīng)存在的表,就加上下面的語句
--創(chuàng)建之前先刪除表/如果存在的話
select@sql='droptable['+@tbname+']'
exec@err=sp_oamethod@obj,'execute',@outout,@sql
-- */

-- 創(chuàng)建表的SQL
select @sql = '' , @fdlist = ''
select @fdlist = @fdlist + ' ,[ ' + a.name + ' ] '
, @sql = @sql + ' ,[ ' + a.name + ' ] '
+ case
when b.name like ' %char '
then case when a.length > 255 then ' memo '
else ' text( ' + cast (a.length as varchar ) + ' ) ' end
when b.name like ' %int ' or b.name = ' bit ' then ' int '
when b.name like ' %datetime ' then ' datetime '
when b.name like ' %money ' then ' money '
when b.name like ' %text ' then ' memo '
else b.name end
FROM syscolumnsa left join systypesb on a.xtype = b.xusertype
where b.name not in ( ' image ' , ' uniqueidentifier ' , ' sql_variant ' , ' varbinary ' , ' binary ' , ' timestamp ' )
and object_id ( @tbname ) = id
select @sql = ' createtable[ ' + @tbname
+ ' ]( ' + substring ( @sql , 2 , 8000 ) + ' ) '
, @fdlist = substring ( @fdlist , 2 , 8000 )
exec @err = sp_oamethod @obj , ' execute ' , @out out, @sql
if @err <> 0 goto lberr

exec @err = sp_oadestroy @obj

-- 導(dǎo)入數(shù)據(jù)
set @sql = ' openrowset( '' MICROSOFT.JET.OLEDB.4.0 '' , '' Excel8.0;HDR=YES;IMEX=1
;DATABASE= ' + @path + @fname + ''' ,[ ' + @tbname + ' $]) '

exec ( ' insertinto ' + @sql + ' ( ' + @fdlist + ' )select ' + @fdlist + ' from ' + @tbname )

return

lberr:
exec sp_oageterrorinfo 0 , @src out, @desc out
lbexit:
select cast ( @err as varbinary ( 4 )) as 錯(cuò)誤號(hào)
, @src as 錯(cuò)誤源, @desc as 錯(cuò)誤描述
select @sql , @constr , @fdlist
go