ASP.NET MVC: Enhancing The WebGrid - Exporting Data to Excel

March 31^st, 2015

The second post in this series focuses on how to export your data from a WebGrid to Excel. Your users will love this!

In the last post, we discussed how to perform batch processing. Today, with this second post in the series, we'll focus on your user's exporting requirements whether they want to export it as an Excel file.

How many times have you heard that after you've built your WebGrid masterpiece, some of your users exclaim, "Wow, that looks great. Can I get that into an Excel format?"

This is probably one of the most useful features you can provide to your users because I constantly get requests for exporting data to Excel.

Would you like fries with that?

For these features, you need to put yourself in the user's shoes and look at what the user sees in your WebGrid.

They see either a page of data (maybe page 2 of 20 records) or an entire list of data. A good UI design would be to give them an option as to whether they want the paged data on the screen or they want the entire list.

But first, we need to add an export button to our WebGrid. Again, we'll take a Glyphicon from our Bootstrap library of icons.

<div class="navbar navbar-default" role="search">
    <ul class="nav navbar-nav navbar-form">
        <li>
            <button id="btnRefresh" href="#" title="Refresh" class="btn btn-default btn-sm">
                <i class="glyphicon glyphicon-refresh"></i>
            </button>
        </li>
        <li>
            <button type="submit" id="btnDelete" name="btnDelete" href="#" value="1" title="Delete" class="btn btn-default btn-sm">
                <i class="glyphicon glyphicon-remove"></i>
            </button>
        </li>
        <li>
            <button type="button" id="btnExport" name="btnExport" href="#" title="Export..." class="btn btn-default btn-sm">
                <i class="glyphicon glyphicon-export"></i>
            </button>
        </li>
    </ul>
</div>

With the export button out of the way and since we are using Bootstrap in our code, let's use the Bootstrap Modal box to ask them about their paging options.

We need two items for our dialog box:

The JavaScript file for the Boostrap components
The HTML for the dialog box.

Wedge the JavaScript file between your jQuery and your custom script at the bottom of your Index.cshtml.

    <script src="~/Scripts/jquery-2.1.3.min.js"></script>
    <script src="~/Scripts/bootstrap.min.js"></script>
    <script type="text/javascript">
        $(function () {
            $("#allBox").on("click", function () {
                $("[name=select]").prop("checked", $("#allBox").is(":checked"));
            });
        });
    </script>
</body>

Now the dialog box. Since the form needs the WebGrid object, we need to place the Modal dialog box in the UserGrid.

Here is the HTML for the dialog box on the UserGrid.cshtml page.

    @using (Html.BeginForm("Export", "User", FormMethod.Post))
    {
        <div class="modal fade export-dialog">
            <div class="modal-dialog modal-sm">
                <div class="modal-content">
                    <div class="modal-header">
                        <button type="button" class="close" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">&times;</span></button>
                        <h4 class="modal-title">Export</h4>
                    </div>
                    <div class="modal-body">
                        <div class="container-fluid">
                            <div class="row">
                                @Html.Hidden("CurrentPage", grid.PageIndex)
                                @Html.Hidden("RowsPerPage", grid.RowsPerPage)
                                @Html.Hidden("PagingEnabled", grid.PageCount > 1)
                                <div class="col-md-3">Range:</div>
                                <div class="col-md-9">
                                    <div class="radio">
                                        <label>
                                            <input type="radio" name="pageOptions" id="pageCurrent" value="pageCurrent" checked>
                                            Current Page
                                        </label>
                                    </div>
                                    <div class="radio">
                                        <label>
                                            <input type="radio" name="pageOptions" id="pageAll" value="pageAll">
                                            All
                                        </label>
                                    </div>
                                </div>
                            </div>
                            <div class="row">
                                <div class="col-md-3">Output:</div>
                                <div class="col-md-9">
                                    <div class="radio">
                                        <label>
                                            <input type="radio" name="exportType" id="exportExcel" value="exportExcel" checked>
                                            Excel
                                        </label>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                    <div class="modal-footer">
                        <button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
                        <button type="submit" class="btn btn-primary">Export</button>
                    </div>
                </div>
            </div>
        </div>
    }

How do we display our dialog box when we click the Export button? We don't even need to write any JavaScript. Everything is declarative (mostly) in Bootstrap. We just add the data-toggle and data-target attributes to point to our dialog box.

<button type="button" id="btnExport" name="btnExport" href="#" title="Export..." class="btn btn-default btn-sm"
        data-toggle="modal" data-target=".export-dialog">
    <i class="glyphicon glyphicon-export"></i>
</button>

Binding, Binding, Binding

I know this may seem like an extra step, but it makes things so much easier when you send your results from a form over to your controller.

General rule I like to follow is that for every form where you post data back to the server, you need a model binder.

Here is our quick ModelBinder and ExportParameters class.

ViewModel\ExportParameters.cs

using System.Web.Mvc;
using WebGridExample.ModelBinders;
 
namespace WebGridExample.ViewModel
{
    public enum RangeOptions { Current, All }
    public enum Output { Excel }
 
    [ModelBinder(typeof(ExportViewModelBinder))]
    public class ExportParameters
    {
        public RangeOptions Range { get; set; }
        public Output OutputType { get; set; }
        public int CurrentPage { get; set; }
        public int PageSize { get; set; }
        public bool PagingEnabled { get; set; }
    }
}

ModelBinders\ExportViewModelBinder.cs

using System;
using System.Web.Mvc;
using WebGridExample.ViewModel;
 
namespace WebGridExample.ModelBinders
{
    public class ExportViewModelBinder : DefaultModelBinder
    {
        public override object BindModel(ControllerContext controllerContext,
            ModelBindingContext bindingContext)
        {
            var request = controllerContext.HttpContext.Request;
            
            // Range
            var range = request.Form.Get("pageOptions");
            var pagingOptions = RangeOptions.All;
            if (range == "pageCurrent")
            {
                pagingOptions = RangeOptions.Current;
            }
            
            // Output
            var output = request.Form.Get("exportType");
            var outputType = Output.Excel;
 
            int currentPage;
            if (!Int32.TryParse(request.Form.Get("CurrentPage"), out currentPage))
            {
                currentPage = 0;
            }
 
            int pageSize;
            if (!Int32.TryParse(request.Form.Get("RowsPerPage"), out pageSize))
            {
                pageSize = 0;
            }
 
            bool pagingEnabled;
            if (!bool.TryParse(request.Form.Get("PagingEnabled"), out pagingEnabled))
            {
                pagingEnabled = false;
            }
            
            return new ExportParameters
            {
                Range = pagingOptions,
                OutputType = outputType,
                CurrentPage = currentPage,
                PageSize = pageSize,
                PagingEnabled = pagingEnabled
            };
        }
    }
}

Now that we have our dialog box, let's concentrate on our User Controller.

Export POST Controller Method

Now we come to our post back method to send the data to our controller through the ExportParameters model populated by the ExportViewModelBinder. It grabs all of the form variables and converts them into something we can work with in our controllers.

[HttpPost]
public ActionResult Export(ExportParameters model)
{
    var records = _repository.GetAll();
    if (model.PagingEnabled)
    {
        records = records.Skip((model.CurrentPage-1) * model.PageSize)
           .Take(model.PageSize);
    }
 
    if (model.OutputType.Equals(Output.Excel))
    {
        var excelFormatter = new ExcelFormatter(records);
        return new ExcelResult(excelFormatter.CreateXmlWorksheet(), "Sample.xlsx");
    }
    
    return Redirect(Url.Content("~/"));
}

See how manageable the ExportParameters are when we can send different models into the Export controller? It's almost like we could....test it!

First, we get the user records and determine if there is paging based on the Grid details. Technically, it would be better to page at the database tier as opposed to the client side to minimize the data traffic, but since this is just a demo, I'll leave that exercise to the reader.

Next, we check to see what output type they requested. Since we only have one output type, we return an ExcelResult ActionResult.

Nice segue into our ExcelResult. :-)

Building your ActionResult

ActionResults are ASP.NET MVC's way of sending specific formatted data back to the browser...and there are a ton of ActionResults out there.

Out of the box, ASP.NET MVC comes with the following ActionResults:

ViewResult - Renders a specifed view to the response stream
PartialViewResult - Renders a specifed partial view to the response stream
EmptyResult - An empty response is returned
RedirectResult - Performs an HTTP redirection to a specifed URL
RedirectToRouteResult - Performs an HTTP redirection to a URL that is determined by the routing engine, based on given route data
JsonResult - Serializes a given ViewData object to JSON format
JavaScriptResult - Returns a piece of JavaScript code that can be executed on the client
ContentResult - Writes content to the response stream without requiring a view
FileContentResult - Returns a file to the client
FileStreamResult - Returns a file to the client, which is provided by a Stream
FilePathResult - Returns a file to the client

As you can see, there are a lot.

If you have a specific format for sending specific data back to the browser, extending ActionResults is definitely the best choice.

Excel-erate your Export

In the Export method in the controller class, you may be wondering what that ExcelFormatter class does. This was my way of abstracting out the generation of the Excel worksheet using OpenXML.

If you want to replace it with some Excel Interop calls, feel free to modify the ExcelFormatter, but make sure you return a collection of bytes to stream that out to your users.

I've also created a WebGridResources.resx that contains a string of an OpenXML Excel template where we are replacing the placeholders with our data.

<?xml version="1.0" encoding="utf-8" ?>
<?mso-application progid="Excel.Sheet" ?>
<workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
          xmlns:o="urn:schemas-microsoft-com:office:office"
          xmlns:x="urn:schemas-microsoft-com:office:excel"
          xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
          xmlns:html="http://www.w3.org/TR/REC-html40">
    $STYLEPLACEHOLDER$
    <worksheet ss:name="Sheet1">
        <table>
            $ROWSPLACEHOLDER$
        </table>
    </worksheet>
</workbook>

Here is the ExcelFormatter:

using System.Linq;
using System.Text;
using WebGridExample.Models;
 
namespace WebGridExample.Controllers
{
    public class ExcelFormatter
    {
        private readonly IQueryable<User> _records;
 
        public ExcelFormatter(IQueryable<User> records)
        {
            _records = records;
        }
 
        public byte[] CreateXmlWorksheet()
        {
            var xmlTemplate = WebGridResources.ExcelXmlTemplate;
            var styles = GetStyles();
            var header = WriteHeader();
            var xmlData = GetRecords();
 
            var excelXml = string.Format("{0}{1}", header, xmlData);
            xmlTemplate = xmlTemplate.Replace("$ROWSPLACEHOLDER$", excelXml);
            xmlTemplate = xmlTemplate.Replace("$STYLEPLACEHOLDER$", styles);
 
            return Encoding.UTF8.GetBytes(xmlTemplate);
        }
 
        private string GetStyles()
        {
            return @"<Styles><Style ss:ID='s1'><NumberFormat ss:Format='dd/mm/yyyy\ hh:mm:ss' />"+
                "</Style></Styles>";
 
        }
 
        private string GetRecords()
        {
            var sb = new StringBuilder();
            foreach (var record in _records)
            {
                sb.Append("<Row ss:AutoFitHeight='0'>");
                sb.Append("<Cell><Data ss:Type='String'>" + record.Id + "</Data></Cell>");
                sb.Append("<Cell><Data ss:Type='String'>" + record.UserName + "</Data></Cell>");
                sb.Append("<Cell><Data ss:Type='String'>" + record.FirstName + "</Data></Cell>");
                sb.Append("<Cell><Data ss:Type='String'>" + record.LastName + "</Data></Cell>");
                sb.Append("<Cell ss:StyleID='s1'><Data ss:Type='Number'>" + 
                    record.LastLogin.ToOADate() + "</Data></Cell>");
                sb.Append("</Row>"); 
            }
 
            return sb.ToString();
        }
 
        private string WriteHeader()
        {
            var header = new StringBuilder();
            header.Append("<Row ss:AutoFitHeight='0'>");
            header.Append("<Cell><Data ss:Type='String'>Id</Data></Cell>");
            header.Append("<Cell><Data ss:Type='String'>User Name</Data></Cell>");
            header.Append("<Cell><Data ss:Type='String'>First Name</Data></Cell>");
            header.Append("<Cell><Data ss:Type='String'>Last Name</Data></Cell>");
            header.Append("<Cell><Data ss:Type='String'>Last Login</Data></Cell>");
            header.Append("</Row>"); 
 
            return header.ToString();
        }
    }
}

Again, you can change just this class to however you want to create your Excel worksheet. It's not touching anything else so you should be safe.

Next, our ExcelResult class. As you can see, the ExcelResult class is very simple.

We setup our MemoryStream and filename through our constructor. When our ActionResult executes, we set the content type and add the header which is formatted for Excel and we push the stream (Excel file) created from our ExcelFormatter through to the response stream.

using System;
using System.IO;
using System.Web.Mvc;
 
namespace WebGridExample.ActionResults
{
    public class ExcelResult : ActionResult
    {
        private readonly Stream _excelStream;
        private readonly String _fileName;
 
        public ExcelResult(byte[] excel, String fileName)
        {
            _excelStream = new MemoryStream(excel);
            _fileName = fileName;
        }
 
        public override void ExecuteResult(ControllerContext context)
        {
            if (context == null)
            {
                throw new ArgumentNullException("We need a context!");
            }
 
            var response = context.HttpContext.Response;
            response.ContentType = "application/vnd.ms-excel";
            response.AddHeader("content-disposition", "attachment; filename=" + _fileName);
            
            byte[] buffer = new byte[4096];
            while (true)
            {
                var read = _excelStream.Read(buffer, 0, buffer.Length);
                if (read == 0) break;
                response.OutputStream.Write(buffer, 0, read);
            }
 
            response.End();
        }
    }
}

Conclusion

In this post, we implemented a simple Excel exporting utility through our WebGrid.

This is just one file format you can use for your users. You can add additional file formats to your exporting process through the Export dialog box...which if you were paying attention, you can see that we left some functionality out, but we'll be adding more formats later in future posts.

Post your comments below if you have any questions about any of the code above.