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.

@using(Html.BeginForm("Upload", "Home", FormMethod.Post, new {enctype = "multipart/form-data"}))
{

    <table>
        <tr>
            <td>File:</td>
            <td>
                <input type="file" name="UploadedFile" />
            </td>
        </tr>
        <tr>
            <td colspan="2">
                <input type="submit" name="Submit" value="Submit" />
            </td>
        </tr>
    </table>
}

Notice, the action name: Upload, Controller Name: Home.

Now come to home controller and add action called Upload.

public ActionResult Upload(FormCollection formCollection)
{
    if (Request != null)
    {
        HttpPostedFileBase file = Request.Files["UploadedFile"];
        if ((file != null) && (file.ContentLength & gt; 0) && !string.IsNullOrEmpty(file.FileName))
        {
            string fileName = file.FileName;
            string fileContentType = file.ContentType;
            byte[] fileBytes = new byte[file.ContentLength];
            var data = file.InputStream.Read(fileBytes, 0, Convert.ToInt32(file.ContentLength));
        }
    }
}

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:

public class Users
{
   public string FirstName { get; set; }
   public string LastName { get; set; }
}

Modify our action method to read the file stream object which we uploaded. Add using OfficeOpenXml; statement.

public ActionResult Upload(FormCollection formCollection)
{
    if (Request != null)
    {
        HttpPostedFileBase file = Request.Files["UploadedFile"];
        if ((file != null) && (file.ContentLength & gt; 0) && !string.IsNullOrEmpty(file.FileName))
        {
            string fileName = file.FileName;
            string fileContentType = file.ContentType;
            byte[] fileBytes = new byte[file.ContentLength];
            var data = file.InputStream.Read(fileBytes, 0, Convert.ToInt32(file.ContentLength));
            var usersList = new List & lt;
            Users & gt;
            ();
            using(var package = new ExcelPackage(file.InputStream))
            {
                var currentSheet = package.Workbook.Worksheets;
                var workSheet = currentSheet.First();
                var noOfCol = workSheet.Dimension.End.Column;
                var noOfRow = workSheet.Dimension.End.Row;
                for (int rowIterator = 2; rowIterator & lt; = noOfRow; rowIterator++)
                {
                    var user = new Users();
                    user.FirstName = workSheet.Cells[rowIterator, 1].Value.ToString();
                    user.LastName = workSheet.Cells[rowIterator, 2].Value.ToString();
                    usersList.Add(user);
                }
            }
        }
    }
    return View("Index");
}

Finally all the users will be available in usersList object.