Home Page Home Page Articoli ExcelDataSetLib, una libreria .NET per esportare dati in formato Excel

ExcelDataSetLib, una libreria .NET per esportare dati in formato Excel

In questo articolo verrà dimostrato come derivare la classe DataSet e integrare funzionalità per esportare in formato Excel XLS, XML o CSV i dati provenienti da un base dati qualsiasi.
Autore: David De Giacomi Livello:
Requisiti del progetto
Per 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 PIA
Se 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\Assembly



La classe ExcelDataSetLib
Il 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 Excel
E' 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 Excel
Una 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 CSV
Questo è 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 Form
A 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.



Voto medio articolo: 5.0 Numero Voti: 3

File allegati


David De Giacomi

David De Giacomi

MVP Program Logo Approda al mondo dell'informatica nell' Aprile del 1994. Un amico gli fa conoscere il Quick Basic del DOS. E' cosi che inizia la sua attività di sviluppatore. Prosegue il suo percorso con altri linguaggi (Visual Basic, Pascal, Java, ANSI C), fino ad arrivare a .NET. Per un certo periodo affianca all'attività ... Profilo completo

Articoli collegati

Garantire l'univocità di una colonna ammettendo valori NULL multipli
Chi sviluppa in ambito professionale o per puro diletto prima o poi si troverà ad affrontare tematiche nuove ed avvincenti. In questo articolo cercherò di illustrare tre tecniche con i relativi pregi e difetti per permettere con SQL Server di garantire l'univocità di una colonna senza alcuna limitazione sui valori NULL
Autore: Lorenzo Benaglia | Difficoltà:
Integrare Crystal Reports con una Windows Form
Ecco come sfruttare in modo semplice e rapido le funzionalità offerte dal tool di reportistica Crystal Reports di Business Objects all'interno di una Windows Form .NET, dalla crazione del Report al Binding dei dati.
Autore: Matteo Raumer | Difficoltà: | Commenti: 2
Il Logging con Integration Services 2005
Perchè si è bloccata la mia applicazione ? Che errori si sono verificati ? Perchè il flusso d'esecuzione non procede nel modo corretto ? Cerchiamo di capire come possono esserci utili le funzionalità di logging offerte da SQL Server e gli Integration Services per rendere più affidabili e consistenti le nostre applicazioni.
Autore: Alessandro Alpi | Difficoltà: | Commenti: 3
Utilizzare i SQL Server Agent Proxies con i Package SSIS
Chi utilizza frequentemente i SSIS Packages e accede a risorse esterne a SQL Server non può fare a meno di conoscere ed utilizzare i Proxy Account (o SQL Server Agent Proxies). Vediamo che cosa sono e come si utilizzano in modo proficuo.
Autore: Alessandro Alpi | Difficoltà: | Commenti: 4
SQL Server 2005 - La Import/Export utility
Chi lavora sui Database si ritrova quasi quotidianamente a dover importare o esportare dati. SQL Server 2005 agevola notevolmente il compito tramite l'Import/Export Utility (ex DTS Import/Export di SQL 2000). Vediamo come funziona.
Autore: Alessandro Alpi | Difficoltà: | Commenti: 14
Visual Studio 2005 Team System for Database Professionals
Scopriamo questo nuovo e potente Tool della famiglia Visual Studio 2005 Team System per creare, sviluppare, testare e gestire con più facilità e flessibilità i database di SQL Server.
Autore: Marco Caruso | Difficoltà:
Parallelismo tra SSIS e DTS, due prodotti a confronto
In questo articolo un'analisi sulle novità dei SQL Server 2005 Integration Services rispetto ai DTS di SQL Server 2000.
Autore: Alessandro Alpi | Difficoltà: | Commenti: 2
Overview dei SQL Server Integration Services (SSIS)
Scopriamo i nuovi Sql Server Integration Services presenti in SQL Server 2005 che sostituisco i DTS aggiungendo delle nuove funzionalità davvero interessanti
Autore: Alessandro Alpi | Difficoltà: | Commenti: 6 | Voto:
Leggere un file XML con SQL Server 2005
Scopriamo in questo articolo alcune potenzialità offerte dal nuovo database SQL 2005, tra cui il nuovo data type XML, e le Stored Procedures per importare e gestire all'interno del database file testuali in formato XML.
Autore: Lorenzo Benaglia | Difficoltà: | Commenti: 5
Microsoft Application Blocks Data Access
Quante volte vi è capitato di riscrivere decine di volte lo stesso pezzo di codice all'interno di un progetto ? Scopriamo in questo articolo l'utilità dei Microsoft Application Blocks che vi permettono di ottimizzare il vostro codice evitando ripetizioni e vi facilitano la lettura.
Autore: Marco Caruso | Difficoltà:
Comprimere e decomprimere files ZIP con .NET e #ZipLib
Visualizzare il contenuto di un file Zip da una applicazione vi sembra una Mission Impossibile? Oppure la vostra azienda ha fatto un mutuo per pagare il componente che vi aiuta nel lavoro? Ecco un articolo che parla della libreria sharpZipLib, libreria Open Source per la gestione dei formati compressi.
Autore: Giovanni Ferron | Difficoltà: | Commenti: 7
Rivoluzione nel campo dei Reports con i SQL Reporting Services
Finalmente Microsoft ci mette a disposizione uno straordinario tool per la produzione di Report perfettamente integrato con l'architettura .NET. Fino ad ora siamo sempre stati abituati ad utilizzare il buono seppur limitato Crystal Report. Vediamo cosa cambia da ora in poi.
Autore: David De Giacomi | Difficoltà: | Commenti: 6 | Voto:
Scopriamo cosa sono gli Office XP PIA
Vediamo in quest'articolo una dettagliata introduzione dei PIA gli assemblies che permettono ad un'applicazione .NET di interagire e di dialogare con i prodotti della suite Microsoft Office XP.
Autore: David De Giacomi | Difficoltà: | Commenti: 6
Migliorare la qualità dei grafici nei Crystal Reports
In questo breve articolo vedremo come aumentare la qualità dei grafici prodotti da Crystal Reports durante il rendering tramite Web con ASP.NET e all'interno di applicazioni WinForms.
Autore: David De Giacomi | Difficoltà:
Costruire Report con ASP.NET e Crystal Reports
Vedremo in questo semplice Tutorial le istruzioni base che ci permetteranno di costruire un report avanzato usando ASP.NET e l'oggetto CrystalReportViewer integrato in questa versione di Visual Studio .NET.
Autore: David De Giacomi | Difficoltà: | Commenti: 14
Copyright © dotNetHell.it 2002-2017
Running on Windows Server 2008 R2 Standard, SQL Server 2012 & ASP.NET 3.5