Export to Excel from SQL Server


I have revisited this topic with the question to export multiple tables to different WorSheet in one Excel file. The OLE Automation script from  Robyn Page and Phil Factor
posted here:
http://www.simple-talk.com/sql/t-sql-programming/sql-server-excel-workbench/#first
With a reader addition to the final script, we can use the stored procedure to do this:
I reposted the script here with the modification. For full explaination, you should visit the above link.
 
 

spDMOExportToExcel @SourceServer=‘.’,

@QueryText

=mydb select * from MyaTable’,

@filename = ‘C:\Book2.xls’,

@WorksheetName

=‘MySheet2’,

–@WorksheetName=’MySheet1′,

@RangeName

=‘bbb’

—————————————————————————

—The stored procedure:

ALTER PROCEDURE [dbo].[spDMOExportToExcel] (

@SourceServer VARCHAR(30),

@SourceUID

VARCHAR(30)=NULL,

@SourcePWD

VARCHAR(30)=NULL,

@QueryText VARCHAR(200),

@filename

VARCHAR(100),

@WorksheetName

VARCHAR(100)=‘Worksheet’,

@RangeName

VARCHAR(80)=‘MyRangeName’

)

AS

DECLARE

@objServer INT,

@objQueryResults

INT,

@objCurrentResultSet

INT,

@objExcel

INT,

@objWorkBooks INT,

@objWorkBook

INT,

@objWorkSheet

INT,

@objRange

INT,

@hr

INT,

@Columns

INT,

@Rows INT,

@Output

INT,

@currentColumn

INT,

@currentRow

INT,

@ResultSetRow INT,

@off_Column

INT,

@off_Row

INT,

@command

VARCHAR(255),

@ColumnName

VARCHAR(255),

@value

VARCHAR(255),

@strErrorMessage VARCHAR(255),

@objErrorObject

INT,

@Alphabet VARCHAR(27)

SELECT

@Alphabet=‘ABCDEFGHIJKLMNOPQRSTUVWXYZ’

IF @QueryText IS NULL

BEGIN

RAISERROR (‘A query string is required for spDMOExportToExcel’,16,1)

RETURN 1

END

— Sets the server to the local server

IF @SourceServer IS NULL SELECT @SourceServer = @@servername

SET

NOCOUNT ON

SELECT

@strErrorMessage = ‘instantiating the DMO’,

@objErrorObject

=@objServer

EXEC

@hr= sp_OACreate ‘SQLDMO.SQLServer’, @objServer OUT

IF

@SourcePWD IS NULL OR @SourceUID IS NULL

BEGIN

–use a trusted connection

IF @hr=0 SELECT @strErrorMessage=

‘Setting login to windows authentication on ‘

+@SourceServer, @objErrorObject=@objServer

IF @hr=0 EXEC @hr=sp_OASetProperty @objServer, ‘LoginSecure’, 1

IF @hr=0 SELECT @strErrorMessage=

‘logging in to the requested server using windows authentication on ‘

+@SourceServer

IF @SourceUID IS NULL AND @hr=0 EXEC @hr=sp_OAMethod @objServer,

‘Connect’, NULL, @SourceServer

IF @SourceUID IS NOT NULL AND @hr=0

EXEC @hr=sp_OAMethod

@objServer

, ‘Connect’, NULL, @SourceServer ,@SourceUID

END

ELSE

BEGIN

IF @hr=0

SELECT @strErrorMessage = ‘Connecting to ”’+@SourceServer+

”’ with user ID ”’+@SourceUID+””,

@objErrorObject

=@objServer

IF @hr=0

EXEC @hr=sp_OAMethod @objServer, ‘Connect’, NULL,

@SourceServer

, @SourceUID, @SourcePWD

END

–now we execute the query

IF

@hr=0 SELECT @strErrorMessage=‘executing the query "’

+@querytext+‘", on ‘+@SourceServer,

@objErrorObject

=@objServer,

@command

= ‘ExecuteWithResults("’ + @QueryText + ‘")’

IF

@hr=0

EXEC @hr=sp_OAMethod @objServer, @command, @objQueryResults OUT

IF

@hr=0

SELECT @strErrorMessage=‘getting the first result set for "’

+@querytext+‘", on ‘+@SourceServer,

@objErrorObject

=@objQueryResults

IF

@hr=0 EXEC @hr=sp_OAMethod

@objQueryResults

, ‘CurrentResultSet’, @objCurrentResultSet OUT

IF

@hr=0

SELECT @strErrorMessage=‘getting the rows and columns "’

+@querytext+‘", on ‘+@SourceServer

IF @hr=0

EXEC @hr=sp_OAMethod @objQueryResults, ‘Columns’, @Columns OUT

IF

@hr=0

EXEC @hr=sp_OAMethod @objQueryResults, ‘Rows’, @Rows OUT

–so now we have the queryresults. We start up Excel

IF

@hr=0

SELECT @strErrorMessage=‘Creating the Excel Application, on ‘

+@SourceServer, @objErrorObject=@objExcel

IF

@hr=0

EXEC @hr=sp_OACreate ‘Excel.Application’, @objExcel OUT

IF

@hr=0 SELECT @strErrorMessage=‘Getting the WorkBooks object ‘

IF

@hr=0

EXEC @hr=sp_OAGetProperty @objExcel, ‘WorkBooks’,

@objWorkBooks

OUT

—-create a workbook

–IF @hr=0

— SELECT @strErrorMessage=’Adding a workbook ‘,

— @objErrorObject=@objWorkBooks

–IF @hr=0

— EXEC @hr=sp_OAGetProperty @objWorkBooks, ‘Add’, @objWorkBook OUT

/*modified*/

IF

@hr=0

EXEC @hr = sp_OASetProperty @objExcel, ‘DisplayAlerts’, ‘FALSE’

–create a workbook or append a workbook

DECLARE

@status int

EXEC

master..xp_fileexist @filename, @Status OUTPUT

IF

@status = 1

BEGIN

SELECT

@strErrorMessage=‘Opening a workbook ‘, @objErrorObject=@objWorkBooks

IF

@hr=0

SELECT @strErrorMessage=‘Opening the workbook as "’+@filename+‘"’, @objErrorObject=@objRange, @command = ‘Open("’ + @filename + ‘")’

IF

@hr=0

EXEC

@hr=sp_OAGetProperty @objWorkBooks, @command, @objWorkBook OUT

END

ELSE

BEGIN

IF

@hr=0

SELECT

@strErrorMessage=‘Adding a workbook ‘, @objErrorObject=@objWorkBooks

IF

@hr=0

EXEC

@hr=sp_OAGetProperty @objWorkBooks, ‘Add’, @objWorkBook OUT

END

/*modified*/

 

 

–and a worksheet

IF @hr=0

SELECT @strErrorMessage=‘Adding a worksheet ‘,

@objErrorObject

=@objWorkBook

IF @hr=0

EXEC @hr=sp_OAGetProperty @objWorkBook, ‘worksheets.Add’,

@objWorkSheet OUT

IF @hr=0

SELECT @strErrorMessage=‘Naming a worksheet as "’

+@WorksheetName+‘"’, @objErrorObject=@objWorkBook

IF

@hr=0

EXEC @hr=sp_OASetProperty @objWorkSheet, ‘name’, @WorksheetName

SELECT

@currentRow = 1

 

–so let’s write out the column headings

SELECT

@currentColumn = 1

WHILE

(@currentColumn <= @Columns AND @hr=0)

BEGIN

IF @hr=0

SELECT @strErrorMessage=‘getting column heading ‘

+LTRIM(STR(@currentcolumn)) ,

@objErrorObject

=@objQueryResults,

@Command

=‘ColumnName(‘

+CONVERT(VARCHAR(3),@currentColumn)+‘)’

IF @hr=0 EXEC @hr=sp_OAGetProperty @objQueryResults,

@command, @ColumnName OUT

IF @hr=0

SELECT @strErrorMessage=‘assigning the column heading ‘+

+ LTRIM(STR(@currentColumn))

+ ‘ from the query string’,

@objErrorObject=@objExcel,

@command

=‘Cells(‘+LTRIM(STR(@currentRow)) +‘, ‘

+ LTRIM(STR(@CurrentColumn))+‘).value’

IF @hr=0

EXEC @hr=sp_OASetProperty @objExcel, @command, @ColumnName

SELECT @currentColumn = @currentColumn + 1

END

–format the headings in Bold nicely

IF

@hr=0

SELECT @strErrorMessage=‘formatting the column headings in bold ‘,

@objErrorObject

=@objWorkSheet,

@command

=‘Range("A1:’

+SUBSTRING(@alphabet,@currentColumn/26,1)

+SUBSTRING(@alphabet,@currentColumn % 26,1)

+‘1’+‘").font.bold’

IF

@hr=0 EXEC @hr=sp_OASetProperty @objWorkSheet, @command, 1

–now we write out the data

SELECT

@currentRow = 2

WHILE

(@currentRow <= @Rows+1 AND @hr=0)

BEGIN

SELECT @currentColumn = 1

WHILE (@currentColumn <= @Columns AND @hr=0)

BEGIN

IF @hr=0

SELECT

@strErrorMessage

=

‘getting the value from the query string’

+ LTRIM(STR(@currentRow)) +‘,’

+ LTRIM(STR(@currentRow))+‘)’,

@objErrorObject

=@objQueryResults,

@ResultSetRow

=@CurrentRow1

IF @hr=0

EXEC @hr=sp_OAMethod @objQueryResults, ‘GetColumnString’,

@value

OUT, @ResultSetRow, @currentColumn

IF @hr=0

SELECT @strErrorMessage=

‘assigning the value from the query string’

+ LTRIM(STR(@CurrentRow1)) +‘, ‘

+ LTRIM(STR(@currentcolumn))+‘)’ ,

@objErrorObject

=@objExcel,

@command

=‘Cells(‘+STR(@currentRow) +‘, ‘

+ STR(@CurrentColumn)+‘).value’

IF @hr=0

EXEC @hr=sp_OASetProperty @objExcel, @command, @value

SELECT @currentColumn = @currentColumn + 1

END

SELECT @currentRow = @currentRow + 1

END

–define the name range

–Cells(1, 1).Resize(10, 5).Name = "TheData"

IF

@hr=0 SELECT @strErrorMessage=‘assigning a name to a range ‘

+ LTRIM(STR(@CurrentRow1)) +‘, ‘

+ LTRIM(STR(@currentcolumn1))+‘)’ ,

@objErrorObject

=@objExcel,

@command=‘Cells(1, 1).Resize(‘+STR(@currentRow1) +‘, ‘

+ STR(@CurrentColumn1)+‘).Name’

IF

@hr=0 EXEC @hr=sp_OASetProperty @objExcel, @command, @RangeName

–Now autofilt the columns we’ve written to

IF

@hr=0 SELECT @strErrorMessage=‘Auto-fit the columns ‘,

@objErrorObject

=@objWorkSheet,

@command=‘Columns("A:’

+SUBSTRING(@alphabet,(@Columns / 26),1)

+SUBSTRING(@alphabet,(@Columns % 26),1)+

‘").autofit’

IF

@hr=0 –insert into @bucket(bucket)

EXEC @hr=sp_OAMethod @objWorkSheet, @command, @output out

 

IF

@hr=0 SELECT @command =‘del "’ + @filename + ‘"’

IF @hr=0 EXECUTE master..xp_cmdshell @Command, no_output

IF

@hr=0

SELECT @strErrorMessage=‘Saving the workbook as "’+@filename+‘"’,

@objErrorObject=@objRange,

@command

= ‘SaveAs("’ + @filename + ‘")’

IF

@hr=0 EXEC @hr=sp_OAMethod @objWorkBook, @command

IF

@hr=0 SELECT @strErrorMessage=‘closing Excel ‘,

@objErrorObject=@objExcel

EXEC

@hr=sp_OAMethod @objWorkBook, ‘Close’

EXEC sp_OAMethod @objExcel, ‘Close’

IF

@hr<>0

BEGIN

DECLARE

@Source VARCHAR(255),

@Description

VARCHAR(255),

@Helpfile

VARCHAR(255),

@HelpID INT

EXECUTE sp_OAGetErrorInfo @objErrorObject,

@source

output,@Description output,

@Helpfile

output,@HelpID output

SELECT @hr, @source, @Description,@Helpfile,@HelpID output

SELECT @strErrorMessage=‘Error whilst ‘

+COALESCE(@strErrorMessage,‘doing something’)

+‘, ‘+COALESCE(@Description,)

RAISERROR (@strErrorMessage,16,1)

END

EXEC

sp_OADestroy @objServer

EXEC

sp_OADestroy @objQueryResults

EXEC

sp_OADestroy @objCurrentResultSet

EXEC

sp_OADestroy @objExcel

EXEC

sp_OADestroy @objWorkBooks

EXEC sp_OADestroy @objWorkBook

EXEC

sp_OADestroy @objRange

RETURN

@hr

GO

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s