A better solution is to generate an Excel file from the real data, not from the dataSource.
1]
In the html page, add
$('#export').click(function () {
var title = "EmployeeData";
var id = guid();
var filter = $("#grid").data("kendoGrid").dataSource._filter;
var data = {
filter: filter,
title: title,
guid: id
};
$.ajax({
url: '/Employee/Export',
type: "POST",
dataType: 'json',
data: JSON.stringify(data),
contentType: "application/json; charset=utf-8",
success: function (result) {
window.location = kendo.format("{0}?title={1}&guid={2}", '/Employee/GetGeneratedExcel', title, id);
}
});
});
2]
Add a method "Export" to the controller:
[HttpPost]
public JsonResult Export(KendoGridFilter filter, string guid)
{
var gridRequest = new KendoGridRequest();
if (filter != null)
{
gridRequest.FilterObjectWrapper = filter.Filters != null ? filter.ToFilterObjectWrapper() : null;
gridRequest.Logic = filter.Logic;
}
var query = GetQueryable().AsNoTracking();
var results = query.FilterBy<Employee, EmployeeVM>(gridRequest);
using (var stream = new MemoryStream())
{
using (var excel = new ExcelPackage(stream))
{
excel.Workbook.Worksheets.Add("Employees");
var ws = excel.Workbook.Worksheets[1];
ws.Cells.LoadFromCollection(results);
ws.Cells.AutoFitColumns();
excel.Save();
Session[guid] = stream.ToArray();
return Json(new { success = true });
}
}
}
3]
Also add the method "GetGeneratedExcel" to the controller:
[HttpGet]
public FileResult GetGeneratedExcel(string title, string guid)
{
// Is there a spreadsheet stored in session?
if (Session[guid] == null)
{
throw new Exception(string.Format("{0} not found", title));
}
// Get the spreadsheet from session.
var file = Session[guid] as byte[];
string filename = string.Format("{0}.xlsx", title);
// Remove the spreadsheet from session.
Session.Remove(title);
// Return the spreadsheet.
Response.Buffer = true;
Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}", filename));
return File(file, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", filename);
}
Also see this project on github.
See this live example project where you can export the Employees to Excel. (Although this returns filtered data, but you can modify the code to ignore the kendo grid filter and always return all data.
Best Answer
It seems to be that only the data is read with
dataSource
, you need to refresh the grid also like this: