Sql Server資料導出EXCEL
還是在用Sql Server2000的,放出一個修改過的Sql
Server資料導出EXCEL存儲過程,提供了分sheet功能,之前的版本是沒有這個功能的,解決超過65535行的問題。
CREATE proc p_exporttb
@sqlstr varchar(8000), --查詢語句,如果查詢語句中使用了order by ,請加上top 100 percent
@path nvarchar(1000), --檔案存放目錄
@fname nvarchar(250), --檔案名
@sheetname varchar(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),@tmpsql varchar(8000)
declare @sheetcount int,@sheetnow int, @recordcount int, @recordnow
int
declare @sheetsql varchar(8000)--建立頁的sql
declare @pagesize int
set @pagesize = 65535--sheet分頁的大小
--set @pagesize = 1000
--參數檢測
if isnull(@fname,'')='' set @fname='temp.xls'
if isnull(@sheetname,'')='' set
@sheetname=replace(@fname,'.','#')
--檢查檔案是否已經存在
if right(@path,1)<>'\' set
@[email protected]+'\'
create table #tb(a bit,b bit,c bit)
set @[email protected][email protected]
insert into #tb exec master..xp_fileexist @sql
--資料庫建立語句
set @[email protected][email protected]
if exists(select 1 from #tb where a=1)
set @constr='DRIVER={Microsoft Excel Driver
(*.xls)};DSN='''';READONLY=FALSE'
+';CREATE_DB="'[email protected]+'";DBQ='[email protected]
else
set
@constr='Provider=Microsoft.Jet.OLEDB.4.0;Extended
Properties="Excel 8.0;HDR=YES'
+';DATABASE='[email protected]+'"'
--連接配接資料庫
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
declare @tbname sysname
set @tbname='##tmp_'+convert(varchar(38),newid())
declare @tbtmpid nvarchar(50)
set @tbtmpid ='tmp_'+convert(varchar(38),newid())+''
--有序列ID @tbtmpid的臨時表
set @sql='select Identity(int,1,1) as ['[email protected]+'], a.* into
['[email protected]+'] from ( select top 100 percent b.* from ( '[email protected]+') b) a'
exec(@sql)
--print(@sql)
--取得記錄總數
set @recordcount= @@rowcount
if @recordcount=0 return
--print @recordcount
select @sql='',@fdlist=''
select @[email protected]+',['+a.name+']'
,@[email protected]+',['+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 tempdb..syscolumns a left join tempdb..systypes b on
a.xtype=b.xusertype
where b.name not
in('image','uniqueidentifier','sql_variant','varbinary','binary','timestamp')
and a.id=(select id from tempdb..sysobjects where
[email protected])
and a.name <> @tbtmpid
set @fdlist=substring(@fdlist,2,8000)
--print @fdlist
--列數為零
if @@rowcount=0 return
set @sheetsql = @sql
--print @sheetsql
--導入資料
--頁數
set @sheetcount = CEILING(@recordcount/CAST(@pagesize as
float))
--print @sheetcount
-- 隻是一個頁而已
IF @sheetcount = 1 BEGIN
'隻是一個頁而已'
set
@sql='create table ['[email protected]
+']('+substring(@sheetsql,2,8000)+')'
exec
@err=sp_oamethod @obj,'execute',@out out,@sql
if
@err<>0 goto lberr
set
@sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel
8.0;HDR=YES
;DATABASE='[email protected][email protected]+''',['[email protected]+'$])'
exec('insert
into '[email protected]+'('[email protected]+')
select '[email protected]+' from
['[email protected]+']')
END
--多個頁
set @sheetnow = @sheetcount
set @recordnow= 0
IF @sheetcount > 1 BEGIN
'多個頁'
WHILE @sheetnow > 0 BEGIN
--建立頁
set
@sql='create table ['[email protected]+'_'+
convert(nvarchar(80),@sheetcount - @sheetnow + 1)
+']('+substring(@sheetsql,2,8000)+')'
exec
@err=sp_oamethod @obj,'execute',@out out,@sql
if
@err<>0 goto lberr
@sql
--建立頁end
IF @sheetnow
= @sheetcount BEGIN
set @tmpsql ='select top '+str(@pagesize)+' '[email protected]+' from ['[email protected]+']'
set @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel
8.0;HDR=YES
;DATABASE='[email protected][email protected]+''',['[email protected]+'_'+convert(nvarchar(80),@sheetcount
- @sheetnow + 1)+'$])'
exec('insert into '[email protected]+'('[email protected]+')
'+ @tmpsql)
END
IF @sheetnow
< @sheetcount
BEGIN set @tmpsql='select top '+str(@pagesize)+' '[email protected]+' from ['[email protected]+'] where
['[email protected]
+'] not in (
select top '+str(@[email protected])+' ['[email protected]+'] from
['[email protected]+'])'
set @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel
8.0;HDR=YES
;DATABASE='[email protected][email protected]+''',['[email protected]+'_'+
convert(nvarchar(80),@sheetcount - @sheetnow + 1)+'$])'
exec('insert into '[email protected]+'('[email protected]+')
'+ @tmpsql)
(@tmpsql) --exec(@tmpsql)
END
(@tmpsql)
--exec
(@tmpsql)
set
@recordnow = @pagesize*(@[email protected]+2)
set
@sheetnow = @sheetnow -1
END
END
set @sql='drop table ['[email protected]+']'
exec(@sql)
exec @err=sp_oadestroy @obj
--結束傳回
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