Export DataGrid To WPF

This article explains and demonstrates how to export DataGrid data to excel without using Microsoft.Office.Interpo dll.

There are various ways to export DataGrid data into excel sheet using Microsoft.Office.Interpo dll and using excel worksheet objects. If we want to export DataGrid data into excel sheet with help of Interpo dll we are doing the following steps:

  1. Taking reference of Microsoft.Office.Inerpo dll.
  2. Creating object of Excel class.
  3. Creating object of WorkSheet class.
  4. Creating object of Range class (We can insert data using cell object, but here I am explaining about range).
  5. And inserting data grid into range.
  6. Saving excel sheet and clearing object.

Here I am going to demonstrate anther way to export data grid data into excel sheet. To do this I have followed up the following 3 simple steps:

  1. Filled data grid with some data.
  2. Copied data grid data with header info into clip board.
  3. Saved these copied data into excel sheet.

Fill DataGrid

Fill your DataGrid with any collection, here I have taken student info collection data; the details about a student.

Copy DataGrid data to clipboard

Copy the data grid data to clipboard to save in excel with the help of ApplicationCommands. Here I have written the code that helps to copy data grid data: 

  1. this.dgvStudents.SelectAllCells();  
  2. this.dgvStudents.ClipboardCopyMode = DataGridClipboardCopyMode.IncludeHeader;  
  3. ApplicationCommands.Copy.Execute(nullthis.dgvStudents);  
  4. this.dgvStudents.UnselectAllCells();  

In this above code 1 I have selected all the cell and in code 2 set the ClipboardCopyMode property to IncludeHeader, that will help us to copy the datagrid data with column header. In line 3 I have executed the copy command with the help of class ApplicationcCommands. In line 4 again I
unselected all the DataGrid cells.

Save copied data from clipboard to excel sheet

The following code helps to store or save data from clipboard to excel sheet with the help of StreamWriter class.

  1. String result = (string) Clipboard.GetData(DataFormats.CommaSeparatedValue);  
  2. try  
  3. {  
  4.     StreamWriter sw = new StreamWriter("export.csv");  
  5.     sw.WriteLine(result);  
  6.     sw.Close();  
  7.     Process.Start("export.csv");  
  8. }  
  9. catch(Exception ex)  
  10. {}  
The data that is now in clipboard is retrieved in line 01 by the help of GetData of clipboard class passed to a string variable. Then in line 04 created an object of StreamWriter class with passing excel csv file name with extension to the constructor of StreamWriter class. Wrote that data into csv file with the help of WriteLine function and closed the StreamWriter. In line 07 open the excel file with the help of process class.

The following is the full code that will help you understand properly.

XAML 
  1. <Window x:Class="WPFDataGridExport.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">  
  2.     <DockPanel>  
  3.         <StackPanel Orientation="Horizontal" FlowDirection="RightToLeft" DockPanel.Dock="Bottom">  
  4.             <Button Content="Export" Click="Button_Click_1" Margin="5"></Button>  
  5.         </StackPanel>  
  6.         <DataGrid AutoGenerateColumns="False" Name="dgvStudents">  
  7.             <DataGrid.Columns>  
  8.                 <DataGridTextColumn Header="Name" Width="*" Binding="{Binding Path=Name, Mode=Default}"></DataGridTextColumn>  
  9.                 <DataGridTextColumn Header="Roll no." Width="100" Binding="{Binding Path=RollNo, Mode=Default}"></DataGridTextColumn>  
  10.                 <DataGridTextColumn Header="Age" Width="100" Binding="{Binding Path=Age, Mode=Default}"></DataGridTextColumn>  
  11.             </DataGrid.Columns>  
  12.         </DataGrid>  
  13.     </DockPanel>  
  14. </Window>  
Behind Code
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Diagnostics;  
  4. using System.IO;  
  5. using System.Linq;  
  6. using System.Text;  
  7. using System.Threading.Tasks;  
  8. using System.Windows;  
  9. using System.Windows.Controls;  
  10. using System.Windows.Data;  
  11. using System.Windows.Documents;  
  12. using System.Windows.Input;  
  13. using System.Windows.Media;  
  14. using System.Windows.Media.Imaging;  
  15. using System.Windows.Navigation;  
  16. using System.Windows.Shapes;  
  17. namespace WPFDataGridExport  
  18. {  
  19.     /// <summary>   
  20.     /// Interaction logic for MainWindow.xaml   
  21.     /// </summary>   
  22.     public partial class MainWindow: Window  
  23.     {  
  24.         public List < Student > Students  
  25.         {  
  26.             get;  
  27.             set;  
  28.         }  
  29.         public MainWindow()  
  30.         {  
  31.             InitializeComponent();  
  32.             FillDate();  
  33.         }  
  34.         private void FillDate()  
  35.         {  
  36.             this.Students = new List < Student > ();  
  37.             this.Students.Add(new Student()  
  38.             {  
  39.                 Name = "Kailash", RollNo = 1, Age = 10  
  40.             });  
  41.             this.Students.Add(new Student()  
  42.             {  
  43.                 Name = "Munna", RollNo = 2, Age = 10  
  44.             });  
  45.             this.Students.Add(new Student()  
  46.             {  
  47.                 Name = "Suraj", RollNo = 3, Age = 10  
  48.             });  
  49.             this.Students.Add(new Student()  
  50.             {  
  51.                 Name = "Kiran", RollNo = 4, Age = 10  
  52.             });  
  53.             this.Students.Add(new Student()  
  54.             {  
  55.                 Name = "Akash", RollNo = 5, Age = 10  
  56.             });  
  57.             this.Students.Add(new Student()  
  58.             {  
  59.                 Name = "Vishal", RollNo = 6, Age = 10  
  60.             });  
  61.             this.Students.Add(new Student()  
  62.             {  
  63.                 Name = "Manoj", RollNo = 7, Age = 10  
  64.             });  
  65.             this.Students.Add(new Student()  
  66.             {  
  67.                 Name = "Ajay", RollNo = 8, Age = 10  
  68.             });  
  69.             this.Students.Add(new Student()  
  70.             {  
  71.                 Name = "Rushi", RollNo = 9, Age = 10  
  72.             });  
  73.             this.Students.Add(new Student()  
  74.             {  
  75.                 Name = "Ruchit", RollNo = 10, Age = 10  
  76.             });  
  77.             this.dgvStudents.ItemsSource = this.Students;  
  78.         }  
  79.         private void Button_Click_1(object sender, RoutedEventArgs e)  
  80.         {  
  81.             this.dgvStudents.SelectAllCells();  
  82.             this.dgvStudents.ClipboardCopyMode = DataGridClipboardCopyMode.IncludeHeader;  
  83.             ApplicationCommands.Copy.Execute(nullthis.dgvStudents);  
  84.             this.dgvStudents.UnselectAllCells();  
  85.             String result = (string) Clipboard.GetData(DataFormats.CommaSeparatedValue);  
  86.             try  
  87.             {  
  88.                 StreamWriter sw = new StreamWriter("export.csv");  
  89.                 sw.WriteLine(result);  
  90.                 sw.Close();  
  91.                 Process.Start("export.csv");  
  92.             }  
  93.             catch(Exception ex)  
  94.             {}  
  95.         }  
  96.     }  
  97. }  
Hope you have learned how to export data grid data into excel sheet in a simple way without using Interop dll.