/*
Stored Procedure name: export data to excel
Function Description: export data to excel
Exec exporttoexcel @ Server = '.',
@ Uname = 'sa ',
@ Pwd = '',
@ Querytext = 'select * From dldata .. bbbbbbbb ',
@ Filename = 'd:/importtoexcel.xls'
*/
If object_id ('porttoexcel ') 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 object
@ 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 the query string'
Return
End
-- Set the server name as local server (@ servername returns the name of the local server running SQL Server)
If @ server is null select @ server =servername
-- Set the user name to the current system user name (use system_user to return the current system user name)
If @ uname is null select @ uname = system_user
Set nocount on
-- Create an sqldmo. sqlserver object
Exec @ hR = sp_oacreate 'sqldmo. sqlserver ', @ sqlserver out
If @ HR <> 0
Begin
Print 'error create sqldmo. sqlserver'
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 connection'
Return
End
End
Else
Begin
Exec @ hR = sp_oamethod @ sqlserver, 'connect ', null, @ server, @ uname, @ pwd
If @ HR <> 0
Begin
Print 'error connection'
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, 'currentresulset', @ 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 an Excel. Application Object
Exec @ hR = sp_oacreate 'excel. application', @ object out
If @ HR <> 0
Begin
Print 'error create excel. application'
Return
End
-- Obtain an Excel Workbook object
Exec @ hR = sp_oagetproperty @ object, 'workbooks ', @ workbooks out
If @ HR <> 0
Begin
Print 'error create workbook'
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 (cell 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_mongoshell 'del '+ @ filename + ''', no_output'
Exec (@ result_str) -- if the @ filename file exists, delete it 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. sqlserver'
Return
End
Go