UGISS User Group Italiano SQL Server
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 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
