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!