Creare report in Excel esportando dati da SQL Server

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:

1. Produrre una query che peschi i dati opportuni e corretti dal database SQL
   Server
2. Scrivere un template in Excel vuoto con le formattazioni opportune che dovrà
   accettare i dati di riepilogo del report
3. 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

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:

SELECT
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 e a questa vista faremo riferimento per recuperare i dati che ci interessano. Diciamo di voler recuperare tramite questa vista i 10 ordini più ricchi effettuati dai clienti della Northwind Traders:

SELECT TOP 10 OrderID, ProductID, ProductName, ExtendedPrice
FROM [Order Details Extended]
ORDER BY ExtendedPrice DESC

Scriviamo il client in VBScript

Ecco il codice del client, leggetelo con attenzione:

Dim sAppPath, sFileReport, sYear, sMonth, sDay
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:

1. modificare il contenuto della variabile sAppPath mettendo il path fisico dove verranno storati i fogli Excel dei report
2. modificare la stringa di connessione sConnSQL con i parametri della vostra connessione a SQL Server.

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!