VB.NET: Popolare File Excel tramite Data Source (it-IT)
Obiettivo
In questo articolo vedremo come utilizzare Visual Basic .NET, assieme alle Excel Interops, per connetterci ad una sorgente dati da cui estrarre informazioni, le quali verranno poi scritte su un modello Excel preesistente. In altre parole, considereremo come sia possibile sviluppare un semplice software di reporting su file Excel.
Introduzione
Recentemente mi sono trovato nella necessità di connettere un programma di precedente realizzazione ad una serie gerarchica piuttosto complessa di file Excel pre-esistenti. Ciò che mi era richiesto era di rendere il mio software capace di compilare della reportistica sulla base di tali Excel, mantenendo cioè inalterato il vecchio modello di stampa in uso (e facilitare l'utenza finale, che avrebbe quindi continuato ad utilizzare tali files come sempre). Tecnicamente, il compito era chiaro fin da subito: dato che la mia applicazione si interfacciava con SQL Server, dovevo trovare un modo per estrarre determinati record e scriverli sui file che mi venivano passati. In più, tali files dovevano poi essere vagliati tramite un processo di revisione, in cui l'utente finale doveva poter scegliere di alterare il contenuto del file stesso, ad esempio decidendo che da un dato momento in poi, ciò che veniva scritto nella cella A1 dovesse essere emesso sulla C14. Di conseguenza, il programma che avrei dovuto scrivere avrebbe avuto necessità di una sorta di "mappa", per capire quali valori scrivere in quali caselle (permettendo quindi una rapida adattabilità in caso di situazioni modificate).
Presento in questa sede una versione semplificata di ciò di cui sto parlando: penso possa essere utile come esempio introduttivo all'interproceduralità, disponendo di un software centrale che gestisce una sessione verso SQL Server, redigendo file Excel secondo un modello predisposto in un ulteriore file di parametrizzazione. Spero possa essere utile a chi legge.
Per fare in modo che il tutto sia il più riutilizzabile possibile, creeramo un progetto stand-alone che possa eseguire query SQL per conto proprio. Pensiamola a questo modo: se disponiamo di un programma che possa eseguire un'applicazione esterna, passando ad essa alcuni parametri di avvio, allora potremo utilizzare il nostro progetto come una sorta di "stampante Excel", che potrà essere lanciata da altri programmi unitamente ad alcuni switch.
Prerequisiti
- Windows 7 o successivo
- Visual Studio 2013 Community Edition o successivo
- Office 2010 (la versione usata nell'articolo: con altre versioni, l'Interop referenziato potrebbe cambiare)
Passo 1: Creare un nuovo progetto, aggiungere referenze
Creiamo un nuovo progetto in Visual Basic, selezionando Windows Form. Poi, andiamo su Project » Add Reference, e selezioniamo Microsoft Excel e Microsoft Office 14.0 Object Libraries. Questo passaggio ci fornirà gli strumenti per poter interagire con i nostri modelli Excel. Nel nostro form principale, avremo necessità di specificare la seguente direttiva di import:
Imports Microsoft.Office.Interop
Ciò renderà più agevole referenziare gli oggetti Office che utilizzeremo più tardi.
Realisticamente, possiamo pensare che la nostra connessione al database, una volta impostata, rimanga sempre la stessa per tutta la durata dell'esecuzione, e la stessa cosa possiamo dire di un eventuale percorso fisico in cui risiederanno i modelli Excel. Possiamo allora definire due variabili Settings a livello Application, in modo da salvare tali parametri nel file di configurazione dell'applicazione, per accedervi poi attraverso il namespace My.Settings.
Passo 2: Creare un file di parametri adatto alle finalità
Il nostro programma ha sicuramente bisogno di una sorta di file di regole, qualcosa a cui accedere per capire quali query vadano eseguite, quali sono le celle Excel a cui accedere (ed i campi specifici che dovranno essere scritti in essi). Inoltre, potremmo voler specificare la tipologia del dato in scrittura: ad esempio, nel caso desiderassimo incollare immagini nel file Excel, dovremo disporre di un parametro che l'applicazione utilizzerà per capire se il dato letto è una semplice stringa, o se non rappresenta invece un percorso verso un file immagine, da trattare quindi diversamente.
Per maggiore semplicità, ho scelto di usare a questo proposito un file CSV, che processeremo per estrarre quanto necessario. Ecco l'esempio, di cui vedremo i dettagli a seguire.
Query# SELECT 'TEXTCODE' + '?' AS Code, 'This is a sample' AS Descr
C4 # Code #TEXT
C5 # Descr #TEXT
H4 # c:\tmp\sample.jpg # IMAGE
Come si nota, ogni riga è introdotta da un testo fisso. Nel caso della prima linea, utilizzeremo la stringa "Query" come una specie di nome di funzione, in modo che - incontrandola - il nostro programma possa capire che ciò che segue è il testo di una query, da lanciare sulla base di una data fonte dati (e vedremo come). Ignoriamo per ora il carattere '?', e concentriamoci solo sulla struttura del file. Le linee dalla seconda in poi sono introdotte dall'indicazione della cella / nome cella, così come appare sul modello Excel (quindi, C4, C5, H4, sono celle realmente presenti a priori). Per ciascuna linea, ho utilizzato il carattere '#' come separatore. Per la prima linea, il secondo campo di linea sarà la quey da eseguire, mentre per le successive il parametro due rappresenta il nome di campo, ottenuto dalla query, da scrivere in quella particolare cella. Un terzo campo identifica la tipologia di dato: TEXT per una normale stringa, IMAGE per un percorso fisico da cui leggere un'immagine (e potremmo ovviamente includere altre tipologie di cella, qualora richiesto).
Eseguendo il nostro programma, esso dovrà accedere ad un file come quello presentato, salvando le informazioni in esso contenuto per essere poi in grado di richiamare il modello Excel nel modo corretto/atteso.
Il punto interrogativo nella query è un ipotetico parametro di lancio che possiamo avere la necessità di specificare. Nel caso in esame, si tratta di una semplice stringa che verrà concatenata alla stringa TEXTCODE, ma in uno scenario reale potrebbe, ad esempio, rappresentare un argomento da passare in una clausola WHERE.
Con un file opzioni come quello visto sopra, è piuttosto semplice scrivere una funzione che ne recuperi il contenuto, salvandolo per uso futuro. Segue il codice in oggetto, con alcuni commenti immediatamente dopo la sua esposizione.
Private Structure Corrispondenze
Dim Cella As String
Dim Variabile As String
Dim Tipo As String
End Structure
Private Sub LeggiOpzioni(modello As String, parametro As String)
Using sR As New IO.StreamReader(My.Settings.ModelDirectory & "\" & modello & ".dat")
_mappa = New List(Of Corrispondenze)
While Not (sR.EndOfStream)
Dim parms() As String = sR.ReadLine.Split("#")
Select Case parms(0).Trim()
Case "Query"
_sqlCmd = parms(1).Replace("?", parametro)
Case Else
Dim item As New Corrispondenze
item.Cella = parms(0).Trim
item.Variabile = parms(1).Trim
item.Tipo = parms(2).Trim
_mappa.Add(item)
End Select
End While
End Using
End Sub
La funzione LeggiOpzioni richiede due argomenti:
- 'modello' è il nome del nostro modello Excel. Ho immaginato che per ciascun file Excel in utilizzo, tale file debba essere accompagnato dal proprio file di parametrizzazione, e quindi potremmo avere ad esempio un file "TEST.xls" (contenente tutte le sue formule, testi fissi, celle predefinite, stili, e così via) ed un file "TEXT.dat", nel quale definiremo invece la query da eseguire, e quali celle debbano essere scritte. Un file di modello ed un file di regole, per dirla in un altro modo.
- 'parametro' sarà la stringa/valore numerico con il quale desideriamo che il nostro programma venga lanciato. Si tratta del valore che andrà a sostituire il punto interrogativo della query, e - ancora - può quindi trattarsi di ciò che si desidera, per esempio per una semplice concatenazione di stringhe, come presentato nell'esempio, oppure come un valore da utilizzare in una clausola WHERE, o altro. Se state pensando ad una potenziale falla di sicurezza nell'utilizzo di tale parametro, avete pienamente ragione: cosa accadrebbe infatti se un utente passasse un parametro contenente codice T-SQL, caratteri speciali, e così via? Esattamente, potrebbe arrivare ad alterare in maniera anche malevola le nostre tabelle. Sarà quindi necessario utilizzare una query parametrizzata, il cui impiego va però oltre i fini del presente articolo, e mi sono quindi limitato in questa sede ad aggiungere nella sezione Bibliografia un link da cui partire per un approfondimento della tematica.
La nostra funzione aprirà il file DAT avente lo stesso nome del modello, leggendone ogni riga e dividendo ognuna di loro utilizzando il separatore '#'. Successivamente, procederà nell'analisi del primo campo: se si tratterà della string "Query", allora salverà il secondo campo in una variabile stringa per eseguirlo come query, sostituendo prima il punto interrogativo con il parametro di avvio (e, come detto poco fa, vi sono delle importanti considerazioni di sicurezza da fare a questo proposito, il che presuppone che in assenza di query parametrizzate, il presente codice non sia consigliato in caso di ambienti di produzione). Nel caso il primo campo sia diverso da "Query", sappiamo che si tratta di celle: ho pertanto creato una struttura denominata Corrispondenze, in modo da creare una lista di tipo List(Of Corrispondenze) in cui salvare il contenuto di ciascuna riga di opzione, per l'utilizzo futuro. Dal primo campo estraiamo il nome della cella, dal secondo il nome del campo T-SQL da applicare, e dal terzo la tipologia di dato che scriveremo (TEXT o IMAGE).
Passo 3: Estrazione dati e loro scrittura su un file Excel pre-esistente
Ora abbiamo tutto ciò che server per processare un modello Excel. Qui vedremo passo-passo la sub routine che esegue tale compito, fino ad arrivare al salvataggio del file.
Passo 3.1: Connessione al database e popolamento DataTable
Per ottimizzare l'interazione con il database, apriremo una connessione, eseguiremo la nostra query, e conserveremo in un oggetto DataTable i record appena letti. In questo modo, potremo chiudere la connessione subito dopo il reperimento dei dati, senza necessità reale di mantenerla effettivamente attiva. Implementeremo tale funzionalità in questo modo:
Dim connection As New SqlConnection(My.Settings.ConnectionString)
connection.Open()
Dim adapter As New SqlDataAdapter(_sqlCmd, connection)
Dim builder As New SqlCommandBuilder(adapter)
Dim dt As New DataTable
adapter.Fill(dt)
connection.Close()
Possiamo riassumere questo snippet così: apertura della connessione specificata in My.Settings.ConnectionString, esecuzione di una data query mediante un DataAdapter, utilizzato poi per il popolamento di una DataTable. Quindi, chiusura della connessione. Nel caso del nostro esempio avremo un singolo record, ma la struttura del nostro oggetto DataTable sarà formata a seconda della definizione della query: in altre parole, nel nostro DataTable troveremo una struttura tabellare, fatte di righe e colonne, con le seconde che avranno lo stesso nome dei nostri campi T-SQL.
Passo 3.2: Creare e referenziare oggetti Excel
Disponendo ora dei dati da scrivere, vediamo come poterlo fare. Ma prima, abbiamo necessità di aprire il modello Excel, posizionarci su un certo worksheet e - più genericamente - referenziare le variabili che ci consentiranno accesso al file Excel.
Dim oExcel As Object
oExcel = CreateObject("Excel.Application")
oExcel.Workbooks.Open(My.Settings.ModelDirectory & "\" & modello & ".xlsx")
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet
oBook = oExcel.ActiveWorkbook
oSheet = oExcel.Worksheets(1)
Ho qui utilizzato il contenuto della variabile My.Settings.ModelDirectory, come della directory da cui i nostri modelli verranno letti. Come si può notare dall'ultima riga, il valore di 1 come worksheet da utilizzare è forzato, presupponendo che i nostri modelli abbiano in effetti un solo foglio.
Passo 3.3: Scrittura su celle
Nel leggere il nostro file di regole, abbiamo compilato una variabile di tipo List(Of Corrispondenze) di nome _mappa, ed ora è il momento di utilizzarla. Sappiamo che ogni elemento di _mappa rappresenta una cella da scrivere, dunque eseguiremo un loop sull'intera struttura, utilizzando le proprietà di ciascun elemento per svolgere il compito prefissato. Nello snippet seguente, si può notare che un primo test viene svolto sulla proprietà "Tipo", in modo da determinare di quale tipologia di dato si stia parlando: testo o immagine? Nel caso sia testuale, compileremo semplicemente la cella tramite il valore della proprietà "Cella".
For Each c As Corrispondenze In _mappa
If c.Tipo.CompareTo("IMAGE") = 0 Then
Dim oRange As Excel.Range = CType(oSheet.Range(c.Cella), Excel.Range)
Dim Left As Double = oRange.Left
Dim Top As Double = oRange.Top
Dim imagesize As Integer = 32
oSheet.Shapes.AddPicture(c.Variabile, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, Left, Top, ImageSize, ImageSize)
Else
oSheet.Range(c.Cella).Value = dt.Rows(0).Item(c.Variabile)
End If
Next
Passo 3.4: Salvataggio del file
Al termine del ciclo, il nostro modello sarà compilato e pronto per essere salvato. Dal momento però che non vogliamo sovrascriverlo, desiderando mantenerlo intatto per successive esecuzioni del programma, dovremo salvarne una copia, ad esempio in una locazione temporanea..
Dim resFile As String = System.IO.Path.GetTempPath() & "\" & modello & "_" & Now.Year.ToString("0000") & Now.Month.ToString("00") & Now.Day.ToString("00") & Now.Hour.ToString("00") & Now.Minute.ToString("00") & Now.Second.ToString("00") & ".xls"
resFile = resFile.Replace("\\", "\")
oExcel.DisplayAlerts = False
oBook.SaveAs(resFile, 1)
Utilizzeremo la funzione GetTempPath() per leggere il percorso di file temporaneo dell'utente corrente, creando poi un nome file che faccia utilizzo del timestamp attuale. Utilizzando i nostri oggetti Interop, procederemo quindi al salvataggio di una copia del modello appena modificato (la costante 1 nel richiamo del metodo SaveAs significa che il file va salvato in formato XLS). Una routine finale rilascerà quindi gli oggetti finora referenziati, aprendo poi il file salvato mediante classe Process(), in modo da mostrarlo all'utente. Vediamo ora il codice sorgente completo, con un ulteriore esempio del suo funzionamento.
Passo 4: Assemblaggio delle varie parti
Imports Microsoft.Office.Interop
Imports System.Data.SqlClient
Public Class MainFrm
Private Structure Corrispondenze
Dim Cella As String
Dim Variabile As String
Dim Tipo As String
End Structure
Dim _sqlCmd As String = ""
Dim _mappa As List(Of Corrispondenze)
Private Sub LeggiOpzioni(modello As String, parametro As String)
Using sR As New IO.StreamReader(My.Settings.ModelDirectory & "\" & modello & ".dat")
_mappa = New List(Of Corrispondenze)
While Not (sR.EndOfStream)
Dim parms() As String = sR.ReadLine.Split("#")
Select Case parms(0).Trim()
Case "Query"
_sqlCmd = parms(1).Replace("?", parametro)
Case Else
Dim item As New Corrispondenze
item.Cella = parms(0).Trim
item.Variabile = parms(1).Trim
item.Tipo = parms(2).Trim
_mappa.Add(item)
End Select
End While
End Using
End Sub
Private Sub LoadVariables(modello)
Try
Application.DoEvents()
Dim connection As New SqlConnection(My.Settings.ConnectionString)
connection.Open()
Dim adapter As New SqlDataAdapter(_sqlCmd, connection)
Dim builder As New SqlCommandBuilder(adapter)
Dim dt As New DataTable
adapter.Fill(dt)
connection.Close()
Dim p As New Process()
Dim ps As New ProcessStartInfo("cmd.exe", "/C taskkill.exe /IM EXCEL.EXE /F")
p.StartInfo = ps
p.Start()
Threading.Thread.Sleep(2000)
Dim oExcel As Object
oExcel = CreateObject("Excel.Application")
oExcel.Workbooks.Open(My.Settings.ModelDirectory & "\" & modello & ".xlsx")
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet
oBook = oExcel.ActiveWorkbook
oSheet = oExcel.Worksheets(1)
For Each c As Corrispondenze In _mappa
If c.Tipo.CompareTo("IMAGE") = 0 Then
Dim oRange As Excel.Range = CType(oSheet.Range(c.Cella), Excel.Range)
Dim Left As Double = oRange.Left
Dim Top As Double = oRange.Top
Dim imagesize As Integer = 32
oSheet.Shapes.AddPicture(c.Variabile, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, Left, Top, ImageSize, ImageSize)
Else
oSheet.Range(c.Cella).Value = dt.Rows(0).Item(c.Variabile)
End If
Next
Dim resFile As String = System.IO.Path.GetTempPath() & "\" & modello & "_" & Now.Year.ToString("0000") & Now.Month.ToString("00") & Now.Day.ToString("00") & Now.Hour.ToString("00") & Now.Minute.ToString("00") & Now.Second.ToString("00") & ".xls"
resFile = resFile.Replace("\\", "\")
oExcel.DisplayAlerts = False
oBook.SaveAs(resFile, 1)
ReleaseObject(oSheet)
oBook.Close(False, Type.Missing, Type.Missing)
ReleaseObject(oBook)
oExcel.Quit()
ReleaseObject(oExcel)
GC.Collect()
Dim pC As New Process
Dim pCs As New ProcessStartInfo(resFile)
pC.StartInfo = pCs
pC.Start()
Catch ex As Exception
MsgBox(ex.Message & Environment.NewLine & ex.StackTrace)
End Try
End Sub
Private Sub ReleaseObject(ByVal o As Object)
Try
While (System.Runtime.InteropServices.Marshal.ReleaseComObject(o) > 0)
End While
Catch
Finally
o = Nothing
End Try
End Sub
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
If Environment.GetCommandLineArgs.Length < 2 Then
Application.ExitThread()
Else
Dim modello As String = Environment.GetCommandLineArgs(1).ToString
Dim parametro As String = Environment.GetCommandLineArgs(2).ToString
LeggiOpzioni(modello, parametro)
LoadVariables(modello)
Application.ExitThread()
End If
End Sub
End Class
Una sessione di esempio dell'esecuzione del programma può essere osservata in questo video:
Codice sorgente
Il codice sorgente presentato può essere scaricato da questo link: https://code.msdn.microsoft.com/Populate-Excel-files-from-39ef8314
Bibliografia
- SqlParameter class (per approfondire la tematica della sicurezza inerente all'esecuzione di query SQL)
- Microsoft Office Excel Interop
- System.Io.Path.GetTempPath
Altre lingue
Il presente articolo è disponibile nelle seguenti localizzazioni