sql导出为excel表 excel version: sql server 7.0/2000created by: alexander chigrikhttp://www.mssqlcity.com/ - 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'/*version: sql server 7.0/2000created by: alexander chigrikhttp://www.mssqlcity.com/ - 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 exporttoexcelgocreate 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_userset nocount onexec @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 endselect @result_str = 'executewithresults(' + @querytext + ')'exec @hr = sp_oamethod @sqlserver, @result_str, @queryresults outif @hr 0begin print 'error with method executewithresults' returnendexec @hr = sp_oamethod @queryresults, 'currentresultset', @currentresultset outif @hr 0begin print 'error get currentresultset' returnendexec @hr = sp_oamethod @queryresults, 'columns', @columns outif @hr 0begin print 'error get columns' returnendexec @hr = sp_oamethod @queryresults, 'rows', @rows outif @hr 0begin print 'error get rows' returnendexec @hr = sp_oacreate 'excel.application', @object outif @hr 0begin print 'error create excel.application' returnendexec @hr = sp_oagetproperty @object, 'workbooks', @workbooks outif @hr 0begin print 'error create workbooks' returnendexec @hr = sp_oagetproperty @workbooks, 'add', @workbook outif @hr 0begin print 'error with method add' returnendexec @hr = sp_oagetproperty @object, 'range(a1)', @range outif @hr 0begin print 'error create range' returnendselect @indrow = 1select @off_row = 0select @off_column = 1while (@indrow <= @rows)beginselect @indcolumn = 1while (@indcolumn <= @columns)beginexec @hr = sp_oamethod @queryresults, 'getcolumnstring', @result_str out, @indrow, @indcolumnif @hr 0begin print 'error get getcolumnstring' returnendexec @hr = sp_oasetproperty @range, 'value', @result_strif @hr 0begin print 'error set value' returnendexec @hr = sp_oagetproperty @range, 'offset', @range out, @off_row, @off_columnif @hr 0begin print 'error get offset' returnendselect @indcolumn = @indcolumn + 1endselect @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' returnendendselect @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' returnendexec @hr = sp_oamethod @workbook, 'close'if @hr 0begin print 'error with method close' returnendexec @hr = sp_oadestroy @objectif @hr 0begin print 'error destroy excel.application' returnendexec @hr = sp_oadestroy @sqlserverif @hr 0begin print 'error destroy sqldmo.sqlserver' returnendgo