Overview
Have you ever needed to export a WebGrid to Excel? In this article I will explain the steps needed to export the contents of a MVC WebGrid to Excel (CSV). The only prerequisite is that your input be IEnumerable<T>. The first step is to create your Util or Helper methods that actually do the conversion. Then we will add the HTMLHelper methods for the view. Then we will create the Model, View and Controller.All of the required components:
· Util Methods
· HTML Helper
· Model
· View
· Controller
Util code for IEnumerable to CSV
There are two methods that are the central components of this solution. They convert data from an IEnumerable object to a CSV format that can be opened with Excel. In the first method, we will create a generic extension method (ToCsv) for IEnumerable data types that will allow a developer to call the method on any IEnumerable object. The second method (ToCsvValue) is a helper method to escape certain characters.Header
To build the header row in Excel we use reflection to get a list of all of the properties of the object. It is worth pointing out here that the headers will be exactly the same as the property names in the object. If you want to get “pretty” names with spaces you will either need to have a lookup or some kind of Regular Expression to format the names. For this example though we will just use the camel case names in the object.Body
To build the body of the CSV file we loop through all of the items in the IEnumerable object. Notice that we call ToCsvValue for each item in the enumeration. This helper method deals with any null values and any quotes that could interrupt the comma separated sequence. Another thing to note here is that the screenshot shows that the grid is paged; however, ToCsv method will add all the available values to the CSV file. If you only want to show the paged values you will have to pass the current page number back to your Model and return only those values to the ToCsv method.<pre> /// <summary> /// Takes a generic IEnumerable and converts it to a CSV for using in an excel file. /// </summary> /// <typeparam name="T"></typeparam> /// <param name="items"></param> /// <returns></returns> public static string ToCsv<T>(this IEnumerable<T> items) where T : class { var csvBuilder = new StringBuilder(); var properties = typeof(T).GetProperties(); //Header row foreach (var prop in properties) { csvBuilder.Append(prop.Name.ToCsvValue() + ","); } csvBuilder.AppendLine("");//Add line break //Body foreach (T item in items) { string line = string.Join(",", properties.Select(p => p.GetValue(item, null).ToCsvValue()).ToArray()); csvBuilder.AppendLine(line); } return csvBuilder.ToString(); } /// <summary> /// Helper method for dealing with nulls and escape characters /// </summary> /// <typeparam name="T"></typeparam> /// <param name="item"></param> /// <returns></returns> public 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); } </pre>
public static MvcHtmlString ImageActionLink( this HtmlHelper helper, string imageUrl, string altText, string actionName, string controllerName, object routeValues, object linkHtmlAttributes, object imgHtmlAttributes) { var linkAttributes = AnonymousObjectToKeyValue(linkHtmlAttributes); var imgAttributes = AnonymousObjectToKeyValue(imgHtmlAttributes); var imgBuilder = new TagBuilder("img"); imgBuilder.MergeAttribute("src", imageUrl); imgBuilder.MergeAttribute("alt", altText); imgBuilder.MergeAttributes(imgAttributes, true); var urlHelper = new UrlHelper(helper.ViewContext.RequestContext, helper.RouteCollection); var linkBuilder = new TagBuilder("a"); //tack on a GUID to prevent AJAX caching var routeDictionary = new RouteValueDictionary(routeValues); routeDictionary.Add("guid", Guid.NewGuid()); linkBuilder.MergeAttribute("href", urlHelper.Action(actionName, controllerName, routeDictionary)); linkBuilder.MergeAttributes(linkAttributes, true); var text = linkBuilder.ToString(TagRenderMode.StartTag); text += imgBuilder.ToString(TagRenderMode.SelfClosing); text += linkBuilder.ToString(TagRenderMode.EndTag); return MvcHtmlString.Create(text); }With a standard naming convention and location for icons you can create an enum that maps to your different icons.
public enum Icons { Add, //Add.png Edit, //Edit.png Error, //Error.png Excel, //Excel.png Info, //Info.png }To complement the enum you can create an IconURL HTMLHelper method that you can use in any view to get an icon path. We use the .Net method GenerateContentUrl to get a path from the root of the web application. It takes two parameters: a string relative path and an HttpContext object that comes from the HtmlHelper object passed in from the view.
/// <summary> /// Returns a string relative path from root to the icon passed. /// </summary> /// <param name=”icon”></param> /// <returns></returns> public static string IconURL(this HtmlHelper helper, Icons icon) { return @UrlHelper.GenerateContentUrl( “~/Content/icons/” + icon.ToString().ToLower() +
“.png”, helper.ViewContext.HttpContext); }
Model
This is where we get our IEnumerable object from Entity Framework. In the GetFilteredItemsForExcel method we use LINQ to SQL to create a new ExcelAlert object which serves as a DTO (data transfer object). This method also call the GetFilteredAlerts method which uses LINQ to SQL and Lambda expressions to filter the list of alerts based on the two parameters we pass in. Returning an IQueryable object allows us to add additional filters before actually making the SQL call to the database.Note _entities is our Entity Framework object.
/// <summary> /// Gets unpaged, filtered alerts for display in excel format. /// </summary> /// <param name="TypeId"></param> /// <param name="SeverityId"></param> /// <returns></returns> public IEnumerable<ExcelAlert> GetFilteredItemsForExcel (int? TypeId, int? SeverityId) { var excelFormatedAlerts = from a in GetFilteredAlerts(TypeId, SeverityId) select new ExcelAlert { AlertLevel = a.AlertLevel.AlertLevelName, AlertId = a.AlertKey, BatchId = a.BatchKey, CounterpartyName = a.Counterparty.CounterpartyName, AlertType = a.AlertType.AlertTypeName, AlertDescription = a.AlertMessage, Timestamp = a.CreateDate }; return excelFormatedAlerts; } /// <summary> /// Return IQueryable of filtered Alerts /// </summary> /// <param name="TypeIdFilter"></param> /// <param name="SeverityIdFilter"></param> /// <returns></returns> public IQueryable<Alert> GetFilteredAlerts(int? TypeIdFilter, int? SeverityIdFilter) { var query = from a in _entities.Alerts.Include("Batch").Include("Counterparty") select a; if (TypeIdFilter != null) query = query.Where(a => a.AlertTypeKey == TypeIdFilter); if (SeverityIdFilter != null) query = query.Where(a => a.AlertLevelKey == SeverityIdFilter); return query.Where(a => !a.IsResolved); }
View
We use the helper methods created above to simplify our view code. Notice we pass our filter id’s to the Action. We need these to ensure that we get the same data that is currently displayed in the WebGrid. If you are not filtering your WebGrid these are not necessary. The Action is “GetExcelFile” and the Controller is “ExcelGen”. Note that the WebGrid is paged in the screenshot; however, the export to Excel will export the entire set of data – not just the current page.@Html.ImageActionLink(Html.IconURL(Icons.Excel), “Generate Excel File”, “GetExcelFile”, “ExcelGen”,
new { TypeId = Model.TypeIdFilter, SeverityId = Model.SeverityIdFilter }, null, null)
The ImageActionLink helper creates the Excel icon that is circled in the image below.
Controller
Our controller takes three parameters for filtering the larger dataset. If you are not filtering the WebGrid you do not need to accept any parameters. In this sample code I am getting a list of system alerts back from the AlertModel class. Once the alerts are processed into CSV format we return alerts.csv to the user./// <summary> Returns an excel file of items that are filtered but not sorted or paged. /// </summary> /// <param name="id"></param> /// <param name="TypeId"></param> /// <param name="SeverityId"></param> /// <returns></returns> public FileResult GetExcelFile(int? TypeId, int? SeverityId) { using (AlertModel model = new AlertModel(_isAdmin)) { var alerts = model.GetFilteredItemsForExcel(TypeId, SeverityId); string result = alerts.ToCsv(); return File(System.Text.Encoding.ASCII.GetBytes(result), "application/excel", "alerts.csv"); } }An IE8 user will see a dialog similar to the one below:
When the user clicks Open they will see something like the following in Excel.
Notice that that the CSV file is not formatted. The user will need to resize columns and add any additional formatting that they need.
