Hi friends, Today I am explaining in my article that how to
download records in excel format from the 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 a different sheet in
an excel file that time we feel something
difficult or some conditions if we
have already some format in excel file and just we have to update the fields in
sheets in excel file that time we also feel default but now I have
solution So I am creating an article for that here I used
3 tables and among 3 tables are no any relationship I.e without any
relationship. Here downloading the first table in sheet1 and first & second
table is downloading sheet2. So finally I used MVC with SQL Server So Now I am going through step by step
Step1: -
First, I created 3 tables in a database like these-
Step2:-
Now we have to add the tables in my MVC application so for that I used entity
framework with the LINQ query.
-> So for that I created an mvc application and go
through, file->New ->Web application ->select mvc ->OK.
->Go to model folder ->Right click->Add ->Select
new item->Select ADO.NET Entity Data
Model ->Click Add->Select database first approch->Click Next
button->Select New Connection and give the connection and whatever need and
then select database ->Click Ok->Choose tables and click ok
After we find like this
Step3:-
First, we have to display records for understanding purposes.
So for that, I am using a partial view to display records of 3 tables in
separately in one page
- For this, I added a controller in the Controller folder
after that, I created 3 PartialView methods
like these BookDetails(),CourseDetails() and TeacherDetails()
- So, see in brief, How to create a partial view, For that write
partial method and give the return type PartialViewResultand write the code for fetching the records
form database like below
so finally I wrote all three tables the logic for fetching the records from the 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 a view to display of the partial view one by one so for that just
right and select partial view in the checkbox, like this
next click
add button and wrote 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 view for course details and teachers 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 call all 3
partial view
public ActionResult Index()
{
return View();
}
now 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 we have to do that when I click download button the all
records should be download in excel format so again remember here I am download book details 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 the 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");
}
Now I have completed the coding part
so finally we can download the excel file
This is sheet1 result for book details
This is sheet2 result
for course details and and teacher details
Thanks, I hope Enjoy
with happy coding
No comments:
Post a Comment