Could not able to open OleDb Connection

Question

I Could not able to open OleDbConnection, I get exception when code calls Open() method. What i am trying to do is load from from excel to Data table.

My local machine has Office 2016 (64), I have installed AccessDatabaseEngine_X64,

Sometime I get error as : external table is not in the expected format excel 2016.
And Sometime I get error as : The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine."

This is my code  and I have tried all of these settings:

<appSettings>

<add key="OleDBProvider" value="Microsoft.ACE.OLEDB.12.0"/>

<!--<add key="ExtendedProperties" value="'Excel 8.0;HDR=Yes;IMEX=1'"/>-->

<!--<add key="ExtendedProperties" value="'Excel 12.0;HDR=Yes;'"/>-->

<add key="ExtendedProperties" value="'Excel 12.0 Xml;HDR=Yes;'"/>

</appSettings>

 

private DataTable ExcelToDataTable(string dataSource, string selectColumn, string keyword)

{

string provider = OleDBProvider;

string ds = dataSource;

string extendedProperties = ExtendedProperties;

string cs = String.Format("Provider={0}; Data Source={1}; Extended Properties={2};", provider, ds, extendedProperties);

var xlConn = new OleDbConnection(cs);

DataTable dtXlSchema;

dt = new DataTable("ReviewTable");

try

{

xlConn.Open();

dtXlSchema = xlConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

for (int i = 0; i < dtXlSchema.Rows.Count; i++)

{

string sTableName = dtXlSchema.Rows[i]["Table_Name"].ToString();

string query = "";

if (!String.IsNullOrWhiteSpace(keyword))

{

query = "Select [" + selectColumn + "] from [" + sTableName + "] where [" + selectColumn + "] like '%" + keyword + "%' Order by [" + selectColumn + "]";

// query = "Select [s-ip] from [" + sTableName + "] where [s-ip] like '%" + kw + "%' Order by [s-ip]";

}

else

{

query = "Select [" + selectColumn + "] from [" + sTableName + "]Order by [" + selectColumn + "]";

// query = "Select [cs-uri-stem] from [" + sTableName + "]Order by [cs-uri-stem]";

// query = "Select [s-ip] from [" + sTableName + "]Order by [s-ip]";

}

OleDbDataAdapter da = new OleDbDataAdapter(query, xlConn);

da.AcceptChangesDuringFill = true;

da.Fill(dt);

da.Dispose();

}

}

catch (Exception ex)

{

WriteError(ex);

}

finally

{

xlConn.Close();

}

return dt;

}