Import And Export Data Using Excel File In ASP.NET MVC

Introduction

This article is about importing data from an excel file to a database table and exporting the data from a database table to the Excel file, which is downloaded automatically after successfully exporting.

This kind of operation can be performed on various types of projects where manual entering of data is needed and the data entered on these sheets like Excel needs to be uploaded to the database through an online portal. Data Entry using Excel is still handy on various domains like Insurance, Health, and Banking, etc.

In this article, I’ll explain a complete step by step process of how to export Table Data using Excel File and Import Data into the Database Table using an Excel File in MVC Framework.

Creating A Database Table

First, we create a Database with a Table in SQL Server Management Studio and we insert sample data to it so that we can test our Export scenario with this data which can be appended into the downloadable Excel Sheet. Using the below query, create an InsuranceCertificate Table with sample data.

CREATE DATABASE InsuranceSample;
GO

USE InsuranceSample;

CREATE TABLE [dbo].InsuranceCertificate (
    [SrNo] INT NOT NULL,
    [Title] NVARCHAR (50) NOT NULL,
    [FirstName] NVARCHAR (100) NOT NULL,
    [LastName] NVARCHAR (100) NOT NULL,
    [DateOfBirth] DATE NOT NULL,
    [Age] INT NOT NULL,
    [Gender] NVARCHAR (50) NOT NULL,
    [MaritalStatus] NVARCHAR (50) NOT NULL,
    [EmployeeNumber] INT NOT NULL,
    [NomineeName] NVARCHAR (100) NOT NULL,
    [NomineeRelationship] NVARCHAR (50) NOT NULL,
    PRIMARY KEY (SrNo)
);

INSERT INTO [dbo].InsuranceCertificate ("SrNo", "Title", "FirstName", "LastName", "DateOfBirth", "Age", "Gender", "MaritalStatus", "EmployeeNumber", "NomineeName", "NomineeRelationship")
VALUES (1, 'Mr', 'John', 'K', '1990-01-01', 30, 'Male', 'Married', 12345, 'Paul', 'Brother');

Creating a Project in Visual Studio

We created a project named ExportImportExcel by selecting ASP.NET Web Application as a template.

Next, we select MVC as a reference in the ASP.NET Web Application template.

Adding a Model using Entity Framework

We add a Model using the ADO.NET Entity Data Model.

Give a Proper to the Item, such as “DataModel”.

We should specify what the Model contains, so, in Entity Data Model Wizard, we choose the Model contents as “EF Designer from Database” which helps to create a Model based on the existing database.

In connection properties, we choose the database connection and settings for the Model.

We can select our connection if available, or we can create a new connection with connection properties.

If a new connection is selected, include your Server Name. (You can find your Server Name by clicking on Connect Object Explorer in SSMS or in Properties) and connect to the Database and select the Table Name finally, we test the connection.

Next, select the Version of Entity Framework you want to use.

We choose the Database Objects to be included in the Model, Here, the Database Object is nothing but our Database Table.

Rename or leave your Model Namespace as it is, then click on Finish.

This creates a Datamodel.edmx Diagram which shows all the properties of our InsuranceCertificate Class like below.

In DataModel.TT, we can also see all the Properties of this Particular Class.

In DataModel.Context.cs File, we can see our Database Context name which is InsuranceSampleEntities and we use this context in our Controller Code while dealing with database Objects.

Adding ClosedXML to the Project

We need to add ClosedXML, which is a .Net Library for reading, manipulating, and writing Excel 2007+ Files. This way, we can use XLWorkbook and add our DataTable data to it which helps in Exporting the data through Excel File.

From Tools -> Nuget Package Manager -> Manage Nuget Packages for Solution -> Select -> Browse -> type "closedxml". Select it and install it on the Project.

Adding Controller to the Project

Add an Empty MVC 5 Controller to the Controllers Folder as "InsuranceCertificateController" and include our code in it.

Adding Code in Controller

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using ClosedXML.Excel;

using ExportImportExcel.Models;

namespace ExportImportExcel.Controllers
{
    public class InsuranceCertificateController : Controller
    {
        InsuranceSampleEntities db = new InsuranceSampleEntities();
        
        // GET: InsuranceCertificate
        public ActionResult Index()
        {
            var insuranceCertificate = db.InsuranceCertificates.ToList();
            return View(insuranceCertificate);
        }

        [HttpPost]
        public FileResult ExportToExcel()
        {
            DataTable dt = new DataTable("Grid");
            dt.Columns.AddRange(new DataColumn[11] {
                new DataColumn("SrNo"),
                new DataColumn("Title"),
                new DataColumn("FirstName"),
                new DataColumn("LastName"),
                new DataColumn("DateOfBirth"),
                new DataColumn("Age"),
                new DataColumn("Gender"),
                new DataColumn("MaritalStatus"),
                new DataColumn("EmployeeNumber"),
                new DataColumn("NomineeName"),
                new DataColumn("NomineeRelationship")
            });

            var insuranceCertificate = from InsuranceCertificate in db.InsuranceCertificates select InsuranceCertificate;

            foreach (var insurance in insuranceCertificate)
            {
                dt.Rows.Add(insurance.SrNo, insurance.Title, insurance.FirstName, insurance.LastName,
                    insurance.DateOfBirth, insurance.Age, insurance.Gender, insurance.MaritalStatus,
                    insurance.EmployeeNumber, insurance.NomineeName, insurance.NomineeRelationship);
            }

            using (XLWorkbook wb = new XLWorkbook()) //Install ClosedXml from Nuget for XLWorkbook
            {
                wb.Worksheets.Add(dt);
                using (MemoryStream stream = new MemoryStream()) //using System.IO;
                {
                    wb.SaveAs(stream);
                    return File(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "ExcelFile.xlsx");
                }
            }
        }

        [HttpPost]
        public ActionResult ImportFromExcel(HttpPostedFileBase postedFile)
        {
            if (ModelState.IsValid)
            {
                if (postedFile != null && postedFile.ContentLength > (1024 * 1024 * 50)) // 50MB limit
                {
                    ModelState.AddModelError("postedFile", "Your file is too large. Maximum size allowed is 50MB!");
                }
                else
                {
                    string filePath = string.Empty;
                    string path = Server.MapPath("~/Uploads/");
                    if (!Directory.Exists(path))
                    {
                        Directory.CreateDirectory(path);
                    }

                    filePath = path + Path.GetFileName(postedFile.FileName);
                    string extension = Path.GetExtension(postedFile.FileName);
                    postedFile.SaveAs(filePath);

                    string conString = string.Empty;
                    switch (extension)
                    {
                        case ".xls": //For Excel 97-03.
                            conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                            break;
                        case ".xlsx": //For Excel 07 and above.
                            conString = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
                            break;
                    }

                    try
                    {
                        DataTable dt = new DataTable();
                        conString = string.Format(conString, filePath);

                        using (OleDbConnection connExcel = new OleDbConnection(conString))
                        {
                            using (OleDbCommand cmdExcel = new OleDbCommand())
                            {
                                using (OleDbDataAdapter odaExcel = new OleDbDataAdapter())
                                {
                                    cmdExcel.Connection = connExcel;

                                    //Get the name of First Sheet.
                                    connExcel.Open();
                                    DataTable dtExcelSchema;
                                    dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                                    string sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
                                    connExcel.Close();

                                    //Read Data from First Sheet.
                                    connExcel.Open();
                                    cmdExcel.CommandText = "SELECT * From [" + sheetName + "]";
                                    odaExcel.SelectCommand = cmdExcel;
                                    odaExcel.Fill(dt);
                                    connExcel.Close();
                                }
                            }
                        }

                        conString = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
                        using (SqlConnection con = new SqlConnection(conString))
                        {
                            using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
                            {
                                //Set the database table name.
                                sqlBulkCopy.DestinationTableName = "InsuranceCertificate";
                                con.Open();
                                sqlBulkCopy.WriteToServer(dt);
                                con.Close();
                                return Json("File uploaded successfully");
                            }
                        }
                    }
                    catch (Exception e)
                    {
                        return Json("error" + e.Message);
                    }
                }
            }
            return Json("no files were selected !");
        }
    }
}

Adding View to the Project

We add a View to the Project to our Views -> “InsuranceCertificate” Folder and Select View Name as Index, Template as Empty, and our Model class as Insurance Certificate and DataContextClass as InsuranceSample Entities and we add the View.

Selecting Appropriate View Options.

If you face the below Error while adding the View, just build the Project with Ctrl+Shift+B, and try to add the view again until it works successfully.

Adding Code to the View

@model IEnumerable<ExportImportExcel.Models.InsuranceCertificate>

@{
    Layout = null;
}

<link href="~/Content/bootstrap.css" rel="stylesheet" />

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
</head>
<body>
    <fieldset>

        <div class="col-lg-12 col-md-12 col-sm-12 col-xs-12 form-group">
            <div class="modal-section-header">
                <p>Insured Details - Insurance Certificate Issuance</p>
            </div>
        </div>

        <div>
            @using (Html.BeginForm("ExportToExcel", "InsuranceCertificate", FormMethod.Post, new { enctype = "multipart/form-data" }))
            {

                <div>
                    <input type="submit" value="Export" class="btn-primary" />
                </div>

            }
        </div>

        <div>

            @using (Html.BeginForm("ImportFromExcel", "InsuranceCertificate", FormMethod.Post, new { enctype = "multipart/form-data" }))
            {
                @Html.ValidationSummary();
                <input type="file" name="postedFile" />
                <div>
                    <input type="submit" button id="upload" value="Import" class="btn-success" />
                </div>
            }

        </div>

    </fieldset>
</body>
</html>


<h2>InsuredDetails</h2>

<div class="col-lg-12 col-md-12 col-sm-12 col-xs-12 form-group">
    <div class="modal-section-header">
        <p>Insurance Certificate Issuance</p>
    </div>

</div>
<div class="row">
    <div class="col-lg-12 col-md-12 col-sm-12 col-xs-12 table-responsive gtcustomgrid">
        <table class="table-bordered">
            <thead class="thead-dark">
                <tr>
                    <th>Sr No</th>
                    <th>Title</th>
                    <th>First Name</th>
                    <th>Last Name</th>
                    <th>Date of Birth</th>
                    <th>Age</th>
                    <th>Gender</th>
                    <th>Marital Status</th>
                    <th>Employee Number</th>
                    <th>Nominee Name</th>
                    <th>Nominee Relationship with Insured</th>

                </tr>
            </thead>
            @foreach (var item in Model)
            {
                <tbody>
                    <tr>
                        <td>@Html.DisplayFor(modelItem => item.SrNo, new { @id = "SrNo", @class = "form-control" })</td>
                        <td>@Html.DisplayFor(modelItem => item.Title, new { @id = "Title", @class = "form-control" })</td>
                        <td>@Html.DisplayFor(modelItem => item.FirstName, new { @id = "FirstName", @class = "form-control" })</td>
                        <td>@Html.DisplayFor(modelItem => item.LastName, new { @id = "LastName", @class = "form-control" })</td>
                        <td>@Html.DisplayFor(modelItem => item.DateOfBirth, new { @id = "DateOfBirth", @class = "form-control" })</td>
                        <td>@Html.DisplayFor(modelItem => item.Age, new { @id = "Age", @class = "form-control" })</td>
                        <td>@Html.DisplayFor(modelItem => item.Gender, new { @id = "Gender", @class = "form-control" })</td>
                        <td>@Html.DisplayFor(modelItem => item.MaritalStatus, new { @id = "MaritalStatus", @class = "form-control" })</td>
                        <td>@Html.DisplayFor(modelItem => item.EmployeeNumber, new { @id = "EmployeeNumber", @class = "form-control" })</td>
                        <td>@Html.DisplayFor(modelItem => item.NomineeName, new { @id = "NomineeName", @class = "form-control" })</td>
                        <td>@Html.DisplayFor(modelItem => item.NomineeRelationship, new { @id = "NomineeRelationship", @class = "form-control" })</td>

                        @*<td>
                                @Html.ActionLink("Create New", "Create")
                                @Html.ActionLink("Edit", "Edit", new { /* id=item.PrimaryKey */ }) |
                                @Html.ActionLink("Details", "Details", new { /* id=item.PrimaryKey */ }) |
                                @Html.ActionLink("Delete", "Delete", new { /* id=item.PrimaryKey */ })
                            </td>*@
                    </tr>
                </tbody>
            }
        </table>
    </div>
</div>
@section scripts{

    <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>

    <script>
        $("#upload").click(function () {
            debugger;
            if (window.FormData == undefined)
                alert("Error: FormData is undefined");

            else {
                var fileUpload = $("#postedFile").get(0);
                var files = fileUpload.files;

                var fileData = new FormData();

                fileData.append(files[0].name, files[0]);

                $.ajax({
                    url: '/InsuranceCertificate/ImportFromExcel',
                    type: 'post',
                    datatype: 'json',
                    contentType: false,
                    processData: false,
                    async: false,
                    data: fileData,
                    success: function (response) {
                        alert(response);
                    }
                });
            }

        });

    </script>

}

Finally, run the project.

Exporting the Excel File

To Export the Excel File with Data, just click on the Export button. This makes an Excel File with Table data downloaded locally, which can be seen in the below Picture.

Note. It is mandatory to add Excel03ConString and Excel07ConString Connection Strings to the web. config file.

It is mandatory to add DBCS to the Connection String, which is your SQL Server Connection Details. Below for DBCS, the details are empty so before adding this connection string to your web.config File <ConnectionStrings> Include all your SQL Server Connection Details in it.

<add name="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/>
<add name="Excel07ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/>
<add name="DBCS" connectionString="data source=;initial catalog=InsuranceSample;user id=;password=;MultipleActiveResultSets=True;"/>

Importing the Excel File

You can import your data entered in the Excel file into the Project by choosing the file from the "Choose file" option and clicking on the "Import" button.

If you forgot to include the above Connection Strings in the web. config file, you'll face the below error after you try to import your Excel file.

If you try to Import/Upload the Excel File with the Same "SrNo", it violates the Primary Key defined in the SQL Table and it will not upload to the database and throws the below Error.

Using try-catch or by explicitly using the Watch Window, you can find the errors causing the abnormality in your Project.

Always try to upload with the new "SrNo" so the Excel Sheet data is imported into the Database Table and displays a successful message like below.

After the "File Uploaded Successfully" message we can go to Index Page and cross-check the details.

Excel Sheet Data has been inserted successfully into the SQL Database Table, which can be seen in the below Picture.

Conclusion

In this article, we have successfully exported and imported data using an Excel file in ASP.NET MVC, with pictures in the process of creating this project. We also discussed handling different errors to achieve the Excel File Import/Export Scenario.

Happy Coding!