ASP.NET MVC web application : How to Read Excel File

Download Source Code!

Introduction

This Article shows you some easy methods to read data from an Excel file:

• First method: using ExcelDataReader
• Second method: using LinqToExcel

Before we begin the explanation of examples, let's know about Excel file.

a) Brief Description of Excel File

Microsoft Excel File is a spreadsheet developed by Microsoft. Its features are calculation, graphic tools, and micro programming language using Visual Basic for Application (VBA).
Example of an Excel file data is shown below:

For a good understanding of the next examples, we should know about:
Worksheet: refers to a sheet of paper, is a single array of data in spreadsheet

 

b) Using ExcelDataReader

This library was useful for me, when I hosted my web project into Windows Azure using LinqToExcel and trying to parsing Excel data, so I got the following error message: "Erreur Microsoft.ACE.OLEDB.12.0 provider is not registered on the local machine". To avoid this bug, I switched from LinqToExcel to ExcelDataReader and I didn't make a lot of modification in my code.
ExcelDataReader is cross-platform that does not depend on Office or Ace(see more details).

1) Requirements

Add the ExcelDataReader to your project references (you can use Nuget packages):

 

2) Implementation

 public IEnumerable<Person> Method1(string fileSrc, string worksheet)  
       {    
         //Reading excel data using ExcelDataReader  
         var excelData = new ExcelData(fileSrc);  
         //Get data from specified worksheet  
         var query = excelData.getData(worksheet);  
         int cp = 0;  
         //intialize the result list  
         var list = new List<Person>();  
         foreach (var line in query)  
         {  
             try  
             {  
               var surname = line[0].ToString();//surname  
               var name = line[1].ToString();//name    
               var date = line[2].ToString();//custom date format  
               list.Add(new Person(surname, name, date));  
             }  
             catch (Exception exception)  
             {  
               ;  
             }  
         }  
         return list.AsEnumerable();  
       }  

c) Using LinqToExcel

In this example, we will use LinqToExcel to parsing an Excel File.

1) Requirements

Add the LinqToExcel lib to your project references (you can use Nuget packages).

2) Implementation

First Implementation

   public IEnumerable<Person> Method2(string fileSrc, string worksheet)  
       {  //Reading excel data using LinqToExcel  
         var excelData = new ExcelQueryFactory(fileSrc);  
         //Get data from specified worksheet  
         var query = excelData.Worksheet(worksheet);  
         //intialize the result list  
         var list = new List<Person>();  
         foreach (var line in query)  
         {  
            try  
            {  
              var surname = line[0].ToString();//surname  
              var name = line[1].ToString();//name  
              var date = line[2].ToString();//date  
              list.Add(new Person(surname, name, date));  
            }  
            catch (Exception exception)  
            {  
              ;  
            }  
        }  
        return list.AsEnumerable();  
      }  

 

Optimized Version

This implementation adds a mapping between Excel header columns and  the Object properties.

 public IEnumerable<Person> Method2(string fileSrc, string worksheet)  
   {  //Reading excel data using LinqToExcel  
     var excelData = new ExcelQueryFactory(fileSrc);  
     //add mapping between Excel header columns and Person Properties  
     excelData.AddMapping("SurName", "surname");  
     excelData.AddMapping("Name", "name");  
     excelData.AddMapping("Date", "date");  
     //Get data from specified worksheet using linq query  
     var list = from a in excelData.Worksheet<Person>(worksheet) select a;  
     return list.AsEnumerable();  
   }  

d) Extra Code for Two Methods

1) Code HTML

  <div class="row">  
   <div class="col-lg-12">  
     <form class="form" method="POST" action="/Home/Index">  
       <div class="form-group">  
         <label>Choose a method :</label>  
         <br>  
         <input type="radio" name="method"   
         value="0" checked /> Method 1 : ExcelDataReader <br>  
         <input type="radio" name="method"   
         value="1" /> Method 2 : LinqToExcel  
       </div>  
       <div class="form-group">  
         <label>Choose a worksheet :</label>  
         <br>  
         <input type="radio" name="worksheet"   
         value="worksheet 1" checked /> Worksheet 1<br>  
         <input type="radio" name="worksheet"   
         value="Worksheet 2" /> Worksheet 2  
       </div>  
       <div class="form-group">  
         <input type="submit"   
         class="form-control btn btn-default" value="Start parsing" />  
       </div>  
     </form>  
   </div>  
 </div>  
 <hr>  
 <div class="row">  
   <div class="col-lg-12">  
      <label>Parsing result :</label>  
   </div>  
 </div>  
 <div class="row">  
   <div class="col-sm-4">  
     <table class="table">  
       @if (@Model != null)  
       {  
         @Html.Raw("<thead><th>SurName</th>  
         <th>Name</th><th>Date</th></thead><tbody>");  
         foreach (var elem in @Model as IEnumerable<WebApplicationReadingCSVFile.Models.Person>)  
          {  
            @Html.Raw("<tr><td>" + @elem.SurName +   
            "</td><td>" + @elem.Name +   
            "</td><td>" + @elem.Date +   
            "</td></tr>");  
          }  
         @Html.Raw("</tbody>");  
       }  
     </table>  
   </div>  
   <div class="col-sm-8"></div>  
 </div>    

2) Code C# ASP.NET MVC

Class Person: It contains the record fields:

  public class Person  
   {  
     public string SurName { get; set; }  
     public string Name { get; set; }  
     public string Date { get; set; }  
     public Person(){}  
     public Person(string surName, string name, string date)  
     {  
       this.SurName = surName;  
       this.Name = name;  
       this.Date = date;  
     }  
   }  

Action Index: is the entry point of the parsing methods:

  public ActionResult Index()  
     {  
       //get the selected radio value  
       string method = Request.Params["method"];  
       string worksheet = Request.Params["worksheet"];  
       //initialize list  
       IEnumerable<Person> list = new List<Person>();  
       if (worksheet != null && method != null && method != "")  
       {  
         //Get Server Path of Excel file  
         string fileSrc = Server.MapPath("~/Content/Files/ExcelFile.xlsx");  
         //Build Result  
         if (method == "0")  
         {  
           //call the first Method  
           list = Method1(fileSrc, worksheet);  
         }  
         else {  
           //call the second Method  
           list = Method2(fileSrc, worksheet);  
         }  
       }  
       //return list as Enumerable to our model  
       return View(list);  
     }  

e) Scenario

1. Initial state: Choose the method of parsing and the worksheet. 
      
2. Click on start parsing button: