Uploading Multiple Records Via Excel Upload in Database Using EntityFramework 6.0 in ASP.Net MVC4

Vishal Gilbile
10y
40.5k
0
0

Article

This article explains how to insert multiple records into a database using an Excel upload. When working on a project there was a requirement for me to upload an Excel file, let's say with the number of records up to 50000 per sheet. But while uploading the Excel file there were certain validations that needs to be done and if any of the records fail then the error message needs to be displayed for that respective row of the Excel on the page and none of the records needs to be saved inside the database table.

Validations could be in any sense, for example:

The age of all employees needs to be >= 18 and <= 60
The age column should not have a non-numeric value
There were certain columns that were mandatory, if the value is not filled in that column of the Excel then the user should be notified of the error. Like name of the Employee should not be null.
Checking thar a date value is in the format of DD/MM/YYYY
and so on

When uploading this Excel file, if the preceding validations are successfully satisfied then the data needs to be saved in the database against a specific batch id that is basically a unique number to identify the records inserted within that specific batch and when they all were inserted. Also a special attention needs to be provided on the Excel column headings that we provided to the user for data entry purposes. Let's say there are 10 columns in the Excel and if the user changes the order of the Excel column heading or if they delete some column or changes the name of the column, this validation also needs to be performed against the uploaded file. For performing this validation we have made use of an ExcelStructure Table that will have Excel fields/columns stored with their respective sizes, types, parameters and whether these fields/columns are mandatory or not. The following is the structure of the ExcelStructure Table. Remember we are using EntityFramework with the Code First Approach. But just for demo/looking purposes our SQL table will have the following query.

CREATE TABLE [dbo].[ExcelStructure]
(

[Row_ID] INT IDENTITY (1, 1) NOT NULL,

[File_ID] INT NOT NULL,

[File_Name] NVARCHAR (MAX) NULL,

[Field_Name] NVARCHAR (MAX) NULL,

[Field_Data] NVARCHAR (MAX) NULL,

[Active_Flag] INT NOT NULL,

[Row_No] INT NOT NULL,

[Field_Delimeter] NVARCHAR (MAX) NULL,

CONSTRAINT [PK_dbo.ExcelStructure] PRIMARY KEY CLUSTERED ([Row_ID] ASC)

);

Now let's move to the code. Here the technology that we are using is ASP.NET MVC4 and EntityFramework 6.0. I'm naming my solution name ImportFromExcel that will have two projects, one would be of type Class Library with the name ImportFromExcel.Domain and the other would be our MVC4 project with the name ImportFromExcel.WebUI with basic as its type. Our ImportFromExcel.Domain project will have the entire model and DbContext classes we are following here the separation of concerns between model and WebApplication. Install the EntityFramework Package inside the ImportFromExcel.Domain and ImportFromExcel.WebUI Project using the Package Manager Console by typing the command as:

Install-package EntityFramework

Note: Make sure you install it in both the projects otherwise a version issue might occur.

We need to add the reference to System.ComponentModel.DataAnnotations namespace. We have added creation folders to our Domain class library project and these folders contain several classes and interface that we'll require to query our database or manipulate the database. Here is the hierarchy of the ImportFromExcel.Domain project application:

Solution Explorer

Here are the definitions of the files.

Employee.cs

using System;

using System.Collections.Generic;

using System.ComponentModel.DataAnnotations;

using System.ComponentModel.DataAnnotations.Schema;

namespace ImportFromExcel.Domain.Entities

{

[Table("Employee")]

public class Employee

{

[Key]

public int EmployeeID { get; set; }//mvc convention by default it will make it as primary key with autogenerated feature turned on

[Required(ErrorMessage = "Name is Required")]

public string Name { get; set; }

[Required(ErrorMessage = "Address is Required")]

public string Address { get; set; }

public string DOB { get; set; }

public int Age { get; set; }

public string Email { get; set; }

}

ExcelStructure.cs

using System;

using System.ComponentModel.DataAnnotations;

using System.ComponentModel.DataAnnotations.Schema;

namespace ImportFromExcel.Domain.Entities

{

[Table("ExcelStructure")]

public class ExcelStructure

{

[Key]

[DatabaseGenerated(DatabaseGeneratedOption.Identity)]

public int Row_ID { get; set; }

public int File_ID { get; set; }

public string File_Name { get; set; }

public string Field_Name { get; set; }

public string Field_Data { get; set; }

public int Active_Flag { get; set; }

public int Row_No { get; set; }

public string Field_Delimeter { get; set; }

}

In order to reduce the dependency between higher end modules and lower end modules we implemented an interface that will help us to do loose coupling between the various components.

IEmployeeRepository.cs

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using ImportFromExcel.Domain.Entities;

namespace ImportFromExcel.Domain.Abstract

{

public interface IEmployeeRepository

{

IQueryable<Employee> Employee { get; }

IQueryable<ExcelStructure> ExcelStructure { get; }

void SaveEmployee(IList<Employee> lstEmployees);

}

EFDBContext.cs: This is our DbContext class

using System;

using System.Collections.Generic;

using System.Data.Entity;

using System.Linq;

using ImportFromExcel.Domain.Entities;

namespace ImportFromExcel.Domain.Concrete

{

public class EFDbContext : DbContext

{

public DbSet<Employee> Employee { get; set; }

public DbSet<ExcelStructure> ExcelStructure { get; set; }

}

EmployeeRepository.cs

using System;

using System.Collections.Generic;

using System.Linq;

using ImportFromExcel.Domain.Abstract;

namespace ImportFromExcel.Domain.Concrete

{

public class EmployeeRepository : IEmployeeRepository

{

EFDbContext context = new EFDbContext();

public IQueryable<Entities.Employee> Emp

{

get { return context.Employee; }

}

public IQueryable<Entities.ExcelStructure> ExcelStructure

{

get { return context.ExcelStructure; }

}

public void SaveEmployee(IList<Entities.Employee> lstEmployees)

{

try

{

context.Employee.AddRange(lstEmployees);

context.SaveChanges();

}

catch (Exception ex)

{

throw ex;

}

Inside the Concrete folder we have a class named ExcelStructureInitializer.cs that basically contains the code for the records that needs to be stored in the Database ExcelStructure Table when we run the application for the first time or even when the model changes or the database is dropped and recreated by the entityFramework. This class inherits from DropCreateDatabaseIfModelChanges class in order to maintain the synchronization between the Model and the EntityFramework Database. The following is the definition of the class.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using ImportFromExcel.Domain.Entities;

using System.Data.Entity;

namespace ImportFromExcel.Domain.Concrete

{

public class ExcelStructureInitializer : DropCreateDatabaseIfModelChanges<EFDbContext>

{

protected override void Seed(EFDbContext context)

{

//base.Seed(context);

var excelStructure = new List<ExcelStructure>()

{

new ExcelStructure(){ File_ID=1, Field_Name="Name", Field_Data="Name|Address|DOB|Age|Email", Active_Flag=1, Row_No=1, File_Name="EmployeeExcel", Field_Delimeter="|"},

new ExcelStructure(){ File_ID=1, Field_Name="DataType", Field_Data="S|S|D|I|S", Active_Flag=1, Row_No=2, File_Name="EmployeeExcel", Field_Delimeter="|"},

new ExcelStructure(){ File_ID=1, Field_Name="Size", Field_Data="50|100|20|10|100", Active_Flag=1, Row_No=3, File_Name="EmployeeExcel", Field_Delimeter="|"},

new ExcelStructure(){ File_ID=1, Field_Name="Mandatory", Field_Data="Y|Y|N|N|N", Active_Flag=1, Row_No=4, File_Name="EmployeeExcel", Field_Delimeter="|"},

new ExcelStructure(){ File_ID=1, Field_Name="Param", Field_Data="@name|@add|@dob|@age|@email", Active_Flag=1, Row_No=5, File_Name="EmployeeExcel", Field_Delimeter="|"},

};

context.ExcelStructure.AddRange(excelStructure);

context.SaveChanges();

}

The one highlighted in the yellow color in the code above will be the Excel Column heading with their Type, size, and mandatory or not. And the one highlighted in the light blue color are the name through that we can refer to these fields in the code file. Every value is separated using the “|” character.

All these records will be added to your Excel structure table when you run the application. Here in this table we specified the Excel column hierarchy and the maximum value that the column can contain, also whether they are mandatory or not and their datatype. For Datatype here I'm using the convention of S for String, I for integers, D for DateTime, DO for Double and so on but you can follow your own conventions.

And we are setting the reference of this class inside the Global.asax file that is within the ImportFromExcel.WebUI application. Here is the line that we set up in the Global.asax file.

using System;

using System.Collections.Generic;

using System.Data.Entity;

using System.Linq;

using System.Web;

using System.Web.Http;

using System.Web.Mvc;

using System.Web.Optimization;

using System.Web.Routing;

using ImportFromExcel.Domain.Concrete;

using ImportFromExcel.WebUI.Infrastructure;

namespace ImportFromExcel.WebUI

{

// Note: For instructions on enabling IIS6 or IIS7 classic mode,

// visit http://go.microsoft.com/?LinkId=9394801

public class MvcApplication : System.Web.HttpApplication

{

protected void Application_Start()

{

AreaRegistration.RegisterAllAreas();

Database.SetInitializer<EFDbContext>(new ImportFromExcel.Domain.Concrete.ExcelStructureInitializer());

WebApiConfig.Register(GlobalConfiguration.Configuration);

FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);

RouteConfig.RegisterRoutes(RouteTable.Routes);

ControllerBuilder.Current.SetControllerFactory(new NinjectControllerFactory());

BundleConfig.RegisterBundles(BundleTable.Bundles);

}

}
}

For the code of the preceding files kindly download the article.

Now we'll be moving towards creating our front end, in other words the ImportFromExcel.WebUI application where we'll create a view and controllers that will communicate with the preceding domain model that we created above.

Note:

Here for DependencyInjection we used Ninject. Also for the full explanation of DependencyInjection you can refer to the following URL:

ASP.NET MVC 4 Dependency Injection

We injected the Ninject controller using the Global.asax file. The one marked with light blue as background in the Global.asax file.
For Excel reading and writing I'm using Bytescout.Spreadsheet.dll
Also add the reference of the ImportFromExcel.Domain.dll to the ImportFromExcel.WebUI project. So that we can refer to the classes.

Here is the hierarchy of the ImportFromExcel.WebUI

Add a connection string to the Web.Config file of the ImportFromExcel.WebUI like the following:

Now to create a helper file that will allow us to validate and upload the Excel content. We'll specify our validation rules inside this file. Create a class with the name of APIHelper.cs inside the AppHelper folder of your WebUI project.

using System;

using System.Collections.Generic;

using System.Data;

using System.Linq;

using System.Reflection;

using System.Web;

using Bytescout.Spreadsheet;

using ImportFromExcel.Domain.Abstract;

using ImportFromExcel.Domain.Entities;

namespace ImportFromExcel.WebUI.AppHelper

{

public class APIHelper

{

private IEmployeeRepository repository;

public APIHelper(IEmployeeRepository repository)

{

this.repository = repository;

}

/// <summary>

/// Read the Excel File

/// </summary>

/// <param name="filePath">File Path</param>

/// <param name="isHDR">Header File</param>

/// <returns>returns DataSet</returns>

public DataSet ReadExcelFile(string filePath, out string msg, string isHDR = "Yes")

{

string details = string.Empty;

//retrieve the Excel structure for a particular File. in our case EmployeeStructure was the file name whose data needs to copied

List<ExcelStructure> lstEntityTable = repository.ExcelStructure.Where(

x => x.File_Name.Equals("EmployeeExcel", StringComparison.InvariantCultureIgnoreCase)).

OrderBy(x => x.Row_No).ToList();

List<string> lstFieldType = new List<string>();

//getting the type of all fields

lstFieldType.AddRange(lstEntityTable[1].Field_Data.ToString().Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries));

//Get the ExcelStructure Stored in Database

DataTable dt = CreateDataTableFromList(lstEntityTable);

//Get the Records Stored in ExcelFile Uploaded by user

DataSet ds = GetDataFromMultipleSheets(filePath, lstFieldType);

string fileName = string.Empty;

//perform validation on every dataTable inside DataSet

for (byte i = 0; i < ds.Tables.Count; i++)

{

if (ds.Tables[i].Rows.Count > 0)

{

details = ValidateExcelHeadings(dt, ds.Tables[i]);

ds.DataSetName = filePath.Substring(filePath.LastIndexOf("\\") + 1);

}

msg = details;

return ds;

}

/// <summary>

/// This function is used for copying the List Excel Structure Data to DataTable

/// </summary>

/// <param name="lstExcel">List<ExcelStructure></param>

/// <returns>returns DataTable</returns>

private DataTable CreateDataTableFromList(List<ExcelStructure> lstExcel)

{

DataTable dt = new DataTable();

PropertyInfo[] props = typeof(ExcelStructure).GetProperties(BindingFlags.Public | BindingFlags.Instance);

foreach (PropertyInfo prop in props)

{

if (!prop.Name.Equals("Row_ID"))

dt.Columns.Add(prop.Name);

}

for (int i = 0; i < lstExcel.Count; i++)

{

DataRow dr = dt.NewRow();

dr[0] = lstExcel[i].File_ID;

dr[1] = lstExcel[i].File_Name;

dr[2] = lstExcel[i].Field_Name;

dr[3] = lstExcel[i].Field_Data;

dr[4] = lstExcel[i].Active_Flag;

dr[5] = lstExcel[i].Row_No;

dr[6] = lstExcel[i].Field_Delimeter;

dt.Rows.Add(dr);

}

return dt;

}

/// <summary>

/// This function is used for reading Data From Multiple sheet and adding those worksheet data as

/// DataTable inside a DataSet

/// </summary>

/// <param name="filePath">FilePath</param>

/// <param name="lstDateType">DataType of the Fields</param>

/// <returns>returns DataSet</returns>

private DataSet GetDataFromMultipleSheets(string filePath, List<string> lstDateType)

{

DataSet ds = new DataSet();

Spreadsheet document = new Spreadsheet(filePath);

int totalColumn = 0;

try

{

//iterate over the workbook worksheets and add those to the DataSet Tables.

for (byte i = 0; i < document.Workbook.Worksheets.Count; i++)

{

Worksheet sheet = document.Workbook.Worksheets[i];

totalColumn = sheet.NotEmptyColumnMax + 1;

//set the DataTable Name to the SheetName specified

DataTable dtTable = ds.Tables.Add(sheet.Name);

//create the dataTable with the specified column of uploaded Excel

for (int col = 0; col < totalColumn; col++)

{

try

{

//Add the columns to the DataTable

dtTable.Columns.Add(sheet.Cell(0, col).Value.ToString().Trim());

}

catch (Exception ex)

{

throw ex;

}

//iterate over the worksheet records until the last used rows

//here we are iterating from row 1 because we are assuming row 0 is an header row

for (int row = 1; row <= sheet.UsedRangeRowMax; row++)

{

object[] data = new object[totalColumn];

for (int column = 0; column < totalColumn; column++)

{

try

{

//check whether the value is of DateTime

if (lstDateType[column].Equals("D"))

{

try

{

data[column] = sheet.Cell(row, column).ValueAsDateTime.ToShortDateString().Trim();

}

catch (Exception)

{

data[column] = sheet.Cell(row, column).Value.ToString().Trim();

}

else

{

data[column] = sheet.Cell(row, column).Value.ToString().Trim();

}

catch (Exception ex)

{

data[column] = sheet.Cell(row, column).Value;

}

dtTable.Rows.Add(data);

}

document.Close();

}

catch (Exception ex)

{

throw ex;

}

return ds;

}

/// <summary>

/// This function is used for validating the Excel headings against those stored in the Database.

/// </summary>

/// <param name="dtUploadedExcel">Uploaded Excel Converted as DataTable</param>

/// <param name="dtExcelStructure">Excel Structure Converted as DataTable</param>

/// /// <returns>returns string message</returns>

private string ValidateExcelHeadings(DataTable dtExcelStructure, DataTable dtUploadedExcel)

{

string error = "";

int number = 0;

try

{

//read the TableHeadings from the ExcelStructure

string strFieldValue = (string)dtExcelStructure.AsEnumerable().Where(x => x["Field_Name"].Equals("Name")).

Select(x => x["Field_Data"]).First();

string[] strFieldHeadings = strFieldValue.Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries);

string[] strFieldType = ((string)dtExcelStructure.AsEnumerable().Where(x => x["Field_Name"].Equals("DataType")).

Select(x => x["Field_Data"]).First()).Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries);

string[] strFieldSize = ((string)dtExcelStructure.AsEnumerable().Where(x => x["Field_Name"].Equals("Size")).

Select(x => x["Field_Data"]).First()).Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries);

string[] strFieldParam = ((string)dtExcelStructure.AsEnumerable().Where(x => x["Field_Name"].Equals("Param")).

Select(x => x["Field_Data"]).First()).Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries);

string[] strFieldMandatory = ((string)dtExcelStructure.AsEnumerable().Where(x => x["Field_Name"].Equals("Mandatory")).

Select(x => x["Field_Data"]).First()).Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries);

//Checking the upload Excel column count with that of the ExcelStructure Table Column Count

if (strFieldHeadings.Length == dtUploadedExcel.Columns.Count)

{

for (int i = 0; i < strFieldHeadings.Length; i++)

{

//Validate Excel Heading

if (strFieldHeadings[i].ToLower() != dtUploadedExcel.Columns[i].ToString().ToLower())

{

error += dtUploadedExcel.Columns[i].ToString() + " Mismach Column, it has to be " + strFieldHeadings[i] + "\\n";

}

if (error != "")

return error;

}

else

{

error = "Mismach Count of Excel Column";

return error;

}

if (error == "")

{

//add an extra column named Error inside the DataTable which contains the excelUploadedData

dtUploadedExcel.Columns.Add("Error");

for (int i = 0; i < dtUploadedExcel.Rows.Count; i++)

{

error = "";

for (int j = 0; j < strFieldHeadings.Length; j++)

{

//Validate Mandetory Fields/Columns

if (strFieldMandatory[j].ToUpper().Trim().Equals("Y", StringComparison.InvariantCultureIgnoreCase))

{

if (dtUploadedExcel.Rows[i][j].ToString() == null || dtUploadedExcel.Rows[i][j].ToString() == "")

{

error += dtUploadedExcel.Columns[j].ToString() + " Should not be Null in line " + (i + 1) + ";";

}

//Adding custom validations like lets say the age of employee should be >18 less than or equal to 60 years

if (strFieldHeadings[j].ToUpper().Trim().Equals("Age", StringComparison.InvariantCultureIgnoreCase))

{

if (dtUploadedExcel.Rows[i][j].ToString() != null || dtUploadedExcel.Rows[i][j].ToString() != "")

{

if (!int.TryParse(dtUploadedExcel.Rows[i][j].ToString(), out number))

{

error += "Age value should be numeric in row: " + (i + 1) + ";";

}

else

{

if (number < 18 || number > 60)

{

error += "Age value should be between 18 and 60 in row: " + (i + 1) + ";";

}

//Check for those column whose datatype is integer specified in ExcelStructure

if (strFieldType[j].ToUpper().Trim().Equals("I") && dtUploadedExcel.Rows[i][j].ToString() != "" &&

!strFieldHeadings[j].ToUpper().Trim().Equals("Age", StringComparison.InvariantCultureIgnoreCase))

{

if (!int.TryParse(dtUploadedExcel.Rows[i][j].ToString(), out number))

{

error += dtUploadedExcel.Columns[j].ColumnName + " Should be Numeric value in line " + (i + 1) + ";";

}

//Checking the type of the Column if it is String then check the length of the value specified in the Excel File against the max value specified

//inside the ExcelStructure Table using the | operator

if (strFieldType[j].ToUpper().Trim().Equals("S") && dtUploadedExcel.Rows[i][j].ToString() != "")

{

//Check every field length compared with what we specified in the ExcelStructure Length For String Type

if (Convert.ToInt32(strFieldSize[j].Trim()) < dtUploadedExcel.Rows[i][j].ToString().Length)

{

error += string.Format("Value Length: {0}. Reached Max Length Of {1}", dtUploadedExcel.Rows[i][j].ToString().Length, strFieldSize[j].Trim()) + ";";

}

//adding the error details to the Error coulmn of the DataTable which can be used for displaying the error details on the Page

dtUploadedExcel.Rows[i]["Error"] = error;

}

return "Success";

}

catch (Exception ex)

{

throw ex;

}

Add a new controller with the name HomeController inside the controller folder of your application. Here is the code for that.

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using ImportFromExcel.Domain.Abstract;

using ImportFromExcel.Domain.Entities;
using ImportFromExcel.WebUI.AppHelper;

namespace ImportFromExcel.WebUI.Controllers
{
    public class HomeController : Controller
    {
        private IEmployeeRepository repository;

        public HomeController(IEmployeeRepository repository)
        {
            this.repository = repository;
        }

        public ActionResult Index()
        {
            return View();
        }

        /// <summary>
       /// This function will be only called when there is a postback using Httppost method from the view. This function accepts an arguement
        /// named file which is basically the name of the input file control specified in the view. Since MVC4 follows the conventional approach by default.
        /// </summary>
        /// <param name="file">HttpPostedFileBase object</param>
        /// <returns>returns ActionResult</returns>

        [HttpPost]
        public ActionResult Index(HttpPostedFileBase file)
        {
            if (file != null)
            {
                string extension = Path.GetExtension(file.FileName);
                if (extension.Equals(".xls") || extension.Equals(".xlsx"))
                {
                    DateTime dt = DateTime.Now;
                    string filePath = "EmployeeData";
                    string format = dt.ToString();
                    format = format.Replace('/', '_');
                    format = format.Replace(':', '_');
                    format = format.Replace(' ', '_');
                    filePath += "_" + format + ".xlsx";
                    string finalFilePath = Server.MapPath("~/Uploads/" + filePath);
                    file.SaveAs(finalFilePath);
                    APIHelper objHelper = new APIHelper(repository);
                    string errorDetails = string.Empty;
                    DataSet ds = objHelper.ReadExcelFile(finalFilePath, out errorDetails);
                    if (errorDetails.Equals("success", StringComparison.InvariantCultureIgnoreCase))
                    {
                        int errorCount = ds.Tables[0].AsEnumerable().Where(x => x["Error"].ToString() != "").Count();
                        if (errorCount > 0)
                            ViewBag.UploadMsg = "Error Uploading Data";
                        else
                        {
                            ListConverter<Employee> converterObj = new ListConverter<Employee>(ds);
                            List<Employee> lstData = converterObj.ConvertDataSetToList();
                            repository.SaveEmployee(lstData);
                            ViewBag.UploadMsg = "Data Saved Successfully...";
                        }
                        return View("Index", ds);
                    }
                    else
                    {
                        ViewBag.UploadMsg = errorDetails;

return View("Index", null);
}

                }
                ViewBag.Error = "Invalid File Format " + extension + " Valid File Formats allowed are .xls or .xlsx";
                return View("Index");
            }

            ViewBag.Error = "Kindly Select an Excel File to upload";
            return View("Index");
            //to display alter box from Controller you can make use of the following line
            // return Content("<script type='text/javascript'>alert('Error Uploading Details');</script>");
        }
    }
}

Now create a strongly typed view from the controller. Here is the code for that.

@model System.Data.DataSet

@using System.Data;

ViewBag.Title = "Index";

Layout = "~/Views/Shared/_Layout.cshtml";

}

<h3>Upload Excel File</h3>

@if (ViewBag.Error != null)

{

alert("@ViewBag.Error.ToString()");

</script>

}

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

{

<div>

<label for="file">Filename:</label>

</div>

}

<p>

@if (ViewBag.UploadMsg != null)

{

if (ViewBag.UploadMsg == "Data Saved Successfully...")

{

alert("@ViewBag.UploadMsg.ToString()");

</script>

}

else if (ViewBag.UploadMsg != "" && Model == null)

{

alert("@ViewBag.UploadMsg.ToString()");

</script>

}

else

{

Html.RenderPartial("~/Views/Shared/_UploadResult.cshtml", Model);

}

</p>

PartialView: _UploadResult.cshtml

@model System.Data.DataSet

@using System.Data;

int counter = 1;

}

<div>

<h3>Upload Errors.</h3>

</div>

<table>

<tr>

<td>Sheet Name</td>

<td>Error Message</td>

</tr>

@foreach (DataTable table in Model.Tables)

{

if (table.Rows.Count > 0)

{

for (int i = 0; i < table.Rows.Count; i++)

{

if (table.Rows[i]["Error"] != "")

{

<tr>

<td>@counter</td>

<td>@table.TableName</td>

<td>@table.Rows[i]["Name"].ToString()</td>

<td>@table.Rows[i]["Error"].ToString()</td>

</tr>

counter++;

}

</table>

</div>

Note: You need to download the application for the smooth functioning

For NinjectControllerFactory that we created in WebUI
For App_Helper Files that are used for uploading and reading the Excel files content
Before running, kindly create an Excel file with some records that you can make use of to upload it.
The following is the dummy ExcelFile:

Here we explicitly did not specify the name of the Employee and age > 60 and non numeric value in the age column.

Now we can run the application and try to upload the Excel file. Here is the final output, since there are validation errors our screen will look like this:

Now let's try to update the Excel and remove all the validation errors.

Stop the application and open ServerExplorer and refersh your database and right-click on the Employee Table and select the option "Show Table Data".

You'll find that our data is stored that we uploaded using Excel.

excel

Here within our application there is the small issue that if you continue to upload new files it will continue to increment the EmployeeID since it takes it as an auto-increment column and also it's a primary key as per EntityFramework Conventions and we didn't add a field named Batch_no so we won't be able to recognize the upload details. But nevertheless since we are learning it, for learning purposes our task has been done. Small and Simple Logic anyone can implement for adding the Batch_no as the primary key for the employee Table and making it an auto-generated column. You can also add the date and time or when the upload was made.

Up Next

Ebook Download

View all

Diving into Knockout.JS

Read by 1.7k people

Download Now!

Learn

View all