Generare Report in Excel con ASP.NET: tre metodi

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:

• importazione di dati da file Excel a pagina web tramite ADO.NET
• manipolazione dei dati del file Excel tramite ADO.NET
• esportazione di dati su file Excel tramite la classe Response
• esportazione di dati su file Excel e creazione di grafici tramite Microsoft Excel Object Library

Excel con ADO.NET

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"

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.

Leggere i dati da un file Excel

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.

Figura 1. Fogli di lavoro

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:

Figura 2. GridView con i dati importati dal file Excel

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

Creare un nuovo file Excel (workbook) ed un nuovo foglio (datasheet)

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.

Inserire un nuovo record

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.

Modificare un record esistente

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.

Limiti nell’uso di Excel con ADO.NET

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.

Creare un foglio Excel sfruttando l’oggetto ‘Response’

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.

Figura 3. Web Form per l’esportazione dei dati

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.

Figura 4. File generato con la classe Response

Questo metodo è molto pratico e veloce ma per realizzare output più sofisticati si deve ricorrere all’uso della Microsoft Excel Object Library.

Creare un foglio Excel con la 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.

Figura 5. Referenziare Microsoft Excel Object Library

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.

Figura 6. Excel generato con la Microsoft Excel Object Library

Aggiungere un grafico con Microsoft Excel Object Library

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.

Figura 7. Grafico generato con Excel Object Library