Bulk Import Excel functionality in MVC3 Razor

We often come across requirement where we need Excel import functionality to upload bulk data to database using Excel file. 
Bulk Import through excel file helps in bulk data insertion which facilitates the application.

Watch Video 1
Watch Video 2

In this article we will cover following points :

• Downloading sample excel file from the application.
• Uploading the downloaded excel file with data.
• Storing the excel file at physical path.
• Reading the Excel file.
• Validating the Excel file.
• Processing the excel data and respective updates to database.

UI:

We have created a simple UI. The UI is as below :

The user can download the excel on clicking the excel image. The use can then upload the same downloaded excel file by choosing and clicking the submit button. The excel file is validated and if the excel file is validated successfully, the data is inserted to database.

Downloading sample excel file from application :

Usually in Bulk upload functionalities the Excel file is provided or downloaded from application because the excel file should be in particular format. The user can download the file, add data to it and can upload the same excel file with data.

In our example we have kept the excel file in a folder.

When user clicks the the excel image, the above excel file is converted to bytes and the file is downloaded to user.

View :

    <div>
        <div style="text-align:center;">
<h2>Click on excel image to download the excel file.</h2>
<img src="../../Content/Images/Excel.jpg" alt="Download this excel" style="height:60px;width:60px;cursor:pointer" id="excel" />
</div>

The above HTML renders the excel image. On click of image the javascript method is called which is as below :

    <script type="text/javascript">
        $(document).ready(function () {
            $("#excel").click(function () {
                location.href = '@Url.Action("DownLoadExcelFile","BulkUploadExcel")';
            });
        });
</script>

The javascrip method redirects to the Action method in Controller. This action method reads the Excel file and downloads it for the user. The Action method is as below :

Controller:

    public ActionResult DownLoadExcelFile()
        {
            byte[] excelBytes = ExtensionMethods.GetFileData("BulkUploadUser.xlsx", Server.MapPath("~/Content/Excel"));
            var cd = new System.Net.Mime.ContentDisposition { FileName = "BulkUploadUser.xlsx" };
            Response.AppendHeader("Content-Disposition", cd.ToString());
            return File(excelBytes, "application/vnd.ms-excel");
        }

        public static byte[] GetFileData(this string fileName, string filePath)
        {
            var fullFilePath = string.Format("{0}/{1}", filePath, fileName);
            if (!File.Exists(fullFilePath))
                throw new FileNotFoundException("The file does not exist.", fullFilePath);
            return File.ReadAllBytes(fullFilePath);
        }

We have created a static class named ExtensionMethods which has a static method named GetFileData. This method accepts the filename and filepath and returns the bytes of the excel file. Finally the File content type is returned which downloads the excel file to user.Uploading the downloaded excel file with data.

Uploading the downloaded excel file with data:

Once the the excel is downloaded, the excel is filled with data and uploaded.

View:

    <div style="text-align:center">
<h2>Download the above excel, Fill the questions and upload the file below !!</h2>
<div>@Html.ValidationSummary(false)</div>
@using (Html.BeginForm("Uploadfile", "BulkUploadExcel", FormMethod.Post, new { @enctype = "multipart/form-data" }))
{
<input type="file" id="file" name="file" /><br />
    <input type="submit" id="Submit" value="Submit" />
}
</div>

The above HTML allows the user to upload the excel file. The file is posted to the Uploadfile action method.

Controller :

    [HttpPost]
        public ActionResult Uploadfile(FileUploadViewModel model)
        {
            //Chcking if modelState is valid or not.
            if (ModelState.IsValid)
            {
                //Create an object of Service class.
                UploadService service = new UploadService();
                //Saved the uploaded file details to database.
                string fileGuid = service.SaveFileDetails(model);
                //The file is then saved to physical folder.
                string savedFileName = "~/UploadedExcelDocuments/" + "_" + fileGuid + model.File.FileName;
                model.File.SaveAs(Server.MapPath(savedFileName));

                //The below code reads the excel file.
                var connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;", Server.MapPath(savedFileName));
                var adapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", connectionString);
                var ds = new DataSet();
                adapter.Fill(ds, "results");
                DataTable data = ds.Tables["results"];

                //The excel file is validated for data entered in excel file.
                bool isValid = service.ValidateExcelFileData(data);
                if (isValid)
                {
                    //If the excel file uploaded validates to true then the data mentioned is saved to database.
                    foreach (DataRow row in data.Rows)
                    {
                        string firstname = row["FirstName"].ToString();
                        string lastname = row["LastName"].ToString();
                        int age = Convert.ToInt32(row["Age"]);
                        string email = row["Email"].ToString();
                        string password = row["Password"].ToString();
                        //Data saved to database.
                        service.SaveUserDetails(firstname, lastname, age, email, password);
                    }
                    service.UpdateExcelStatus(Guid.Parse(fileGuid), true, string.Empty);
                }
                else
                {
                    service.UpdateExcelStatus(Guid.Parse(fileGuid), true, "Failure");
                }
            }
            else
            {
                //the model is invalid and the error messages are returned with model to view. 
                return View("BulkUpload", model);
            }
            //return RedirectToAction("BulkUpload");
            return View("BulkUpload");
        }

The above method reads the excel file, validates it and update the database with data in the excel. Below are the service class methods which are called from above method.

Service class :

    public class UploadService
    {
        ExcelFailureRepository failureRepository = new ExcelFailureRepository();
        public string SaveFileDetails(FileUploadViewModel model)
        {
            UploadedExcel file = null;
            Guid id = Guid.NewGuid();
            using (BulkUploadEntities dataContext = new BulkUploadEntities())
            {
                file = new UploadedExcel();
                file.Id = id;
                file.FileName = model.File.FileName;
                file.FileBytes = ConvertToBytes(model.File);
                file.UserId = 1;
                file.UploadedDate = DateTime.Now;
                file.IsProcessed = false;
                dataContext.UploadedExcel.AddObject(file);
                dataContext.SaveChanges();
            }
            return file.Id.ToString();
        }

        public byte[] ConvertToBytes(HttpPostedFileBase Image)
        {
            byte[] imageBytes = null;
            BinaryReader reader = new BinaryReader(Image.InputStream);
            imageBytes = reader.ReadBytes((int)Image.ContentLength);
            return imageBytes;
        }

        public bool ValidateExcelFileData(DataTable dataTable)
        {
            bool isValidFirstName = false;
            bool isValidLastName = false;
            bool isValidAge = false;
            bool isValidEmail = false;
            bool isValidPassword = false;
            bool isRowPresent = false;
            foreach (DataRow row in dataTable.Rows)
            {
                isValidFirstName = ValidateFirstName(row["FirstName"].ToString());
                isValidLastName = ValidateLastName(row["LastName"].ToString());
                isValidAge = ValidateAge(row["Age"].ToString());
                isValidEmail = ValidateEmail(row["Email"].ToString());
                isValidPassword = ValidatePassword(row["Password"].ToString());
                isRowPresent = true;
            }
            if (isValidFirstName && isValidLastName && isValidAge && isValidEmail && isValidPassword)
            {
                return true;
            }
            else
            {
                if(isRowPresent == false)
                failureRepository.FailureReasonList.Add("Empty Excel !!. No row present in Excel.");
                return false;
            }
        }

        #region Excel Validation
        public bool ValidateFirstName(string firstname)
        {
            if (firstname == "")
            {
                failureRepository.FailureReasonList.Add("FirstName is missing.");
                return false;
            }
            else
            {
                return true;
            }
        }

        public bool ValidateLastName(string lastname)
        {
            if (lastname == "")
            {
                failureRepository.FailureReasonList.Add("LastName is missing.");
                return false;
            }
            else
            {
                return true;
            }
        }

        public bool ValidateAge(string age)
        {
            if (age == "")
            {
                failureRepository.FailureReasonList.Add("Age is missing.");
                return false;
            }
            else
            {
                return true;
            }
        }

        public bool ValidateEmail(string email)
        {
            if (email == "")
            {
                failureRepository.FailureReasonList.Add("Email is missing.");
                return false;
            }
            else
            {
                return true;
            }
        }

        public bool ValidatePassword(string password)
        {
            if (password == "")
            {
                failureRepository.FailureReasonList.Add("Password is missing.");
                return false;
            }
            else
            {
                return true;
            }
        }

        #endregion

        public void SaveUserDetails(string firstname, string lastname, int age, string email, string password)
        {
            UserDetails user = new UserDetails();
            user.FirstName = firstname;
            user.LastName = lastname;
            user.Age = age;
            user.Email = email;
            user.Password = password;
            using (BulkUploadEntities dataContext = new BulkUploadEntities())
            {
                dataContext.UserDetails.AddObject(user);
                dataContext.SaveChanges();
            }
        }

        public void UpdateExcelStatus(Guid excelID, bool processed, string failureReason)
        {
            using (BulkUploadEntities dataContext = new BulkUploadEntities())
            {
                UploadedExcel excelFile = new UploadedExcel();
                excelFile = dataContext.UploadedExcel.Where(q => q.Id == excelID).SingleOrDefault();
                excelFile.IsProcessed = processed;
                excelFile.ProcessingStatus = (failureReason == string.Empty) ? "Success" : "Failure";
                excelFile.FailureReason = (failureReason != string.Empty) ? GetFailureReason() : null;
                dataContext.SaveChanges();
            }
        }

        public string GetFailureReason()
        {
            string failureReason = string.Empty;
            if (failureRepository.FailureReasonList.Count > 0)
            {
                foreach (string reason in failureRepository.FailureReasonList)
                {
                    failureReason = failureReason + reason;
                }
            }
            else
            {
                failureReason = "Failure";
            }
            return failureReason;
        }
    }

Storing the excel file at physical path:

The code for storing the excel file at physical path is as follow :

    string savedFileName = "~/UploadedExcelDocuments/" + "_" + fileGuid + model.File.FileName;
    model.File.SaveAs(Server.MapPath(savedFileName));

The File property in the model is of type HttpPostedFileBase class. This class exposes SaveAs method which accepts path at which the file should be save.

Reading the Excel file:

The excel file which is saved at physical path using the above code, is read and the data is filled in the DataTable.

   string savedFileName = "~/UploadedExcelDocuments/" + "_" + fileGuid + model.File.FileName;
   model.File.SaveAs(Server.MapPath(savedFileName));

   //The below code reads the excel file.
    var connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;", Server.MapPath(savedFileName));
    var adapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", connectionString);
    var ds = new DataSet();
    adapter.Fill(ds, "results");
    DataTable data = ds.Tables["results"];

The above code reads the data from first sheet. The data is then filled in DataTable.

Validating the excel file:

The data in the DataTable is then validated for blank and invalid values. The code for validating the Excel data is present above under service class.

Processing the excel data and respective updates to database:

The uploaded excel data is saved to database, if the excel validation is true. The code for saving the data is present above under service class.

Database Used :

Excel File with Data:

Database Data:

When the excel is uploaded with empty cell or completely blank, the validation fails and data is not saved and Processing status is set to failure with failure reason.

In the above excel we kept LastName blank and uploaded the excel file.

The processing status is failure and Failure reason is logged in database and user details are not saved.

So, this is how we can implement buld upload excel imlementation in MVC3 Razor.