/ * Store Procedure Name: Export Data to Excel Features Description: Export Data to Excel
EXEC EXPORTTOEXCEL @server = '.', @Uname = 'sa', @PWD = '', @querytext = 'select * from dldata..bbbbb', @filename = 'd: /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) = 'd: /ImportToExcel.xls') ASDECLARE @SQLServer int, --SQLDMO.SQLServer objects @QueryResults int, --QueryResults objects @CurrentResultSet int, @object int, --Excel.Application objects @WorkBooks int, @WorkBook int, @Range int, @hr int, @Columns int, @Rows 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' returnend
- Set the server named local server (@@ servername Returns the local server name of SQL Server) if @server is null select @server = @@ servername
- Set the user name as the current system username (return to the current system user name using system_user) if @uname is null select @uname = system_user
Set nocount on
- Create a sqldmo.sqlserver object EXEC @hr = sp_oacreate 'sqldmo.sqlserver', @sqlserver outif @hr <> 0begin print 'Error Create SqldMo.sqlserVer' Returnend
- connecting to the SQL Server system IF @pwd IS NULLBEGIN EXEC @hr = sp_OAMethod @SQLServer, 'Connect', null, @server, @uname IF @hr <> 0 BEGIN PRINT 'error Connect' RETURN ENDENDELSEBEGIN EXEC @hr = sp_OAMethod @SQLServer, 'Connect', null, @server, @uname, @pwd IF @hr <> 0 BEGIN PRINT 'error Connect' RETURN ENDEND - The ExecuteWithResults method executes a Transact-SQL command batch --returning batch result sets in a QueryResults objectSELECT @result_str = 'ExecuteWithResults ( "' @QueryText '")' EXEC @hr = sp_OAMethod @SQLServer, @result_str, @QueryResults oUTIF @hr <> 0BEGIN PRINT 'error with method ExecuteWithResults' RETURNEND
--The CurrentResultSet property controls access to the result sets of a QueryResults objectEXEC @hr = sp_OAMethod @QueryResults, 'CurrentResultSet', @CurrentResultSet OUTIF @hr <> 0BEGIN PRINT 'error get CurrentResultSet' RETURNEND
--The Columns property exposes the number of columns contained --in the current result set of a QueryResults objectEXEC @hr = sp_OAMethod @QueryResults, 'Columns', @Columns OUTIF @hr <> 0BEGIN PRINT 'error get Columns' RETURNEND
--The Rows property returns the number of rows in a referenced - query result set or the number of rows existing in a tableEXEC @hr = sp_OAMethod @QueryResults, 'Rows', @Rows OUTIF @hr <> 0BEGIN PRINT' error get Rows' Returnend
- create the Excel.Application object EXEC @hr = sp_OACreate 'Excel.Application', @object OUTIF @hr <> 0BEGIN PRINT 'error create Excel.Application' RETURNEND-- get Excel Workbook object EXEC @hr = sp_OAGetProperty @object, 'Workbooks', @Workbooks outif @hr <> 0begin print 'error create workbooks' Returnend
- Add a worksheet in the workbook object EXEC @hr = sp_oagetproperty @Workbooks, 'add', @Workbook outif @hr <> 0begin print 'error with method add' Returnend
--RANGE object (A1 cell) 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) 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 outness @Range Out @ > 0 Begin Print 'Error Create Range' Return End
End
Select @Result_Str = 'exec master..xp_cmdshell' 'del' @FileName '', no_output'exec (@Result_str) - If there is a @FileName file, first delete 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
