Nessun risultato. Prova con un altro termine.
Guide
Notizie
Software
Tutorial

Creare report in Excel esportando dati da SQL Server

Una tecnica veloce e potente per presentare al meglio i nostri dati
Una tecnica veloce e potente per presentare al meglio i nostri dati
Link copiato negli appunti

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:


    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

    Scriviamo il client in VBScript

    Ecco il codice del client, leggetelo con attenzione:


    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
    2. modificare la stringa di connessione sConnSQL

    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:

    Screenshot

    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!

Ti consigliamo anche