first you can download EEPLUS library,through Nu Get.EPPlus is a really good library to help you generate Excel spreadsheets together with C#!
After that include header files in Controller,In which we are going to define the method for download to excel sheet
like as showing in below
using OfficeOpenXml;
using OfficeOpenXml.Style;
using OfficeOpenXml.Table;
using System.Data;
using System.IO;
public void DownloadToXml()
{
namespace Suite.Controllers
{
DataSet dataToExcel = budget.DocsDS; //budget.DocsDS its a dataset(set of datatables),so here acces your datatable
string excelSheetName = "BudgetDocument";
string fileName = "BudgetDocs";
string currentDirectorypath = Server.MapPath("~/Reports");
string finalFileNameWithPath = string.Empty;
using (var pck = new ExcelPackage())
fileName = string.Format("{0}_{1}", fileName, DateTime.Now.ToString("dd-MM-yyyy"));
finalFileNameWithPath = string.Format("{0}\\{1}.xlsx", currentDirectorypath, fileName);
if(System.IO.File.Exists(Server.MapPath("~/Reports/" + fileName + ".xlsx")))
System.IO.File.Delete(Server.MapPath("~/Reports/" + fileName + ".xlsx"));
var newFile = new FileInfo(finalFileNameWithPath);
//Step 1 : Create object of ExcelPackage class and pass file path to constructor.
using (var package = new ExcelPackage(newFile))
{
//Step 2 : Add a new worksheet to ExcelPackage object and give a suitable name
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(excelSheetName);
//Step 3 : Start loading datatable form A1 cell of worksheet.
worksheet.Cells["A1"].LoadFromDataTable(dataToExcel.Tables[0], true, TableStyles.Light1);
//Step 4 : (Optional) Set the file properties like title, author and subject
package.Workbook.Properties.Title = @"This code is part of tutorials available at http://bytesofcode.hubpages.com";
package.Workbook.Properties.Author = "Bytes Of Code";
package.Workbook.Properties.Subject = @"Register here for more http://hubpages.com/_bytes/user/new/";
//Step 5 : Save all changes to ExcelPackage object which will create Excel 2007 file.
package.Save();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.TransmitFile(Server.MapPath("~/Reports/" + fileName + ".xlsx"));
Response.End();
}
}
}