Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles
(untagged)

Export to Excel Functionality in WPF DataGrid

0.00/5 (No votes)
24 Oct 2010 2  
This article describes about the export functionality of Excel sheet from WPF datagrid.

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:

UIScreenDev.JPG

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:

/// <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; }
    }
}

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:

Flow.JPG

Step #7

Now we will explore the code.

/// <summary>
/// Class for generator of Excel file
/// </summary>
/// <typeparam name="T"></typeparam>
/// <typeparam name="U"></typeparam>
public class ExportToExcel<T, U>
    where T : class
    where U : List<T>
{
    public List<T> dataToPrint;
    // Excel object references.
    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;
    // Optional argument variable
    private object _optionalValue = Missing.Value;
    
    /// <summary>
    /// Generate report and sub functions
    /// </summary>
    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);
        }
    }
    /// <summary>
    /// Make Microsoft Excel application visible
    /// </summary>
    private void OpenReport()
    {
        _excelApp.Visible = true;
    }
    /// <summary>
    /// Populate the Excel sheet
    /// </summary>
    private void FillSheet()
    {
        object[] header = CreateHeader();
        WriteData(header);
    }
    /// <summary>
    /// Write data into the Excel sheet
    /// </summary>
    /// <param name="header"></param>
    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.

Output.JPG

When we press the 'Export' button of books and Employee datagrid with coding as below: 

/// <summary>
/// Event for generating excel sheet for books datagrid
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
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();
}
/// <summary>
/// Event for generating excel sheet for employee datagrid
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
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:

BookOutput.JPG

and for Employee button as below:

EmployeeOutput.JPG

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
{
    /// <summary>
    /// Interaction logic for Window1.xaml
    /// </summary>
    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;
        }         
        /// <summary>
        /// Event for generating excel sheet for books datagrid
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        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();
        }
        /// <summary>
        /// Event for generating excel sheet for employee datagrid
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnEmployee_Click(object sender, RoutedEventArgs e)
        {
            ExportToExcel<Employee, Employees> s = 
			new ExportToExcel<Employee, Employees>();
            s.dataToPrint = (Employees)dgEmployee.ItemsSource;
            s.GenerateReport();
        }
    }
    
    /// <summary>
    /// Class for generator of Excel file
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <typeparam name="U"></typeparam>
    public class ExportToExcel<T, U>
        where T : class
        where U : List<T>
    {
        public List<T> dataToPrint;
        // Excel object references.
        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;
        // Optional argument variable
        private object _optionalValue = Missing.Value;

        /// <summary>
        /// Generate report and sub functions
        /// </summary>
        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);
            }
        }
        /// <summary>
        /// Make MS Excel application visible
        /// </summary>
        private void OpenReport()
        {
            _excelApp.Visible = true;
        }
        /// <summary>
        /// Populate the Excel sheet
        /// </summary>
        private void FillSheet()
        {
            object[] header = CreateHeader();
            WriteData(header);
        }
        /// <summary>
        /// Write data into the Excel sheet
        /// </summary>
        /// <param name="header"></param>
        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:

  1. 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.
  2. 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.
  3. 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).

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here