How To Download SQL Records In Excel Format Using MVC

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.

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

{

publicclass 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()