导出数据到Excel概述

  this stored procedure can be used to insert the result set of theparticular select statement into excel file (c:\importtoexcel.xls,by default).you can pass the server name, user name, user password, the selectstatement to execute, and the file name to store the results set,as in the example below: exec exporttoexcel @server = '.',                   @uname = 'sa',                   @querytext = 'select au_fname from pubs..authors',                   @filename = 'c:\importtoexcel.xls'

/*version: sql server 7.0/2000created by: alexander chigrik- all about ms sql(sql server articles, faq, scripts, tips and test exams).

this stored procedure can be used to insert the result set of theparticular select statement into excel file (c:\importtoexcel.xls,by default).you can pass the server name, user name, user password, the selectstatement to execute, and the file name to store the results set,as in the example below:

exec exporttoexcel @server = '.',                   @uname = 'sa',                   @querytext = 'select au_fname from pubs..authors',                   @filename = 'c:\importtoexcel.xls'*/

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

create procedure exporttoexcel (  @server sysname = null,  @uname sysname = null,  @pwd sysname = null,  @querytext varchar(200) = null,  @filename varchar(200) = 'c:\importtoexcel.xls')asdeclare @sqlserver int,        @queryresults int,        @currentresultset int,        @object int,        @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

-- sets the server to the local serverif @server is null select @server = @@servername

-- sets the username to the current user nameif @uname is null select @uname = system_user

set nocount on

exec @hr = sp_oacreate 'sqldmo.sqlserver', @sqlserver outif @hr <> 0begin    print 'error create sqldmo.sqlserver'    returnend

--  connect to the sql serverif @pwd is null  begin    exec @hr = sp_oamethod @sqlserver, 'connect', null, @server, @uname    if @hr <> 0       begin         print 'error connect'         return       end  endelse  begin    exec @hr = sp_oamethod @sqlserver, 'connect', null, @server, @uname, @pwd    if @hr <> 0      begin        print 'error connect'        return      end  end

select @result_str = 'executewithresults("' + @querytext + '")'exec @hr = sp_oamethod @sqlserver, @result_str, @queryresults outif @hr <> 0begin    print 'error with method executewithresults'    returnend

exec @hr = sp_oamethod @queryresults, 'currentresultset', @currentresultset outif @hr <> 0begin    print 'error get currentresultset'    returnend

exec @hr = sp_oamethod @queryresults, 'columns', @columns outif @hr <> 0begin    print 'error get columns'    returnend

exec @hr = sp_oamethod @queryresults, 'rows', @rows outif @hr <> 0begin    print 'error get rows'    returnend

exec @hr = sp_oacreate 'excel.application', @object outif @hr <> 0begin    print 'error create excel.application'    returnend

exec @hr = sp_oagetproperty @object, 'workbooks', @workbooks outif @hr <> 0begin    print 'error create workbooks'    returnend

exec @hr = sp_oagetproperty @workbooks, 'add', @workbook outif @hr <> 0begin    print 'error with method add'    returnend

exec @hr = sp_oagetproperty @object, 'range("a1")', @range outif @hr <> 0begin    print 'error create range'    returnend

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

while (@indrow <= @rows)beginselect @indcolumn = 1

while (@indcolumn <= @columns)begin

exec @hr = sp_oamethod @queryresults, 'getcolumnstring', @result_str out, @indrow, @indcolumnif @hr <> 0begin    print 'error get getcolumnstring'    returnend

exec @hr = sp_oasetproperty @range, 'value', @result_strif @hr <> 0begin    print 'error set value'    returnend

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

select @indcolumn = @indcolumn + 1

end

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

end

select @result_str = 'exec master..xp_cmdshell ''del ' + @filename + ''', no_output'exec(@result_str)select @result_str = 'saveas("' + @filename + '")'exec @hr = sp_oamethod @workbook, @result_strif @hr <> 0begin    print 'error with method saveas'    returnend

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

exec @hr = sp_oadestroy @objectif @hr <> 0begin    print 'error destroy excel.application'    returnend

exec @hr = sp_oadestroy @sqlserverif @hr <> 0begin    print 'error destroy sqldmo.sqlserver'    returnendgo