How to resolve the error or no response while reading from excel file in .xlsx format using Oledb in c#?
I am reading the data from Excel via Oledb connection. I am getting the error "attempted to read write protected memory. this is often an indication that other memory is corrupt"." or with no response on filling data to data table for excel files with .xlsx format. Else with .xls format it works fine.
My code is as below:
if (fileExtension == ".xls" || fileExtension == ".XLS")
{
connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filelocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
}
else if (fileExtension == ".xlsx" || fileExtension == ".XLSX")
{
connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filelocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
}
DataTable dt = new DataTable();
System.Data.OleDb.OleDbConnection MyConnection;
System.Data.DataSet ds;
System.Data.OleDb.OleDbDataAdapter MyCommand;
MyConnection = new System.Data.OleDb.OleDbConnection(connStr);
MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection);
ds = new System.Data.DataSet();
MyCommand.Fill(dt);
MyConnection.Close();
What I have tried:
My code is as below:
if (fileExtension == ".xls" || fileExtension == ".XLS")
{
connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filelocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
}
else if (fileExtension == ".xlsx" || fileExtension == ".XLSX")
{
connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filelocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
}
DataTable dt = new DataTable();
System.Data.OleDb.OleDbConnection MyConnection;
System.Data.DataSet ds;
System.Data.OleDb.OleDbDataAdapter MyCommand;
MyConnection = new System.Data.OleDb.OleDbConnection(connStr);
MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection);
ds = new System.Data.DataSet();
MyCommand.Fill(dt);
MyConnection.Close();