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.
  1. CREATE DATABASE InsuranceSample;    
  2. GO    
  3. USE InsuranceSample      
  4. CREATE TABLE [dbo].InsuranceCertificate (    
  5.     [SrNo]                               INT                           NOT NULL,    
  6.     [Title]                                NVARCHAR (50)    NOT NULL,    
  7.     [FirstName]                     NVARCHAR (100)  NOT NULL,    
  8.     [LastName]                     NVARCHAR (100)  NOT NULL,    
  9.     [DateOfBirth]                   DATE                        NOT NULL,    
  10.     [Age]                                 INT                           NOT NULL,    
  11.     [Gender]                           NVARCHAR (50)    NOT NULL,    
  12.     [MaritalStatus]                NVARCHAR (50)    NOT NULL,    
  13.     [EmployeeNumber]        INT                           NOT NULL,    
  14.     [NomineeName]             NVARCHAR (100)  NOT NULL,    
  15.     [NomineeRelationship]  NVARCHAR (50)    NOT NULL,    
  16.     PRIMARY KEY (SrNo)      
  17. );    
  18.     
  19. INSERT INTO [dbo].InsuranceCertificate ("SrNo""Title""FirstName""LastName""DateOfBirth""Age""Gender""MaritalStatus""EmployeeNumber""NomineeName""NomineeRelationship")     
  20. VALUES (1, 'Mr''John''K''1990-01-01', 30, 'Male''Married', 12345, 'Paul''Brother');     

Creating a Project in Visual Studio

 
We create a project named ExportImportExcel by selecting ASP.NET Web Application as a template.
 
Import And Export Data Using Excel File In ASP.NET MVC
 
Next, we select MVC as a reference in the ASP.NET Web Application template. 
 
Import And Export Data Using Excel File In ASP.NET MVC
 

Adding a Model using Entity Framework

 
We add a Model using ADO.NET Entity Data Model.
 
Import And Export Data Using Excel File In ASP.NET MVC
 
Give a Proper to the Item, such as “DataModel”.
 
Import And Export Data Using Excel File In ASP.NET MVC
 
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.
 
Import And Export Data Using Excel File In ASP.NET MVC
 
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 and finally, we test the connection. 
 
Import And Export Data Using Excel File In ASP.NET MVC
 
Next, select the Version of Entity Framework you want to use.
 
Import And Export Data Using Excel File In ASP.NET MVC
 
We choose the Database Objects to be included in the Model, Here, Database Object is nothing but our Database Table.
 
Import And Export Data Using Excel File In ASP.NET MVC
 
Rename or leave your Model Namespace as it is, then we click on Finish.
 
This creates a DataModel.edmx Diagram which shows all the properties of our InsuranceCertificate Class like below.
 
Import And Export Data Using Excel File In ASP.NET MVC
 
In DataModel.TT, we can also see all the Properties on 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.
 
Import And Export Data Using Excel File In ASP.NET MVC
 

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.
 
Import And Export Data Using Excel File In ASP.NET MVC
 

Adding Controller to the Project

 
Add an Empty MVC 5 Controller to the Controllers Folder as "InsuranceCertificateController" and include our code in it.
 
Import And Export Data Using Excel File In ASP.NET MVC
 
Adding Code in Controller:
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Configuration;  
  4. using System.Data;  
  5. using System.Data.OleDb;  
  6. using System.Data.SqlClient;  
  7. using System.IO;  
  8. using System.Linq;  
  9. using System.Web;  
  10. using System.Web.Mvc;  
  11. using ClosedXML.Excel;  
  12.   
  13. using ExportImportExcel.Models;  
  14.   
  15. namespace ExportImportExcel.Controllers  
  16. {  
  17.     public class InsuranceCertificateController : Controller  
  18.     {  
  19.         InsuranceSampleEntities db = new InsuranceSampleEntities();  
  20.         // GET: InsuranceCertificate  
  21.         public ActionResult Index()  
  22.         {  
  23.             var insuranceCertificate = db.InsuranceCertificates.ToList();  
  24.             return View(insuranceCertificate);  
  25.         }  
  26.   
  27.         [HttpPost]  
  28.         public FileResult ExportToExcel()  
  29.         {  
  30.             DataTable dt = new DataTable("Grid");  
  31.             dt.Columns.AddRange(new DataColumn[11] { new DataColumn("SrNo"),  
  32.                                                      new DataColumn("Title"),  
  33.                                                      new DataColumn("FirstName"),  
  34.                                                      new DataColumn("LastName"),  
  35.                                                      new DataColumn("DateOfBirth"),  
  36.                                                      new DataColumn("Age"),  
  37.                                                      new DataColumn("Gender"),  
  38.                                                      new DataColumn("MaritalStatus"),  
  39.                                                      new DataColumn("EmployeeNumber"),  
  40.                                                      new DataColumn("NomineeName"),  
  41.                                                      new DataColumn("NomineeRelationship")});  
  42.   
  43.             var insuranceCertificate = from InsuranceCertificate in db.InsuranceCertificates select InsuranceCertificate;  
  44.   
  45.             foreach (var insurance in insuranceCertificate)  
  46.             {  
  47.                 dt.Rows.Add(insurance.SrNo, insurance.Title, insurance.FirstName, insurance.LastName,   
  48.                     insurance.DateOfBirth, insurance.Age, insurance.Gender, insurance.MaritalStatus,  
  49.                     insurance.EmployeeNumber, insurance.NomineeName, insurance.NomineeRelationship);  
  50.             }  
  51.   
  52.             using (XLWorkbook wb = new XLWorkbook()) //Install ClosedXml from Nuget for XLWorkbook  
  53.             {  
  54.                 wb.Worksheets.Add(dt);  
  55.                 using (MemoryStream stream = new MemoryStream()) //using System.IO;  
  56.                 {  
  57.                     wb.SaveAs(stream);  
  58.                     return File(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet""ExcelFile.xlsx");  
  59.                 }  
  60.             }  
  61.         }  
  62.   
  63.         [HttpPost]  
  64.         public ActionResult ImportFromExcel(HttpPostedFileBase postedFile)  
  65.         {  
  66.             if (ModelState.IsValid)  
  67.             {  
  68.                 if (postedFile != null && postedFile.ContentLength > (1024 * 1024 * 50))  // 50MB limit  
  69.                 {  
  70.                     ModelState.AddModelError("postedFile""Your file is to large. Maximum size allowed is 50MB !");  
  71.                 }  
  72.   
  73.                 else  
  74.                 {  
  75.                         string filePath = string.Empty;  
  76.                         string path = Server.MapPath("~/Uploads/");  
  77.                         if (!Directory.Exists(path))  
  78.                         {  
  79.                             Directory.CreateDirectory(path);  
  80.                         }  
  81.   
  82.                         filePath = path + Path.GetFileName(postedFile.FileName);  
  83.                         string extension = Path.GetExtension(postedFile.FileName);  
  84.                         postedFile.SaveAs(filePath);  
  85.   
  86.                         string conString = string.Empty;  
  87.                         switch (extension)  
  88.                         {  
  89.                             case ".xls"//For Excel 97-03.  
  90.                                 conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;  
  91.                                 break;  
  92.                             case ".xlsx"//For Excel 07 and above.  
  93.                                 conString = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;  
  94.                                 break;  
  95.                         }  
  96.   
  97.                         try  
  98.                         {  
  99.                             DataTable dt = new DataTable();  
  100.                             conString = string.Format(conString, filePath);  
  101.   
  102.                             using (OleDbConnection connExcel = new OleDbConnection(conString))  
  103.                             {  
  104.                                 using (OleDbCommand cmdExcel = new OleDbCommand())  
  105.                                 {  
  106.                                     using (OleDbDataAdapter odaExcel = new OleDbDataAdapter())  
  107.                                     {  
  108.                                         cmdExcel.Connection = connExcel;  
  109.   
  110.                                         //Get the name of First Sheet.  
  111.                                         connExcel.Open();  
  112.                                         DataTable dtExcelSchema;  
  113.                                         dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);  
  114.                                         string sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();  
  115.                                         connExcel.Close();  
  116.   
  117.                                         //Read Data from First Sheet.  
  118.                                         connExcel.Open();  
  119.                                         cmdExcel.CommandText = "SELECT * From [" + sheetName + "]";  
  120.                                         odaExcel.SelectCommand = cmdExcel;  
  121.                                         odaExcel.Fill(dt);  
  122.                                         connExcel.Close();  
  123.                                     }  
  124.                                 }  
  125.                             }  
  126.   
  127.                             conString = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;  
  128.                             using (SqlConnection con = new SqlConnection(conString))  
  129.                             {  
  130.                                 using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))  
  131.                                 {  
  132.                                     //Set the database table name.  
  133.                                     sqlBulkCopy.DestinationTableName = "InsuranceCertificate";    
  134.                                     con.Open();  
  135.                                     sqlBulkCopy.WriteToServer(dt);  
  136.                                     con.Close();  
  137.                                     return Json("File uploaded successfully");  
  138.                                 }  
  139.                             }  
  140.                         }  
  141.   
  142.                         //catch (Exception ex)  
  143.                         //{  
  144.                         //    throw ex;  
  145.                         //}  
  146.                         catch (Exception e)  
  147.                         {  
  148.                             return Json("error" + e.Message);  
  149.                         }  
  150.                         //return RedirectToAction("Index");  
  151.                 }  
  152.             }  
  153.             //return View(postedFile);  
  154.             return Json("no files were selected !");  
  155.         }  
  156.   
  157.     }  
  158. }  

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.
 
Import And Export Data Using Excel File In ASP.NET MVC
 
Selecting Appropriate View Options
 
Import And Export Data Using Excel File In ASP.NET MVC
 
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.
 
Import And Export Data Using Excel File In ASP.NET MVC
 
Adding Code to the View:
  1. @model IEnumerable<ExportImportExcel.Models.InsuranceCertificate>  
  2.   
  3. @{  
  4.     Layout = null;  
  5. }  
  6.   
  7. <link href="~/Content/bootstrap.css" rel="stylesheet" />  
  8.   
  9. <!DOCTYPE html>  
  10.   
  11. <html>  
  12. <head>  
  13.     <meta name="viewport" content="width=device-width" />  
  14.   
  15. </head>  
  16. <body>  
  17.     <fieldset>  
  18.   
  19.         <div class="col-lg-12 col-md-12 col-sm-12 col-xs-12 form-group">  
  20.             <div class="modal-section-header">  
  21.                 <p>Insured Details - Insurance Certificate Issuance</p>  
  22.             </div>  
  23.         </div>  
  24.   
  25.         <div>  
  26.             @using (Html.BeginForm("ExportToExcel""InsuranceCertificate", FormMethod.Post, new { enctype = "multipart/form-data" }))  
  27.             {  
  28.   
  29.                 <div>  
  30.                     <input type="submit" value="Export" class="btn-primary" />  
  31.                 </div>  
  32.   
  33.             }  
  34.         </div>  
  35.   
  36.         <div>  
  37.   
  38.             @using (Html.BeginForm("ImportFromExcel""InsuranceCertificate", FormMethod.Post, new { enctype = "multipart/form-data" }))  
  39.             {  
  40.                 @Html.ValidationSummary();  
  41.                 <input type="file" name="postedFile" />  
  42.                 <div>  
  43.                     <input type="submit" button id="upload" value="Import" class="btn-success" />  
  44.                 </div>  
  45.             }  
  46.         </div>  
  47.   
  48.     </fieldset>  
  49. </body>  
  50. </html>  
  51.   
  52.   
  53. <h2>InsuredDetails</h2>  
  54.   
  55. <div class="col-lg-12 col-md-12 col-sm-12 col-xs-12 form-group">  
  56.     <div class="modal-section-header">  
  57.         <p>Insurance Certificate Issuance</p>  
  58.     </div>  
  59.   
  60. </div>  
  61. <div class="row">  
  62.     <div class="col-lg-12 col-md-12 col-sm-12 col-xs-12 table-responsive gtcustomgrid">  
  63.         <table class="table-bordered">  
  64.             <thead class="thead-dark">  
  65.                 <tr>  
  66.                     <th>Sr No</th>  
  67.                     <th>Title</th>  
  68.                     <th>First Name</th>  
  69.                     <th>Last Name</th>  
  70.                     <th>Date of Birth</th>  
  71.                     <th>Age</th>  
  72.                     <th>Gender</th>  
  73.                     <th>Marital Status</th>  
  74.                     <th>Employee Number</th>  
  75.                     <th>Nominee Name</th>  
  76.                     <th>Nominee Relationship with Insured</th>  
  77.   
  78.                 </tr>  
  79.             </thead>  
  80.             @foreach (var item in Model)  
  81.             {  
  82.                 <tbody>  
  83.                     <tr>  
  84.                         <td>@Html.DisplayFor(modelItem => item.SrNo, new { @id = "SrNo", @class = "form-control" })</td>  
  85.                         <td>@Html.DisplayFor(modelItem => item.Title, new { @id = "Title", @class = "form-control" })</td>  
  86.                         <td>@Html.DisplayFor(modelItem => item.FirstName, new { @id = "FirstName", @class = "form-control" })</td>  
  87.                         <td>@Html.DisplayFor(modelItem => item.LastName, new { @id = "LastName", @class = "form-control" })</td>  
  88.                         <td>@Html.DisplayFor(modelItem => item.DateOfBirth, new { @id = "DateOfBirth", @class = "form-control" })</td>  
  89.                         <td>@Html.DisplayFor(modelItem => item.Age, new { @id = "Age", @class = "form-control" })</td>  
  90.                         <td>@Html.DisplayFor(modelItem => item.Gender, new { @id = "Gender", @class = "form-control" })</td>  
  91.                         <td>@Html.DisplayFor(modelItem => item.MaritalStatus, new { @id = "MaritalStatus", @class = "form-control" })</td>  
  92.                         <td>@Html.DisplayFor(modelItem => item.EmployeeNumber, new { @id = "EmployeeNumber", @class = "form-control" })</td>  
  93.                         <td>@Html.DisplayFor(modelItem => item.NomineeName, new { @id = "NomineeName", @class = "form-control" })</td>  
  94.                         <td>@Html.DisplayFor(modelItem => item.NomineeRelationship, new { @id = "NomineeRelationship", @class = "form-control" })</td>  
  95.   
  96.                         @*<td>  
  97.                                 @Html.ActionLink("Create New""Create")  
  98.                                 @Html.ActionLink("Edit""Edit"new { /* id=item.PrimaryKey */ }) |  
  99.                                 @Html.ActionLink("Details""Details"new { /* id=item.PrimaryKey */ }) |  
  100.                                 @Html.ActionLink("Delete""Delete"new { /* id=item.PrimaryKey */ })  
  101.                             </td>*@  
  102.                     </tr>  
  103.                 </tbody>  
  104.             }  
  105.         </table>  
  106.     </div>  
  107. </div>  
  108. @section scripts{  
  109.   
  110.     <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>  
  111.   
  112.     <script>  
  113.         $("#upload").click(function () {  
  114.             debugger;  
  115.             if (window.FormData == undefined)  
  116.                 alert("Error: FormData is undefined");  
  117.   
  118.             else {  
  119.                 var fileUpload = $("#postedFile").get(0);  
  120.                 var files = fileUpload.files;  
  121.   
  122.                 var fileData = new FormData();  
  123.   
  124.                 fileData.append(files[0].name, files[0]);  
  125.   
  126.                 $.ajax({  
  127.                     url: '/InsuranceCertificate/ImportFromExcel',  
  128.                     type: 'post',  
  129.                     datatype: 'json',  
  130.                     contentType: false,  
  131.                     processData: false,  
  132.                     async: false,  
  133.                     data: fileData,  
  134.                     success: function (response) {  
  135.                         alert(response);  
  136.                     }  
  137.                 });  
  138.             }  
  139.   
  140.         });  
  141.     </script>  
  142.   
  143. }  
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.
 
Import And Export Data Using Excel File In ASP.NET MVC
 
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.
  1. <add name="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/>    
  2. <add name="Excel07ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/>  
  3. <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 above Connection Strings in the web.config file, you'll face the below error after you try to import your Excel file.
 
Import And Export Data Using Excel File In ASP.NET MVC
 
If you try to Import/Upload the Excel File with 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.
 
Import And Export Data Using Excel File In ASP.NET MVC
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.
 
Import And Export Data Using Excel File In ASP.NET MVC
 
After the "File Uploaded Successfully" message we can go to Index Page and cross-check the details.
 
Import And Export Data Using Excel File In ASP.NET MVC
 
Excel Sheet Data has inserted successfully into the SQL Database Table, which can be seen in the below Picture.
 
Import And Export Data Using Excel File In ASP.NET MVC
 

Conclusion

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