Export data to Excel from Silverlight/WPF DataGrid
Data export from DataGrid to Excel is very common task, and it can be solved with different ways, and chosen way depend on kind of app which you are design. If you are developing app for enterprise, and it will be installed on several computes, then you can to advance a claim (system requirements) with which your app will be work for client. Or customer will advance system requirements on which your app should work. In this case you can use COM for export (use infrastructure of Excel or OpenOffice). This approach will give you much more flexibility and give you possibility to use all features of Excel app. About this approach I’ll speak below. Other way – your app is for personal use, it can be installed on any home computer, in this case it is not good to ask user to install MS Office or OpenOffice just for using your app. In this way you can use foreign tools for export, or export to xml/html format which MS Office can read (this approach used by JIRA). But in this case will be more difficult to satisfy user tasks, like create document with landscape rotation and with defined fields for printing.
Integration with Excel from Silverlight 4 and .NET 4
In Silverlight 4 and .NET 4 we have dynamic objects, which give us possibility to use MS Office COM objects without referenced to MS Office dlls. So for creating excel document in .NET 4 you can write this code::
If you want to use AutomationFactory in Silverlight 4 app you need to set “Required elevated trust when running outside the browser” in project settings. You can check at code that your app have this privileges with property AutomationFactory.IsAvailable.
First, lets design new method, which will export to Excel two-dimension array:
Above I wrote how to get instance of Excel app. Now we will write some additional requirements for export:
excel.ScreenUpdating = false;
dynamic workbook = excel.workbooks;
dynamic worksheet = excel.ActiveSheet;
constint left = 1;
constint top = 1;
int height = data.GetLength(0);
int width = data.GetLength(1);
int bottom = top + height - 1;
int right = left + width - 1;
if (height == 0 || width == 0)
In this code we set that Excel will not show changes until we allow. This approach will give us little win in performance. Next we create new workbook and get active sheet of this book. And then get dimension of range where we will place our data.
Next step – export to Excel. When you export to excel with set data by cell this is slowly approach than export data to range of cells (you can try to compare speed of exporting with 1000 rows). So we will use setting data for range of cells:
With this code we set borders, set auto size for cells and mark out first row (with background color and special style for text – it will be bold): it will be header, which will show DataGrid column’s headers. If you want to set more you can use Excel macros to get how to change document view: you need to start record macro, then change interface by hand, end record macro and look at macro code.
At the end of export you need to clean resources. In .NET for solve this you can use method Marshal.ReleaseComObject(…), but Silverlight doesn’t have this method, but we can set null to variables and then invoke garbage collector collect method:
So we have method which export array to Excel, now we need to write method which will export DataGrid data to array. In WPF we can get all items with Items property, but in Silverlight this property is internal. But we can use ItemsSource property and cast it to List:
List<object> list = grid.ItemsSource.Cast<object>().ToList();
Before we realize export I want to think about features we need:
In some cases we want to export not all columns from data grid, so we need an approach to disable export some of columns.
In some cases columns don’t have header (text header), but in excel we want to see text header or header with other text than data grid have, so we need an approach to set header text for export.
It is easy to get which properties of object need to show in excel cell for columns with types inherited from DataGridBoundColumn because it has Binding with Path, with which we can get path for export value. But in case when we use DataGridTemplateColumn it is more hardly to find out which values of which property need to export. This is why we need an approach to set custom path for export (more we can use SortMemberPath).
We need to set formatting for export to Excel.
I solved all of this problems with attached properties:
/// Include current column in export report to excel
With this code we get all columns with true values of IsExported attached property (I set true as default value for this attached property above) and for which I can get export path (binding or custom setting path, or SortMemberPath is not null).
Next we will create new two-dimension array, first dimension is number of elements plus one – for header. And then set text headers into first row of array:
// Create data array (using array for data export optimization)
object[,] data = newobject[list.Count + 1, columns.Count];
// First row will be headers
for (int columnIndex = 0; columnIndex < columns.Count; columnIndex++)
Method GetPath is easy, it try to get path from set by attached property value or binding or SortMemberPath. I only support easy paths: with only properties as chain of path, I don’t support arrays or static elements in paths, and of course I mean that binding set for current row item:
Where ExportToExcel is extension method for DataGridm which invoke export to Excel method with separate thread. That's all. In Silverlight 4 code will be exactly the same. Below I’ll put anchor with samples for Silverlight 4 and WPF 4 (solution for Visual Studio 2010).
My approach very easy allows you to set how to export data from DataGrid with attached properties. If you want to use this approach I recommend you design new features: show busy indicator when data is exporting, and use OpenOffice when Excel is not installed on computer. Thanks.