Could not able to open oledbconnection...

See more:

.NET4

OleDb

C#7.0

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 :

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;
      }

What I have tried:

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>