Introduction
Often developers get requirements from business users to make the data in any collection items (listview, gridview, listbox) exportable, helping them to use it according to their need - when the application is not running. For this purpose, developers normally export the data to PDF, Word, Excel and RTF formats. DataGrid control was introduced in WPFToolkit which can be used along with VS 2008. Currently, Visual Studio 2010 has it with in the bundle itself. In this article, we will explore how to create an Excel file from a datagrid. We will try to explain the concept in a very simple manner. According to the need, developers can change them.
Exporting the WPF DataGrid to Microsoft Excel
Step #1
Open a Visual Studio 2008 instance. Ensure WPF Toolkit is installed on your machine. If not, kindly download form this link. Add the reference of WPFToolkit without fail.
Step #2
Create a WPF screen as shown in the code snippet XAML File:
<Window x:Class="ExportToExcel.Window1"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
xmlns:toolkit="clr-namespace:Microsoft.Windows.Controls;assembly=WPFToolkit"
xmlns:local="clr-namespace:ExportToExcel"
Title="DataGrid Excel Export" Height="400" Width="250">
<Grid>
<Grid.RowDefinitions>
<RowDefinition Height="30"/>
<RowDefinition Height="150"/>
<RowDefinition Height="30"/>
<RowDefinition Height="150"/>
</Grid.RowDefinitions>
<Button Grid.Row="0" HorizontalAlignment="Right" Width="50"
Height="25" Content="Export" Click="btnEmployee_Click"
Name="btnEmployee"/>
<toolkit:DataGrid Grid.Row="1" Name="dgEmployee"
SelectionMode="Single" AutoGenerateColumns="False" IsReadOnly="True"
Margin="5,5,5,5">
<toolkit:DataGrid.Columns>
<toolkit:DataGridTextColumn Binding="{Binding Path=Id}"
Header="Id" Width="45"/>
<toolkit:DataGridTextColumn Binding="{Binding Path=Name}"
Header="Name" Width="100"/>
<toolkit:DataGridTextColumn Binding="{Binding Path=Designation}"
Header="Designation" Width="*"/>
</toolkit:DataGrid.Columns>
</toolkit:DataGrid>
<Button Grid.Row="2" HorizontalAlignment="Right" Width="50"
Height="25" Content="Export" Click="btnBook_Click"
Name="btnBook"/>
<toolkit:DataGrid Grid.Row="3" Name="dgBook"
SelectionMode="Single" AutoGenerateColumns="False" IsReadOnly="True"
Margin="5,5,5,5">
<toolkit:DataGrid.Columns>
<toolkit:DataGridTextColumn Binding="{Binding Path=ISBN}"
Header="ISBN" Width="45"/>
<toolkit:DataGridTextColumn Binding="{Binding Path=Title}"
Header="Title" Width="100"/>
<toolkit:DataGridTextColumn Binding="{Binding Path=Author}"
Header="Author" Width="*"/>
</toolkit:DataGrid.Columns>
</toolkit:DataGrid>
</Grid>
</Window>
Step #3
The screen will look like this:
Step #4
We are going to bind two different classes to two datagrids. The first datagrid is bound with class Employees and second one with Books:
public class Employees : List<employee> { }
public class Employee
{
private int id;
public int Id
{
get { return id; }
set { id = value; }
}
private string name;
public string Name
{
get { return name; }
set { name = value; }
}
private string designation;
public string Designation
{
get { return designation; }
set { designation = value; }
}
}
public class Books : List<book> { }
public class Book
{
private int iSBN;
public int ISBN
{
get { return iSBN; }
set { iSBN = value; }
}
private string title;
public string Title
{
get { return title; }
set { title = value; }
}
private string author;
public string Author
{
get { return author; }
set { author = value; }
}
}
Step #5
Add a little data to the classes and bind them to the datagrid.
public Window1()
{
InitializeComponent();
Employees emps = new Employees();
Employee e1 = new Employee();
e1.Id = 52590;
e1.Name = "Sathish";
e1.Designation = "Developer";
emps.Add(e1);
Employee e2 = new Employee();
e2.Id = 52592;
e2.Name = "Karthick";
e2.Designation = "Developer";
emps.Add(e2);
Employee e3 = new Employee();
e3.Id = 52593;
e3.Name = "Raja";
e3.Designation = "Manager";
emps.Add(e3);
Employee e4 = new Employee();
e4.Id = 12778;
e4.Name = "Sumesh";
e4.Designation = "Project Lead";
emps.Add(e4);
Employee e5 = new Employee();
e5.Id = 12590;
e5.Name = "Srini";
e5.Designation = "Project Lead";
emps.Add(e5);
dgEmployee.ItemsSource = emps;
Books books = new Books();
Book b1 = new Book();
b1.ISBN = 582912;
b1.Title = "C#";
b1.Author = "James";
books.Add(b1);
Book b2 = new Book();
b2.ISBN = 174290;
b2.Title = "WPF";
b2.Author = "Smith";
books.Add(b2);
Book b3 = new Book();
b3.ISBN = 095177;
b3.Title = ".NET";
b3.Author = "Robert";
books.Add(b3);
Book b4 = new Book();
b4.ISBN = 112275;
b4.Title = "Java";
b4.Author = "Steve";
books.Add(b4);
Book b5 = new Book();
b5.ISBN = 998721;
b5.Title = "COBOL";
b5.Author = "John";
books.Add(b5);
dgBook.ItemsSource = books;
}
Step #6
The operation of exporting to Excel can be understood from the below flow diagram:
Step #7
Now we will explore the code.
public class ExportToExcel<T, U>
where T : class
where U : List<T>
{
public List<T> dataToPrint;
private Excel.Application _excelApp = null;
private Excel.Workbooks _books = null;
private Excel._Workbook _book = null;
private Excel.Sheets _sheets = null;
private Excel._Worksheet _sheet = null;
private Excel.Range _range = null;
private Excel.Font _font = null;
private object _optionalValue = Missing.Value;
public void GenerateReport()
{
try
{
if (dataToPrint != null)
{
if (dataToPrint.Count != 0)
{
Mouse.SetCursor(Cursors.Wait);
CreateExcelRef();
FillSheet();
OpenReport();
Mouse.SetCursor(Cursors.Arrow);
}
}
}
catch (Exception e)
{
MessageBox.Show("Error while generating Excel report");
}
finally
{
ReleaseObject(_sheet);
ReleaseObject(_sheets);
ReleaseObject(_book);
ReleaseObject(_books);
ReleaseObject(_excelApp);
}
}
private void OpenReport()
{
_excelApp.Visible = true;
}
private void FillSheet()
{
object[] header = CreateHeader();
WriteData(header);
}
private void WriteData(object[] header)
{
object[,] objData = new object[dataToPrint.Count, header.Length];
for (int j = 0; j < dataToPrint.Count; j++)
{
var item = dataToPrint[j];
for (int i = 0; i < header.Length; i++)
{
var y = typeof(T).InvokeMember
(header[i].ToString(), BindingFlags.GetProperty, null, item, null);
objData[j, i] = (y == null) ? "" : y.ToString();
}
}
AddExcelRows("A2", dataToPrint.Count, header.Length, objData);
AutoFitColumns("A1", dataToPrint.Count + 1, header.Length);
}
/// <summary>
/// Method to make columns auto fit according to data
/// </summary>
/// <param name="startRange"></param>
/// <param name="rowCount"></param>
/// <param name="colCount"></param>
private void AutoFitColumns(string startRange, int rowCount, int colCount)
{
_range = _sheet.get_Range(startRange, _optionalValue);
_range = _range.get_Resize(rowCount, colCount);
_range.Columns.AutoFit();
}
/// <summary>
/// Create header from the properties
/// </summary>
/// <returns></returns>
private object[] CreateHeader()
{
PropertyInfo[] headerInfo = typeof(T).GetProperties();
// Create an array for the headers and add it to the
// worksheet starting at cell A1.
List<object> objHeaders = new List<object>();
for (int n = 0; n < headerInfo.Length; n++)
{
objHeaders.Add(headerInfo[n].Name);
}
var headerToAdd = objHeaders.ToArray();
AddExcelRows("A1", 1, headerToAdd.Length, headerToAdd);
SetHeaderStyle();
return headerToAdd;
}
/// <summary>
/// Set Header style as bold
/// </summary>
private void SetHeaderStyle()
{
_font = _range.Font;
_font.Bold = true;
}
/// <summary>
/// Method to add an excel rows
/// </summary>
/// <param name="startRange"></param>
/// <param name="rowCount"></param>
/// <param name="colCount"></param>
/// <param name="values"></param>
private void AddExcelRows
(string startRange, int rowCount, int colCount, object values)
{
_range = _sheet.get_Range(startRange, _optionalValue);
_range = _range.get_Resize(rowCount, colCount);
_range.set_Value(_optionalValue, values);
}
/// <summary>
/// Create Excel application parameters instances
/// </summary>
private void CreateExcelRef()
{
_excelApp = new Excel.Application();
_books = (Excel.Workbooks)_excelApp.Workbooks;
_book = (Excel._Workbook)(_books.Add(_optionalValue));
_sheets = (Excel.Sheets)_book.Worksheets;
_sheet = (Excel._Worksheet)(_sheets.get_Item(1));
}
/// <summary>
/// Release unused COM objects
/// </summary>
/// <param name="obj"></param>
private void ReleaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
MessageBox.Show(ex.Message.ToString());
}
finally
{
GC.Collect();
}
}
}
The class ExportToExcel is enforced to get two parameters, T and U. T parameter should be class and U should be List of T objects. This is to ensure that the class gets proper inputs. Also, to a datagrid we usually bind a List of objects. The GenerateReport method will create/initialize the Excel application, populate Excel columns and rows.
Most of the code is self explanatory, with two methods CreateHeader and WriteData methods having more emphasis. The CreateHeader uses reflection to get the properties of the class that we have passed. From this data, we will print the column header in the Excel sheet. The WriteData method knows how many columns are there (from CreateHeader method) and total data (from dataToPrint.Count). A two-dimensional array is formed and it is populated using reflection. The resulting two-dimensional array is populated in the Excel sheet.
Step #8
When we run the code, the WPF form will be shown to the user.
When we press the 'Export' button of books and Employee datagrid with coding as below:
private void btnBook_Click(object sender, RoutedEventArgs e)
{
ExportToExcel<Book, Books> s = new ExportToExcel<Book, Books>();
ICollectionView view = CollectionViewSource.GetDefaultView(dgBook.ItemsSource);
s.dataToPrint = (Books)view.SourceCollection;
s.GenerateReport();
}
private void btnEmployee_Click(object sender, RoutedEventArgs e)
{
ExportToExcel<Employee, Employees> s = new ExportToExcel<Employee, Employees>();
s.dataToPrint = (Employees)dgEmployee.ItemsSource;
s.GenerateReport();
}
We will get an Excel file with data as shown for Book:
and for Employee button as below:
The complete listing of code is as below:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using tool = Microsoft.Windows.Controls;
using System.ComponentModel;
namespace ExportToExcel
{
public partial class Window1 : Window
{
public Window1()
{
InitializeComponent();
Employees emps = new Employees();
Employee e1 = new Employee();
e1.Id = 52590;
e1.Name = "Sathish";
e1.Designation = "Developer";
emps.Add(e1);
Employee e2 = new Employee();
e2.Id = 52592;
e2.Name = "Karthick";
e2.Designation = "Developer";
emps.Add(e2);
Employee e3 = new Employee();
e3.Id = 52593;
e3.Name = "Raja";
e3.Designation = "Manager";
emps.Add(e3);
Employee e4 = new Employee();
e4.Id = 12778;
e4.Name = "Sumesh";
e4.Designation = "Project Lead";
emps.Add(e4);
Employee e5 = new Employee();
e5.Id = 12590;
e5.Name = "Srini";
e5.Designation = "Project Lead";
emps.Add(e5);
dgEmployee.ItemsSource = emps;
Books books = new Books();
Book b1 = new Book();
b1.ISBN = 582912;
b1.Title = "C#";
b1.Author = "James";
books.Add(b1);
Book b2 = new Book();
b2.ISBN = 174290;
b2.Title = "WPF";
b2.Author = "Smith";
books.Add(b2);
Book b3 = new Book();
b3.ISBN = 095177;
b3.Title = ".NET";
b3.Author = "Robert";
books.Add(b3);
Book b4 = new Book();
b4.ISBN = 112275;
b4.Title = "Java";
b4.Author = "Steve";
books.Add(b4);
Book b5 = new Book();
b5.ISBN = 998721;
b5.Title = "COBOL";
b5.Author = "John";
books.Add(b5);
dgBook.ItemsSource = books;
}
private void btnBook_Click(object sender, RoutedEventArgs e)
{
ExportToExcel<Book, Books> s = new ExportToExcel<Book, Books>();
ICollectionView view =
CollectionViewSource.GetDefaultView(dgBook.ItemsSource);
s.dataToPrint = (Books)view.SourceCollection;
s.GenerateReport();
}
private void btnEmployee_Click(object sender, RoutedEventArgs e)
{
ExportToExcel<Employee, Employees> s =
new ExportToExcel<Employee, Employees>();
s.dataToPrint = (Employees)dgEmployee.ItemsSource;
s.GenerateReport();
}
}
public class ExportToExcel<T, U>
where T : class
where U : List<T>
{
public List<T> dataToPrint;
private Excel.Application _excelApp = null;
private Excel.Workbooks _books = null;
private Excel._Workbook _book = null;
private Excel.Sheets _sheets = null;
private Excel._Worksheet _sheet = null;
private Excel.Range _range = null;
private Excel.Font _font = null;
private object _optionalValue = Missing.Value;
public void GenerateReport()
{
try
{
if (dataToPrint != null)
{
if (dataToPrint.Count != 0)
{
Mouse.SetCursor(Cursors.Wait);
CreateExcelRef();
FillSheet();
OpenReport();
Mouse.SetCursor(Cursors.Arrow);
}
}
}
catch (Exception e)
{
MessageBox.Show("Error while generating Excel report");
}
finally
{
ReleaseObject(_sheet);
ReleaseObject(_sheets);
ReleaseObject(_book);
ReleaseObject(_books);
ReleaseObject(_excelApp);
}
}
private void OpenReport()
{
_excelApp.Visible = true;
}
private void FillSheet()
{
object[] header = CreateHeader();
WriteData(header);
}
private void WriteData(object[] header)
{
object[,] objData = new object[dataToPrint.Count, header.Length];
for (int j = 0; j < dataToPrint.Count; j++)
{
var item = dataToPrint[j];
for (int i = 0; i < header.Length; i++)
{
var y = typeof(T).InvokeMember(header[i].ToString(),
BindingFlags.GetProperty, null, item, null);
objData[j, i] = (y == null) ? "" : y.ToString();
}
}
AddExcelRows("A2", dataToPrint.Count, header.Length, objData);
AutoFitColumns("A1", dataToPrint.Count + 1, header.Length);
}
/// <summary>
/// Method to make columns auto fit according to data
/// </summary>
/// <param name="startRange"></param>
/// <param name="rowCount"></param>
/// <param name="colCount"></param>
private void AutoFitColumns(string startRange, int rowCount, int colCount)
{
_range = _sheet.get_Range(startRange, _optionalValue);
_range = _range.get_Resize(rowCount, colCount);
_range.Columns.AutoFit();
}
/// <summary>
/// Create header from the properties
/// </summary>
/// <returns></returns>
private object[] CreateHeader()
{
PropertyInfo[] headerInfo = typeof(T).GetProperties();
// Create an array for the headers and add it to the
// worksheet starting at cell A1.
List<object> objHeaders = new List<object>();
for (int n = 0; n < headerInfo.Length; n++)
{
objHeaders.Add(headerInfo[n].Name);
}
var headerToAdd = objHeaders.ToArray();
AddExcelRows("A1", 1, headerToAdd.Length, headerToAdd);
SetHeaderStyle();
return headerToAdd;
}
/// <summary>
/// Set Header style as bold
/// </summary>
private void SetHeaderStyle()
{
_font = _range.Font;
_font.Bold = true;
}
/// <summary>
/// Method to add an excel rows
/// </summary>
/// <param name="startRange"></param>
/// <param name="rowCount"></param>
/// <param name="colCount"></param>
/// <param name="values"></param>
private void AddExcelRows(string startRange, int rowCount,
int colCount, object values)
{
_range = _sheet.get_Range(startRange, _optionalValue);
_range = _range.get_Resize(rowCount, colCount);
_range.set_Value(_optionalValue, values);
}
/// <summary>
/// Create Excel application parameters instances
/// </summary>
private void CreateExcelRef()
{
_excelApp = new Excel.Application();
_books = (Excel.Workbooks)_excelApp.Workbooks;
_book = (Excel._Workbook)(_books.Add(_optionalValue));
_sheets = (Excel.Sheets)_book.Worksheets;
_sheet = (Excel._Worksheet)(_sheets.get_Item(1));
}
/// <summary>
/// Release unused COM objects
/// </summary>
/// <param name="obj"></param>
private void ReleaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
MessageBox.Show(ex.Message.ToString());
}
finally
{
GC.Collect();
}
}
}
/// <summary>
/// List of Employee Class
/// </summary>
public class Employees : List<Employee> { }
/// <summary>
/// Employee Class
/// </summary>
public class Employee
{
private int id;
public int Id
{
get { return id; }
set { id = value; }
}
private string name;
public string Name
{
get { return name; }
set { name = value; }
}
private string designation;
public string Designation
{
get { return designation; }
set { designation = value; }
}
}
/// <summary>
/// List of Book class
/// </summary>
public class Books : List<Book> { }
/// <summary>
/// Book Class
/// </summary>
public class Book
{
private int iSBN;
public int ISBN
{
get { return iSBN; }
set { iSBN = value; }
}
private string title;
public string Title
{
get { return title; }
set { title = value; }
}
private string author;
public string Author
{
get { return author; }
set { author = value; }
}
}
}
Further Improvements
We are currently exploring the below possibilities:
- If you move the column order, the Excel sheet data produced will be the same as the data in the
List. We want to print as seen in the DataGrid.
- If you sort the column, the Excel sheet data produced will be same as the data in the
List. We want to print as seen in the DataGrid.
- We are exploring dynamic
Datagrid to be exported (means user can add any number of rows, and it should be possible for them to export).
