Reading an Excel file without any dependancy on oledb provider or something else

[NK sharma]

Hi ,

I am reading Excel file and then saving the cells in my database by assigning to the variables.

Actually its an application in which i import information about students(name, surname, classname, student ID etc).

So i need something to read the excel file.

I cant use microsoft.interop.excel and i used oledb earlier it worked well but still there was some problem when i tried to read the excel file onanother computer so i think oledb is also dependent.

So i need something which is independent of configuration.

Or atleas dependancies.

I got this link for this but i dont know to use this.

http://www.codeproject.com/Articles/18184/Excel-Adapter-for-ADO-NET

And right now i am using the following code but that is not working on other systems which have vista and excel 2003

public ErrorItem ProcessExcel(string filePath, Section section, bool itIsXLSX)
{


ErrorItem errorItem = new ErrorItem("Erreur lors de limportation du fichier Excel pour la section " + section.Name + " . Vérifiez que les lignes respectent le format Classe, Nom, Prénom.");
int lineCounter = 0;

try
{

string strConn;
if(!itIsXLSX)
strConn = " Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+filePath+";"+"Extended Properties=\"Excel 8.0;HDR=YES\"";
else
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+filePath+";"+"Extended Properties=\"Excel 12.0 Xml;HDR=YES\"";

using (OleDbConnection oleDB = new OleDbConnection(strConn))
{

DataSet DS = new DataSet();
string[] items = new string[10];

oleDB.Open();

// Get the name of the first worksheet:
DataTable dbSchema = oleDB.GetOleDbSchemaTable (OleDbSchemaGuid.Tables, null);
if (dbSchema == null || dbSchema.Rows.Count < 1)
{
throw new Exception ("Error: Could not determine the name of the first worksheet.");
}
string firstSheetName = dbSchema.Rows [0] ["TABLE_NAME"].ToString ();

// Now we have the table name; proceed as before:
OleDbCommand dbCommand = new OleDbCommand ("SELECT * FROM [" + firstSheetName + "]", oleDB);

//OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [Feuille1$]", oleDB);

DataTable dataTable = new DataTable();
OleDbDataReader reader = dbCommand.ExecuteReader();

//while(reader.Read())
dataTable.Load(reader);
int noOfColumns= dataTable.Columns.Count;
foreach (DataRow row in dataTable.Rows)
{
//Array.Clear(items, 0, items.Length);
lineCounter++;
string []currentLine ;
for(int i=0; i<6;i++)
{
if (noOfColumns < i + 1)
items = "";
else
{
items = (row.ItemArray.ToString());
items= items.Trim();
}
}

currentLine = items;
string messageForError = "";
int columnsNos = dataTable.Rows.Count;
if (columnsNos < 3 || items[dictionaryForCSVFormat["Classe"]] == "" || items[dictionaryForCSVFormat["Nom"]] == "" || items[dictionaryForCSVFormat["Prénom"]] == "")
{
if (items[dictionaryForCSVFormat["Nom"]] == "")
{
messageForError = "-> Nom manquant. Il est nommé comme: SANS_NOM \n";
string error = " Erreur à la ligne " + lineCounter + " du fichier :Excel " + messageForError ;
errorItem.ErrorQueue.Enqueue(error);
items[dictionaryForCSVFormat["Nom"]] = "SANS_NOM";
}
if (items[dictionaryForCSVFormat["Classe"]] == "")
{
messageForError = "-> Nom de classe manquant. Il est nommé comme: SANS_CLASSE\n";
string error = " Erreur à la ligne " + lineCounter + " du fichier :Excel " + messageForError ;
errorItem.ErrorQueue.Enqueue(error);
items[dictionaryForCSVFormat["Classe"]] = "SANS_CLASSE";
}
if (items[dictionaryForCSVFormat["Prénom"]] == "")
{
messageForError = "-> Prénom manquant. Il est nommé comme:Sans_Prénom\n";
string error = " Erreur à la ligne " + lineCounter + " du fichier :Excel " + messageForError ;
errorItem.ErrorQueue.Enqueue(error);
items[dictionaryForCSVFormat["Prénom"]] = "Son_Prénom";
}
saveValuesInDB(items, section, lineCounter);
}
else
saveValuesInDB(items, section,lineCounter);


}
ProcessLastNameCounter(section);
if (haveWorkersBeenImportedFlag)
{
System.Windows.Forms.MessageBox.Show("ATTENTION: Des personnes ont été importées dans la classe ENSEIGNANTS.\n" +
"Elles sont considérées comme faisant partie du personnel de lécole, et auront droit à des produits gratuits.\n" +
"Si vous pensez que ces personnes ne font pas partie du personnel, veuillez les déplacer hors de la classe ENSEIGNANTS", "Avertissement");
}
return errorItem;

}

}
catch (Exception ex)
{

throw ex;
}
}


thank you for your help.


Regards!

Continue reading...