Upload and Read Excel File in MVC

In this blog you will learn how to upload and read excel file in MVC.

Sometimes we have a requirement to upload an excel file, read that excel file and save into database or perform operation on uploaded excel file.

This blog demonstrate the following:

  1. How to upload the file in MVC.
  2. Read excel file content.

How to upload the file in MVC

In your MVC razor view add the following html content.

  1. @using(Html.BeginForm("Upload""Home", FormMethod.Post, new {enctype = "multipart/form-data"}))  
  2. {  
  3.   
  4.     <table>  
  5.         <tr>  
  6.             <td>File:</td>  
  7.             <td>  
  8.                 <input type="file" name="UploadedFile" />  
  9.             </td>  
  10.         </tr>  
  11.         <tr>  
  12.             <td colspan="2">  
  13.                 <input type="submit" name="Submit" value="Submit" />  
  14.             </td>  
  15.         </tr>  
  16.     </table>  
  17. }  
Notice, the action name: Upload, Controller Name: Home.

Now come to home controller and add action called Upload.
  1. public ActionResult Upload(FormCollection formCollection)   
  2. {  
  3.     if (Request != null)   
  4.     {  
  5.         HttpPostedFileBase file = Request.Files["UploadedFile"];  
  6.         if ((file != null) && (file.ContentLength & gt; 0) && !string.IsNullOrEmpty(file.FileName))   
  7.         {  
  8.             string fileName = file.FileName;  
  9.             string fileContentType = file.ContentType;  
  10.             byte[] fileBytes = new byte[file.ContentLength];  
  11.             var data = file.InputStream.Read(fileBytes, 0, Convert.ToInt32(file.ContentLength));  
  12.         }  
  13.     }  
  14. }  
Now try to run the app by putting a breakpoint and see if all is working until now,

Now add dll called EPPLUS from NUGET which is used for reading and writing files.

Let's say I have a file with FirstName & LastName.

Now add a class called users:
  1. public class Users  
  2. {  
  3.    public string FirstName { getset; }  
  4.    public string LastName { getset; }  
  5. }  
Modify our action method to read the file stream object which we uploaded. Add using OfficeOpenXml; statement.
  1. public ActionResult Upload(FormCollection formCollection)   
  2. {  
  3.     if (Request != null)   
  4.     {  
  5.         HttpPostedFileBase file = Request.Files["UploadedFile"];  
  6.         if ((file != null) && (file.ContentLength & gt; 0) && !string.IsNullOrEmpty(file.FileName))   
  7.         {  
  8.             string fileName = file.FileName;  
  9.             string fileContentType = file.ContentType;  
  10.             byte[] fileBytes = new byte[file.ContentLength];  
  11.             var data = file.InputStream.Read(fileBytes, 0, Convert.ToInt32(file.ContentLength));  
  12.             var usersList = new List & lt;  
  13.             Users & gt;  
  14.             ();  
  15.             using(var package = new ExcelPackage(file.InputStream))   
  16.             {  
  17.                 var currentSheet = package.Workbook.Worksheets;  
  18.                 var workSheet = currentSheet.First();  
  19.                 var noOfCol = workSheet.Dimension.End.Column;  
  20.                 var noOfRow = workSheet.Dimension.End.Row;  
  21.                 for (int rowIterator = 2; rowIterator & lt; = noOfRow; rowIterator++)   
  22.                 {  
  23.                     var user = new Users();  
  24.                     user.FirstName = workSheet.Cells[rowIterator, 1].Value.ToString();  
  25.                     user.LastName = workSheet.Cells[rowIterator, 2].Value.ToString();  
  26.                     usersList.Add(user);  
  27.                 }  
  28.             }  
  29.         }  
  30.     }  
  31.     return View("Index");  
  32. }  
Finally all the users will be available in usersList object.