Introduction

Generally, most of the time, we work on Excel import functionality in our web application. As per our requirement, we import data from an Excel ( .xls or .xlsx ) file and read the data from Excel file through OLEDB connection and assign in a datatable. So when I developed the application in my local machine and implemented the Excel import functionality, I did not get any issue in debug time. But when I hosted my application in IIS, I got an error message like "The ' Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine ". I Googled it, but I did not get the exact solution  and most of the solutions are related to changing the application build target platform from any CPU to 86. Yes, I also tried a lot with the above solutions, but does not work for me. But when I changed some IIS level configuration, the issue was resolved. Let me explain it briefly.

Using the Code

In the below code, I am just trying to upload one .xls file and trying to fetch the tabular data from Excel file and assigning to a datatable. Let me share my code here.

string FileToConvert = Server.MapPath( " ." ) + " MyImportFile.xls" ; // Checking the file has uploaded by the User or not if (fuUploadFile.HasFile) fuUploadFile.SaveAs(FileToConvert); dtExcelData = new DataTable(); HDR = hasHeaders ? " Yes" : " No" ; // Connection string as per the file type if (filePath.Substring(filePath.LastIndexOf( ' .' )).ToLower() == " .xlsx" ) strCon = " Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + " ;Extended Properties=\"Excel 12.0;HDR=" + HDR + " ;IMEX=0\"" ; strCon = " Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + " ;Extended Properties=\"Excel 8.0;HDR=" + HDR + " ;IMEX=0\"" ; using (OleDbConnection conn = new OleDbConnection(strCon)) conn.Open(); DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null , null , null , " TABLE" }); // Looping a first Sheet of Xl File DataRow schemaRow = schemaTable.Rows[0]; string sheet = schemaRow[ " TABLE_NAME" ].ToString(); if (!sheet.EndsWith( " _" )) string query = " SELECT * FROM [" + sheet + " ]" ; OleDbDataAdapter daexcel = new OleDbDataAdapter(query, conn); dtExcelData.Locale = CultureInfo.CurrentCulture; daexcel.Fill(dtExcelData); // conn.Close(); dtExcelData = dtExcelData.AsEnumerable().Where(row => !row.ItemArray.All(f => f is System.DBNull || String .IsNullOrEmpty(f.ToString()))) .CopyToDataTable(); Trace.Write( " ReadFromExcelFile" , " No file was uploaded" ); catch (Exception ex) // Handle the exception

Let Me Share My Error As Well

When I deployed my application in IIS and tried to import a .xls file, I got the below error:

Unhandled Exception in /MyWeb/ExportWeb:
        Type: InvalidOperationException
        Message: The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine.
Exception stack trace(s):
   at System.Data.OleDb.OleDbServicesWrapper.GetDataSource
   (OleDbConnectionString constr, DataSourceWrapper& datasrcWrapper)
   at System.Data.OleDb.OleDbConnectionInternal..ctor
   (OleDbConnectionString constr, OleDbConnection connection)
   at System.Data.OleDb.OleDbConnectionFactory.CreateConnection
   (DbConnectionOptions options, 
   Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection
   (DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection
   (DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection
   (DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.OleDb.OleDbConnection.Open()
Event information:
        Event code: 106660
        Event message: The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine.
        Event time: 09/11/2015 10:25:07
        Event ID: 7eba2342sdfdf3423423

So Here is the Solution

Just go to the IIS and check your application pool.

Select the application pool and click on the "Advanced Settings" of the selected application pool.

Then change the property "Enable 32-Bit Applications" to True.

So after changing it, my import functionality works fine for me.