Requisiti del progettoPer poter seguire questo articolo è necessario avere installato
Visual Studio .NET 2003 e
Microsoft Excel 2003 (11.0). In particolare oltre ad Excel devono essere installati gli
Assembly di Interoperabilità Primaria (PIA) o (Primary Interop Assemblies).
I
PIAs sono DLL .NET in codice Managed che consentono di interagire con l'
Object Model di
Microsoft Excel. Già ne avevo parlato in un articolo tempo fa accessibile da qui:
http://www.dotnethell.it/articles/OfficeXPPia.aspx ">Scopriamo cosa sono gli Office XP PIA
In base alla versione di Excel o Office in vostro possesso possono presentarsi 3 scenari diversi:
- Microsoft Excel 2003 (11.0) (Se avete già installato il
Framework 1.1 i PIAs sono installati automaticamente se scegliete una installazione completa di Office. Se non l'avete installato dovrete installarli manualmente. (a seguire le istruzioni)
- Microsoft Excel XP 2002 (10.0) (E' necessario scaricare a parte il pacchetto contenente i PIAs. Nel link appena citato qui sopra trovate i riferimenti)
- Microsoft Excel 2000 (9.0) (Non esistono PIAs per questa versione di Office, è necessario infatti referenziare direttamente i componenti COM, verrà creata una wrapper class di supporto).
Come installare i PIASe dovete installare manualmente i PIA è sufficiente andare nel
Pannello di Controllo ed entrare nella sezione
"Aggiungi Rimuovi Applicazioni". Cercate l'installazione di
Microsoft Office 2003 Professional" e premete
Cambia.
Partirà l'installazione di
Office 2003. Scegliete
"Aggiungi/Rimuovi Caratteristiche" dall'installazione corrente di Office, e successivamente checkate il box
"Personalizzazione Avanzata Applicazioni". Comparirà a questo punto l'elenco dei pacchetti di Office installati.
Aprite il ramo di
Microsoft Excel e attivate il
"Supporto alla programmibilità .NET". Proseguite premendo
"Aggiorna" fino al termine dell'installazione.
I PIAs verranno automaticamente registrati nella
GAC (Global Assembly Cache) e possono essere visibili entrando nella cartella
C:\WINNT\Assembly o
C:\WINDOWS\AssemblyLa classe ExcelDataSetLibIl progetto è di tipo
"Class Library". L'output sarà chiaramente un Assembly .NET DLL che potremo utilizzare nelle nostre applicazioni. La cosa fondamentale per far funzionare il tutto è referenziare l'
Object Library di
Microsoft Excel 2003 quindi la versione
11.0. La procedura è semplice:
- Tasto destro sulla cartella riferimenti e
"Aggiungi Riferimento" - Cercare e selezionare
"Microsoft Excel 11.0 Object Library"- Premere Ok
Se la nostra operazione si è conclusa con successo, noterete che selezionando il riferimento Excel appena creato la finestra proprietà sottostante sarà più o meno simile alla seguente:
E' da notare che nel campo Path troverete il riferimento proprio all'Assembly PIA che abbiamo installato in precedenza infatti il path punta all'assembly di interoperabilità di Excel "C:\Windows\GAC\...Microsoft.Office.Interop.Excel.dll"
In questa classe sono andato ad implementare tre metodi per l'esportazione dei dati da un DataSet generico:
1) ExportExcelPIA (effettua un'esportazione da DataSet a file Excel formato binario .xls)
2) ExportExcelXML (effettua un'esportazione da DataSet a file Excel formato .xls)
3) ExportExcelCSV (effettua un'esportazione da DataSet a file .csv)
Andiamo a vedere il funzionamento dei tre metodi.
1) Esportare dati tramite PIA di ExcelE' questa l'operazione forse richiesta dalla maggior parte del pubblico cioè la possibilità di creare con .NET un file di Excel classico in formato binario cioè in formato .xls. Il formato .xls è proprietario di Microsoft quindi non c'è una documentazione ufficiale che spiega come serializzare i dati come si potrebbe fare con un file .gif o simili. Non esistono neanche (che io sappia) librerie di terze parti che consentono questa operazione. Microsoft infatti generalmente assieme ai suoi prodotti distribuisce anche delle librerie DLL che permettono di interfacciarsi al prodotto stesso. Nel caso di Office per ogni prodotto della Suite è presente anche una DLL che ci fa accedere all'
Object Model (l'insieme di classi, metodi e proprietà esposti), nel nostro caso di Excel.
Vien da sè che il requisito fondamentale per poter accedere all'Object Model di Excel è avere installato sul PC o sul Server che lancerà questo metodo anche il prodotto principale cioè appunto Excel. Diversamente non è possibile. Questo può sembrare un forte limite, e in parte lo è. Perchè devo installare su un server di produzione Excel se mi serve solo per creare dei file ed esportare dati ?
Il codice è molto semplice in particolare per chi ha esperienza di programmazione con VBA.
System.Data.DataTable k=this.Tables[tableName];
object oMissing = System.Reflection.Missing.Value;
ApplicationClass ap=new ApplicationClass();
Workbook wbk=(Workbook)ap.Workbooks.Add(oMissing);
Worksheet sht=(Worksheet)wbk.Sheets[1];
for (int i=0;i<k.Rows.Count;i++)
{
for (int j=0;j<k.Columns.Count;j++)
{
Range range=(Range)sht.Cells[i+1,j+1];
range.Value2=k.Rows[j].ToString();
}
}
ap.DisplayAlerts=false;
ap.Workbooks[1].SaveAs(outputFile,oMissing,oMissing,oMissing,oMissing,oMissing,XlSaveAsAccessMode.xlNoChange,
oMissing,oMissing,oMissing,oMissing,oMissing);
ap.Quit();
Questo metodo come del resto gli altri due accetta in ingresso dua parametri:
- Il nome della Tabella all'interno del DataSet
- Il nome del file di Output
Se esaminiamo il codice noteremo che nella prima riga si ottiene subito il riferimento alla
DataTable contenente i dati da esportare. Successivamente c'è la definizione della variabile di tipo
object oMissing che ci servirà in seguito e poi viene la parte chiave ossia quando si va ad istanziare Excel.
Dopo aver creato la nuova istanza (
new ApplicationClass()) è necessario aggiungere un nuovo
WorkBook e in seguito recuperare il riferimento al Primo
Sheet (di solito ne vengono creati 3 di default).
Nella parte centrale del codice ci sono due cicli for
nested (nidificati) che scorrono riga per riga e colonna per colonna i dati all'interno del DataSet e li vanno ad inserire nello Sheet di Excel tramite la Collection
Cells.
Nella parte finale è necessario disabilitare gli
Alerts che potrebbero apparire durante la fase di salvataggio. A questo punto salvare il file su disco utilizzando il metodo (
SaveAs) e dando come parametro il percorso al file. Infine con il metodo
Quit chiudere l'istanza di Excel creata precedentemente per liberare la memoria. Troverete alla destinazione specificata un file .xls in formato binario.
2) Esportare dati in formato XML di ExcelUna novità introdotta da Microsoft recentemente è la possibilità di salvare i file prodotti dalla suite di Office in formato universale XML. Questo addirittura sarà il formato di salvataggio di Default nel nuovo
Office 12 come segnalato da questa news:
http://www.dotnethell.it/news/Office12XML.aspx ">Office 12.0 adotterà come formato predefinito l'XML
La rivoluzione di questo rilascio è il fatto che d'ora in poi chiunque potrà creare file compatibili Excel o Word per esempio senza doversi per forza appoggiare al formato proprietario binario. Quindi vuol dire che file di Office potranno essere prodotti da Mac, da Linux, e da qualunque software o linguaggio di programmazione.
Ma che regole bisogna seguire per costruire un file di Excel ? Microsoft ha rilasciato per l'occasione delle specifiche aperte e scaricabili liberamente da qui chiamate
Office 2003 Reference Schemas:
http://www.microsoft.com/downloads/details.aspx?FamilyId=FE118952-3547-420A-A412-00A2662442D9&displaylang=en ">Download Office 2003 Reference Schemas
Il secondo metodo della classe infatti noterete non istanzierà più il componente COM di Excel ma sempicemente creerà un
XmlDocument. In questo caso il metodo chiave è
GetXMLDocument che accetta gli stessi parametri del precedente ma fornisce in uscita un
XmlDocument che verrà passato di ritorno ai metodi di supporto
ExportExcelXML. E' stato deciso di creare un metodo aggiuntivo (GetXMLDocument) per non duplicare il codice e renderlo più leggibile.
System.Data.DataTable k=this.Tables[tableName];
string baseURI="urn:schemas-microsoft-com:office:spreadsheet";
XmlDocument doc=new XmlDocument();
string basexml="<?xml version=\"1.0\"?><?mso-application progid=\"Sheet\"?>";
basexml+="<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"></Workbook>";
doc.LoadXml(basexml);
XmlElement node = doc.CreateElement(null, "Worksheet", baseURI );
XmlAttribute at = doc.CreateAttribute("ss","Name", baseURI);
at.Value="FoglioX";
node.Attributes.Append(at);
XmlElement tab=doc.CreateElement(null,"Table",baseURI);
for (int i=0;i<k.Rows.Count;i++)
{
XmlElement row=doc.CreateElement(null,"Row",baseURI);
XmlAttribute at1=doc.CreateAttribute("ss","Index",baseURI);
at1.Value=(i+1).ToString();
row.Attributes.Append(at1);
for (int j=0;j<k.Columns.Count;j++)
{
XmlElement cell=doc.CreateElement(null,"Cell",baseURI);
XmlAttribute at2=doc.CreateAttribute("ss","Index",baseURI);
at2.Value=(j+1).ToString();
XmlElement data=doc.CreateElement(null,"Data",baseURI);
data.InnerText=k.Rows[j].ToString();
XmlAttribute type=doc.CreateAttribute("ss","Type",baseURI);
type.Value="String";
data.Attributes.Append(type);
cell.Attributes.Append(at2);
cell.AppendChild(data);
row.AppendChild(cell);
}
tab.AppendChild(row);
}
node.AppendChild(tab);
doc.DocumentElement.AppendChild(node);
return doc;
Questo metodo è un po' più complesso del precedente perchè si presuppone che il programmatore abbia un'ottima conoscenza degli
Schemas di Office sopracitati e di come funziona il
DOM (Document Object Model) XML.
Banalmente viene istanziato un nuovo
XmlDocument viene caricato come Header (
LoadXml) una stringa particolare comune a tutti i file di Excel XML e successivamente come nel primo metodo ci sono due cicli for nidificati che vanno ad aggiungere all'albero del DOM i vari nodi e attributi necessari.
Anche qui come nel primo metodo verrà salvato un file, sempre con estensione XLS ma noterete aprendolo con il notepad che il contenuto è in formato testuale XML e non più binario. Vedete una screenshot di seguito.
3) Esportare dati in formato CSVQuesto è forse il metodo che ha meno bisogno di spiegazioni. In questo caso viene creato un file esterno tramite
TextWriter, viene creato un oggetto
StringBuilder di supporto, che fornisce migliori performance in fase di concatenazione di stringhe e una volta terminata la composizione della stringa il contenuto viene scaricato direttamente sullo Stream in uscita e quindi scritto su disco.
Utilizzare la DLL in una Windows FormA titolo di esempio è possibile creare una Windows Form che utilizzi la DLL appena creata per esportare dati da un Database SQL Server su un file Excel. E'sufficiente referenziare nei "Riferimenti" la DLL
e creare un Button sulla Form.
Inserire il seguente codice:
SqlConnection sqlconn=new SqlConnection("Server=localhost;UID=sa;PWD=sa;Initial Catalog=Northwind");
sqlconn.Open();
SqlDataAdapter da=new SqlDataAdapter("select employeeid,lastname,firstname from employees",sqlconn);
DataSetExcel g=new DataSetExcel();
da.Fill(g,"Prova");
sqlconn.Close();
g.ExportExcelXML("Prova","C:\\prova.xls");
Lanciamo l'applicazione premiamo sul pulsante appena creato. L'output sarà un file Excel in formato XML. Questo è ciò che appare se lo apriamo in Excel:
e se proviamo ad aprire il file con il blocco note questo sarà l'output:
Conclusioni ed eventuali ulteriori implementazioni
Abbiamo visto come con poche istruzioni possiamo creare una classe di supporto che ci consenta di esportare dati da un generico DataSet, riempito opportunamente da precedenti chiamate a Database o in altro modo, verso destinazioni classiche come file in Excel o file separati da firgola (CSV).
Questa classe una volta compilata in DLL può essere referenziata normalmente da applicazioni Windows Forms, come già abbiamo visto, ma anche Web Forms. E' così possibile creare dei Report Excel in ASP.NET da inviare direttamente all'utente che ne faccia eventualmente richiesta.
Chiunque abbia la necessità di esportare dati in altri formati può semplicemente estendere la classe implementando nuovi metodi per esempio un Export in formato PDF oppure in formato tabella HTML oppure ancora in formati proprietari che possono essere in uso nella vostra azienda.