Improved Export capability Kendo UI Grid

The example provided by Telerik to export to excel from the grid would only export the currently viewed page of the grid.  This modification will export the entire grid contents, and if a filter is applied, will export all filtered data only.

The change is listed here (original remarked out)

 //href = href.replace(/pageSize=([^&]*)/, 'pageSize=' + grid.dataSource._pageSize);
 href = href.replace(/pageSize=([^&]*)/, 'pageSize=' + grid.dataSource.total());

Grid Event specification:
@(Html.Kendo().Grid() .Name("gvPatients") .Events(e => { e.Edit("editmode"); e.DataBound("onDataBound"); })
And The link which fires the controller action:

.ToolBar(toolbar => { toolbar.Template(@@item.CreateButton() Clear All Filters Export

@Html.ActionLink("Advanced Patient Search","Index","Search")

); }) @Html.ActionLink("Advanced Patient Search","Index","Search") );
@Html.ActionLink("Advanced Patient Search","Index","Search") ); //toolbar.Custom() // .Text("Export To Excel") // .HtmlAttributes(new { id = "export" }) // .Url(Url.Action("ExportPhysicians", "ListManagement", new { page = 1, pageSize = "~", filter = "~", sort = "~" })); })


JavaScript function:
function onDataBound(e) { $(".k-grid-ViewDetails").find("span").addClass("k-icon k-i-search"); var grid = $('#gvPatients').data('kendoGrid'); // ask the parameterMap to create the request object for you var requestObject = (new kendo.data.transports["aspnetmvc-server"]({ prefix: "" })) .options.parameterMap({ page: grid.dataSource.page(), sort: grid.dataSource.sort(), filter: grid.dataSource.filter() }); // Get the export link as jQuery object var $exportLink = $('#export'); // Get its 'href' attribute - the URL where it would navigate to var href = $exportLink.attr('href'); // Update the 'page' parameter with the grid's current page href = href.replace(/page=([^&]*)/, 'page=' + requestObject.page || '~'); // Update the 'sort' parameter with the grid's current sort descriptor href = href.replace(/sort=([^&]*)/, 'sort=' + requestObject.sort || '~'); // Update the 'pageSize' parameter with the grid's current pageSize //href = href.replace(/pageSize=([^&]*)/, 'pageSize=' + grid.dataSource._pageSize); href = href.replace(/pageSize=([^&]*)/, 'pageSize=' + grid.dataSource.total()); //update filter descriptor with the filters applied href = href.replace(/filter=([^&]*)/, 'filter=' + (requestObject.filter || '~')); // Update the 'href' attribute $exportLink.attr('href', href); }

And the Controller Action:
using System.Collections; using NPOI.HSSF.UserModel; using System.IO; public FileResult ExportPatients([DataSourceRequest]DataSourceRequest request) { //Get the data representing the current grid state - page, sort and filter IEnumerable patients = BusinessLogic.PatientService.GetPatients().OrderBy(m => m.lastName).ThenBy(m => m.firstName); //Create new Excel workbook var workbook = new HSSFWorkbook(); //Create new Excel sheet var sheet = workbook.CreateSheet(); //(Optional) set the width of the columns sheet.SetColumnWidth(0, 50 * 256); sheet.SetColumnWidth(1, 50 * 256); sheet.SetColumnWidth(2, 10 * 256); sheet.SetColumnWidth(3, 30 * 256); sheet.SetColumnWidth(4, 30 * 256); sheet.SetColumnWidth(5, 30 * 256); sheet.SetColumnWidth(6, 30 * 256); sheet.SetColumnWidth(7, 30 * 256); sheet.SetColumnWidth(8, 30 * 256); sheet.SetColumnWidth(9, 30 * 256); sheet.SetColumnWidth(10, 30 * 256); sheet.SetColumnWidth(11, 30 * 256); sheet.SetColumnWidth(12, 30 * 256); sheet.SetColumnWidth(13, 30 * 256); sheet.SetColumnWidth(14, 30 * 256); sheet.SetColumnWidth(15, 30 * 256); sheet.SetColumnWidth(16, 30 * 256); sheet.SetColumnWidth(17, 30 * 256); sheet.SetColumnWidth(18, 30 * 256); sheet.SetColumnWidth(19, 30 * 256); sheet.SetColumnWidth(20, 30 * 256); sheet.SetColumnWidth(21, 30 * 256); sheet.SetColumnWidth(22, 30 * 256); sheet.SetColumnWidth(23, 30 * 256); sheet.SetColumnWidth(24, 30 * 256); sheet.SetColumnWidth(25, 30 * 256); //Create a header row var headerRow = sheet.CreateRow(0); //Set the column names in the header row headerRow.CreateCell(0).SetCellValue("MRN"); headerRow.CreateCell(1).SetCellValue("Last Name"); headerRow.CreateCell(2).SetCellValue("Title"); headerRow.CreateCell(3).SetCellValue("Home Phone"); headerRow.CreateCell(4).SetCellValue("Office Phone"); headerRow.CreateCell(5).SetCellValue("Cell Phone"); headerRow.CreateCell(6).SetCellValue("SSN"); headerRow.CreateCell(7).SetCellValue("genderID"); headerRow.CreateCell(8).SetCellValue("raceID"); headerRow.CreateCell(9).SetCellValue("Race Other"); headerRow.CreateCell(10).SetCellValue("DOB"); headerRow.CreateCell(11).SetCellValue("Pharmacy"); headerRow.CreateCell(12).SetCellValue("Pharmacy Phone"); headerRow.CreateCell(13).SetCellValue("Pharmacy Fax"); headerRow.CreateCell(14).SetCellValue("Lab"); headerRow.CreateCell(15).SetCellValue("Lab Phone"); headerRow.CreateCell(16).SetCellValue("Email"); headerRow.CreateCell(17).SetCellValue("Is Active"); headerRow.CreateCell(18).SetCellValue("Is Expired"); headerRow.CreateCell(19).SetCellValue("Address"); headerRow.CreateCell(20).SetCellValue("Address Contd."); headerRow.CreateCell(21).SetCellValue("City"); headerRow.CreateCell(22).SetCellValue("State"); headerRow.CreateCell(23).SetCellValue("ZIP"); headerRow.CreateCell(24).SetCellValue("Country"); headerRow.CreateCell(25).SetCellValue("Notes"); //(Optional) freeze the header row so it is not scrolled sheet.CreateFreezePane(0, 1, 0, 1); int rowNumber = 1; //Populate the sheet with values from the grid data foreach (Patient product in patients) { //Create a new row var row = sheet.CreateRow(rowNumber++); //Set values for the cells row.CreateCell(0).SetCellValue(product.medicalRecordNumber.ToString()); row.CreateCell(1).SetCellValue(product.lastName); row.CreateCell(2).SetCellValue(product.firstName); row.CreateCell(3).SetCellValue(product.homePhone); row.CreateCell(4).SetCellValue(product.workPhone); row.CreateCell(5).SetCellValue(product.cellPhone); row.CreateCell(6).SetCellValue(product.SSN); row.CreateCell(7).SetCellValue(product.genderID.ToString()); row.CreateCell(8).SetCellValue(product.raceID.ToString()); row.CreateCell(9).SetCellValue(product.raceOther); row.CreateCell(10).SetCellValue(product.DOB.ToString()); row.CreateCell(11).SetCellValue(product.pharm); row.CreateCell(12).SetCellValue(product.pharmPhone); row.CreateCell(13).SetCellValue(product.pharmFax); row.CreateCell(14).SetCellValue(product.lab); row.CreateCell(15).SetCellValue(product.labPhone); row.CreateCell(16).SetCellValue(product.labFax); row.CreateCell(17).SetCellValue(product.email); row.CreateCell(18).SetCellValue(product.isActive.ToString()); row.CreateCell(19).SetCellValue(product.isExpired.ToString()); row.CreateCell(20).SetCellValue(product.addressLine1); row.CreateCell(21).SetCellValue(product.addressLine2); row.CreateCell(22).SetCellValue(product.city); row.CreateCell(23).SetCellValue(product.State); row.CreateCell(24).SetCellValue(product.country); row.CreateCell(25).SetCellValue(product.notes); } //Write the workbook to a memory stream MemoryStream output = new MemoryStream(); workbook.Write(output); //Return the result to the end user return File(output.ToArray(), //The binary data of the XLS file "application/vnd.ms-excel", //MIME type of Excel files "PatientsExport.xls"); //Suggested file name in the "Save as" dialog which will be displayed to the end user } }