
guide
Tutti i linguaggi per diventare uno sviluppatore di app per Android.
Sfruttare gli xls come fonti dati con ADO.NET, creare fogli elettronici grazie ad HttpResponse e con la Excel Object Library
In questo articolo vediamo come, dal punto di vista del programmatore .NET, l’interazione con un file Excel sia equivalente all’interazione con una base di dati. Ciò è reso possibile da ADO.NET, che gestisce i file Excel come se fossero delle base di dati in cui i fogli di lavoro sono paragonabili alle tabelle e le colonne sono paragonabili ai campi.
L’utilità di una interazione di questo tipo si manifesta soprattutto quando è necessario fornire report generati automaticamente dall’applicazione, in modo più marginale potremmo utilizzare dei file Excel per mantenere variabili o testi all’interno di un CMS.
Nel corso dell’articolo realizzeremo una applicazione Web, composta da tre webform che gestiscono la classifica di una gara di tiro con l’arco. Questa classifica è depositata su diversi fogli di lavoro di un file Excel ed è consultabile attraverso l’applicazione.
In questo modo possiamo affrontare diversi argomenti:
ADO.Net ci permette di recuperare dati da cartelle di lavoro Excel, in gergo Workbook, aggiungere o modificare dati in un workbook esistente oppure creare un nuovo workbook e nuovi fogli di lavoro, in gergo datasheets.
Usare ADO.NET con Excel significa adattarsi ad alcune differenze rispetto alle comuni connessioni a DB. In questo caso non possiamo sfruttare la generazione automatica del codice, dobbiamo scrivere tutto il codice e definire i data adapters, commands e dataset.
Inoltre, per la stringa di connessione dobbiamo una sintassi simile a questa:
"Provider=Microsoft.Jet.Oledb.4.0;Data Source=|DataDirectory|torneoarco.xls;Extended Properties=Excel 8.0"
Campo | Descrizione |
---|---|
Provider | Dobbiamo indicare la versione del provider (è necessario utilizzare la versione 4.0 del provider Jet, in quanto la versione 3.51 non supporta i driver ISAM di Jet) |
Data Source | La sintassi per il nome del file è la solita comune anche ai file access o SQL Server |
Extended Properties | Dobbiamo indicare la versione di Excel (esempio: specificare Excel 5.0 per una cartella di lavoro di Excel 95 ed Excel 8.0 per una cartella di lavoro di Excel 2002 e successivi) |
Per referenziare una tabella di un workbook Excel dobbiamo invece usare la sintassi:
[atleta$]
oppure
[atleta$A1:C10]
Dove atleta
è il nome di un foglio di lavoro Excel, il dollaro indica che il foglio esiste e A1:C10
è l’intervallo di colonne del foglio da prendere in considerazione. Se il foglio di lavoro non esiste ancora allora non si deve aggiungere il carattere dollaro.
Per leggere i dati, possiamo usare due tecniche ADO.NET. La prima consiste nell’uso di un OleDB Data Reader, la seconda nell’uso di OleDB Data Adapter e DataSet. Noi useremo la seconda tecnica su un file Excel chiamato torneoarco.xls
.
Il file contiene diversi fogli: il primo riporta l’elenco degli atleti che hanno partecipato ad una gara di tiro con l’arco, il secondo foglio indica il punteggio conseguito dagli atleti nella manche di tiro da 50 metri di distanza. La gara è costituita da 4 manches su distanze diverse (30m
, 50m
, 70m
, 90m
).
Realizziamo una webform che visualizzi in una GridView
il contenuto di un foglio Excel che l’utente sceglie da un menu a comparsa. Vediamo subito il risultato con il contenuto del foglio “atleti” del nostro file torneoarco.xls
:
Vediamo ora il codice associato all’evento click del pulsante Importa
(versione VB.NET):
String strExcelConn = System.Configuration.ConfigurationManager.ConnectionStrings["ExcelConnection"].ToString();
String strSQL = "SELECT * FROM [" + ddlFogli.SelectedItem.Text + "$]";
using (OleDbConnection dbConn = new OleDbConnection(strExcelConn))
{
using (OleDbCommand cmd = new OleDbCommand(strSQL, dbConn))
{
dbConn.Open();
DataSet dsExcel = new DataSet();
OleDbDataAdapter daExcel = new OleDbDataAdapter(cmd);
daExcel.Fill(dsExcel);
gvRisultati.DataSource = dsExcel;
gvRisultati.DataBind();
}
}
Il procedimento è molto semplice, creiamo un OleDbCommand
al quale associamo sia la query diretta al foglio Excel, selezionato dall’utente, sia la stringa di connessione al file Excel (che leggiamo dal web.config
). Associamo il comando all’OleDbDataAdapter
ed infine riempiamo un DataSet con il risultato della query. Carichiamo la GridView
con il contenuto del DataSet
.
Possiamo realizzare anche delle query complesse, nell’esempio seguente vediamo come costruire la classifica totale della gara di tiro con l’arco in cui per ogni atleta sono indicati i punteggi conseguiti nelle varie manches ed il punteggio totale viene calcolato sommando i punteggi delle singole manches:
SELECT A.id_atleta, A.atleta, N.punteggio AS 90m, S.punteggio AS 70m, C.punteggio AS 50m, T.punteggio AS 30m, (N.punteggio + S.punteggio + C.punteggio + T.punteggio) AS totale FROM [atleti$] A, [90m$] N, [70m$] S, [50m$] C, [30m$] T WHERE(A.id_atleta = N.id_atleta and A.id_atleta = S.id_atleta and A.id_atleta = C.id_atleta and A.id_atleta = T.id_atleta) ORDER BY (N.punteggio + S.punteggio + C.punteggio + T.punteggio) DESC
Per creare un nuovo datasheet si deve usare il commando SQL CREATE TABLE
. Se il workbook specificato nella stringa di connessione non esiste allora verrà creato anch’esso.
Vediamo un esempio (versione VB.NET):
String strExcelConn = System.Configuration.ConfigurationManager.ConnectionStrings["prova"].ToString();
String strSQL = "CREATE TABLE atleti (id_atleta number, atleta char(50))";
using (OleDbConnection dbConn = new OleDbConnection(strExcelConn))
{
using (OleDbCommand cmd = new OleDbCommand(strSQL, dbConn))
{
dbConn.Open();
cmd.ExecuteNonQuery();
}
}
Definiamo un oggetto OleDBConnection
ed un oggetto OleDBCommand
come fatto nell’esempio precedente. Associamo all’OleDBCommand
la query di creazione tabella e la stringa di connessione al nuovo file XML (prova
).
L’esecuzione del comando creerà il workbook (perché non esiste) con il nome e nella posizione indicate nel file di configurazione e aggiungerà al file il foglio atleti
, con le colonne id_atleta
di tipo numerico e atleta
di tipo testo.
Per inserire nuove righe (i nostri record) nel foglio di lavoro utilizziamo, come è ovvio, il commando INSERT
di SQL. Ecco un esempio (versione VB.NET):
String strExcelConn = System.Configuration.ConfigurationManager.ConnectionStrings["prova"].ToString();
String strSQL = "INSERT INTO [atleti$](id_atleta, atleta) VALUES(11,'Simone Moretti')";
using (OleDbConnection dbConn = new OleDbConnection(strExcelConn))
{
using (OleDbCommand cmd = new OleDbCommand(strSQL, dbConn))
{
dbConn.Open();
cmd.ExecuteNonQuery();
}
}
Il codice è praticamente identico al precedente, l’unica differenza è la query SQL. Notiamo che nella INSERT
il foglio atleti
è seguito dal carattere dollaro ($
), perché adesso il foglio atleti
esiste, mentre nella CREATE TABLE
precedente non esisteva ancora. L’esecuzione di questo codice aggiunge un record al foglio atleti caratterizzato da un campo id_atleta valorizzato con il numero 11
ed un campo atleta valorizzato con il testo Simone Moretti
.
Per aggiornare un record esistente utilizziamo il comando SQL UPDATE
. Anche questa volta il codice è lo stesso, modificheremo solo la stringa del comando:
strSQL = "UPDATE [atleti$] SET atleta='Mario Moretti' WHERE id_atleta=11";
In questo caso modifichiamo il record del foglio atleti
identificato dal campo id_atleta
uguale al valore 11
.
Ci sono dei limiti nell’uso di Excel come base di dati in ADO.NET. In particolare, non è possibile cancellare record da fogli di lavoro né cancellare interi fogli di lavoro. Inoltre non è possibile inserire formule nelle celle del foglio.
Ora osserviamo come scrivere su un file Excel i dati di una GridView
sfruttando la classe HttpResponse
che consente di includere le informazioni sulla risposta HTTP da un’operazione ASP.NET.
Creata la pagina, associamo il codice al click sul pulsante Esporta in Excel usando Response
.
Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename=Export1.xls");
Response.Charset = "";
Response.ContentType = "application/vnd.xls";
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
htmlWrite.WriteLine("<strong><font size='4'> Torneo d'Arco IDI DI MARZO - Arco Olimpico</font></strong>");
// viene reindirizzato il rendering verso la stringa in uscita
gvRisultati.RenderControl(htmlWrite);
Response.Write(stringWrite.ToString());
Response.End();
I metodi e le proprietà della classe HttpResponse vengono esposti tramite la proprietà Response
. La prima cosa da fare è richiamare il metodo Clear
della classe che consente di cancellare tutto l’output di contenuto dal flusso del buffer.
Poi, grazie al metodo AddHeader
, indichiamo che stiamo agganciando un file Excel nominato Export1.xls
al flusso di output della risposta HTTP.
Continuiamo affermando che il file è di tipo Excel attraverso la proprietà ContentType
. Infine con il metodo Write
scriviamo sul flusso di output la versione HTML della rappresentazione client della GridView
(una semplice <table>
) e inviamo al client tutto l’output attualmente memorizzato nel buffer attraverso il metodo End
.
Nel flusso di output, grazie al metodo WriteLine
, abbiamo aggiunto anche una riga di testo con alcuni tag HTML che rappresentano l’intestazione del file Excel.
Nota: per effettuare il rendering della GridView
dobbiamo sovrascrivere il metodo VerifyRenderingInServerForm
e impostare la proprietà EnableEventValidation
a false
nel tag Page
della webform.
Questo metodo è molto pratico e veloce ma per realizzare output più sofisticati si deve ricorrere all’uso della Microsoft Excel Object Library.
La Microsoft Excel Object Library è un PIA (Primary Interop Assembly) ovvero un assembly che contiene la descrizione ufficiale della libreria di tipi usati in Microsoft Excel. Ogni versione di Excel ha un PIA distinto, ad esempio, la versione XP ha la Microsoft Excel Object Library versione 10.0. Per poter utilizzare questa libreria in ASP.NET dobbiamo referenziare l’assembly nella nostra applicazione web.
Generalmente questo assembly viene installato insieme a Microsoft Excel sulla macchina, ma per alcune versioni si deve procedere ad una installazione manuale, scaricando l’assembly dal sito della Microsoft. Ad esempio l’assembly per la versione XP di Excel è disponibile quest’indirizzo. Quando aggiungiamo la reference a questo assembly viene aggiunta una riga corrispondente nel web.config
:
<assemblies>
<add assembly="Microsoft.Office.Interop.Excel,Version=10.0.4504.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>
</assemblies>
Se questa riga non viene aggiunta significa che l’assembly non è presente nella GAC (Global Assembly Cache) e dobbiamo procedere con il download dell’assembly e l’installazione manuale.
Ora procediamo con il creare un file Excel simile a quello dell’esempio precedente sfruttando le potenzialità della Microsoft Excel Object Library. Vediamo il codice (versione VB.NET):
Workbook xlWorkBook;
Worksheet xlWorkSheet;
xlWorkBook = new Application().Workbooks.Add(Missing.Value);
xlWorkBook.Application.Visible = true;
xlWorkSheet = (Worksheet) xlWorkBook.ActiveSheet;
// riempiamo un dataset con i dati del file Excel
DataSet dsData = getData();
int i = 2;
// costruiamo la riga di intestazione
xlWorkSheet.Cells[1, 1] = "id_atleta";
xlWorkSheet.Cells[1, 2] = "atleta";
xlWorkSheet.Cells[1, 3] = "90m";
xlWorkSheet.Cells[1, 4] = "70m";
xlWorkSheet.Cells[1, 5] = "50m";
xlWorkSheet.Cells[1, 6] = "30m";
xlWorkSheet.Cells[1, 7] = "totale";
// mettiamo l'intestazione in grassetto
xlWorkSheet.get_Range("$A1", "$G1").Font.ColorIndex = Constants.xlColor3;
xlWorkSheet.get_Range("$A1", "$G1").Font.Bold = true;
foreach (DataRow dr in dsData.Tables[0].Rows)
{
xlWorkSheet.Cells[i, 1] = dr[0];
xlWorkSheet.Cells[i, 2] = dr[1];
xlWorkSheet.Cells[i, 3] = dr[2];
xlWorkSheet.Cells[i, 4] = dr[3];
xlWorkSheet.Cells[i, 5] = dr[4];
xlWorkSheet.Cells[i, 6] = dr[5];
// creiamo una formula per effettuare la somma dei vari punteggi
xlWorkSheet.Cells[i, 7] = "=SOMMA($C{0}:$F{0})".Replace("{0}", i.ToString());
i++;
}
// diamo la giusta larghezza alle colonne
xlWorkSheet.Columns.AutoFit();
Creiamo un oggetto Workbook
nuovo e inseriamo al suo interno un foglio di lavoro rappresentato dall’oggetto WorkSheet
. Definiamo le celle di intestazione del foglio per mezzo della collezione Cells
ed impostiamo il font di queste celle sfruttando la proprietà Font
dell’oggetto . Il
Range
rappresenta un sottoinsieme del foglio, nel nostro caso la riga di celle A1:G1
.
Impostiamo il valore di tutte le altre celle di figura 6 ciclando in un dataset che contiene il risultato di una query effettuata sul file torneoarco.xls
. Nella settima colonna del foglio di lavoro abbiamo messo una formula che calcola la somma dei punteggi delle varie manches per ogni atleta. Da notare che se stiamo usando la versione inglese di Microsoft Excel dobbiamo sostituire la parola chiave SOMMA
con l’equivalente SUM
.
Aggiungiamo un grafico al file Excel che riepiloga i punteggi conseguiti dai vari atleti:
// creiamo un grafico con i vari punteggi per atleta
Chart chart;
chart = (Chart) xlWorkBook.Charts.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
chart.ChartType = XlChartType.xlColumnClustered;
chart.SetSourceData(xlWorkSheet.get_Range("A1", "G11"), 2);
chart.HasTitle = true;
chart.ChartTitle.Text = "Classifica";
((Axis) chart.Axes(1, XlAxisGroup.xlPrimary)).HasTitle = true;
((Axis) chart.Axes(1, XlAxisGroup.xlPrimary)).AxisTitle.Text = "Atleti";
((Axis) chart.Axes(2, XlAxisGroup.xlPrimary)).HasTitle = true;
((Axis) chart.Axes(2, XlAxisGroup.xlPrimary)).AxisTitle.Text = "Punteggio";
Creiamo un oggetto Chart
, indichiamo che il grafico sarà un grafico a colonne attraverso la proprietà ChartType
impostata sul valore xlColumnClustered. Indichiamo che la sorgente dati del grafico è il range A1:G11
del foglio generato precedentemente.
Specifichiamo che il titolo del grafico sarà “Classifica” sfruttando la proprietà ChartTitle
. Aggiungiamo il titolo “Punteggio” all’asse verticale e il titolo “Atleti” all’asse orizzontale usando la proprietà AxisTitle
dell’oggeto Axes
.
Se vuoi aggiornamenti su Generare Report in Excel con ASP.NET: tre metodi inserisci la tua email nel box qui sotto:
Compilando il presente form acconsento a ricevere le informazioni relative ai servizi di cui alla presente pagina ai sensi dell'informativa sulla privacy.
La tua iscrizione è andata a buon fine. Se vuoi ricevere informazioni personalizzate compila anche i seguenti campi opzionali:
Compilando il presente form acconsento a ricevere le informazioni relative ai servizi di cui alla presente pagina ai sensi dell'informativa sulla privacy.
In questo video vedremo come creare una texture che ricorda delle assi di legno affiancate. Per seguire questo tutorial non […]
Tutti i linguaggi per diventare uno sviluppatore di app per Android.
Come creare applicazioni per il Web con PHP e MySQL per il DBMS.
Tutte le principali tecnologie per diventare uno sviluppatore mobile per iOS.
I fondamentali per lo sviluppo di applicazioni multi piattaforma con Java.
Diventare degli esperti in tema di sicurezza delle applicazioni Java.
Usare Raspberry Pi e Arduino per avvicinarsi al mondo dei Maker e dell’IoT.
Le principali guide di HTML.it per diventare un esperto dei database NoSQL.
Ecco come i professionisti creano applicazioni per il Cloud con PHP.
Lo sviluppo professionale di applicazioni in PHP alla portata di tutti.
Come sviluppare applicazioni Web dinamiche con PHP e JavaScript.
Fare gli e-commerce developer con Magento, Prestashop e WooCommerce.
Realizzare applicazioni per il Web utilizzando i framework PHP.
Creare applicazioni PHP e gestire l’ambiente di sviluppo come un pro.
Percorso base per avvicinarsi al web design con un occhio al mobile.
Realizzare siti Web e Web application con WordPress a livello professionale.
Conversioni personalizzate tra classi, creare metodi particolari per le trasformazioni tra tipi
Utilizzare il controllo XMLDataSource e sfruttare il metodo XPath() di .NET
Cos’è e come funziona il controllo Repeater
Una guida per apprendere e migliorare le tecniche di programmazione di app destinate al mercato di Windows Store con C# e XAML, per raggiungere il livello di preparazione delle certificazioni Microsoft