SQL Server and Excel

Excel|server
/*
Stored Procedure name: Exporting data to Excel
Feature Description: Export data to 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 = NULL,
@filename varchar = ' D:\ImportToExcel.xls '
)
As
DECLARE @SQLServer int,--SQLDMO. SQL Server Objects
@QueryResults int,--queryresults object
@CurrentResultSet int,
@object int,--excel.application object
@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 ' query string '
Return
End

--Set server name to local server (@ @servername return to the local server name running SQL Server)
IF @server is NULL SELECT @server = @ @servername

--Sets the user name to the current system username (use System_user to return the current system user name)
IF @uname is NULL SELECT @uname = System_user

SET NOCOUNT on

--Create a Sqldmo.sqlserver object
EXEC @hr = sp_OACreate ' SQLDMO. SQL Server ', @SQLServer out
IF @hr <> 0
BEGIN
PRINT ' ERROR Create SQLDMO. SQL Server '
Return
End

--Connect to the SQL Server system
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

--Create a Excel.Application object
EXEC @hr = sp_OACreate ' Excel.Application ', @object out
IF @hr <> 0
BEGIN
PRINT ' ERROR Create Excel.Application '
Return
End

--Get an Excel workbook object
EXEC @hr = sp_OAGetProperty @object, ' workbooks ', @WorkBooks out
IF @hr <> 0
BEGIN
PRINT ' ERROR create workbooks '
Return
End

--Add a worksheet to the Workbook object
EXEC @hr = sp_OAGetProperty @WorkBooks, ' Add ', @WorkBook out
IF @hr <> 0
BEGIN
PRINT ' Error with method Add '
Return
End

--range object (A1 cell)
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, 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)--If a @filename file exists, delete first
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. SQL Server '
Return
End
Go