Home > Scripts > Esportazione del result-set di una query su foglio MS Excel

Esportazione del result-set di una query su foglio MS Excel

Sergio Govoni 6 Marzo 2008 Scripts 8,043 Views

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 = */ ‘<Password>’, 
                       /*@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

*/

Tags

Chi è Sergio Govoni

Sergio Govoni è laureato in Scienze e Tecnologie Informatiche. Da oltre 16 anni lavora presso una software house che produce un noto sistema ERP, distribuito a livello nazionale ed internazionale, multi azienda client/server su piattaforma Win32. Attualmente si occupa di progettazione e analisi funzionale, coordina un team di sviluppo ed è responsabile tecnico di prodotto. Lavora con SQL Server dalla versione 7.0 e si è occupato d'implementazione e manutenzione di database relazionali in ambito gestionale, ottimizzazione delle prestazioni e problem solving. Nello staff di UGISS si dedica alla formazione e alla divulgazione in ambito SQL Server e tecnologie a esso collegate, scrivendo articoli e partecipando come speaker ai workshop e alle iniziative del primo e più importante User Group Italiano sulla tecnologia SQL Server. Ha conseguito la certificazione MCP, MCTS SQL Server. Per il suo contributo nelle comunità tecniche e per la condivisione della propria esperienza con altri, dal 2010 riceve il riconoscimento SQL Server MVP (Microsoft Most Valuable Professional). Nel corso dell'anno 2011 ha contribuito alla scrittura del libro SQL Server MVP Deep Dives Volume 2 (http://www.manning.com/delaney/).

Leggi Anche

Aggiornamento statistiche SQL per database che contengono External Table PolyBase

Recentemente, ho avuto l’occasione di configurare un piano di manutenzione database per un DB in …

User Group Italiano SQL Server - Via Del Capanno 26, Lodi - P.IVA: 03015800968
Tutti i contenuti (c) UGISS, 2024