Alzi la mano che non si è mai sentito chiedere da un cliente: "vorrei che i dati fossero importati da questi file excel". Una richiesta più che legittima. Dato che molte aziende come primo baluardo di informatizzazione hanno adottato Excel alla stregua di una base dati, spesso nel momento in cui si decide di affidarsi ad un consulente per fare il primo vero passo verso lo sfruttamento del computer come strumento di analisi e produzione, ci si trova ad avere una montagna di dati letteralmente accatastata in fogli Excel che in qualche modo, possibilmente non manuale deve entrare a far parde di una base dati nascente. La domanda a Mr. Key di quest'oggi riguarda proprio questo problema ed è stata l'occasione di approfondire l'interazione con i fogli excel.

Chiunque abbia mai usato un file excel sa che è in realtà esso è una cartella che contiene un numero imprecisato di fogli di lavoro (Worksheet). Ogni foglio di lavoro è suddiviso in Colonne e Righe. Per inciso le Colonne potranno essere al massimo 256 mentre le right 65536. E' evidente che una organizzazione di questo tipo consente di considerare un file excel alla stregua di un database (ad esempio Access) in cui i fogli di lavoro siano le tabelle e naturalmente esse siano suddivise come consueto in righe e colonne. Se si riuscisse a lanciare un comando SQL perciò si potrebbero agevolmente estrarre i dati. Per fortuna esistono OLEDB e JET. Infatti grazie ad essi è davvero molto semplice andare a prelevare i dati presenti nei fogli per farne più o meno quello che si vuole.

Innanzitutto occorre scrivere la string di connessione che è la seguente:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\path\to\file.xls;
   
Extended Properties="Excel 8.0;HDR=YES;"

Come si può vedere il Provider è Jet 4.0, lo stesso che si usa per Access, e la data source è il file excel. Infine occorre specificare le Extended Properties. In particolare "Excel 8.0" di ovvio significato e HDR che informa il provider di usare (YES) o non usare (NO) la prima riga per dare il nome alle colonne della tabella. Sulle Extended Properties occorre segnalare che esse vanno sempre poste tra doppi apici, come indicato nell'esempio, perchè altrimenti in qualche caso si otterrà un errore foriero di lunghe sedute alla ricerca di inutili soluzioni. A me ad esempio, mentre scrivevo il codice allegato al presente articolo, è successo che a causa di una banale svista nel fare l'escape degli apici in una stringa C#, mi si presentava un errore che diceva "Could not find installable ISAM". ISAM, acronimo di Indexed Sequential Access Method è un sistema che consente l'accesso sequenziale o indicizzato a file e viene usato da Jet per l'accesso ai file Excel. Se provate a digitare il messaggio di errore in Google, vi troverete di fronte a molte migliaia di entry che spiegano come modificare il registry per risolvere questa evenienza. Purtroppo nessuno di essi ha ragione perciò prima di reinstallare completamente e inutilmente office, come suggerito leggete questo breve post che spiega che tale errore si verifica semplicemente perchè nella stringa di connessione mancano gli apici.

Una volta che la stringa di connessione è correttamente composta, per accerede alle "tabelle" basta semplicemente conoscere la seguente regola: il nome della tabella è dato dal nome del foglio di lavoro, comprensivo di spazi e seguito dal simbolo di $. Il fatto che sia composto anche da spazi richiede che esso venga specificato tra parentesi quadre per mantenerne l'unitarietà. Ecco quindi un breve spezzone di codice che estrae un foglio e lo mostra in una DataGrid:

/// 
/// Read the data in the selected sheet and 
/// display it into the datagrid
/// 

private void ReadCurrentSheet()
{
    
try
    
{
        
// compose the sql statement using square bracket to surround tablename
        
string sql = 
            
string.Format( "select * from [{0}]", SheetCombo.SelectedValue );

        
// open the connection to the file
        
using( OleDbConnection connection = 
         
new OleDbConnection( this.ConnectionString ) )
        {
            connection.Open();

            
// create an adapter
            
using( OleDbDataAdapter adapter = 
              
new OleDbDataAdapter( sql, connection ) )
            {
                
// clear the datatable to avoi old data persistance
                
mData.Clear();
                mData.Columns.Clear();

                
// fille the datatable
                
adapter.Fill( mData );
            }
        }
    }
    
catch( Exception ex )
    {
        DisplayException( ex );
    }
}

L'esempio è davvero banale. Usa una semplice "select * from" per leggere tutti i record presenti nel foglio di lavoro. Naturalmente sarà possibile sempre usare una clausola where, ricordando di specificare le parentesi quadre per i nomi colonna. Nell'esempio però ho voluto andare oltre. Cercando di scrivere una piccola applicazione in grado di visualizzare qualunque file excel indipendentemente dal numero di fogli di lavoro presenti, ho dovuto appunto cercare i fogli all'interno del file. Per ottenere questo risultato è necessario fare uso del metodo GetOleDbSchemaTable() specificando OleDbSchemaGuid.Tables coe parametro. Risultato di questo metodo una DataTable contenente un numero di informazioni molto elevato tra cui, oltre al nome dei fogli anche la data di creazione e di ultima modifica. Variando il parametro OleDbSchemaGuid, sicuramente ci si imbatterà in altre utilissime informazioni a proposito del file e dei fogli in esso contenuti.

Ecco quindi il metodo che popola la Combo che i nomi dei fogli di lavoro:

/// 
/// Read the worksheets into the selected 
/// files and fill the SheetCombo control
/// 

private void ReadWorksheets()
{
    
try
    
{
        
// open connection to the file
        
using( OleDbConnection connection = 
         new OleDbConnection(this.ConnectionString))
        {
            connection.Open();

            
// read file schema
            
DataTable sheetData = 
                connection.GetOleDbSchemaTable( 
                    OleDbSchemaGuid.Tables, 
null );
                    
            
// assign result to SheetCombo
            
SheetCombo.DisplayMember = 
              SheetCombo.ValueMember = "TABLE_NAME";

            SheetCombo.DataSource = sheetData;
            
            
// read the firt sheet data
            
ReadCurrentSheet();
        }
    }
    
catch( Exception ex )
    {
        DisplayException( ex );
    }
}

In particolare la combo viene bindata alla colonna TABLE_NAME per mostrare il nome dei fogli che contiene già anche il simbolo del $ in coda al nome. Perciò quando si fa la query per estrarre i dati, è sufficiente aggiungere solamente le parentesi quadre. L'applicazione che ho scritto fa uso innanzitutto di questo metodo per scoprire i fogli contenuti nel file ed in seguito del metodo ReadCurrentSheet() per popolare la datagrid. In questo modo, si potrà aprire ed esplorare qualsiasi foglio excel.

Download: AskMrKey.Examples.ExcelWithAdoNet.zip (39Kb)

Keywords: leggere file excel in ado.net

powered by IMHO 1.2

tags: - categories:

Commenti (2) -

# | Radicalmente | 19.09.2005 - 22.25

# | LUNA | 01.07.2008 - 04.04

Ciao!Questa pagina è stupenda e mi ha molto aiutata!
Volevo però fare una domanda..che cosa è App.ico nelle soluzioni explorer?
Perchè ho provato a ricopiare il codice rifacendo da me la form, ma quando premo il bottone non mi parte il collegamento...l'unica cosa di diverso è questa APP.ico..volevo sapere se poteva centrare...
Ringrazio anticipatamente e spero in una risposta
Saluti,
LUNA.

Aggiungi Commento