Esportazione del result-set di una query su foglio MS Excel
La stored procedure CS_SP_DMOExportToExcel rappresenta un esempio di utilizzo degli oggetti SQLDMO.SQLServer per esportare il risultato di una query su foglio Excel specificando il worksheet su cui trasferire i dati. Il worksheet specificato dal parametro @WorksheetIndex verrà “pulito” e ri-assegnato con i dati della query. Il file XLS esistente non verrà sovrascritto.
/* Stored Procedure CS_SP_DMOExportToExcel per esportazione dati su Excel */
IF (OBJECT_ID(‘CS_SP_DMOExportToExcel’, ‘P’) IS NOT NULL)
DROP PROCEDURE DBO.[CS_SP_DMOExportToExcel]
GO
/* Creazione stored procedure */
CREATE PROCEDURE [dbo].[CS_SP_DMOExportToExcel]
(@SourceServer AS VARCHAR(30),
@SourceUID AS VARCHAR(30)=NULL,
@SourcePWD AS VARCHAR(30)=NULL,
@QueryText AS VARCHAR(8000),
@filename AS VARCHAR(256),
@WorksheetName AS VARCHAR(100)=‘Worksheet’,
@WorksheetIndex AS INT,
@RangeName AS VARCHAR(80)=‘MyRangeName’,
@RowsAffected AS VARCHAR(512) OUTPUT)
AS BEGIN
/*
Descrizione : Stored procedure per esportazione dati da query T-SQL
a file Excel. Se il file Excel specificato non esiste
viene creato
Parametri : @SourceServer = Nome server
@SourceUID = Nome utente
@SourcePWD = Password
@QueryText = Query T-SQL di estrazione dei dati da esportare
@filename = Path e nome del file XLS
@WorksheetName = Nome del foglio in cui esportare i dati
@WorksheetIndex = Posizione del foglio (attenzione, il foglio
corrispondente alla posizione specificata verrà sovrascritto)
@RangeName = Nome range
@RowsAffected = Parametro di OUTPUT, restituisce il numero di righe estratte dalla
query @QueryText ed esportate sul foglio XLS
Sviluppata il : 04 Mar 2008
Ultimo agg.to : 23 Mar 2008
*/
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),
@T INT,
@FindFile INT, /* 0=False, -1=True */
@ColumnType INT
SELECT @Alphabet = ‘ABCDEFGHIJKLMNOPQRSTUVWXYZ’
SET @RowsAffected = '’
IF (@QueryText IS NULL)
BEGIN
RAISERROR (‘Errore: Non è stata specificata la query per la stored procedure CS_SP_DMOExportToExcel’, 16, 1)
RETURN 1
END
IF ((@RangeName = ‘’) OR
(@RangeName IS NULL))
BEGIN
RAISERROR (‘Errore: Non è stato specificato il nome del range. Parametro @RangeName per la stored procedure CS_SP_DMOExportToExcel’, 16, 1)
RETURN 2
END
-- Impostazione del server a local server
IF (@SourceServer IS NULL)
SELECT @SourceServer = @@servername
-- Disabilitazione messaggi
SET NOCOUNT ON
SELECT @strErrorMessage = ‘Istanza di DMO’,
@objErrorObject = @objServer
EXEC @hr = sp_OACreate ‘SQLDMO.SQLServer’, @objServer OUT
IF (@SourcePWD IS NULL) OR
(@SourceUID IS NULL)
BEGIN
-- Trusted connection
IF (@hr = 0)
SELECT @strErrorMessage = ‘Impostazione login con windows authentication su '
+@SourceServer,
@objErrorObject = @objServer
IF (@hr = 0)
EXEC @hr = sp_OASetProperty @objServer, ‘LoginSecure’, 1
IF (@hr = 0)
SELECT @strErrorMessage = ‘Login in corso al server richiesto, utilizzando windows authentication su '
+@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 = ‘Connessione a ‘’’+@SourceServer+
’’’ con user ID ‘’’+@SourceUID+’’’’,
@objErrorObject = @objServer
IF (@hr = 0)
EXEC @hr = sp_OAMethod @objServer,
‘Connect’,
NULL,
@SourceServer,
@SourceUID,
@SourcePWD
END
-- Esecuzione della query
IF (@hr = 0)
SELECT @strErrorMessage = ‘Esecuzione query “’
+@querytext+’”, su ‘+@SourceServer,
@objErrorObject = @objServer,
@command = ‘ExecuteWithResults("’ + @QueryText + ‘")’
IF (@hr = 0)
EXEC @hr = sp_OAMethod @objServer, @command, @objQueryResults OUT
IF (@hr = 0)
SELECT @strErrorMessage = ‘Restituzione del primo result set per “’
+@querytext+’”, su ‘+@SourceServer,
@objErrorObject = @objQueryResults
IF (@hr = 0)
EXEC @hr = sp_OAMethod @objQueryResults, ‘CurrentResultSet’, @objCurrentResultSet OUT
IF (@hr = 0)
SELECT @strErrorMessage = ‘Restituzione di righe e colonne “’
+@querytext+’”, su ‘+@SourceServer
IF (@hr = 0)
EXEC @hr = sp_OAMethod @objQueryResults, ‘Columns’, @Columns OUT
IF (@hr = 0)
EXEC @hr = sp_OAMethod @objQueryResults, ‘Rows’, @Rows OUT
-- Assegnazione parametro di output row(s) affected
IF (@hr = 0)
SET @RowsAffected = ‘(’ + LTRIM(RTRIM(STR(@Rows))) + ’ row(s) affected)’
-- Il risultato della query è disponibile. Start up della connessione a Excel
IF (@hr = 0)
SELECT @strErrorMessage = ‘Creazione dell’‘applicazione Excel su '
+@SourceServer,
@objErrorObject = @objExcel
IF (@hr = 0)
EXEC @hr = sp_OACreate ‘Excel.Application’, @objExcel OUT
IF (@hr = 0)
SELECT @strErrorMessage = ‘Restituzione dell’‘oggetto WorkBooks '
-- Apertura oggetto WorkBooks foglio Excel per sovrascrivere solo il
-- WorkSheet
IF (@hr = 0)
EXEC @hr = sp_OAMethod @objExcel,
‘WorkBooks.Open’,
@objWorkBooks OUT,
@filename
IF (@hr = 0)
BEGIN
-- Apertura WorkBooks Excel avvenuta correttamente
Set @FindFile = -1
-- Selezione WorkSheet passato come parametro
IF (@objWorkSheet <> 0)
EXEC @hr = sp_OADestroy @objWorkSheet
EXEC @hr = sp_OAMethod @objWorkBooks,
‘Worksheets.Item’,
@objWorkSheet OUT,
@WorksheetIndex
IF (@hr = 0)
PRINT (‘Selezione WorkSheet avvenuta correttamente’)
ELSE
PRINT (‘Selezione WorkSheet terminata con errori’)
-- Attivazione WorkSheet
IF (@hr = 0)
EXEC @hr = sp_OAMethod @objWorkSheet, ‘Activate’
IF (@hr = 0)
PRINT (‘Attivazione WorkSheet avvenuta correttamente’)
ELSE
PRINT (‘Attivazione WorkSheet terminata con errori’)
-- Eliminazione WorkSheet
IF (@hr = 0)
EXEC @hr = sp_OAMethod @objWorkSheet, ‘Cells.Clear’, @T OUT
/*EXEC @hr = sp_OAMethod @objWorkSheet, ‘Delete’, @T OUT*/
IF (@hr = 0)
PRINT (‘Pulizia celle WorkSheet avvenuta correttamente’)
ELSE
PRINT (‘Pulizia celle WorkSheet terminata con errori’)
END
ELSE BEGIN
-- Apertura WorkBooks fallita, il file Excel potrebbe non esistere
Set @FindFile = 0
-- Creazione oggetto WorkBooks
EXEC @hr = sp_OAGetProperty @objExcel, ‘WorkBooks’, @objWorkBooks OUT
-- Creazione workbook
IF (@hr = 0)
SELECT @strErrorMessage = ‘Aggiunta di un workbook ‘,
@objErrorObject = @objWorkBooks
IF (@hr = 0)
EXEC @hr = sp_OAGetProperty @objWorkBooks, ‘Add’, @objWorkBook OUT
-- Creazione worksheet
IF (@hr = 0)
SELECT @strErrorMessage = ‘Aggiunta di un worksheet ‘,
@objErrorObject = @objWorkBook
IF (@hr = 0)
EXEC @hr = sp_OAGetProperty @objWorkBook,
‘worksheets.Add’,
@objWorkSheet OUT
END
IF (@hr = 0)
SELECT @strErrorMessage = ‘Assegnazione nome worksheet con “’+@WorksheetName+’”’,
@objErrorObject = @objWorkBook
IF (@hr = 0)
EXEC @hr = sp_OASetProperty @objWorkSheet, ’name’, @WorksheetName
SELECT @currentRow = 1
-- Scrittura column headings
SELECT @currentColumn = 1
WHILE (@currentColumn <= @Columns AND @hr = 0)
BEGIN
IF (@hr = 0)
SELECT @strErrorMessage = ‘Restituzione 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 = ‘Assegnazione column heading ‘+
+ LTRIM(STR(@currentColumn))
+ ’ dalla query’,
@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
-- Modifica del formato per la prima riga (headings)
IF (@hr = 0)
SELECT @strErrorMessage = ‘Formattazione colonne con 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
-- Scrittura dei dati
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 = ‘Restituzione dei valori dalla query’
+ LTRIM(STR(@currentRow)) +’,’
+ LTRIM(STR(@currentRow)) +’)’,
@objErrorObject = @objQueryResults,
@ResultSetRow = @CurrentRow-1
-- Restituzione column type
IF (@hr = 0)
SELECT @strErrorMessage = ‘Restituzione column type ’ +LTRIM(STR(@currentcolumn)),
@objErrorObject = @objQueryResults,
@Command = ‘ColumnType(’ +CONVERT(VARCHAR(3),@currentColumn)+’)’
IF (@hr = 0)
EXEC @hr = sp_OAGetProperty @objQueryResults, @command, @ColumnType OUT
IF (@hr = 0)
BEGIN
IF (@ColumnType = 1)
EXEC @hr = sp_OAMethod @objQueryResults,
‘GetColumnString’,
@value OUT,
@ResultSetRow,
@currentColumn
ELSE IF ((@ColumnType = 6) OR
(@ColumnType = 7) OR
(@ColumnType = 8))
EXEC @hr = sp_OAMethod @objQueryResults,
‘GetColumnFloat’,
@value OUT,
@ResultSetRow,
@currentColumn
ELSE
EXEC @hr = sp_OAMethod @objQueryResults,
‘GetColumnString’,
@value OUT,
@ResultSetRow,
@currentColumn
END
IF (@hr = 0)
SELECT @strErrorMessage = ‘Assegnazione dei valori restituiti dalla query’
+ LTRIM(STR(@CurrentRow-1)) +’, '
+ 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
-- Definizione nome range
--Cells(1, 1).Resize(10, 5).Name = “TheData”
IF (@hr = 0)
SELECT @strErrorMessage = ‘Assegnazione del nome al range '
+ LTRIM(STR(@CurrentRow-1)) +’, '
+ LTRIM(STR(@currentcolumn-1)) +’)’ ,
@objErrorObject = @objExcel,
@command = ‘Cells(1, 1).Resize(’+ STR(@currentRow-1) +’, '
+ STR(@CurrentColumn-1) +’).Name’
IF (@hr = 0)
EXEC @hr = sp_OASetProperty @objExcel, @command, @RangeName
-- Auto-fit delle colonne scritte
IF (@hr = 0)
SELECT @strErrorMessage = ‘Auto-fit delle colonne ‘,
@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 (@FindFile = 0)
BEGIN
IF (@hr = 0)
SELECT @command = ‘del “’ + @filename + ‘”’
IF (@hr = 0)
EXECUTE master..xp_cmdshell @Command, no_output
IF (@hr = 0)
SELECT @strErrorMessage = ‘Salvataggio del workbook come “’+@filename+’”’,
@objErrorObject = @objRange,
@command = ‘SaveAs("’ + @filename + ‘")’
IF (@hr = 0)
EXEC @hr = sp_OAMethod @objWorkBook, @command
END
ELSE BEGIN
IF (@hr = 0)
EXEC @hr = sp_OAMethod @objExcel, ‘ActiveWorkbook.Save’
END
IF (@hr = 0)
SELECT @strErrorMessage = ‘Chiusura Excel ‘,
@objErrorObject = @objExcel
IF (@FindFile = 0)
EXEC @hr = sp_OAMethod @objWorkBook, ‘Close’
ELSE
EXEC @hr = sp_OAMethod @objExcel, ‘Workbooks.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
-- Libero la memoria allocata
EXEC sp_OADestroy @objServer
EXEC sp_OADestroy @objQueryResults
EXEC sp_OADestroy @objCurrentResultSet
EXEC sp_OADestroy @objExcel
EXEC sp_OADestroy @objWorkSheet
EXEC sp_OADestroy @objWorkBooks
EXEC sp_OADestroy @objWorkBook
EXEC sp_OADestroy @objRange
EXEC sp_OADestroy @objErrorObject
-- Abilitazione messaggi
SET NOCOUNT OFF
RETURN @hr
END
/*
-- Utilizzo con autenticazione SQL Server
DECLARE @ROWS VARCHAR(512)
CS_SP_DMOExportToExcel /*@SourceServer = */ ‘<Server_Name>’,
/*@SourceUID = */ ‘<User_Name>’,
/*@SourcePWD = */ ‘
/*@QueryText = */ ‘USE DBTEST Select Titolo From Articoli’,
/*@Filename = */ ‘C:\TitoliTest.xls’,
/*@WorksheetName = */ ‘MyTitoli’,
/*@WorksheetIndex = */ 1,
/*@RangeName = */ ‘MyRangeTitoli’,
@Rows OUTPUT,
-- Utilizzo con integrated security
DECLARE @ROWS VARCHAR(512)
CS_SP_DMOExportToExcel /*@SourceServer = */ ‘<Server_Name>’,
/*@QueryText = */ ‘USE DBTEST Select Titolo From Articoli’,
/*@filename = */ ‘C:\TitoliTest.xls’,
/*@WorksheetName = */ ‘MyTitoli’,
/*@WorksheetIndex = */ 1,
/*@RangeName = */ ‘MyRangeTitoli’,
@Rows OUTPUT
*/
