|
Article relates to
|
Telerik Grid for ASP.NET MVC 2010.1.218+
|
|
Created by
|
Atanas Korchev, Telerik
|
|
Last modified
|
2/19/2010
|
|
Last modified by
|
2/19/2010
|
HOW-TO
Export the contents of Telerik Grid for ASP.NET MVC as Microsoft Excel spreadsheet
DESCRIPTION
In numerous cases you may need to export the data displayed by the grid. This knowledge base article shows how to implement Excel export in a few lines of code.
SOLUTION
The Excel export relies on the open source project NPOI. Here is the code in the view:
<%= Html.ActionLink("Export to Excel", "Export", new { page = 1, orderBy = "~", filter = "~"}, new { id = "exportLink" }) %>
<script type="text/javascript">
function onDataBound() {
var grid = $(this).data('tGrid');
var $exportLink = $('#exportLink');
var href = $exportLink.attr('href');
href = href.replace(/page=([^&]*)/, 'page=' + grid.currentPage);
href = href.replace(/orderBy=([^&]*)/, 'orderBy=' + (grid.orderBy || '~'));
href = href.replace(/filter=(.*)/, 'filter=' + (grid.filterBy || '~'));
$exportLink.attr('href', href);
}
</script>
<%= Html.Telerik().Grid(Model)
.Name("Grid")
.Columns(columns =>
{
columns.Bound(o => o.OrderID);
columns.Bound(o => o.ShipAddress);
columns.Bound(o => o.CustomerID);
columns.Bound(o => o.ShipCountry);
columns.Bound(o => o.OrderDate);
})
.Pageable()
.Sortable()
.Filterable()
.DataBinding(dataBinding => dataBinding
.Ajax()
.Select("IndexAjax", "Home"))
.ClientEvents(events => events.OnDataBound("onDataBound"))
%>
Upon clicking the action link a controller method will export the grid data and stream it back as Excel file. The "onDataBound" client-side event of the grid is handled to update the URL arguments required for export (current page, order and filter expressions). Here is the implementation of the controller action which does the export:
public ActionResult Export(int page, string orderBy, string filter)
{
//Get the data representing the current grid state - page, sort and filter
GridModel model = Model().ToGridModel(page, 10, orderBy, string.Empty, filter);
var orders = model.Data.Cast<Order>();
//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, 10 * 256);
sheet.SetColumnWidth(1, 50 * 256);
sheet.SetColumnWidth(2, 50 * 256);
sheet.SetColumnWidth(3, 50 * 256);
//Create a header row
var headerRow = sheet.CreateRow(0);
//Set the column names in the header row
headerRow.CreateCell(0).SetCellValue("OrderID");
headerRow.CreateCell(1).SetCellValue("ShipAddress");
headerRow.CreateCell(2).SetCellValue("CustomerID");
headerRow.CreateCell(3).SetCellValue("OrderDate");
//(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 (Order order in orders)
{
//Create a new row
var row = sheet.CreateRow(rowNumber++);
//Set values for the cells
row.CreateCell(0).SetCellValue(order.OrderID);
row.CreateCell(1).SetCellValue(order.ShipAddress);
row.CreateCell(2).SetCellValue(order.CustomerID);
row.CreateCell(3).SetCellValue(order.OrderDate.ToString());
}
//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
"GridExcelExport.xls"); //Suggested file name in the "Save as" dialog which will be displayed to the end user
}