Export Telerik MVC Grid to CSV Dynamically based on a View Model

We may have seen examples from Telerik where they had exported data to CSV but Column Headers and Column Values are assigned manually to StreamWriter object.  For this post we will improve further on that by doing it dynamically, which makes sense when you are generating your grid in dynamic fashion as well.  This is good as it saves you time as you will not hard code all of the Columns you need as well as giving that flexibility later when you want to add or remove items from the Gird View without doing some code changes, this will also gives you the option to save user preferences.

Lets start.

First lets assume you have a grid, dynamic or not but in this example I am generating my columns dynamically.   Normally it would look like this for dynamic column generation.

@(Html.Telerik().Grid<YourViewModel>()
.Name("grdTransactions")
.Columns(c =>
{
    //Dynamic Grid View Preferences
    foreach (var g in Model.InvoiceTransactionGridPreferences)
    {
        c.Bound(g.ColumnName)
        .Title(g.ColumnHeader)
        .Width(g.Width)
        .ReadOnly(g.IsReadOnly)
        .Groupable(g.IsGroupable)
        .Filterable(g.IsFilterable)
        .Encoded(false)
        .Format(g.Format)
        .HtmlAttributes(new { @class = g.Class });
    }
})
.DataBinding(d => d
    .Ajax()
    .OperationMode(GridOperationMode.Client)
    .Select("SelectTransactions", "YourController", new { accountId = Model.AccountId, invoicePeriod = Model.InvoicePeriod })
    .Update("UpdateTransactions", "YourController", new { accountId = Model.AccountId, invoicePeriod = Model.InvoicePeriod }))
.Pageable(p => p.PageSize(Model.PageSize))
.Sortable()
.Scrollable()
.Filterable()
.Groupable();

or not dynamic like such

@(Html.Telerik().Grid<YourViewModel>()
.Name("grdTransactions")
.Columns(c =>
{
   c.Bound(o => o.Description).Width(100);
   c.Bound(o => o.Nett).Width(100);
   c.Bound(o => o.GST).Width(100);
   c.Bound(o => o.Gross).Width(100);

})
.DataBinding(d => d
    .Ajax()
    .OperationMode(GridOperationMode.Client)
    .Select("SelectTransactions", "YourController", new { accountId = Model.AccountId, invoicePeriod = Model.InvoicePeriod })
    .Update("UpdateTransactions", "YourController", new { accountId = Model.AccountId, invoicePeriod = Model.InvoicePeriod }))
.Pageable(p => p.PageSize(Model.PageSize))
.Sortable()
.Scrollable()
.Filterable()
.Groupable();

Next is to copy the javascript that was suggested in Telerik’s documentation on MVC grid for exporting CSV here (http://demos.telerik.com/aspnet-mvc/grid/customcommand), we adjusted it slightly to add the parameters we need.  So it looks like this now.

<script type ="text/javascript">
    function onDataBound() {

        var grid = $(this).data('tGrid');

        // 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=' + grid.currentPage);

        // Update the 'orderBy' parameter with the grids' current sort state
        href = href.replace(/orderBy=([^&]*)/, 'orderBy=' + (grid.orderBy || '~'));

        // Update the 'filter' parameter with the grids' current filtering state
        href = href.replace(/filter=([^&]*)/, 'filter=' + (grid.filterBy || '~'));

        // Pass other remaining parameters you may have
        href = href.replace(/accountId=([^&]*)/, 'accountId=' + $('#AccountId').val());
        href = href.replace(/batchId=(.*)/, 'batchId=' + $('#InvoicePeriod').val());

        // Update the 'href' attribute
        $exportLink.attr('href', href);

        InvoiceActions.invoiceGridDataBound();
    }
</script> 

@(Html.Telerik().Grid<YourViewModel>()
.Name("grdTransactions")
.Columns(c =>
{
    //Dynamic Grid View Preferences
    foreach (var g in Model.InvoiceTransactionGridPreferences)
    {
        c.Bound(g.ColumnName)
        .Title(g.ColumnHeader)
        .Width(g.Width)
        .ReadOnly(g.IsReadOnly)
        .Groupable(g.IsGroupable)
        .Filterable(g.IsFilterable)
        .Encoded(false)
        .Format(g.Format)
        .HtmlAttributes(new { @class = g.Class });
    }
})
.DataBinding(d => d
    .Ajax()
    .OperationMode(GridOperationMode.Client)
    .Select("SelectTransactions", "YourController", new { accountId = Model.AccountId, invoicePeriod = Model.InvoicePeriod })
    .Update("UpdateTransactions", "YourController", new { accountId = Model.AccountId, invoicePeriod = Model.InvoicePeriod }))
.Pageable(p => p.PageSize(Model.PageSize))
.Sortable()
.Scrollable()
.Filterable()
.Groupable()
.ClientEvents(c => c.OnDataBound("onDataBound"))
.ToolBar(t => t
    .Custom()
    .HtmlAttributes(new { id = "export" })
    .Text("Export to CSV")
    .Action("ExportToCSV", "YourController", new { page = 1, orderBy = "~", filter = "~", accountId = Model.AccountId, invoicePeriod = Model.InvoicePeriod})));

Now go to your controller then add the following Action for exporting your csv.

public ActionResult ExportToCSV(int page, string orderBy, string filter, int? accountId, int? invoicePeriod)
{
    //Get Related Transaction, make sure that your Query Method retruns and IEnumerable<T> of the view model you are using
    var transactions = yourQuery.GetTransactions(accountId, invoicePeriod)
        .AsQueryable()
        .ToGridModel(page, int.MaxValue, orderBy, string.Empty, filter)
        .Data
        .Cast<YourViewModel>();

    //Convert To IEnumarable<T> to CSV
    string csvData = transactions.ToCsv<YourViewModel>();

    //Get all Column Headers Dynamically
    var queriedColumns = transactions.GetType()
                .GetInterfaces()
                .Where(x => x.IsGenericType && x.GetGenericTypeDefinition() == typeof(IEnumerable<>))
                .Single()
                .GetGenericArguments()
                .Single();

    var columns = queriedColumns.GetProperties();

    string[] columnNames = columns.Select(column => column.Name).ToArray();

    var columnNamesString = string.Join(",", columnNames);

    //Prepare Output
    MemoryStream output = new MemoryStream();
    StreamWriter stringWriter = new StreamWriter(output, Encoding.UTF8);

    stringWriter.Write(columnNamesString);
    stringWriter.WriteLine();
    stringWriter.Write(csvData);

    stringWriter.Flush();
    output.Position = 0;

    return File(output, "text/comma-separated-values", "Export.csv");
}

If you notice I used an extension method ToCsv, thanks to this post (http://mikehadlow.blogspot.com.au/2008/06/linq-to-csv.html) it saved me from coding.  You will also notice that the GridModel extension from Telerik will handle all the sorting, filtering and grouping so you just need to just pass it hence you have the URL builder JavaScript earlier.

public static class IEnumerableToCSV
    {
        public static string ToCsv<T>(this IEnumerable<T> items)
           where T : class
        {
            var csvBuilder = new StringBuilder();
            var properties = typeof(T).GetProperties();
            foreach (T item in items)
            {
                string line = string.Join(",", properties.Select(p => p.GetValue(item, null).ToCsvValue()).ToArray());
                csvBuilder.AppendLine(line);
            }
            return csvBuilder.ToString();
        }

        private static string ToCsvValue<T>(this T item)
        {
            if (item == null) return "\"\"";

            if (item is string)
            {
                return string.Format("\"{0}\"", item.ToString().Replace("\"", "\\\""));
            }
            double dummy;
            if (double.TryParse(item.ToString(), out dummy))
            {
                return string.Format("{0}", item);
            }
            return string.Format("\"{0}\"", item);
        }
    }

Simple Implementation of MVC Cascading Ajax Drop Down

Don’t you miss developing the traditional way? where you can just use Update Panels and Auto Post backs, it was so easy to develop the UI before where you can see it in the design view and interact with the objects in a rich manner. But that was the past, now we have MVC where its presents us a different way of developing things the proper way.  If you are starting to use MVC like me then  you might be thinking how do I make cascading drop downs without the Auto Post back and Update Panels then read further as this is how I implemented it in the most easiest way.

Lets start! Lets say you have the following structure below.

You need to have models for each of them

Now you need a controller to perform all of the operations, Lets call it CascadingDropDown.cs, we need 3 Actions Results one for the Index which will Load the page and populate the Category DropDown, Select Category to populate Sub Category Drop Down and Select Sub Category to populate Products Drop Down.  If you notice the name might be confusing that is because it is named after an Action Result not action to perform, which means what Action was invoked for this to happen.  Below is a sample code.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace MvcApplication1.Controllers
{
    using Models;

    public class CascadingDropDownController : Controller
    {
        public ActionResult Index()
        {
            ProductCatalog productCatalog = new ProductCatalog();
            productCatalog.Categories = ProductCatalog.GetCategories();

            return View(productCatalog);
        }

        [HttpPost]
        public ActionResult SelectCategory(int? selectedCategoryId)
        {
            ProductCatalog productCatalog = new ProductCatalog();
            productCatalog.SubCategories = new List<SubCategory>();

            if (selectedCategoryId.HasValue)
            {
                productCatalog.SubCategories = (from s in ProductCatalog.GetSubCategories()
                                                where s.CategoryId == selectedCategoryId
                                                orderby s.Name
                                                select s).ToList();
            }

            return PartialView("SubCategoriesUserControl", productCatalog);

        }

        [HttpPost]
        public ActionResult SelectSubCategory(int? selectedSubCategoryId)
        {
            ProductCatalog productCatalog = new ProductCatalog();
            productCatalog.Products = new List<Product>();

            if (selectedSubCategoryId.HasValue)
            {
                productCatalog.Products = (from s in ProductCatalog.GetProducts()
                                           where s.SubCategoryId == selectedSubCategoryId
                                           orderby s.Name
                                           select s).ToList();
            }

            return PartialView("ProductsUserControl", productCatalog);

        }
    }
}

Take note we use partial view on the SelectCategory and SelectSubCategory as we will send a partial view to the response.  While on the Index we need the full rendering to the response.  Now you have your controller we need to create those views.  We need 1 view for Index and 3 partial views for the 3 dropdowns.

Now lets create the Index view, all you have to do is to right-click on the Index method on your controller then you can start coding.  This view will display all of the dropdowns.

Index.cshtml

@model MvcApplication1.Models.ProductCatalog
@{
    ViewBag.Title = "Index";
    Layout = "~/Views/Shared/_Layout.cshtml";
}
<table cellpadding="0" cellspacing="4" border="0">
    <tr>
        <td>Category&nbsp;</td>
        <td>&nbsp;:</td>
        <td>@Html.Partial("CategoriesUserControl", Model)</td>
    </tr>
    <tr>
        <td>Sub - Category&nbsp;</td>
        <td>&nbsp;:</td>
        <td><div id="SubCategories">@Html.Partial("SubCategoriesUserControl", Model)</div></td>
    </tr>
    <tr>
        <td>Products&nbsp;</td>
        <td>&nbsp;:</td>
        <td><div id="Products">@Html.Partial("ProductsUserControl", Model)</div></td>
    </tr>
</table>

Now lets create partial views and for those who does not know how just right-click on the parent folder where your Index view is and Select Add -> View then tick the “create as a partial view”.

Now for the codes:

CategoriesUserControl.cshtml

@model MvcApplication1.Models.ProductCatalog
@using (Ajax.BeginForm("SelectCategory", "CascadingDropDown", new AjaxOptions { UpdateTargetId = "SubCategories" }))
{ 
    @Html.DropDownListFor(
            m => m.SelectedCategoryId,
            new SelectList(Model.Categories, "Id", "Name"),
           string.Empty
        )
}
<script type="text/javascript">
    $('#SelectedCategoryId').change(function () {
        $(this).parents('form').submit();
    });
</script>

SubCategoriesUserControl.cshtml

@model MvcApplication1.Models.ProductCatalog
@if (Model.SubCategories != null && Model.SubCategories.Count() > 0)
{
    using (Ajax.BeginForm("SelectSubCategory", "CascadingDropDown", new AjaxOptions { UpdateTargetId = "Products" }))
    { 
    @Html.HiddenFor(m => m.SelectedCategoryId)
    @Html.DropDownListFor(
            m => m.SelectedSubCategoryId,
            new SelectList(Model.SubCategories, "Id", "Name"),
            string.Empty
            )
    }
}
<script type="text/javascript">
    $('#SelectedSubCategoryId').change(function () {
        $(this).parents('form').submit();
    });   
</script>

ProductsUserControl.cshtml

@model MvcApplication1.Models.ProductCatalog
@if (Model.Products != null && Model.Products.Count() > 0)
{
    @Html.DropDownList(
        "Products",
            new SelectList(Model.Products, "Id", "Name"),
            string.Empty
            )
}

If you notice instead of Html.BeginForm we use Ajax.BeginForm using the parameters below.

• actionName will be name of your method in your controller
• controllerName will be theame of your controller
• AjaxOptions as the name says AjaxOptions, at this point we only need the UpdateTargetId which we defined as <div> in the index.cshtml file.

If you notice there are jQuery scripts below CategoriesUserControl.cshtml and SubCategoriesUserControl.cshtml, they will handle the auto postback on the partially rendered HTML.

Also on your _Layout.cshtml add this on the header if you haven’t done it yet

<script src="@Url.Content("~/Scripts/jquery-1.5.1.min.js")" type="text/javascript"></script>
<script src="@Url.Content("~/Scripts/jquery.unobtrusive-ajax.min.js")" type="text/javascript"></script>

Note : If you are using ASP.Net MVC3 then by default it uses unobtrusive jquery so you need to remove all MicrosoftAjax scripts and use only those two above otherwise the onchange event will open a new page instead of partially rendering it.

Note : If you are using Telerik MVC Controls then you only need the unobtrusive ajax the other script is already referenced by default if you are using Telerik.

Make sure also on your web.config you have the following lines.

<add key="ClientValidationEnabled" value="true"/>
<add key="UnobtrusiveJavaScriptEnabled" value="true"/>

At this point you have done everything you need and your ready to roll.

If you need the full source code you can download it here (http://www.codeproject.com/KB/Blogs/258172/CascadingDropDownMVC.zip).