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 >
<!--percorso e cartella dove archiviare il file-->
<add key="FOLDERARCHIVIO" value="E:\TestFile"></add>
<!--nome del file da elaborare-->
<add key="FOLDERNOMEFILEXCEL" value="E:\NomeFileExcel.xls"></add>
<!--true viene archiavito il file nella cartella folderarchivio false il file non viene archiviato-->
<add key="ARCHIVIAZIONE" value="true"></add>
<!--Nome del foglio excel che viene elaborato-->
<add key="NOMEEFOGLIO" value="Foglio1"></add>
<!--nomi delle colonne da cui estrae i dati-->
<add key="NOMICOLONNEEXCEL" value="A,B"></add>
<!--true = che calcola il vero nome delle colonne - false = che prende il nome così com'è scritto-->
<add key="RILEVANOMICOLoNNE" value="true"></add>
<!--nome della tabella su cui inserire i dati-->
<add key="NOMETABELLADB" value="Anagrafica"></add>
<!---rileva i campi della tabella su cui inserire i campi il numero dei campi deve essere uguale a quello delle colonne Exel-->
<add key="CAMPITABELLA" value="Nome, Cognome"></add>
<!---stringa di connessione-->
<add key="CONNECTION" value="Data Source=NomeServer;Persist Security Info=True;User ID=UserName;password=Password;"></add>
<!--Il tipo di database a cui collegarsi (es. oracle, sql server, etc)-->
<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 += NomiColonne[ContaElementi] + ",";
ValoreColonna += "'" + pRigaValori[ContaElementi] + "',";
}
NomeColonna = NomeColonna.Substring(0, NomeColonna.Length -1);
ValoreColonna = ValoreColonna.Substring(0, ValoreColonna.Length -1);
return "INSERT INTO " + pNomeTabella + " (" + NomeColonna +") VALUES (" + ValoreColonna + ")";
}
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.