Si riporta l'articolo scritto per il sito
http://www.iprogrammatori.it/articoli/programmazione/art_net-importare-i-dati-da-un-file-excel-in_1390.aspx
Introduzione
In questo articolo vedremo come creare un semplice programma (Windows Application) che forniti alcuni parametri, estrapola i dati da un file Excel e li inserisce in una tabella di Oracle.
Creazione progetto
Creare una tabella (Anagrafica) con i seguenti campi, Nome e Cognome di tipo Varchar 255.
A questo punto salvate la tabella con il nome Anagrafica.
Create un file Excel, impostando in A1 il valore Nome, ed in B1 il valore Cognome.
A questo punto inserite nelle successive righe (a2, a3, a4, b2, b3,b4) i dati di vostro piacimento.
Terminata la creazione di tabella e file, aprite Visual Studio 2010 o versione successiva, e selezionate come linguaggio di programmazione C#, il modello selezionate Windows Application o console application.
Stesura del codice
Passate in visualizzazione codice, aggiungete i riferimenti alla libreria “Microsoft.Practices.EnterpriseLibrary.Common” e “Microsoft.Practices.EnterpriseLibrary.Data” “Microsoft.Practices.ObjectBuilder” le librerie EnterpriseLibrary, ci permettono di seguire query (insert) anche nel caso che un domani vogliamo cambiare il tipo di database, per esempio in Sql Server, in questo modo evitiamo di riscrivere tutto l’applicativo.
Aggiungiamo al progetto un file di tipo “AppSetting” in questo file ci saranno i parametri che permetteranno di gestire le informazioni di elaborazione, come nome colonne, nome foglio, file Excel, tabella, nome colonne, ed altro ancora.
Qui di seguito si riporta il contenuto del file di configurazione.
<appSettings >
<add key="FOLDERARCHIVIO" value="E:\TestFile"></add>
<add
key="FOLDERNOMEFILEXCEL" value="E:\NomeFileExcel.xls"></add>
<add
key="ARCHIVIAZIONE" value="true"></add>
<add key="NOMEEFOGLIO" value="Foglio1"></add>
<add
key="NOMICOLONNEEXCEL" value="A,B"></add>
<add key="RILEVANOMICOLoNNE" value="true"></add>
<add key="NOMETABELLADB" value="Anagrafica"></add>
<add key="CAMPITABELLA" value="Nome, Cognome"></add>
<add key="CONNECTION" value="Data Source=NomeServer;Persist Security Info=True;User ID=UserName;password=Password;"></add>
<add
key="PROVIDERNAME" value="System.Data.OracleClient"></add>
</appSettings>
I parametri sono semplici da comprendere, forse un parametro di non facile comprensione può essere quello relativo al valore “RilevaNomiColonne”, il quale indica che vanno calcolate i veri nomi delle colonne, se per esempio mettiamo A, B, lui rileverà i veri nomi che abbiamo messo nell’intestazione.
Nell’evento click del pulsante o nel load della form o quando viene eseguito l’applicativo di tipo console, scriviamo il seguente codice.
try
{
string PercorsoArchivio =
System.Configuration.ConfigurationManager.AppSettings["FOLDERARCHIVIO"];
Boolean Archiviazione = Convert.ToBoolean(System.Configuration.ConfigurationManager.AppSettings["ARCHIVIAZIONE"]);
string
FolderFileExcel = System.Configuration.ConfigurationManager.AppSettings["FOLDERNOMEFILEXCEL"];
string
NomiColonneExcel = System.Configuration.ConfigurationManager.AppSettings["NOMICOLONNEEXCEL"];
string
NomeFoglio = System.Configuration.ConfigurationManager.AppSettings["NOMEEFOGLIO"];
Boolean
RilevaNomiExcel = Convert.ToBoolean(System.Configuration.ConfigurationManager.AppSettings["RILEVANOMICOLONNE"]);
string
NomeTabellaDB = System.Configuration.ConfigurationManager.AppSettings["NOMETABELLADB"];
string
NomiColonneTabellaDB = System.Configuration.ConfigurationManager.AppSettings["CAMPITABELLA"];
string
ConnectionString = System.Configuration.ConfigurationManager.AppSettings["CONNECTION"];
string
ProvviderName = System.Configuration.ConfigurationManager.AppSettings["PROVIDERNAME"];
if
(NomiColonneExcel.Split(',').Count() !=
NomiColonneTabellaDB.Split(',').Count())
{
Messagebox.Show("Il numero dei campi
Excel e numero dei campi della tabella non sono uguali, verificare la
correttezza dei dati.");
}
else
{
//continuo l'elaborazione.
string SqlExcel
= "SELECT
";
if (RilevaNomiExcel
== true)
{
SqlExcel +=
GetNameColumn(lFolderFileExcel, lNomeFoglio, NomiColonneExcel);
}
else
{
SqlExcel +=
NomiColonneExcel;
}
SqlExcel = SqlExcel + " FROM ["
+ NomeFoglio + "$];";
EseguiQuery(FolderFileExcel,
SqlExcel, NomeTabellaDB, NomiColonneTabellaDB, ConnectionString , ProvviderName);
}
}
catch
(Exception ex)
{
Messagebox.Show(ex.Message,
ex);
}
Come si è visto dal frammento del codice precedente, dopo aver rilevato I dati del file di configurazione, si crea la query sql da eseguire sul file Excel, come se fosse a tutti gli effetti una tabella di database, per la query select, viene generato verificando l’effettivo nome delle colonne, il tutto tramite la funzione “GetNameColumn”.
Mentre la funzione EseguiSql, esegue l’operazione di importazione dati.
Vediamo in dettaglio le varie funzioni ed istruzioni.
La funzione GetNameColumn, riportato qui di seguito, accetta tre parametri, il nome del file, il foglio su cui effettuare la connessione e quali colonne prendere in esame (a,b), separati dalla virgola,
Il codice estrapola i vari nomi delle colonne, che corrispondono alle intestazione di colonne, che abbiamo impostato inizialmente quando si è creato il file Excel, in A1 abbiamo inserito “Nome” ed in B1 “cognome.
Ogni colonna è racchiusa tra parentesi quadrate, perché nel caso che sono presenti colonne con spazi, questo le considera come nomi colonne.
Qui di seguito si riporta il codice completo di tale funzione.
private static string GetNameColumn(string
pPercorsoNomeFileExcel, string pNomeFoglio, string pLetteraColonna)
{
const string cLettereExcel
= "ABCDEFGHIJKLMNOPQRSTUVWXYZ"; //26
DataTable
pColonne = new DataTable();
string
NomeColonna = "";
string
strConnectionString = "";
if
(pPercorsoNomeFileExcel.Trim().EndsWith(".xlsx"))
strConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data
Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES\";",
pPercorsoNomeFileExcel);
else
if (pPercorsoNomeFileExcel.Trim().EndsWith(".xls"))
{
strConnectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source={0};Extended Properties=\"Excel 8.0;HDR=Yes\";",
pPercorsoNomeFileExcel);
}
using
(OleDbConnection Connection = new OleDbConnection(strConnectionString))
{
Connection.Open();
OleDbDataReader
Record;
OleDbDataAdapter
DtaQuery = new OleDbDataAdapter("Select top 1 * from [" + pNomeFoglio + "$] ;",
Connection);
DataTable
DttDati = new DataTable();
DtaQuery.Fill(DttDati);
string[] Colonne = pLetteraColonna.Split(',');
for (int
ContaColonne = 0; ContaColonne < Colonne.Length; ContaColonne++)
{
if (ContaColonne > 0)
{
NomeColonna += ",";
}
int Indice = -1;
if (Colonne[ContaColonne].Trim().Length
> 1)
{
for (int ContaElementi = 0;
ContaElementi < lColonne[ContaColonne].Length; ContaElementi++)
{
if (ContaElementi > 0)
{
Indice += 25 +
cLettereExcel.IndexOf(Colonne[ContaColonne].Trim().Substring(ContaElementi +
1));
}
else
{
Indice =
cLettereExcel.IndexOf(lColonne[ContaColonne].Trim().Substring(ContaElementi +
1)) + 1;
}
}
}
else
{
Indice =
cLettereExcel.IndexOf(lColonne[ContaColonne].Trim());
}
NomeColonna += "[" + DttDati.Columns[Indice].ColumnName.Trim() + "]";
}
Connection.Close();
}
return NomeColonna;
}
La funzione EseguiQuery, che accetta come argomenti, il nome del file Excel, la query SQL da eseguire su Excel, il nome della tabella e nome dei campi su cui eseguire l’inserimento, la stringa di connessione per collegarsi al database ed il tipo di Database.
La funzione effettua un colleganto al file Excel, ed esegue l’istruzione SQL per Excel, che gli viene passata, per ogni record che viene trovato, genera il codice sql di tipo Insert, da eseguire sul database Oracle (oppure su altro database) tramite la funzione “GetQueryInsertDB” , a questo punto viene eseguita la funzione EseguiInsert, che restituisce 0 nel caso che non ha buon esito, oppure 1 in caso di inserimento.
Qui di seguito si riporta la descrizione di tale funzione.
private static void EseguiQuery(string
pPercorsoNomeFileExcel, string pQueryExcel, string pNomeTabellaDB, string
pCampiDB, string pConnectionString, string pProviderName)
{
try
{
string
strConnectionString = "";
if
(pPercorsoNomeFileExcel.Trim().EndsWith(".xlsx"))
strConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data
Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES\";",
pPercorsoNomeFileExcel);
else
if (pPercorsoNomeFileExcel.Trim().EndsWith(".xls"))
{
strConnectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source={0};Extended Properties=\"Excel
8.0;HDR=Yes;IMEX=1;MaxScanRows=1;\";", pPercorsoNomeFileExcel);
}
using
(OleDbConnection Connection = new OleDbConnection(strConnectionString))
{
Connection.Open();
OleDbDataReader
Record;
OleDbCommand
Query = new OleDbCommand(pQueryExcel);
Query.Connection = Connection;
Record = Query.ExecuteReader();
DataTable
DttDatiRisultato = new DataTable();
DttDatiRisultato.Load(lRecord);
string
QueryInsert = "";
foreach
(DataRow Elemento in
DttDatiRisultato.Rows)
{
QueryInsert =
GetQueryInsertDB(pNomeTabellaDB, pCampiDB, Elemento);
//Eseguo la query
if (EseguiInsert(pConnectionString, pProviderName, QueryInsert)
> 0 )
{
Messagebox.Show("Record inserito correttamente." );
}
else
{
Messagebox.Show("Record non inserito.");
}
}
}
}
catch
(Exception ex)
{
throw
ex;
}
La funzione GetInsertQueryDB, che accetta come argomenti il nome tabella e nomi colonne, oltre ad un oggetto di tipo DataRow, nel quale sono presenti i valori da inserire nella base dati, genera l’istruzione SQL che servirà ad inserire i dati nella tabella.
Qui di seguito si riporta tale funzione.
private static string GetQueryInsertDB(string
pNomeTabella, string pNomiColonneDB, DataRow pRigaValori)
{
string NomeColonna = "";
string ValoreColonna = "";
string[] NomiColonne = pNomiColonneDB.Split(',');
for (int ContaElementi
= 0; ContaElementi < NomiColonne.Length; ContaElementi++)
{
NomeColonna += lNomiColonne[ContaElementi] + ",";
ValoreColonna += "'" +
pRigaValori[ContaElementi] + "',";
}
NomeColonna
= lNomeColonna.Substring(0, NomeColonna.Length -1);
ValoreColonna
= lValoreColonna.Substring(0, ValoreColonna.Length -1);
return "INSERT INTO
" + pNomeTabella + " ("
+ NomeColonna +") VALUES (" +
lValoreColonna + ")";
}
Siamo giunti all’ultima funzione, quella che permetterà di eseguire l’istruzione SQL di tipo Insert per inserire i valori nella base dati.
Utilizzando le classi messe a disposizione da EnterpriseLibray, possiamo utilizzare questo codice, su diversi database, ci basterà cambiera il tipo di provvider e connessione, nel file di configurazione.
La funzione accetta come parametri, la stringa di connessione, il tipo di provvider, e l’istruzione SQL. Tramite il metodo “ExecuteNonQuery” viene restituto il numero dei record inseriti (1) oppure in caso di errore, nessuno (0).
int EseguiInsert(string Connection,
string ProviderName, string
Query)
{
int
Return = 0;
DbProviderFactory
dbProviderFactory = DbProviderFactories.GetFactory(ProviderName);
Database
db = new GenericDatabase(Connection,
dbProviderFactory);
DbCommand
DBCommand = default(DbCommand);
DBCommand = db.GetSqlStringCommand(Query);
Return = db.ExecuteNonQuery(DBCommand);
return Return;
}
Conclusioni
In questo articolo abbiamo visto come possiamo realizzare una semplice applicazione di grande aiuto con notevole flessibilità ed adattabilità.
Tramite i parametri situati nel file di configurazione, possiamo inserire i dati presi da un file Excel ed inserirli in una tabella Oracle, senza rimettere codice, oppure cambiando il tipo di provider inserire in altri database, come in Sql Server, senza anche in questo caso rimettere mano nel codice.
Può tornare utile in quei scenari, in cui si devono importare diverse righe di Excel, su un database Oracle o altro.
Post
Le opere pubblicate in questo blog sono sotto la licenza Creative Commons. Attribuzione- No commerciale e no derivate.
