Upload & Read Excel File

This article demonstrate

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 below 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.

Lets say I have file with FirstName & LastName

Now add a class called users

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

       public string LastName { get; set; }
}

Now lets 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.

Happy Kooding…. Hope this helps!!

Advertisements