Export to Excel using Dynamic Data

If you are using Dynamic Data ASP.NET web application you are likely using the Entity Framework and a Domain Service. With these key technologies it is easy to add an “Export to Excel” link to all your List pages in your project. The best part is that once you add this, you don’t have to write it again for each table. This is a write once use for everything solution.

To implement the writing of data to Excel I don’t want to use anything that requires Microsoft Excel to be installed (except by the people that are downloading the file). I chose a free solution from CarlosAg.net called Excel Xml Writer. Click here to go to the product page and download it. It is implemented using C# and is a managed solution (no COM, interops, etc). The file that is generated is a xml version of the Excel file format so it is easy to debug and tweak if needed. Best of all the installation is simple: just add a reference to the single .dll. There should be no other dependencies.

I wanted to use the same column names that are in List page that I am exporting and I didn’t want to have to hard code them. I wanted them to be read from the MyDomainService.metadata.cs. If you use the DisplayAttribute to specify user friendly names, my code uses them instead of the column names. Also, not every property in the table are shown. It depends on what the MetaTable.GetScaffoldColumns() returns which is what the DefaultAutoFieldGenerator() for the List.aspx.cs page uses.

To encapsulate this logic, I created a class called PopulateExcelWorksheetForDynamicData.cs.  To download the source for the file click the filename. I’m not going to go through all the code step by step. However, the basic idea is that the Populate() method is called. You pass it an IQueryable object and the MetaTable information. Based on that the MetaTAble.GetScaffoldColumns() is called to determine what columns should be shown. For any foreign key columns I resolve to the related (called the parent table) and display the appropriate column based on the DisplayColumnAttribute or default DisplayColumn if one is not specified via the attribute.

The datatypes that are specified in the MetaTable are converted to Excel types. Special formatting such as DateTimes require a special format in order for Excel to know how to show them as dates. I make some assumptions about the format the user will want to see. This is hardcoded, but could be extended or changed.

You can tweak the formatting of the file as well. There are two formats I use. See: SetCannedStyle1(), SetDefaultStyle(). I haven’t used SetCannedStyle1() in a while, but I think it should still work.

That is the guts of the logic. Now it is just a matter of creating a button or a link on the List.aspx.cs page and executing code similar to the following.

protected void btnExportToExcel_Click(object sender, EventArgs e)
       {
           IQueryable queryResults = null;
           using (var service = new DistiPromoDomainService())
           {
               // execute the query that was used to populate the GridView.
               var selectMethodInfo = service.GetType().GetMethod(GridDataSource.QueryName);
               queryResults = selectMethodInfo.Invoke(service, null) as IQueryable;

  // add in any filters that are applied via the QueryExtender as well
   var queryExtenderExpressions = queryExtender.Expressions;

                foreach (DataSourceExpression expr in queryExtenderExpressions)
                {
                    queryResults = expr.GetQueryable(queryResults);
                }

               var workbook = new Workbook();
               var sheet = workbook.Worksheets.Add("Sheet 1");
               var excel = new PopulateExcelWorksheetForDynamicData(sheet);
               excel.Populate(queryResults, table);
               PopulateExcelWorksheetForDynamicData.SetDefaultStyle(workbook);
               // don't use spaces as this will cause issues. You can also try .xls, but you will get an corruption warning in IE.
               string filename = Title.Replace(" ", "-") + "-Export.xml";
               PopulateExcelWorksheetForDynamicData.SendToBrowserForDownload(workbook, filename, Context.Response);
           }
       }
You may want to tweak the filename. That should do it.