Jan 15, 2015

Upload and Read Excel File (.xls, .xlsx) in ASP.NET MVC

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:

Excel Read

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.

10 comments

  1. It’s throwing error
    ‘IExcelDataReader’ does not contain a definition for ‘IsFirstRowAsColumnNames’ and no extension method ‘IsFirstRowAsColumnNames’ accepting a first argument of type ‘IExcelDataReader’ could be found (are you missing a using directive or an assembly reference?)

    1. If you are using ExcelDataReader 3+ you will find that there isn’t any method for AsDataSet() for your reader object, You need also to install another package for ExcelReader.DataSet, then you can use AsDataSet() method.
      Also there is not a property for IsFirstRowAsColumnNames instead of that you need to set it inside of ExcelDataSetConfiguration

      var conf = new ExcelDataSetConfiguration
      {
      ConfigureDataTable = _ => new ExcelDataTableConfiguration
      {
      UseHeaderRow = true
      }
      };

  2. Hi great work sir, can you help me this i want uploaded .xls file save in databse table with prorper cloumns entry in table.

    1. I tried the Same code in view if (Model != null) , Model is getting Null , I Have use the namespace of Datatable Though Model is getting null . Any reason ??

Leave a Reply

Your email address will not be published. Required fields are marked *