How To Download SQL Records In Excel Format Using MVC

Mithilesh Kumar
7y
16.2k
0
8

Article

Today, in this article, I will explain how to download records in Excel format from database. If we are downloading an Excel file from one table, then we can download in an easy way. But suppose, we have to download from more than one table; or we have to download to different sheets in an Excel file; or if we have some already-existing format in an Excel file and we just have to update the fields in sheets; then it's very difficult. However, now I have a solution.

Here, I have used 3 tables with no relationship among them. Here, I'm downloading the first table in sheet1 and the first and second table are downloading sheet 2. I will use MVC with SQL Server.

Step1

First, let's create 3 tables in the database.

Step2

Now, we have to add the tables in my MVC application. I have used Entity Framework with LINQ query.

For that, I created an MVC application and went through, File->New ->Web application ->select MVC ->OK.

Go to Model folder ->Right click -> Add -> New item -> ADO.NET Entity Data Model -> click Add -> select database first approch->Click Next.

Select New Connection and give the connection details, then select database ->Click OK.

Choose tables and click OK.

Step3

First, we have to display records for understanding purpose. So for that I am using partial view to display records of 3 tables in separately in one page

For this, I added a controller in Controller folder after that I created 3 PartialView methods like these BookDetails(),CourseDetails() and TeacherDetails()

->So, see in brief, How to create partial view, For that write partial method and give the return type PartialViewResult and write the code for fetching the records form database like below

So finally I wrote all three tables' logic for fetching the records from database

//for fetching the book details
public PartialViewResult BookDetails()
{
var bookDetails=new List<BookDetail>();
using (MKDBEntities DBContext = new MKDBEntities())
{
bookDetails = DBContext.BookDetails.ToList();
}
return PartialView(bookDetails);
}
//for fetching the course details
public PartialViewResult CourseDetails()
{
var courseDetails = new List<Course>();
using (MKDBEntities DBContext = new MKDBEntities())
{
courseDetails = DBContext.Courses.ToList();
}
return PartialView(courseDetails);
}
//for fetching the teachers details
public PartialViewResult TeacherDetails()
{
var teacherDetails = new List<Teacher>();
using (MKDBEntities DBContext = new MKDBEntities())
{
teacherDetails = DBContext.Teachers.ToList();
}
return PartialView(teacherDetails);
}

->Now I am creating view to display of the partial view one by one so for that just right and select partial view in check box, like this

Next click add button and write view code

@model IEnumerable<ExcelFileDownload.Models.BookDetail>
<table class="table" style="border:2px solid">
<tr class="btn-primary">
<th>Order Number</th>
<th>Author</th>
<th>Publisher</th>
<th>Price</th>
</tr>
@foreach (var item in Model)
{
<tr class="btn-info">
<td>@item.BookName</td>
<td>@item.Author</td>
<td>@item.Publisher</td>
<td>@item.Price</td>
</tr>
}
</table>

So similarly I created 2 more partial views for course details and teacher details

CourseDetails.cshtml

@model IEnumerable<ExcelFileDownload.Models.Course>
<table class="table" style="border:2px solid">
<tr class="btn-danger">
<th>Course Name</th>
<th>Location</th>
</tr>
@foreach (var item in Model)
{
<tr class="btn-warning">
<td>@item.CourseName</td>
<td>@item.Location</td>
</tr>
}
</table>

TeachersDetails.cshtml

@model IEnumerable<ExcelFileDownload.Models.Teacher>
<table class="table" style="border:2px solid">
<tr class="btn-success">
<th>Teacher Name</th>
<th>Teacher Type</th>
</tr>
@foreach (var item in Model)
{
<tr class="btn-primary">
<td>@item.TeacherName</td>
<td>@item.TeacherType</td>
</tr>
}
</table>

Now we can see the output below but before that we have to create a view page for calling all 3 partial views

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

Now we've added a view page and called 3 partial views

@{
ViewBag.Title = "Index";
}
<h2>All Details</h2>
<div>
@using (Ajax.BeginForm("DownloadExcel", "Demo", new AjaxOptions { HttpMethod = "POST" }))
{
Html.RenderAction("BookDetails");
Html.RenderAction("CourseDetails");
Html.RenderAction("TeacherDetails");
<input type="submit" value="Download In Excel" />
}
</div>

Now we can see the output

Step4

Now when I click the download button all the records should be downloaded in excel format so again remember here I am downloading bookdetails in sheet1 and course details and teacher details in sheet2 in Excel file

Here I gave an Excel file for default format in DetailFormatInExcel folder.

So for that I created a separate class in models folder and I gave the name BussinessLayer and wrote all logic to create excel format one by one.

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
namespace ExcelFileDownload.Models
{
public class BusinessLayer
{
public DataTable GetXlsTableCourse()
{
var dt = new DataTable();
dt.Columns.Add(new DataColumn
{
AllowDBNull = false,
AutoIncrement = true,
AutoIncrementSeed = 1,
ColumnName = "Course Name",
DataType = typeof(string)
});
dt.Columns.Add(new DataColumn
{
AllowDBNull = true,
ColumnName = "Location",
DataType = typeof(string)
});
return LoadTableData(dt);
}
public DataTable LoadTableData(DataTable dt)
{
var courseDetails = new List<Course>();
MKDBEntities DBContext = new MKDBEntities();
courseDetails = DBContext.Courses.ToList();
foreach (var item in courseDetails)
{
var dr = dt.NewRow();
dr["Course Name"] = item.CourseName;
dr["Location"] = item.Location;
dt.Rows.Add(dr);
dt.AcceptChanges();
}
return dt;
}
public DataTable GetXlsTableTeacher()
{
var dt = new DataTable();
dt.Columns.Add(new DataColumn
{
AllowDBNull = true,
ColumnName = "Teacher Name",
DataType = typeof(string)
});
dt.Columns.Add(new DataColumn
{
AllowDBNull = true,
ColumnName = "Teacher Type",
DataType = typeof(string)
});
return LoadTableDataDetails(dt);
}
public DataTable LoadTableDataDetails(DataTable dt)
{
var teacherDetails = new List<Teacher>();
MKDBEntities DBContext = new MKDBEntities();
teacherDetails = DBContext.Teachers.ToList();
foreach (var item in teacherDetails)
{
var dr = dt.NewRow();
dr["Teacher Name"] = item.TeacherName;
dr["Teacher Type"] = item.TeacherType;
dt.Rows.Add(dr);
dt.AcceptChanges();
}
return dt;
}
public DataTable GetXlsTableBooks()
{
var dt = new DataTable();
dt.Columns.Add(new DataColumn
{
AllowDBNull = true,
ColumnName = "BookName",
DataType = typeof(string)
});
dt.Columns.Add(new DataColumn
{
AllowDBNull = true,
ColumnName = "Author",
DataType = typeof(string)
});
dt.Columns.Add(new DataColumn
{
AllowDBNull = true,
ColumnName = "Publisher",
DataType = typeof(string)
});
dt.Columns.Add(new DataColumn
{
AllowDBNull = true,
ColumnName = "Price",
DataType = typeof(string)
});
return LoadTableBookDetails(dt);
}
public DataTable LoadTableBookDetails(DataTable dt)
{
var bookDetails = new List<BookDetail>();
MKDBEntities DBContext = new MKDBEntities();
bookDetails = DBContext.BookDetails.ToList();
foreach (var item in bookDetails)
{
var dr = dt.NewRow();
dr["BookName"] = item.BookName;
dr["Author"] = item.Author;
dr["Publisher"] = item.Publisher;
dr["Price"] = item.Price;
dt.Rows.Add(dr);
dt.AcceptChanges();
}
return dt;
}
}
}

Now I created a method in controller and I gave the name DownloadExcel()

public ActionResult DownloadExcel()
{
BusinessLayer BAL = new BusinessLayer();
var workbook = new Spire.Xls.Workbook();
workbook.LoadFromFile(Server.MapPath("~/DetailFormatInExcel/DetailsFormat.xlsx"));
var worksheet1 = workbook.Worksheets[0];
var worksheet2 = workbook.Worksheets[1];
byte[] array = null;
var dt1 = BAL.GetXlsTableBooks();
worksheet1.InsertDataTable(dt1, false, 3, 1);
var dt2 = BAL.GetXlsTableCourse();
worksheet2.InsertDataTable(dt2, false, 3, 1);
var dt3 = BAL.GetXlsTableTeacher();
worksheet2.InsertDataTable(dt3, false, 3, 3);
using (var ms = new System.IO.MemoryStream())
{
workbook.SaveToStream(ms, FileFormat.Version2010);
ms.Seek(0, System.IO.SeekOrigin.Begin);
array = ms.ToArray();
}
return File(array, "application / vnd.openxmlformats - officedocument.spreadsheetml.sheet"," Detail.xlsx");
}