Produrre report partendo da viste di dati presenti su SQL Server è sicuramente una delle richieste più tipiche che vengono fatte ad uno sviluppatore di database. Certamente i report che produciamo non potranno essere presentati su semplici file di testo o su una interfaccia ostica ed oscura come quella del query analyzer.
Questo perché i report finiscono poi nelle mani di altri settori dell'azienda
come il marketing o le vendite e quindi devono essere leggibili e fruibili anche
ad utenti non esperti di SQL o di programmazione in genere. Oltre a questo sui
report il più delle volte vengono fatte analisi, calcoli, elaborazioni ed altro
ancora necessari a trarre conclusioni più o meno azzeccate sui destini dell'azienda...
Alla luce di queste considerazioni il formato migliore con cui produrre i nostri report è sicuramente il classico foglio Excel!
Nel nostro caso esporteremo dei dati di esempio dal database di prova Nothwind
fornito con SQL Server su un foglio EXCEL.
Il client per gestire l'esportazione sarà scritto in VBScripting (suffisso *.vbs) utilizzando ADO per la stabilire le connessioni con SQL Server e gestendo l'oggetto Excel per la creazione ed il salvataggio del foglio di lavoro.
Analizziamo ora i passi necessari da compiere:
- Produrre una query che peschi i dati opportuni e corretti dal database SQL
Server - Scrivere un template in Excel vuoto con le formattazioni opportune che dovrà
accettare i dati di riepilogo del report - Scrivere il client in VBScript che dovrà eseguire i seguenti compiti:a - Creare ex-novo un foglio di Excel b - Connettersi al database Northwind
c - Eseguire la query per recuperare i dati che alimenteranno i report
d - Connettersi al foglio di Excel appena creato
e - Esportare i dati della query all'interno del foglio
f - Chiudere gli oggetti istanziati e rilasciare la memoria occupata
Consideriamo però con attenzione le limitazioni di un foglio Excel:
- Dimensione massima del foglio di lavoro: 65.536 righe per 256 colonne
- Contenuto delle celle (in testo): 32.767Â caratteri al massimo
- modificare il contenuto della variabile sAppPath
- modificare la stringa di connessione sConnSQL
Vi ricordo di scaricare sempre la versione più aggiornata dell'engine di
accesso ai dati Microsoft Jet 4.0, per evitare problemi ed inconvenienti.
Scriviamo la query
La query che scriveremo è già fortunatamente presente nel database Nothwind,
eccone il contenuto:
dbo.[Order Details].OrderID,
dbo.[Order Details].ProductID,
dbo.Products.ProductName,
dbo.[Order Details].UnitPrice,
dbo.[Order Details].Quantity,
dbo.[Order Details].Discount,
CONVERT(money, (dbo.[Order Details].UnitPrice * dbo.[Order Details].Quantity) * (1 - dbo.[Order Details].Discount) / 100) * 100 AS ExtendedPrice
FROM dbo.Products
INNER JOIN
dbo.[Order Details] ON dbo.Products.ProductID = dbo.[Order Details].ProductID
Questa query per comodità è stata salvata in una Vista dal nome Order Details Extended
ricchi
Ecco il codice del client, leggetelo con attenzione:Scriviamo il client in VBScript
Dim sConnSQL, cnSQL, sQuerySQL
sQuerySQL = "SELECT TOP 20 "
sQuerySQL = sQuerySQL & "OrderID, ProductID, ProductName, ExtendedPrice
"
sQuerySQL = sQuerySQL & "FROM Northwind.dbo.[Order Details Extended]"
sQuerySQL = sQuerySQL & "ORDER BY ExtendedPrice DESC "
sYear = cstr(year(date))
if len(cstr(Month(date))) = 1 then sMonth = "0" & cstr(Month(date))
if len(cstr(Day(date))) = 1 then sDay = "0" & cstr(Day(date))
sAppPath = "<a href="file:///D:/Documenti/Articoli%20per%20il%20web/Html/Art16/">D:DocumentiArticoli per il webHtmlArt16</a>"
sFileReport = sAppPath & "report_"
sFileReport = sFileReport & sYear & sMonth & sDay
sFileReport = sFileReport & ".xls"
sConnSQL = "Provider=SQLOLEDB.1;User ID=sa;Password=;Initial Catalog=Northwind;Data
Source=FULCANELLI;Language=Italian"
'Chiamo le routines dell'applicazione
Call ApriConnSQL() 'Connessione
Call CreaFoglioExcel() 'Creazione del foglio e copia dei dati
Call ReleaseMemory() 'Rilascio la memoria allocata ed esco
'Definisco le routines dell'applicazione
Sub ApriConnSQL()
 Set cnSQL = CreateObject("ADODB.Connection")
cnSQL.Open sConnSQL
end sub
Sub ReleaseMemory()
If cnSQL.State = 1 Then cnSQL.close()
 Set cnSQL = nothing
End Sub
Sub CreaFoglioExcel()
On Error Resume Next
Dim oExcelApplication, oExcelBook, oExcelSheet, oFSO
 Dim aData, rs
 Set oFSO = CreateObject("Scripting.FileSystemObject")
if oFSO.FileExists( sFileReport ) then
oFSO.DeleteFile sFileReport
 end if
 Set oFSO = Nothing
 Set oExcelApplication = CreateObject("Excel.Application")
oExcelApplication.visible = False
If Err Then
Err.Clear
On Error GoTo 0
Exit Sub
 End If
 Set oExcelBook = oExcelApplication.Workbooks.Add
 Set oExcelSheet = oExcelBook.Worksheets(1)
 'Creo le intestazioni del foglio excel
 oExcelSheet.Range("A1").Value = "ID Ordine"
oExcelSheet.Range("B1").Value = "ID Prodotto"
 oExcelSheet.Range("C1").Value = "Nome del prodotto"
 oExcelSheet.Range("D1").Value = "Totale dell'ordine"
oExcelSheet.Range("A1:D1").Font.Bold = True
Set rs = CreateObject("ADODB.Recordset")
rs.Open sQuerySQL , cnsql 'copio l'intero recordset all'interno dell'intervallo
 'prestabilito con il metodo CopyFromRecordset, comodo no!
 oExcelSheet.Range("A2").CopyFromRecordset rs
 Set rs = nothing
 Set oExcelSheet = nothing
 'Salvo
oExcelBook.SaveAs( sFileReport )
 Set oExcelBook = Nothing
oExcelApplication.Quit
 Set oExcelApplication = Nothing
 On Error GoTo 0
End Sub
Per veder girare questo script VB dovete fare due piccole modifche:
Ovviamente avere installato EXCEL sul PC in cui farete girare lo script!
Questo esempio è ampiamente personalizzabile, difatti se avete ACCESS al posto di SQL Server basterà cambiare la stringa di connessione mettendo quella opportuna.
Noterete che formato del file Excel creato è del tipo suffisso_annomesegiorno.xls, l'idea è molto utile per tenere traccia giornaliera dei report e quindi in un futuro avere a disposizione uno storico dei documenti creati! L'anteprima del risultato sarà simile a questa:
Per scaricare il codice allegato cliccate qui!!!
Tocco finale!
Come ciliegina sulla torta potrebbe essere interessante schedulare questo script VB dal Task Scheduler di NT, così potrete avere ad intervalli regolari e programmati i report più utili da fornire al reparto marketing dell'azienda. Nel prossimo articolo vedremo come manipolare il foglio di excel che abbiamo creato per aggiungere formattazioni, celle calcolate ed inoltre inviare via email il report appena creato al Boss!
