?
/*=================== 導入/導出 Excel 的基本方法 ===================*/

從Excel文件中,導入數據到SQL數據庫中,很簡單,直接用下面的語句:

/*========================================================*/

?EXEC master..xp_cmdshell 'bcp " exec weberp.dbo.DailyCash_Excel @PayAccount=null,@StartDate=null,@EndDate=null,@TrackStation=null" queryout "C:\authors.xls" -c -S "(local)" -U "sa" -P "password"'
--如果接受數據導入的表已經存在

insert into 表 select * from

OPENROWSET('MICROSOFT.JET.OLEDB.4.0'

,'Excel 5.0;HDR=YES;DATABASE=c:test.xls',sheet1$)

--如果導入數據并生成表

select * into 表 from

OPENROWSET('MICROSOFT.JET.OLEDB.4.0'

,'Excel 5.0;HDR=YES;DATABASE=c:test.xls',sheet1$)

/*========================================================*/

--如果從SQL數據庫中,導出數據到Excel,如果Excel文件已經存在,而且已經按照要接收的數據創建好表頭,就可以簡單的用:

insert into OPENROWSET('MICROSOFT.JET.OLEDB.4.0'

,'Excel 5.0;HDR=YES;DATABASE=c:test.xls',sheet1$)

select * from 表

--如果Excel文件不存在,也可以用BCP來導成類Excel的文件,注意大小寫:

--導出表的情況

EXEC master..XP_cmdshell 'bcp 數據庫名.dbo.表名 out "c:test.xls" /c -/S"服務器名" /U"用戶名" -P"密碼"'

--導出查詢的情況

EXEC master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout "c:test.xls" /c -/S"服務器名" /U"用戶名" -P"密碼"'

/*--說明:

c:test.xls 為導入/導出的Excel文件名.

sheet1$???? 為Excel文件的工作表名,一般要加上$才能正常使用.

--*/

/*--數據導出EXCEL?

? 導出表中的數據到Excel,如果文件不存在,將自動創建文件,如果表不存在,將自動創建表?

? 調用示例?
? ExportToExcel? @tbname='test',@path='c:\',@fname='test.xls'?

? --*/?

? if? exists? (select? *? from? dbo.sysobjects? where? id? =? object_id(N'[dbo].[ExportToExcel]')? and? OBJECTPROPERTY(id,? N'IsProcedure')? =? 1)?

? drop? procedure? [dbo].[ExportToExcel]?

? GO?

??

? create? proc? ExportToExcel?

? @Pac? , --要導出的表名,注意只能是表名/視圖名?

? @path? nvarchar(1000), --文件存放目錄?

? @fname? nvarchar(250)='' --文件名,默認為表名?

? as?

? declare @err int,@src nvarchar(255),@desc? nvarchar(255),@out? int?

? declare @obj int,@constr nvarchar(1000),@sql? varchar(8000),@fdlist? varchar(8000)?

? --參數檢測?

? if isnull(@fname,'')='' set @fname=@tbname+'.xls'?

? --檢查文件是否已經存在?

? 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?

??

? --數據庫創建語句?

? set @sql=@path+@fname?

? if exists(select? 1? from? #tb? where? a=1)?

? set? @constr='DRIVER={Microsoft Excel Driver (*.xls)};DSN=TT;READONLY=FALSE'?

?????? +';CREATE_DB='+@sql+';DBQ='+@sql?

? else?

? set? @constr='Provider=Microsoft.Jet.OLEDB.4.0;Extended? Properties="Excel? 8.0;HDR=YES'?

? +';DATABASE='+@sql+'"'?

??

? --連接數據庫?

?print @constr

? 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?

??

? --創建表的SQL?

? select? @sql='',@fdlist=''?

? select? @fdlist=@fdlist+','+a.name?

? ,@sql=@sql+',['+a.name+']? '?

? +case? when? b.name? in('char','nchar','varchar','nvarchar')? then?

?? 'text('+cast(case? when? a.length>255? then? 255? else? a.length? end? as? varchar)+')'?

? when? b.name? in('tynyint','int','bigint','tinyint')? then? 'int'?

? when? b.name? in('smalldatetime','datetime')? then? 'datetime'?

? when? b.name? in('money','smallmoney')? then? 'money'?

? else? b.name? end?

? FROM? syscolumns? a? left? join? systypes? b? on? a.xtype=b.xusertype?

? where? b.name? not? in('image','text','uniqueidentifier','sql_variant','ntext','varbinary','binary','timestamp')?

? and? object_id(@tbname)=id?

? select? @sql='create? table? ['+@tbname?

? +']('+substring(@sql,2,8000)+')'?

? ,@fdlist=substring(@fdlist,2,8000)?

?? print @sql

? exec? @err=sp_oamethod? @obj,'execute',@out? out,@sql?

? if? @err<>0? goto? lberr?

??

? exec? @err=sp_oadestroy? @obj?

? --導入數據?

? set? @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=YES?

? ;DATABASE='+@path+@fname+''',['+@tbname+'$])'?

??

? exec('insert? into? '+@sql+'('+@fdlist+')? select? '+@fdlist+'? from? '+@tbname)?

??

? return?

??

? lberr:?

? exec? sp_oageterrorinfo? 0,@src? out,@desc? out?

? lbexit:?

? select? cast(@err? as? varbinary(4))? as? 錯誤號?

? ,@src? as? 錯誤源,@desc? as? 錯誤描述?

? select? @sql,@constr,@fdlist?

? go?


CREATE?? PROC?? SqlToExcel??
? (??
? @Path?????????????????? varchar(100),--文件存放路徑??
? @Fname???????????????? varchar(100),--文件名字??
? @SheetName???????? varchar(80),---工作表名字??
? @SqlStr?????????????? varchar(8000)--查詢語句,如果查詢語句中使用了order?????? by?????? ,請加上top?????? 100?????? percent,注意,如果導出表/視圖,用上面的存儲過程??????
? )??
? AS??
? SET?? NOCOUNT?? ON??
???
? declare???? @sql???????????????? varchar(8000)??
? declare???? @obj???????????????? int--OLE對象??
? declare???? @constr?????????? varchar(8000)??
? declare???? @err???????????????? int??
? declare???? @out???????????????? int??
? declare???? @fdlist?????????? varchar(8000)??
? declare???? @tbname?????????? sysname--臨時表??
? declare???? @Src???????????????? nvarchar(200)??
? declare???? @Desc?????????????? nvarchar(200)??
???
? set?? @tbname='##tmp_'+convert(varchar(38),newid())??
???
? exec('select?? *?? into?? ['+@tbname?? +']?? from?? '+'('+@sqlStr+')?? A')??
???
? select?? @fdlist?? =?? ''??
???
? set?? @sql=?? @path+@fname??
??? set?? @constr='DRIVER={Microsoft?? Excel?? Driver?? (*.xls)};DSN='''';READONLY=FALSE'??
??????????????? +';CREATE_DB="'+@sql+'";DBQ='+@sql??
???
? --生成Excel的列??
? set?? @sql?? =?? ''??
? select?? @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,??????
? @fdlist?? =?? @fdlist+','+'['+a.name+']'????
? from?? tempdb..syscolumns?? a???? join?? tempdb..systypes?? b?? on?? a.xtype?? =?? b.xusertype????
? where?? b.name?? not?? in('image','uniqueidentifier','sql_variant','varbinary','binary','timestamp')????
????????????? and?? id?? in(select?? id?? from?? tempdb..sysobjects?? where?? name?? =?? @tbname)?? order?? by?? colorder??
? if?? @@rowcount=0?? return??
???
? set?? @fdlist?? =?? substring(@fdlist,2,8000)??
???
? --連接數據庫??
? exec?? @err=sp_oacreate?? 'adodb.connection',@obj?? out??
? if?? @err?? <>?? 0???? goto?? lberror??
? exec?? @err=sp_oamethod?? @obj,'open',null,@constr??
? if?? @err?? <>?? 0???? goto?? lberror??
? --創建工作薄??
? select?? @sql='create?? table?? ['+@sheetname??
??? +']('+substring(@sql,2,8000)+')'??
???
? exec?? @err=sp_oamethod?? @obj,'execute',@out?? out,@sql--@sql為excute方法提供參數??
???
? if?? @err?? <>?? 0???? goto?? lberror??
???
? exec?? @err=sp_oadestroy?? @obj??
???
? --導入數據??
? set?? @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel?? 8.0;HDR=YES??
??????? ;DATABASE='+@path+@fname+''',['+@sheetname+'$])'??
? --print?? @sql??
? exec?? ('insert?? into?? '+@sql+'('+@fdlist+')?? select?? '+@fdlist+'?? from?? ['+@tbname+']')??
???
? exec('drop?? table?? ['+@tbname+']')??
? return??
???
???
? lberror:??
??? exec?? sp_oageterrorinfo?? 0,@src?? out,@desc?? out??
???
? lbexit:??
??? select?? cast(@err?? as?? varbinary(4))?? as?? 錯誤號??
????? ,@src?? as?? 錯誤源,@desc?? as?? 錯誤描述??
??? select?? @sql,@constr,@fdlist??
???
???
? GO??

轉載于:https://www.cnblogs.com/BlogNetSpace/articles/1215068.html

總結

以上是生活随笔為你收集整理的t-sql导出EXCEL语句的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。