SQL Server 与 Excel

2024-08-31 00:47:58

字体：大中小

来源：转载

供稿：网友

/*
存储过程名称：导出数据到excel
功能描述：导出数据到excel

exec exporttoexcel @server = '.',
                   @uname = 'sa',
                   @pwd = '',
                   @querytext = 'select * from dldata..bbbbbb',
                   @filename = 'd:/importtoexcel.xls'
*/

if object_id('exporttoexcel') is not null drop proc exporttoexcel
go

create procedure exporttoexcel (
@server sysname = null,
@uname sysname = null,
@pwd sysname = null,
@querytext varchar(200) = null,
@filename varchar(200) = 'd:/importtoexcel.xls'
)
as
declare @sqlserver int, --sqldmo.sqlserver对象
        @queryresults int, --queryresults对象
        @currentresultset int,
        @object int, --excel.application对象
        @workbooks int,
        @workbook int,
        @range int,
        @hr int,
        @columns int,
        @rows int,
        @indcolumn int,
        @indrow int,
        @off_column int,
        @off_row int,
        @code_str varchar(100),
        @result_str varchar(255)

if @querytext is null
begin
   print 'set the query string'
   return
end

--设置服务器名为本地服务器(@@servername返回运行sql server的本地服务器名称)
if @server is null select @server = @@servername

--设置用户名为当前系统用户名(使用system_user返回当前系统用户名)
if @uname is null select @uname = system_user

set nocount on

--创建sqldmo.sqlserver对象
exec @hr = sp_oacreate 'sqldmo.sqlserver', @sqlserver out
if @hr <> 0
begin
   print 'error create sqldmo.sqlserver'
   return
end

--连接到sql server系统
if @pwd is null
begin
   exec @hr = sp_oamethod @sqlserver, 'connect', null, @server, @uname
   if @hr <> 0
   begin
      print 'error connect'
      return
   end
end
else
begin
   exec @hr = sp_oamethod @sqlserver, 'connect', null, @server, @uname, @pwd
   if @hr <> 0
   begin
      print 'error connect'
      return
   end
end

--the executewithresults method executes a transact-sql command batch
--returning batch result sets in a queryresults object
select @result_str = 'executewithresults("' + @querytext + '")'
exec @hr = sp_oamethod @sqlserver, @result_str, @queryresults out
if @hr <> 0
begin
    print 'error with method executewithresults'
    return
end

--the currentresultset property controls access to the result sets of a queryresults object
exec @hr = sp_oamethod @queryresults, 'currentresultset', @currentresultset out
if @hr <> 0
begin
   print 'error get currentresultset'
   return
end

--the columns property exposes the number of columns contained
--in the current result set of a queryresults object
exec @hr = sp_oamethod @queryresults, 'columns', @columns out
if @hr <> 0
begin
   print 'error get columns'
   return
end

--the rows property returns the number of rows in a referenced
--query result set or the number of rows existing in a table
exec @hr = sp_oamethod @queryresults, 'rows', @rows out
if @hr <> 0
begin
   print 'error get rows'
   return
end

--创建excel.application对象
exec @hr = sp_oacreate 'excel.application', @object out
if @hr <> 0
begin
   print 'error create excel.application'
   return
end

--获得excel工作簿对象
exec @hr = sp_oagetproperty @object, 'workbooks', @workbooks out
if @hr <> 0
begin
   print 'error create workbooks'
   return
end

--在工作簿对象中加入一工作表
exec @hr = sp_oagetproperty @workbooks, 'add', @workbook out
if @hr <> 0
begin
   print 'error with method add'
   return
end

--range对象(a1单元格)
exec @hr = sp_oagetproperty @object, 'range("a1")', @range out
if @hr <> 0
begin
   print 'error create range'
   return
end

select @indrow = 1
select @off_row = 0
select @off_column = 1

while (@indrow <= @rows)
begin
   select @indcolumn = 1
   while (@indcolumn <= @columns)
   begin
      --the getcolumnstring method returns a queryresults object result set member converted to a string value
      exec @hr = sp_oamethod @queryresults, 'getcolumnstring', @result_str out, @indrow, @indcolumn
      if @hr <> 0
      begin
         print 'error get getcolumnstring'
         return
      end

      exec @hr = sp_oasetproperty @range, 'value', @result_str
      if @hr <> 0
      begin
         print 'error set value'
         return
      end

      exec @hr = sp_oagetproperty @range, 'offset', @range out, @off_row, @off_column
      if @hr <> 0
      begin
         print 'error get offset'

         return
      end

      select @indcolumn = @indcolumn + 1

   end

   select @indrow = @indrow + 1
   select @code_str = 'range("a' + ltrim(str(@indrow)) + '")'
   exec @hr = sp_oagetproperty @object, @code_str, @range out
   if @hr <> 0
   begin
      print 'error create range'
      return
   end

end

select @result_str = 'exec master..xp_cmdshell ''del ' + @filename + ''', no_output'
exec(@result_str) --如果存在@filename文件，则先删除
select @result_str = 'saveas("' + @filename + '")'
exec @hr = sp_oamethod @workbook, @result_str
if @hr <> 0
begin
    print 'error with method saveas'
    return
end

exec @hr = sp_oamethod @workbook, 'close'
if @hr <> 0
begin
    print 'error with method close'
    return
end

exec @hr = sp_oadestroy @object
if @hr <> 0
begin
    print 'error destroy excel.application'
    return
end

exec @hr = sp_oadestroy @sqlserver
if @hr <> 0
begin
    print 'error destroy sqldmo.sqlserver'
    return
end
go

收集最实用的网页特效代码！

上一篇：SQL Server 安全检查列表

下一篇：SQL SERVER打补丁后的版本号