Export to CSV file centrally in Asp.Net MVC

Question

I am doing Export to csv functionality in my MVC project. Currently if i write code on each page it works properly. but i want to avoid duplication of code doing export functionality centrally.

Here is my controller code

public ActionResult Export(string filterBy)
        {
            GridState gs = new GridState();
            gs.Filter = filterBy;
            gs.Page = 1;
            gs.Size = int.MaxValue;
            IEnumerable cities = City.GetAll().AsEnumerable().AsQueryable().ToGridModel(gs).Data;
            MemoryStream output = new MemoryStream();
            StreamWriter writer = new StreamWriter(output, Encoding.UTF8);
            writer.Write("Country Name,");
            writer.Write("State Name,");
            writer.Write("City Name,");
            writer.Write("City STD Code,");
            writer.Write("Is Display");
            writer.WriteLine();
            foreach (CityViewModel city in cities)
            {
                writer.Write(city.CountryName);
                writer.Write(",");
                writer.Write("\"");
                writer.Write(city.StateName);
                writer.Write("\"");
                writer.Write(",");
                writer.Write("\"");
                writer.Write(city.City.Name);
                writer.Write("\"");
                writer.Write(",");
                writer.Write(city.City.STDCode);
                writer.Write("\"");
                writer.Write(",");
                writer.Write(city.City.IsDisplay);
                writer.WriteLine();
            }
            writer.Flush();
            output.Position = 0;

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

        }

This is my View:

@model Telerik.Web.Mvc.GridModel<QuexstERP.BusinessCore.BusinessEntities.SysAdmin.CityViewModel>
@using Telerik.Web.Mvc.UI
@{
    ViewBag.Title = "City List";
}

@(Html.Telerik().Grid(Model.Data)
        .Name("Grid")
        .DataKeys(keys => keys.Add(c => c.City.Id))
            .ToolBar(commands => commands.Insert().ButtonType(GridButtonType.Image).ImageHtmlAttributes(new { style = "margin-left:0", title = "Add" }))
                                .ToolBar(commands => commands
                .Custom()
                .HtmlAttributes(new { id = "TestFilter", onclick = "command_onClick(this)" })
                .Text("Export to csv")
                                        .Action("Export", "City", new { filterBy = "~" }))
            .DataBinding(dataBinding =>
                dataBinding.Server()
                    .Select("Select", "City", new { GridButtonType.Text })
                            .Insert("Create", "City", new { GridButtonType.Text })
                        .Update("Save", "City", new { GridButtonType.Text })
                        .Delete("Delete", "City", new { GridButtonType.Text }))
            .Columns(columns =>
            {
                columns.Command(commands =>
                {
                    commands.Custom("Edit").Action("Edit", "City").ImageHtmlAttributes(new { @class = "t-edit" }).ButtonType(GridButtonType.Image).HtmlAttributes(new { title = "Edit", @class = "RightAlign" });
                }).Width(40).Title("Edit").Visible(OperationHelper.EditOperation);

                columns.Command(commands =>
                {
                    commands.Delete().ButtonType(GridButtonType.Image).HtmlAttributes(new { title = "Delete", @class = "RightAlign" });
                }).Width(40).Title("Delete").Visible(OperationHelper.DeleteOperation);

                columns.Bound(p => p.CountryName).Width(200).Title("Country");
                columns.Bound(p => p.StateName).Width(200).Title("State");
                columns.Bound(p => p.City.Name).Width(310).Title("City");
                columns.Bound(p => p.City.STDCode).Width(200).Title("STD Code");
                columns.Bound(p => p.City.IsDisplay).Width(110).Title("IsDisplay");
            })
            .Pageable()
                .Scrollable()
            .Sortable()
                .Filterable()
                    .Resizable(command => command.Columns(true))
    )
@section HeadContent {

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

            var grid = $('#Grid').data('tGrid');

            var $cmd = $('#TestFilter');

            // Get its 'href' attribute - the URL where it would navigate to
            var href = $cmd.attr('href');

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

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

            // Update the 'page' parameter with the grids' current page
            href = href.replace(/page=([^&]*)/, 'page=' + grid.currentPage);

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

    </script>
}

I want to do Export to CSV centrally. as many of the form like state, country and country in my project have the exporting functionality. is it possible to write one generic class and pass parameter to it. and export to csv is done centrally???

Answer 1

Just for the reference. Don't bind data to the grid and then export it to excel. It will give you

the file you are trying to open is in a different format than specified by the file extension

Error when you try to open Excel.

I have done this solution for above problem

public static void ExportToExcel(IEnumerable<dynamic> data, string sheetName)
{
    XLWorkbook wb = new XLWorkbook();
    var ws = wb.Worksheets.Add(sheetName);
    ws.Cell(2, 1).InsertTable(data);
    HttpContext.Current.Response.Clear();
    HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    HttpContext.Current.Response.AddHeader("content-disposition", String.Format(@"attachment;filename={0}.xlsx",sheetName.Replace(" ","_")));

    using (MemoryStream memoryStream = new MemoryStream())
    {
        wb.SaveAs(memoryStream);
        memoryStream.WriteTo(HttpContext.Current.Response.OutputStream);
        memoryStream.Close();
    }

    HttpContext.Current.Response.End();
}

It get data dynamically. use ClosedXml so no error and the Export to Excel functionality is central in application Hope this will help someone. :)

Answer 2

We achieved something similar to this by binding datasources to a GridView within a controller method e.g.

var data = GetSomeData();
var grid = new GridView { DataSource = data };
grid.DataBind();
Response.ClearContent();
Response.AddHeader("content-disposition", "attachment; filename=MyExcel.xls");

Response.ContentType = "application/vnd.ms-excel";
var sw = new StringWriter();
var htw = new HtmlTextWriter(sw);

grid.RenderControl(htw);

Response.Write(sw.ToString());
Response.End();

This way you can just set the relevant datasource on the GridView and return a dump of the grid data in the output stream. With a bit of refactoring we were able to make this generic and it is now used by all of our UI grids for exporting to Excel/CSV.