WPF: Exporting Data from a DataGrid Control
Introduction
The DataGrid control in WPF provides a flexible way to display, sort, group and filter tabular data. A common requirement is the ability to export this data to some physical file that can be imported into Microsoft Excel or some similar software for further processing.
This post provides an example of how you could create a .csv file out of a data-bound collection of objects of any type by using reflection and extending the functionality of the System.Windows.Controls.DataGrid control by implementing an extension method.
CSV
CSV stands for “Comma Separated Values” and is a common and widely supported file format for storing tabular data in plain text. A CSV file consists of a number of records, separated by line breaks. Each record consists of columns that are separated by some character, typically a comma (,) or a semicolon (;).
Sample code
You can download the sample code in this post from the MSDN Samples Code Gallery here.
The sample application consists of a single window with two public properties that returns collections of Product objects and related Category objects respectively, and an event handler for an export button that will call the extension method of the DataGrid that creates the actual export file:
public partial class MainWindow : Window
{
public MainWindow()
{
InitializeComponent();
this.DataContext = this;
/* initialize categories */
this.Categories = new List<Category>();
this.Categories.Add(new Category() { Id = 1, Name = "Category A" });
this.Categories.Add(new Category() { Id = 2, Name = "Category B" });
/* initialize products */
this.Products = new List<Product>();
for (int i = 0; i < 100; ++i)
{
Product item = new Product();
item.Id = i;
item.Name = string.Format("Item {0}", i);
bool b = (i % 2).Equals(0);
item.IsAvailable = b;
item.Category = b ? this.Categories[0] : this.Categories[1];
this.Products.Add(item);
}
}
public IList<Category> Categories
{
get;
private set;
}
public IList<Product> Products
{
get;
private set;
}
private void Export_Click(object sender, RoutedEventArgs e)
{
const string path = "test.csv";
IExporter csvExporter = new CsvExporter(';');
dataGrid.ExportUsingRefection(csvExporter, path);
}
}
public class Product
{
public int Id { get; set; }
public string Name { get; set; }
public bool IsAvailable { get; set; }
public Category Category { get; set; }
}
public class Category
{
public int Id { get; set; }
public string Name { get; set; }
}
The XAML markup then defines a DataGrid control that is bound to the Products collection, with a column per property of the Product type:
<Window x:Class="Mm.ExportableDataGrid.Wpf.MainWindow"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
Title="MainWindow" Height="350" Width="525">
<Window.Resources>
<CollectionViewSource x:Key="categories" Source="{Binding Categories}"/>
</Window.Resources>
<DockPanel>
<Button Content="Export data" Click="Export_Click" DockPanel.Dock="Bottom"/>
<DataGrid x:Name="dataGrid" ItemsSource="{Binding Products}" AutoGenerateColumns="False">
<DataGrid.Columns>
<DataGridTextColumn Header="Name" Binding="{Binding Name}" />
<DataGridTextColumn Header="Id" Binding="{Binding Id}" />
<DataGridCheckBoxColumn Header="Checked" Binding="{Binding IsAvailable}"/>
<DataGridComboBoxColumn Header="Category"
ItemsSource="{Binding Source={StaticResource categories}}"
SelectedValueBinding="{Binding Category}" DisplayMemberPath="Name"/>
<DataGridHyperlinkColumn ContentBinding="{Binding Name}" Binding="{Binding Name}"/>
</DataGrid.Columns>
</DataGrid>
</DockPanel>
</Window>
Note that the ItemsSource property of the DataGridComboBoxColumn cannot be bound directly to the Categories collection of the DataContext. This is because the columns in the DataGrid don’t inherit the DataContext as they are never added to the visual tree.
Extension methods
An extension method is a feature in C# that lets you “add” new methods to an existing type without creating a new derived type or otherwise modifying the original type. To implement an extension method for some type you simply create a static method in a static class. The first parameter of the method specifies the type that the method operates on and it must be preceded with the this modifier.
Below is for example how you would create an extension method for the built-in DataGrid control that takes two additional parameters. This method can then be called for any DataGrid provided that it has an access modifier that makes it visible to the client code that calls it.
public static class DataGridExtensions
{
public static void ExportUsingRefection(this DataGrid grid, IExporter exporter, string exportPath)
{
...
}
}
IExporter
Besides the string argument that specifies the path where the export file will be saved on the disk, the ExportUsingReflection extension method above also takes a parameter of type IExporter. This is a custom interface for creating the rows and columns of the file:
public interface IExporter
{
void AddColumn(string value);
void AddLineBreak();
string Export(string exportPath);
}
By letting classes that implement this interface take care of the creation of the file, the application will be able to support additional export formats without requiring any changes to the implementation of the export method itself. This is actually a sample implementation of the builder design pattern where the interface above acts as the builder, the CsvExporter class below as a concrete builder and where the export file becomes the final product.
public class CsvExporter : IExporter
{
private readonly StringBuilder sb = new StringBuilder();
private readonly string _delimiter;
public CsvExporter(char delimiter)
{
_delimiter = delimiter.ToString();
}
public char Delimiter
{
get { return _delimiter[0]; }
}
public void AddColumn(string value)
{
sb.Append(value.Replace(_delimiter,
string.Format("\"{0}\"", _delimiter)));
sb.Append(_delimiter);
}
public void AddLineBreak()
{
sb.Remove(sb.Length - 1, 1); //remove trailing delimiter
sb.AppendLine();
}
public string Export(string exportPath)
{
if (string.IsNullOrEmpty(exportPath))
{
Random rnd = new Random();
exportPath = string.Format("{0}.csv", rnd.Next());
}
else if (!Path.GetExtension(exportPath).ToLower().Equals(".csv"))
{
throw new ArgumentException("Invalid file extension.", "exportPath");
}
File.WriteAllText(exportPath, sb.ToString().Trim());
sb.Clear();
return exportPath;
}
}
If you are not familiar with design patterns, these provide solutions to common software design problems and are all about reusable design and interactions of objects.
DataGrid
Each row in a DataGrid control is bound to an object in the source collection and each column is bound to a property of an object in the same collection. By default, if you don’t set the AutoGenerateColumns property to false, the DataGrid control generates columns automatically when you set the ItemsSource property.
The DataGrid control has a Column property that contains all the columns in it. These must be of type System.Windows.Controls.DataGridColumn and there are five built-in derived types of this class; DataGridCheckBoxColumn, DataGridHyperlinkColumn and DataGridTextColumn which all derives from the DataGridBoundColumn base type that adds support for binding, DataGridComboBoxColumn and DataGridTemplateColumn. The type of column that is automatically generated when the AutoGenerateColumns is set to true depends on the type of the source property.
Any System.String (string) property of the class that represents the objects in the ItemsSource collection will generate a DataGridTextColumn, a System.Boolean (bool) property will generate a DataGridCheckBoxColumn, a System.Uri property will generate a DataGridHyperlinkColumn and an enumeration type (System.Enum) property will generate a DataGridComboBoxColumn.
The DataGridTemplateColumn is used when you want to define a custom column by specifying templates to be used when displaying and editing values.
ICollectionView
Whenever you bind to some collection of data in WPF, you are always binding to an automatically generated view and not to the actual collection itself. A view is a class that implements the System.ComponentModel.ICollectionView interface and provides functionality to sort, filter, group and keeping track of the current item in a collection. All collections have a default view which is shared by all bindings to the collection.
The default collection view for a source collection that implements the System.Collections.IList interface is the System.Windows.Data.ListCollectionView class. You can get the default view of a collection by using the static CollectionViewSource.GetDefaultView method and this comes in handy when you want to export sorted or grouped data from a DataGrid.
Order and Sorting
The user can sort columns in a DataGrid control by clicking the column header, provided that the CanUserSortColumns of the DataGrid is set to its default value of true. Also, the order of columns in the source collection doesn’t necessarily determine the order in which they appear in the DataGrid. If the CanUserReorderColumns property of the DataGrid control is set to true, which it also is by default, the user can change the order by dragging column headers with the mouse. There is a DisplayOrder property of the DataGridColumn class that determines the current position of the column in the grid, by which you can sort the columns before iterating them through in the export method:
public static void ExportUsingRefection(this DataGrid grid, IExporter exporter, string exportPath)
{
if (grid.ItemsSource == null || grid.Items.Count.Equals(0))
throw new InvalidOperationException("You cannot export any data from an empty DataGrid.");
IEnumerable<DataGridColumn> columns = grid.Columns.OrderBy(c => c.DisplayIndex);
ICollectionView collectionView = CollectionViewSource.GetDefaultView(grid.ItemsSource);
foreach (object o in collectionView)
{
if (o.Equals(CollectionView.NewItemPlaceholder))
continue;
foreach (DataGridColumn column in columns)
{
...
}
exporter.AddLineBreak();
}
/* Create and open export file */
Process.Start(exporter.Export(exportPath));
}
Reflection
To be able to retrieve and export the property values that are displayed in the columns of the DataGrid, you can use reflection. The System.Reflection namespace in C# provides functionality to dynamically create an instance of a type or get the type from an existing object and invoke its methods or access its fields and properties. The latter is exactly what you want do in the above foreach loop. The System.Reflection.PropertyInfo class provides access to a property of a type. It has a GetValue method which returns the value of the property for a specified object. To get a PropertyInfo instance for a property with a specific name, you simply call the GetProperty (string) method of the type of the object.
DataGridBoundColumn
Below is how you can retrieve and export the property values from any column that derives from the DataGridBoundColumn class. Note that you get the name of the bound source property from the Path property of the binding of the DataGridBoundColumn. Also note that the below code will export a string value of “-”, rather than an empty string, for a DataGridCheckBoxColumn that is bound to a nullable Boolean (null?) property with a value of NULL.
foreach (DataGridColumn column in columns)
{
if (column is DataGridBoundColumn)
{
string propertyValue = string.Empty;
/* Get the property name from the column's binding */
BindingBase bb = (column as DataGridBoundColumn).Binding;
if (bb != null)
{
Binding binding = bb as Binding;
if (binding != null)
{
string boundProperty = binding.Path.Path;
/* Get the property value using reflection */
PropertyInfo pi = o.GetType().GetProperty(boundProperty);
if (pi != null)
{
object value = pi.GetValue(o);
if (value != null)
propertyValue = value.ToString();
else if (column is DataGridCheckBoxColumn)
propertyValue = "-";
}
}
}
exporter.AddColumn(propertyValue);
}
...
}
DataGridComboBoxColumn
The code to export the value that is shown in a DataGridComboBoxColumn looks a bit different. The SelectedValueBinding property of a DataGridComboBoxColumn is bound to a property of an object in the DataGrid, i.e. a Product object in this case, while the DisplayMemberPath property is used to tell which property value of the currently selected item in the source collection of the ComboBox that should be displayed. It is probably this value that you want to be included in the export file. Note that in this particular example, the SelectedValueBinding property is bound to a property of type Category of the Product class. However, there is also a SelectedValuePath property of the DataGridComboBoxColumn that could be used to specify which property value of the Category object that should be set as the value for the source property of the SelectedValueBinding.
For example, if the Product class had a CategoryId property of type System.Int32 (int) rather than a property of Category type, the SelectedValueBinding property of the DataGridComboBoxColumn should be set to CategoryId and the SelectedValuePath property to Id:
<DataGridComboBoxColumn Header="Category" ItemsSource="{Binding Source={StaticResource categories}}" SelectedValueBinding="{Binding CategoryId}" SelectedValuePath="Id"/>
The sample export method takes both these scenarios into consideration. If the ItemsSource property of the DataGridComboBoxColumn is NULL, which it will be if you set the ItemsSource of the ComboBox using bindings in the ElementStyle and EditingElementStyle styles of the DataGridComboBoxColumn rather than binding the ItemsSource of the actual DataGridComboBoxColumn to a resource, the value of the property specified by the SelectedValuePath is exported. Otherwise, it will try to export the value of the property of the Category class specified by the DisplayMemberPath:
else if (column is DataGridComboBoxColumn)
{
DataGridComboBoxColumn cmbColumn = column as DataGridComboBoxColumn;
string propertyValue = string.Empty;
/* Get the property name from the column's binding */
BindingBase bb = cmbColumn.SelectedValueBinding;
if (bb != null)
{
Binding binding = bb as Binding;
if (binding != null)
{
string boundProperty = binding.Path.Path; //returns "Category" (or CategoryId)
/* Get the selected property */
PropertyInfo pi = o.GetType().GetProperty(boundProperty);
if (pi != null)
{
object boundProperyValue = pi.GetValue(o); //returns the selected Category object or CategoryId
if (boundProperyValue != null)
{
Type propertyType = boundProperyValue.GetType();
if (propertyType.IsPrimitive || propertyType.Equals(typeof(string)))
{
if (cmbColumn.ItemsSource != null)
{
/* Find the Category object in the ItemsSource of the ComboBox with
* an Id (SelectedValuePath) equal to the selected CategoryId */
IEnumerable<object> comboBoxSource = cmbColumn.ItemsSource.Cast<object>();
object obj = (from oo in comboBoxSource
let prop = oo.GetType().GetProperty(cmbColumn.SelectedValuePath)
where prop != null && prop.GetValue(oo).Equals(boundProperyValue)
select oo).FirstOrDefault();
if (obj != null)
{
/* Get the Name (DisplayMemberPath) of the Category object */
if (string.IsNullOrEmpty(cmbColumn.DisplayMemberPath))
{
propertyValue = obj.GetType().ToString();
}
else
{
PropertyInfo displayNameProperty = obj.GetType()
.GetProperty(cmbColumn.DisplayMemberPath);
if (displayNameProperty != null)
{
object displayName = displayNameProperty.GetValue(obj);
if (displayName != null)
propertyValue = displayName.ToString();
}
}
}
}
else
{
/* Export the scalar property value of the selected object
* specified by the SelectedValuePath property of the DataGridComboBoxColumn */
propertyValue = boundProperyValue.ToString();
}
}
else if (!string.IsNullOrEmpty(cmbColumn.DisplayMemberPath))
{
/* Get the Name (DisplayMemberPath) property of the selected Category object */
PropertyInfo pi2 = boundProperyValue.GetType()
.GetProperty(cmbColumn.DisplayMemberPath);
if (pi2 != null)
{
object displayName = pi2.GetValue(boundProperyValue);
if (displayName != null)
propertyValue = displayName.ToString();
}
}
else
{
propertyValue = o.GetType().ToString();
}
}
}
}
}
exporter.AddColumn(propertyValue);
}
As seen in the above code you can determine whether the type of the property specified by the SelectedValueBinding property is one of the primitive types by using the IsPrimitive property of the System.Type class. The primitive types in C# are Boolean, Byte, SByte, Int16, UInt16, Int32, UInt32, Int64, UInt64, IntPtr, UIntPtr, Char, Double, and Single.
DataGridTemplateColumn
When it comes to exporting data from a DataGridTemplateColumn or any other DataGridColumn with a custom CellTemplate, you can’t expect the export method to be able to figure out by itself what to write to the export file as the template may contain any FrameworkElement:
<DataGridTemplateColumn Header="Custom template">
<DataGridTemplateColumn.CellTemplate>
<DataTemplate>
<!-- Any FrameworkElement objects can be added here -->
<Rectangle Fill="Red"/>
</DataTemplate>
</DataGridTemplateColumn.CellTemplate>
</DataGridTemplateColumn>
Attached properties
You could choose to simply ignore these types of columns in the export or let the XAML developer specify which value to export by setting a custom attached property of the DataGridColumn. An attached property in WPF is a special type of dependency property that allows you to define the property in one class and still be able to set and store the value of it in an instance of a completely different class. It is intended to be used as a type of global property that is settable on any object of the same type that you specify when you create the dependency property using the System.Windows.DependencyProperty.RegisterAttached method:
public class ExportBehaviour
{
public static readonly DependencyProperty ExportStringProperty =
DependencyProperty.RegisterAttached("ExportString", //name of attached property
typeof(string), //type of attached property
typeof(ExportBehaviour), //type of this owner class
new PropertyMetadata(string.Empty)); //the default value of the attached property
public static string GetExportString(DataGridColumn column)
{
return (string)column.GetValue(ExportStringProperty);
}
public static void SetExportString(DataGridColumn column, string value)
{
column.SetValue(ExportStringProperty, value);
}
}
Setting the value of the custom attached property in XAML is straight forward as shown below. Just remember to import the namespace where the custom class lives.
<DataGrid x:Name="dataGrid" ItemsSource="{Binding Products}" AutoGenerateColumns="False"
xmlns:local="clr-namespace:Mm.ExportableDataGrid">
<DataGrid.Columns>
...
<DataGridTemplateColumn Header="Custom template" local:ExportBehaviour.ExportString="string...">
...
</DataGridTemplateColumn>
</DataGrid.Columns>
</DataGrid>
In the extension method you then probably want to check if the column has the attached property set before trying to export the content of it based on its type, assuming that you want the value of the attached property to take precedence over any other binding value:
Dispatcher
You might also want to perform the execution of the export method on a background thread to be able to maintain a responsive user interface. This will require you to use the System.Windows.Threading.Dispatcher class when accessing some of the properties as trying to access any user interface (UI) object on another thread than on which it was created will cause a runtime exception to be thrown:
public static class DataGridExtensions
{
public static void ExportUsingRefection(this DataGrid grid, IExporter exporter, string exportPath)
{
/* Execute the private DoExportUsingRefection method on a background thread by starting a new task */
Task.Factory.StartNew(() => { DoExportUsingRefection(grid, exporter, exportPath); });
}
private static void DoExportUsingRefection(this DataGrid grid, IExporter exporter, string exportPath)
{
...
bool checkAccess = grid.Dispatcher.CheckAccess();
ICollectionView collectionView = null;
IList<DataGridColumn> columns = null;
if (checkAccess)
{
columns = grid.Columns.OrderBy(c => c.DisplayIndex).ToList();
collectionView = CollectionViewSource.GetDefaultView(grid.ItemsSource);
}
else
{
grid.Dispatcher.Invoke(() => { columns = grid.Columns.OrderBy(c => c.DisplayIndex).ToList(); });
grid.Dispatcher.Invoke(() => { collectionView = CollectionViewSource.GetDefaultView(grid.ItemsSource); });
}
foreach (object o in collectionView)
{
...
foreach (DataGridColumn column in columns)
{
string exportString = string.Empty;
if (checkAccess)
exportString = ExportBehaviour.GetExportString(column);
else
grid.Dispatcher.Invoke(() => { exportString = ExportBehaviour.GetExportString(column); });
if (!string.IsNullOrEmpty(exportString))
{
...
}
else if (column is DataGridBoundColumn)
{
...
}
else if (column is DataGridComboBoxColumn)
{
...
string displayMemberPath = string.Empty;
if (checkAccess)
displayMemberPath = cmbColumn.DisplayMemberPath;
else
grid.Dispatcher.Invoke(() => { displayMemberPath = cmbColumn.DisplayMemberPath; });
...
}
}
...
}
...
}
}
Just like most other classes in WPF, the DataGrid derives, through other classes, from the System.Windows.Threading.DispatcherObject class. This class has a Dispatcher property that gets the Dispatcher that the object is tied to and it also provides two methods to check and verify that the current thread has access to the control. The difference between the CheckAccess and VerifyAccess methods is that the former returns a Boolean value to indicate whether the current thread can access the object while the latter throws an exception if the executing thread doesn’t have access to the object.
The Dispatcher class is used to send worker items to the UI thread (or dispatcher thread) for processing. Since the UI thread will be blocked during the time it takes for these actions to be executed, you should always try to keep the work that the Dispatcher does small and quick in order for your application to be able to quickly respond to UI events such as mouse clicks and key presses at any time.
ContextMenu
If you want to provide the users of your application a convenient way to export the data through the extension method, you could implement a custom control that derives from the DataGrid control and adds an option to its context menu. If the custom control is part of some control library that may be used in a lot of different projects and application modules, you should keep in mind that the ItemsSource property of the ContextMenu of the DataGrid in some cases may be bound to some source collection. If it is not, you can simply add a System.Windows.Controls.MenuItem object to the Items collection of the System.Windows.Controls.ContextMenu. Otherwise, you can use a System.Windows.Data.CompositeCollection. A CompositeCollection can contain both items of different types as well as other collections.
Below is an example of a custom DataGrid control that adds an export MenuItem object to its context menu. If the context menu has been bound to some collection by a client side developer, this collection is set as the value for the Collection property of a System.Windows.Data.CollectionContainer which is then added to a CompositeCollection. A System.Windows.Controls.Separator and a MenuItem is also added to the CompositeCollection through another CollectionContainer before the CompositeCollection object is set as the new ItemsSource of the ContextMenu. Note that the command associated with the added MenuItem object calls the extension method.
public class ExportableDataGrid : DataGrid
{
private readonly IExporter _exporter = new CsvExporter(';');
private readonly ICommand _exportCommand = new RoutedCommand();
public ExportableDataGrid()
: base()
{
this.CommandBindings.Add(new CommandBinding(_exportCommand, ExecutedExportCommand,
CanExecuteExportCommand));
this.Loaded += ExportableDataGrid_Loaded;
}
private void ExecutedExportCommand(object sender, ExecutedRoutedEventArgs e)
{
this.ExportUsingRefection(_exporter, string.Empty);
}
private void CanExecuteExportCommand(object sender, CanExecuteRoutedEventArgs e)
{
e.CanExecute = this.Items.Count > 0;
}
private void ExportableDataGrid_Loaded(object sender, RoutedEventArgs e)
{
if (this.ContextMenu == null)
this.ContextMenu = new ContextMenu();
this.ContextMenu.Loaded += ContextMenu_Loaded;
}
private void ContextMenu_Loaded(object sender, RoutedEventArgs e)
{
MenuItem mi = new MenuItem();
mi.Header = "Export to CSV";
mi.Command = _exportCommand;
if (this.ContextMenu.ItemsSource != null)
{
CompositeCollection cc = new CompositeCollection();
CollectionContainer boundCollection = new CollectionContainer();
boundCollection.Collection = this.ContextMenu.ItemsSource;
cc.Add(boundCollection);
CollectionContainer exportCollection = new CollectionContainer();
List<Control> exportMenuItems = new List<Control>(2);
exportMenuItems.Add(new Separator());
exportMenuItems.Add(mi);
exportCollection.Collection = exportMenuItems;
cc.Add(exportCollection);
this.ContextMenu.ItemsSource = cc;
}
else
{
if (this.ContextMenu.HasItems)
this.ContextMenu.Items.Add(new Separator());
this.ContextMenu.Items.Add(mi);
}
this.ContextMenu.Loaded -= ContextMenu_Loaded;
}
}
<Window x:Class="Mm.ExportableDataGrid.Wpf.MainWindow"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
xmlns:local="clr-namespace:Mm.ExportableDataGrid"
Title="MainWindow" Height="350" Width="525">
<DockPanel>
<Button Content="Export data" Click="Export_Click" DockPanel.Dock="Bottom"/>
<local:ExportableDataGrid x:Name="dataGrid" ItemsSource="{Binding Products}"
AutoGenerateColumns="False">
<DataGrid.Columns>
...
</DataGrid.Columns>
</local:ExportableDataGrid>
</DockPanel>
</Window>
http://magnusmontin.files.wordpress.com/2013/09/exportwindow.png
