public ActionResult ExportDatatoExcel()
{
try
{
IQueryable <Models.DBECompany> query;
List <Models.DBECompany> CompanyList;
using (var db = new Models.DBESearchDirectoryEntities())
{
Models.DBECompany data = (Models.DBECompany)Session["query"];
query = GetSearchResults(data, db);
query = query
.Select(c => c);
CompanyList = query.ToList();
}
GridView gv = new GridView();
gv.DataSource = CompanyList;
gv.DataBind();
var fileName = "Company_List-" + DateTime.Now.ToString("yyyyMMdd_hhss") + ".xlsx";
var file = new FileInfo(fileName);
ExcelPackage excel = new ExcelPackage();
var workSheet = excel.Workbook.Worksheets.Add("List");
var totalCols = gv.Rows[0].Cells.Count;
var totalRows = gv.Rows.Count;
var headerRow = gv.HeaderRow;
for (var i = 1; i <= totalCols; i++)
{
workSheet.Cells[1, i].Value = headerRow.Cells[i - 1].Text;
}
for (var j = 1; j <= totalRows; j++)
{
for (var i = 1; i <= totalCols; i++)
{
var a = CompanyList.ElementAt(j - 1);
workSheet.Cells[j + 1, i].Value = a.GetType().GetProperty(headerRow.Cells[i - 1].Text).GetValue(a);
}
}
using (var memoryStream = new MemoryStream())
{
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment; filename=" + fileName);
excel.SaveAs(memoryStream);
memoryStream.WriteTo(Response.OutputStream);
Response.Flush();
Response.End();
}
return(RedirectToAction("Search"));
}
catch (Exception)
{
ViewBag.Message = "Your data is too Large to Export to Excel. Please filter your Search to avoid the error. Thank you!";
return(View());
}
}