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

By | May 2, 2012

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 == nullreturn """";

            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);
        }
    }


Leave a Reply