Exporting DataGrid To Excel In WPF

This article is going to be helpful for those who often deal with grids and exporting the data. Before we proceed, I would like to mention a few things.

1. I am using IList object to bind it to the DataGrid ItemsSource.
2. While exporting DataGrid to Excel, I am using an extension method, which converts IList object to DataTable. (Extension method reference)

As shown below, I created a collection of the Person class having four properties (for the starters).

public class Person
{
    public string FirstName {
        get;
        set;
    }
    public string LastName {
        get;
        set;
    }
    public string City {
        get;
        set;
    }
    public string Country {
        get;
        set;
    }
}

Utilizing the Person class to create the objects needs to be bound to the Grid.

IList < Person > lstPersons = new List < Person > ();
for (int i = 0; i < 5; i++) {
    lstPersons.Add(new Person {
        FirstName = "fname " + i, LastName = "lname " + i, City = "city " + i, Country = "country " + i
    });
}
dgExcel.ItemsSource = lstPersons; //dgExcel is the datagrid.

Using the extension method, given below, to IList, you can convert the list, mentioned above, to DataTable. For simplicity, I have created a separate class, having the extension method, which deals with the conversion.

public static DataTable ToDataTable < T > (this IList < T > data)
{
    PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
    DataTable dt = new DataTable();
    foreach(PropertyDescriptor prop in properties) {
        dt.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ? ? prop.PropertyType);
    }
    foreach(T item in data) {
        DataRow row = dt.NewRow();
        foreach(PropertyDescriptor pdt in properties) {
            row[pdt.Name] = pdt.GetValue(item) ? ? DBNull.Value;
        }
        dt.Rows.Add(row);
    }
    return dt;
}

Before you go further, please take a look at the various articles, which helps you to implement the Office Interop with your Application, used below.

Defining the class level variables, as I am dealing with WPF and saving Excel on the button click, I am going for the global variables.

Microsoft.Office.Interop.Excel.Application excel;
Microsoft.Office.Interop.Excel.Workbook workBook;
Microsoft.Office.Interop.Excel.Worksheet workSheet;
Microsoft.Office.Interop.Excel.Range cellRange;

Using the method, mentioned below, create an Excel Application and create the Workbook. I am making Excel Workbook invisible, so that you can do the rest of the work on the screen. If you like, you can make it visible and later save it.

(Take a look at highlighted line, don’t forget to specify the full name of the class, including the namespace, else, you will have to deal with ambiguity for DataRow Class.)

private void GenerateExcel(DataTable DtIN)
{
    try {
        excel = new Microsoft.Office.Interop.Excel.Application();
        excel.DisplayAlerts = false;
        excel.Visible = false;
        workBook = excel.Workbooks.Add(Type.Missing);
        workSheet = (Microsoft.Office.Interop.Excel.Worksheet) workBook.ActiveSheet;
        workSheet.Name = "LearningExcel";
        System.Data.DataTable tempDt = DtIN;
        dgExcel.ItemsSource = tempDt.DefaultView;
        workSheet.Cells.Font.Size = 11;
        int rowcount = 1;
        for (int i = 1; i <= tempDt.Columns.Count; i++) //taking care of Headers.
        {
            workSheet.Cells[1, i] = tempDt.Columns[i - 1].ColumnName;
        }
        foreach(System.Data.DataRow row in tempDt.Rows) //taking care of each Row
            {
                rowcount += 1;
                for (int i = 0; i < tempDt.Columns.Count; i++) //taking care of each column
                {
                    workSheet.Cells[rowcount, i + 1] = row[i].ToString();
                }
            }
        cellRange = workSheet.Range[workSheet.Cells[1, 1], workSheet.Cells[rowcount, tempDt.Columns.Count]];
        cellRange.EntireColumn.AutoFit();
    } catch (Exception) {
        throw;
    }
}

I am using the method, given below, to generate Excel sheet from the list.

GenerateExcel(lstPersons.ToDataTable());

On click event of the button, it is done to store the opened Excel file to the location, specified in the code.

Please note that, the SaveAs method takes the file path as a string and the path is not the filename only. It is the full path, including the filename. You can specify the path, as I did or it will choose the current folder and will create Excel file in the same.

private void btnLocation_Click(object sender, RoutedEventArgs e)
{
        workBook.SaveAs(System.IO.Path.Combine(@ "Drive:\Folder(s)\","
                    Excel book Name "));
                    workBook.Close(); excel.Quit();
                }

Output: Once you click on the button on top of  the grid, you should be able to see Excel file in the location, mentioned above, with the grid data.





FYI: There is plenty of scope for improvement in the code, mentioned above. Feel free to do so. If there is any better way, please leave your comments for the same.

Please leave your suggestion(s)/question(s) in the comments. I will be happy to help.