Hi M.Ncube
I have written a small function to read from excel file. It uses a different approach to connect. I have a simple .Net windows app that reads data from Excel/CSV.
Hope this helps
public static bool ReadExcel()
{
MDIParent.dtExcel = null;
MDIParent.lstEntitySheets = new List<DataTable>();
OpenFileDialog openFileDialog = new OpenFileDialog();
openFileDialog.ShowDialog();
openFileDialog.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
openFileDialog.Filter = "Microsoft Excel 97-2003 (.xls)|*.xls|Microsoft Excel 2007 (.xlsx)|*.xlsx";
filePath = openFileDialog.FileName;
string connectionString = string.Empty;
if (filePath.EndsWith(".xls"))
connectionString = @"provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';"; //for below excel 2007
else if (filePath.EndsWith(".xlsx"))
connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1';;"; //for above excel 2007
else
{
MessageBox.Show("Please choose .xlsx file only.", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error);
return false;
}
try
{
OleDbConnection oledbConnection = new OleDbConnection(connectionString);
OleDbDataAdapter oledbdataAdapter = new OleDbDataAdapter();
OleDbCommand oledbCommand = new OleDbCommand();
oledbConnection.Open();
MDIParent.dtExcel = oledbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
MDIParent.dtExcel.Columns.Add(new DataColumn("IsValid", typeof(bool)));
///READ FILE ROWS
for (int j = 0; j < MDIParent.dtExcel.Rows.Count; j++)
{
if (MDIParent.dtExcel.Rows[j]["TABLE_NAME"].ToString().ToLower() == "information$")
MDIParent.dtExcel.Rows.Remove(MDIParent.dtExcel.Rows[j]);
}
oledbConnection.Close();
}
catch (Exception ex)
{
if (ex.InnerException != null && !string.IsNullOrEmpty(ex.InnerException.Message))
{
MessageBox.Show("Could not read the file due to an Error." + Environment.NewLine + "ERROR: " + ex.InnerException.Message);
Config.SetLog("Could not read the file due to an Error.");
Config.SetLog("ERROR: " + ex.InnerException.Message);
}
else
{
MessageBox.Show("Could not read the file due to an Error." + Environment.NewLine + "ERROR: " + ex.Message);
Config.SetLog("Could not read the file due to an Error.");
Config.SetLog("ERROR: " + ex.Message);
}
return false;
}
return true;
}
Regards
Pranav
