Negli scenari di sviluppo recenti è sempre più presente la richiesta di importazione e integrazione di dati provenenienti da
Excel nei nostri applicativi.
Introduzione
Il
Framework .NET 2.0 ci fornisce diverse strade per adempiere a questi compiti , le tre più conosciute ed utilizzate sono le seguenti:
1) Utilizzare le API (Assembly PIAs) e relativo Object Model di Excel
2) Utilizzare una connessione ODBC (OdbcConnection)
3) Utilizzare il Drag & Drop
Affronteremo in questo articolo le modalità
2) e
3). Per chi fosse interessato invece alla prima modalità indico questi due articoli che possono essere utili per capire cosa sono gli
Assembly PIAs (che non sono altro che assembly di interoperabilità che consentono così agevolmente di accedere all'
Object Model di Excel da .NET) e come utilizzarli per creare file di Excel (per leggerli è sufficiente implementare il meccanismo inverso).
ExcelDataSetLib, una libreria .NET per esportare dati in formato Excel Scopriamo cosa sono gli Office PIAs Scenario
Iniziamo quindi con la modalità numero
2) che non richiede dipendenze installate sulla macchina quindi sarà possibile implementarla senza la presenza di
Excel installato sul PC. Il nostro scenario prevederà dunque una
Windows Application in cui sarà presente una
ListBox che conterrà un elenco di articoli.
E' possibile collegarsi al file Excel da .NET usando o una connessione
ODBC (
OdbcConnection) o una connessione
OLEDB (
OleDbConnection) tramite
Driver JET 4.0.
Implementazione tramite ODBC/OLEDB
Per collegarsi tramite
OdbcConnection si può creare una nuova
origine dati ODBC accedendo al
Pannello di Controllo di Windows, successivamente selezionare
Strumenti di Amministrazione e cliccando infine su
Amministrazione origine dati ODBC:
oppure utilizzare direttamente una
ConnectionString come la seguente:
Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=C:\MyExcel.xlsIn questo modo si può rendere la connessione parametrica ed è possibile a runtime caricare facilmente file diversi. Analogamente si può fare anche via
OLEDB e il
Driver JET 4.0:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0"E' buona abitudine andare ad inserire
ConnectionString e impostazioni varie di una applicazione Windows Forms nel file
app.config.
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<add key="SQLConnection" value="Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0; "/>
</appSettings>
</configuration>
Utilizzeremo come file
Excel d'esempio un semplice file con dei valori casuali inseriti nella colonna A nel
Foglio1:
Ed ecco qui di seguito il frammento di codice chiave che ci consente di aprire un file
Excel ed eseguire delle Query come se fosse un database relazionale. Infatti sarà necessario utilizzare la tradizionale
OleDbConnection, un
OleDbCommand con il relativo statement
SQL e un
OleDbDataAdapter per riempire la nostra
DataTable.
N.B. Vi ricordo che è anche possibile utilizzare altri comandi
SQL come
INSERT e DELETE e quindi non solo leggere ma anche inserire o eliminare dati nel foglio di
Excel.
string filename = @” c:\test\fileExcel.xls”;
OleDbConnection connection = new OleDbConnection(ConfigurationManager.AppSettings["ConnectionStringODBC"].ToString()+ "Data Source=" + fileName);
using (connection)
{
connection.Open();
{
string SQL = "select * from Foglio1”;
OleDbCommand command = new OleDbCommand(SQL, connection);
OleDbDataAdapter adpt = new OleDbDataAdapter(command);
DataTable table = new DataTable();
adpt.Fill(table);
command.Dispose();
connection.Close();
}
La
DataTable riempita a questo punto può agevolmente diventare il
Data Source di una nostra
DataGrid o
ListBox. Non verrà mostrato il codice di DataBind della Table con la GridView in quanto ampiamente descritto in altri articoli, per esempio qui:
Tutorial ADO.NET ed esempi pratici - Parte 2 Implementazione tramite Drag & Drop
La modalità più suggestiva e che riscuote sicuramente maggior successo anche per usabilità e interattività per l'utente è quella che si basa sul
Drag and Drop delle celle direttamente dal file Excel aperto. La prima operazione che dobbiamo compiere è impostare la proprietà
AllowDrop della
ListBox a
true. Ciò permette alla
ListBox di essere sensibile ad eventuali operazioni di trascinamento.
Ora dovremmo preoccuparci di gestire gli eventi fondamentali del
Drag and Drop riepilogati qui di seguito:
- DragEnter- DragDropIn particolare l'evento
DragEnter ci servirà per fare un controllo di coerenza sui dati che stiamo trascinando nella nostra
ListBox, permettendo o meno all'utente di trascinare particolari tipi di valori. Nel nostro caso daremo all'utente la possibilità di fare
Drag and Drop di soli
valori ASCII separati da virgola.
if(e.Data.GetDataPresenter(DataFormats.CommaSeparatedValue))
{
e.Effect = DragDropEffect.All
}
A questo punto possiamo sbizzarrirci con l'evento
DragDrop della
ListBox che scatta nel momento in cui si rilascia il mouse dopo aver iniziato il trascinamento andando a gestire la logica di importazione dei dati. Per semplificare l'esempio andremo a implementare un'operazione banale come l'importazione di un elenco di codici nella ListBox.
In questo esempio avremo bisogno anche di un'area temporanea in cui immagazzinare i dati, faremo quindi ricorso alla classe
MemoryStream:
MemoryStream memoryData = (MemoryStream)e.Data.GetData(DataFormats.CommaSeparatedValue,false);
if(memoryData!=null && memoryData.lenght>0)
{
byte[] buffer = new byte[memeoryData.lenght];
int i = memoryData.Read(buffer,0,(int)memoryData.lenght);
ASCIIEncoding asciiEncoding = new ASCIIEncoding();
string result = asciiEncoding.GetString(buffer);
}
Ora ci è sufficiente attraverso la funzione
Split della classe
String andare a fare il parse per individuare i nostri codici ed attraverso un ciclo
foreach popolare la nostra
ListBox:
string separator = ",";
string[] commaSeparatedCodes = result.Split(separator.ToCharArray());
foreach(string code in commaSeparatedCodes)
{
this.listBox1.Items.Add(code);
}
A questo punto, così come nell'implementazione precedente, troveremo lo stesso risultato ossia la nostra
ListBox popolata con i codici presenti nelle celle del file Excel.
Naturalmente è possibile estendere questi concetti e costruire applicazioni più evolute ed avanzate che possano anche gestire non solo dati multi-riga ma anche dati multi-colonna quindi importare integralmente griglie di dati da
Excel.