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 !");
        }
    }
}

@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>

}

<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;"/>