Are you using traditional way to read .xls and .xlsx files in ASP.NET/C#? If yes, you might be using following providers:

Microsoft.Jet.OLEDB.4.0 provider for Excel 97-2003 format (.xls)

Microsoft.ACE.OLEDB.12.0 provider for Excel 2007+ format (.xlsx)

and you may get the following common errors:

The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine.

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

and probably, you would go for following approach to fix them

1. Installation of Office 2007 Data Connectivity Components or Office 2010 Database Engine on the Server.

2. The application pool that the site is running set "Enable 32-bit applications" to "True"

3. In project, Change Platform target from: Any CPU to x86 and Rebuild it.

What would happen if you have shared hosting or Azure Website hosting and You are not allowed to install ACE Database engine on the server?

In this post, we will see how to upload and read both .xls and .xlsx excel files without any server dependencies in ASP.NET MVC. We will use ExcelDataReader, an Open Source project to read excel files.

Steps:

1. Create ASP.NET MVC 5 Empty Project

2. To install ExcelDataReader, run the following command in the Package Manager Console:

Install-Package ExcelDataReader

3. Add New Controller say HomeController and add following action:

 public ActionResult Upload()
        {
            return View();
        }

4. Add View of Upload action and use following code:

@model System.Data.DataTable
@using System.Data;

<h2>Upload File</h2>

@using (Html.BeginForm("Upload", "Home", null, FormMethod.Post, new { enctype = "multipart/form-data" }))
{
    @Html.AntiForgeryToken()    
    @Html.ValidationSummary()
    
    <div class="form-group">
        <input type="file" id="dataFile" name="upload" />
    </div>
    
    <div class="form-group">
        <input type="submit" value="Upload" class="btn btn-default" />
    </div>
    
    if (Model != null)
    {
        <table>
            <thead>
                <tr>
                    @foreach (DataColumn col in Model.Columns)
                    {         
                        <th>@col.ColumnName</th>
                    }
                </tr>
            </thead>
            <tbody>
                @foreach (DataRow row in Model.Rows)
                {        
                    <tr>
                        @foreach (DataColumn col in Model.Columns)
                        {             
                            <td>@row[col.ColumnName]</td>
                        }
                    </tr>
                }
            </tbody>
        </table>
    }
}

We will read excel, get data in DataTable and show DataTable in View.

5. To read the submitted file:

 [HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult Upload(HttpPostedFileBase upload)
        {
            if (ModelState.IsValid)
            {

                if (upload != null && upload.ContentLength > 0)
                {
                    // ExcelDataReader works with the binary Excel file, so it needs a FileStream
                    // to get started. This is how we avoid dependencies on ACE or Interop:
                    Stream stream = upload.InputStream;

                    // We return the interface, so that
                    IExcelDataReader reader = null;


                    if (upload.FileName.EndsWith(".xls"))
                    {
                        reader = ExcelReaderFactory.CreateBinaryReader(stream);
                    }
                    else if (upload.FileName.EndsWith(".xlsx"))
                    {
                        reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
                    }
                    else
                    {
                        ModelState.AddModelError("File", "This file format is not supported");
                        return View();
                    }

                    reader.IsFirstRowAsColumnNames = true;
                    
                    DataSet result = reader.AsDataSet();
                    reader.Close();

                    return View(result.Tables[0]);
                }
                else
                {
                    ModelState.AddModelError("File", "Please Upload Your file");
                }
            }
            return View();
        }

It is assumed the file will have column names in first row.

Output:

Conclusion:

ExcelDataReader has no dependencies on external database engines which may not be available on a remote server. It is helpful to deploy application to Windows Azure Websites, Shared hosting or any other machine where the ACE Database engine is not available. We have seen how easy to read excel sheets in ASP.NET MVC with ExcelDataReader.

Hope It helps. Feel free to share your opinion in comment box.