Home > Scripts > Aggiornare una tabella pivot di Microsoft Excel

Aggiornare una tabella pivot di Microsoft Excel

L’aggiornamento dei dati di una tabella pivot in Microsoft Excel, viene tipicamente configurato per essere eseguito in modo automatico all’apertura del documento stesso. In alcune situazioni, però, potrebbe essere richiesto un aggiornamento automatico ogni X minuti oppure al verificarsi di un determinato evento senza dover necessariamente aprire il file Excel che contiene la tabella pivot.

La stored procedure dbo.USP_DMO_Excel_Pivot_RefreshTable, definita di seguito, utilizza OLE Automation Stored Procedures per aggiornare i dati esposti su una tabella pivot di un determinato file Excel.

/* drop if exists */
if object_id(‘USP_DMO_Excel_Pivot_RefreshTable’) is not null
  drop procedure [dbo].USP_DMO_Excel_Pivot_RefreshTable;
go

/* Create stored procedure */
create procedure [dbo].USP_DMO_Excel_Pivot_RefreshTable
(@FileName varchar(512),
 @WorksheetIndex int,
 @PivotTableName varchar(100),
 @Password nvarchar(64),
 @WriteResPassword nvarchar(64),
 @Debug tinyint)
as
begin
  /*
    Descrizione:

      Refresh dei dati esposti su un foglio di lavoro MS Excel
      (refresh pivot table and query table)

    Parametri:

      – @FileName:
          Path e nome del file excel (es. e:\temp\refresh.xls)

      – @WorksheetIndex:
          Posizione del foglio che espone i dati da aggiornare.
          Può essere nullo o maggiore di zero, se nullo saranno
          aggiornati tutti i fogli della cartella di lavoro

      – @PivotTableName:
          Nome tabella pivot. Se non specificato oppure nullo,
          viene eseguito il refresh di tutte le tabelle pivot
          presenti nel foglio di lavoro

      – @Password:
          Specifica una stringa contenente la password richiesta
          per l’apertura di una cartella di lavoro protetta

      – @WriteResPassword:
          Specifica una stringa contenente la password richiesta
          per la scrittura di una cartella di lavoro a scrittura riservata

      – @Debug:
          Modalità di debug
          Può assumere i valori: 1= Debug ON, 0= Debug OFF
  */

  — Controllo parametri di input
  declare @msg varchar(256)

  — @FileName
  if ((@FileName = ”) or
      (@FileName is null))
  begin
    set @msg = object_name(@@procid) +
               ‘: Non è stato specificato il nome del file Excel.’
    raiserror(@msg, 16, 1)
    return
  end

  — @WorksheetIndex
  if (@WorksheetIndex <= 0)
  begin
    set @msg = object_name(@@procid) +
               ‘: L”indice (numerico) relativo al foglio di lavoro ‘ +
               ‘sul file Excel non è valido. ‘ +
               ‘Il valore di @WorksheetIndex è: ‘ + ltrim(str(@WorksheetIndex))
    raiserror(@msg, 16, 1)
    return
  end

  — @debug
  if (@debug not in (0, 1))
  begin
    set @msg = object_name(@@procid) +
               ‘: Modalità di debug non supportata, ‘ +
               ‘@debug può assumere i seguenti valori: ‘ +
               ‘1= Debug ON, 0= Debug OFF. ‘ +
               ‘Il valore di @debug è: ‘ + ltrim(str(@debug))
    raiserror(@msg, 16, 1)
    return
  end

  declare @hr int,
          @objExcel int,
          @objWorkBooks int,
          @objWorkSheet int,
          @WorksheetsCount int,
          @i int,
          @j int,
          @strErrorMessage varchar(255),
          @FindFile int,
          @objErrorObject int,
          @cmd varchar(128),
          @PivotTablesCount int,
          @QueryTablesCount int,
          @tmpPivotTableName varchar(100)
 
  — Disabilitazione messaggi
  set nocount on

  exec @hr = sp_OACreate ‘Excel.Application’, @objExcel output
 
  if (@hr = 0)
    select @strErrorMessage = ‘Restituzione dell”oggetto WorkBooks ‘

  — Apertura oggetto WorkBooks relativo al foglio Excel
  if (@hr = 0)
  begin
    if ((@Password <> ”) or (@WriteResPassword <> ”))
    begin
      — Apertura con password
      if (@debug = 1)
        print(‘Apertura della cartella di lavoro ‘ + @FileName + ‘ con password… ‘)

      exec @hr = sp_OAMethod
                   @objExcel,
                   ‘WorkBooks.Open’,
                   @objWorkBooks output,                 
                   @FileName,
                   Null,
                   Null,
                   Null,
                   @Password,
                   @WriteResPassword
    end
    else begin
      — Apertura senza password
      exec @hr = sp_OAMethod @objExcel, ‘WorkBooks.Open’, @objWorkBooks output, @FileName
    end
  end

  if (@hr <> 0)
  begin
    set @msg = object_name(@@procid) +
               ‘: Si è verificato un errore durante l”apertura della ‘ +
               ‘cartella di lavoro protetta da password. Controllare ‘ +
               ‘la correttezza dei parametri di input ‘ +
               ‘(@Password e @WriteResPassword).’
    print(@msg)
  end
  else begin
    if (@debug = 1)
      print(‘Apertura della cartella di lavoro ‘ + @FileName + ‘ avvenuta correttamente’)
  end

  if (@hr = 0)
  begin
    — Apertura WorkBooks Excel avvenuta correttamente
    set @FindFile = -1
      
    if (@WorksheetIndex > 0) and
       (@WorksheetIndex is not null)
    begin
      — Selezione WorkSheet passato come parametro
      set @i = @WorksheetIndex
      set @WorksheetsCount = @WorksheetIndex
    end
    else begin
      set @i = 1
      exec @hr = sp_OAMethod @objWorkBooks, ‘Worksheets.Count’, @WorksheetsCount output

      if (@debug = 1)
        if (@hr = 0)
        begin
          print(”)
          print(‘@WorkSheetCount= ‘ + ltrim(str(@WorksheetsCount)))
        end
        else
          print(‘Conteggio Worksheets terminato con errori, @WorkSheetCount= ‘ + ltrim(str(@WorksheetsCount)))
    end

    while (@hr = 0) and
          (@i <= @WorksheetsCount)
    begin
      exec @hr = sp_OAMethod @objWorkBooks, ‘Worksheets.Item’, @objWorkSheet output, @i

      if (@debug = 1)
        if (@hr = 0)
        begin
          print(”)
          print(‘Selezione WorkSheet ‘ + ltrim(str(@i)) + ‘ avvenuta correttamente’)
        end
        else begin
          print(”)
          print(‘Selezione WorkSheet ‘ + ltrim(str(@i)) + ‘ terminata con errori’)
        end

      — Attivazione WorkSheet
      if (@hr = 0)
        exec @hr = sp_OAMethod @objWorkSheet, ‘Activate’

      if (@debug = 1)
        if (@hr = 0)
          print(‘Attivazione WorkSheet ‘ + ltrim(str(@i)) + ‘ avvenuta correttamente’)
        else
          print(‘Attivazione WorkSheet ‘ + ltrim(str(@i)) + ‘ terminata con errori’)

      — Refresh PivotTables
      if (@hr = 0)
      begin
        if (ltrim(rtrim(@PivotTableName)) = ”)
        begin
          exec @hr = sp_OAMethod @objWorkSheet, ‘PivotTables.Count’, @PivotTablesCount output

          if (@debug = 1)
            if (@hr = 0)
              print(‘@PivotTablesCount = ‘ + ltrim(str(@PivotTablesCount)) + ‘ su WorkSheet ‘ + ltrim(str(@i)))

          set @j = 1

          while ((@hr = 0) and (@j <= @PivotTablesCount))
          begin
            if (@debug = 1)
            begin
              set @cmd = ‘PivotTables(‘ + ltrim(str(@j)) + ‘).Name’
              exec @hr = sp_OAMethod @objWorkSheet, @cmd, @tmpPivotTableName output
              if (@hr = 0)
                print(‘Aggiornamento PivotTables = "’ + @tmpPivotTableName + ‘"’)
            end

            if (@hr = 0)
            begin
              set @cmd = ‘PivotTables(‘ + ltrim(str(@j)) + ‘).RefreshTable’
              exec @hr = sp_OAMethod @objWorkSheet, @cmd
            end

            if (@hr = 0)
            begin
              set @cmd = ‘PivotTables(‘ + ltrim(str(@j)) + ‘).SaveData’
              exec @hr = sp_OAMethod @objWorkSheet, @cmd
            end

            if (@debug = 1)
              if (@hr = 0)
                print(‘Aggiornamento PivotTables(‘ + ltrim(str(@j)) + ‘) avvenuto correttamente’)
              else
                print(‘Aggiornamento PivotTables(‘ + ltrim(str(@j)) + ‘) terminato con errori: ‘ + str(@hr))

            set @j = (@j + 1)
          end  — while
        end
        else begin   
          if (@debug = 1) 
            if (@hr = 0)
              print(‘Aggiornamento PivotTables = ‘ + @PivotTableName)

          if (@hr = 0)
          begin
            set @cmd = ‘PivotTables("’ + @PivotTableName + ‘").RefreshTable’
            exec @hr = sp_OAMethod @objWorkSheet, @cmd
          end

          if (@hr = 0)
          begin
            set @cmd = ‘PivotTables("’ + @PivotTableName + ‘").SaveData’
            exec @hr = sp_OAMethod @objWorkSheet, @cmd
          end

          if (@debug = 1)
            if (@hr = 0)
              print(‘Aggiornamento PivotTables("’ + @PivotTableName + ‘") avvenuto correttamente’)
            else
              print(‘Aggiornamento PivotTables("’ + @PivotTableName + ‘") terminato con errori: ‘ + str(@hr))
        end
      end

      — Refresh QueryTables
      if (@hr = 0)
      begin
        exec @hr = sp_OAMethod @objWorkSheet, ‘QueryTables.Count’, @QueryTablesCount output

        if (@debug = 1)
          if (@hr = 0)
            print(‘@QueryTablesCount = ‘ + ltrim(str(@QueryTablesCount)) + ‘ su WorkSheet ‘ + ltrim(str(@i)))

        set @j = 1

        while ((@hr = 0) and (@j <= @QueryTablesCount))
        begin
          if (@debug = 1)
          begin
            if (@hr = 0)
              print(‘Aggiornamento QueryTables.Item(‘ + ltrim(str(@j)) + ‘)’)
          end

          if (@hr = 0)
          begin
            set @cmd = ‘QueryTables.Item(‘ + ltrim(str(@j)) + ‘).Refresh’
            exec @hr = sp_OAMethod @objWorkSheet, @cmd
          end

          if (@debug = 1)
            if (@hr = 0)
              print(‘Salvataggio QueryTables.Item(‘ + ltrim(str(@j)) + ‘)’)

          if (@hr = 0)
          begin
            set @cmd = ‘QueryTables.Item(‘ + ltrim(str(@j)) + ‘).SaveData’
            exec @hr = sp_OAMethod @objWorkSheet, @cmd
          end

          if (@debug = 1)
            if (@hr = 0)
              print(‘Aggiornamento QueryTables.Item(‘ + ltrim(str(@j)) + ‘) avvenuto correttamente’)
            else
              print(‘Aggiornamento QueryTables.Item(‘ + ltrim(str(@j)) + ‘) terminato con errori: ‘ + str(@hr))

          set @j = (@j + 1)
        end  — while
      end

      set @i = (@i + 1)

    end — while

  end
  else
    set @FindFile = 0

  if (@hr = 0) and
     (@FindFile = -1)
  begin
    exec @hr = sp_OAMethod @objExcel, ‘Application.ActiveWorkbook.Save’, NULL

    if (@debug = 1)
    begin
      print(”)
      if (@hr = 0)   
        print(‘Salvataggio Workbook avvenuto correttamente’)
      else
        print(‘Salvataggio Workbook terminato con errori: ‘ + str(@hr))
    end

    if (@hr = 0)
      exec @hr= sp_OAMethod @objExcel, ‘Quit’

    if (@debug = 1) 
      if (@hr = 0)
        print(‘Uscita da Excel avvenuta correttamente’)
      else
        print(‘Uscita da Excel terminata con errori: ‘ + str(@hr))
  end
  
  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, ”)

    exec @hr = sp_OAMethod @objExcel, ‘Quit’
    exec sp_OADestroy @objExcel

    raiserror (@strErrorMessage, 16, 1)
  end

  — Libero la memoria allocata
  –exec sp_OADestroy @objWorkSheet
  –exec sp_OADestroy @objWorkBooks
  — sp_OADestroy commentata in modo esplicito
  –exec sp_OADestroy @objExcel
  –exec sp_OADestroy @objErrorObject

  — Abilitazione messaggi
  set nocount off
  return @hr 
end;

By default, SQL Server blocca l’accesso alle stored procedure OLE Automation (sp_OA*) perché questo componente è di default disabilitato per motivi di sicurezza. E’ tuttavia possibile abilitare l’accesso alle stored procedure OLE Automation utilizzando sp_configure per modificare l’opzione OLE Automation Procedures.

L’opzione OLE Automation Procedures specifica se può essere creata (oppure no) un’istanza di un oggetto OLE Automation all’interno di un batch T-SQL. Per visualizzare lo stato corrente di questa opzione è sufficiente eseguire:

sp_configure ‘Ole Automation Procedures’;

go

reconfigure;

go

Per abilitare l’accesso alle stored procedure OLE Automation è sufficiente eseguire:

sp_configure ‘Ole Automation Procedures’, 1;

go

reconfigure;

go

Esempio

Per aggiornare la tabella pivot "PT1" sul foglio di lavoro con indice 1 relativo al file Excel C:\Temp\Refresh.xls (protetto da password in apertura e scrittura), sarà sufficiente eseguire:

exec dbo.USP_DMO_Excel_Pivot_RefreshTable
  @FileName = ‘c:\temp\Refresh.xls’,
  @WorksheetIndex = 1,
  @PivotTableName = ‘PT1’,
  @Password = ‘openpwd’,
  @WriteResPassword = ‘writepwd’,
  @Debug = 1;

Risultato

Apertura della cartella di lavoro c:\temp\Refresh.xls con password…
Apertura della cartella di lavoro c:\temp\Refresh.xls avvenuta correttamente
 
@WorkSheetCount= 2
 
Selezione WorkSheet 1 avvenuta correttamente
Attivazione WorkSheet 1 avvenuta correttamente
@PivotTablesCount = 2 su WorkSheet 1
Aggiornamento PivotTables = "Tabella_pivot1"
Aggiornamento PivotTables(1) avvenuto correttamente
Aggiornamento PivotTables = "PT1"
Aggiornamento PivotTables(2) avvenuto correttamente
@QueryTablesCount = 0 su WorkSheet 1
 
Selezione WorkSheet 2 avvenuta correttamente
Attivazione WorkSheet 2 avvenuta correttamente
@PivotTablesCount = 2 su WorkSheet 2
Aggiornamento PivotTables = "PT1"
Aggiornamento PivotTables(1) avvenuto correttamente
Aggiornamento PivotTables = "Tabella_pivot1"
Aggiornamento PivotTables(2) avvenuto correttamente
@QueryTablesCount = 0 su WorkSheet 2
 
Salvataggio Workbook avvenuto correttamente
Uscita da Excel avvenuta correttamente

Articoli correlati

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

sp_alter_column – La stored procedure per modificare una colonna!

Sarà successo anche a voi di dover modificare il tipo di dato di una colonna …