XAML DataGrid Export data to Excel using MVVM design pattern

The C# and XAML code sample describes about how to Export the DataGrid data to Excel in Silverlight and WPF using MVVM design pattern.

Create a New Silverlight or WPF project and create the MVVM Folder structure as View, ViewModel and Model.

a) Add DataGrid Control and Button Control to XAML file as below.
<Button Content="Export" Width="99" Margin="5" ></Button>
<DataGrid Name="gridExport" AutoGenerateColumns="False" />

b) Add new Class file ViewModel.cs to ViewModelFolder and add Constructor to .cs file as below
Public GridViewViewModel(){}

c) Add class file with Properties which are used as binding Properties for DataGrid.
public class Employee
{
public string EmployeeName { get; set; }

public string Desigantion { get; set; }

public string Organization { get; set; }

public string EmployeeId { get; set; }
}

d) Create Public property of ObservableCollection <Employee> and add items to this collection in ViewModel, to be used as ItemsSource for DataGrid
public ObservableCollection<Employee> Employees { get; set; }

private void LoadEmployeeData()
{
this.Employees = new ObservableCollection<Employee>();
this.Employees.Add(new Employee { EmployeeId = "00001", EmployeeName = "AAAAAAAA", Desigantion = "DDDDDD", Organization = "OOOOOO" });
this.Employees.Add(new Employee { EmployeeId = "00002", EmployeeName = "BBBBBBBB", Desigantion = "DDDDDD", Organization = "OOOOOO" });
this.Employees.Add(new Employee { EmployeeId = "00003", EmployeeName = "CCCCCCCC", Desigantion = "DDDDDD", Organization = "OOOOOO" });
this.Employees.Add(new Employee { EmployeeId = "00004", EmployeeName = "EEEEEEEE", Desigantion = "DDDDDD", Organization = "OOOOOO" });
this.Employees.Add(new Employee { EmployeeId = "00005", EmployeeName = "FFFFFFFF", Desigantion = "DDDDDD", Organization = "OOOOOO" });
this.Employees.Add(new Employee { EmployeeId = "00006", EmployeeName = "GGGGGGGG", Desigantion = "DDDDDD", Organization = "OOOOOO" });
this.Employees.Add(new Employee { EmployeeId = "00007", EmployeeName = "HHHHHHHH", Desigantion = "DDDDDD", Organization = "OOOOOO" });
this.Employees.Add(new Employee { EmployeeId = "00008", EmployeeName = "IIIIIIII", Desigantion = "DDDDDD", Organization = "OOOOOO" });
this.Employees.Add(new Employee { EmployeeId = "00009", EmployeeName = "JJJJJJJJ", Desigantion = "DDDDDD", Organization = "OOOOOO" });
this.Employees.Add(new Employee { EmployeeId = "000010", EmployeeName = "KKKKKKKK", Desigantion = "DDDDDD", Organization = "OOOOOO" });
this.Employees.Add(new Employee { EmployeeId = "000011", EmployeeName = "LLLLLLLL", Desigantion = "DDDDDD", Organization = "OOOOOO" });
this.Employees.Add(new Employee { EmployeeId = "000012", EmployeeName = "MMMMMMMM", Desigantion = "DDDDDD", Organization = "OOOOOO" });
}

e) Now open the XAML file and add the ViewModel as DataContext and bindings to DataGrid.
<Button Content="Export" Width="99" HorizontalAlignment="Right" Margin="5" Command="{Binding ExportCommand}" CommandParameter="{Binding ElementName=gridExport}" ></Button>
<DataGrid Name="gridExport" AutoGenerateColumns="False" ItemsSource="{Binding Employees}" CanUserAddRows="False" Margin="5">
<DataGrid.Columns>
<DataGridTextColumn Header="Employee ID" Binding="{Binding EmployeeId}" ></DataGridTextColumn>
<DataGridTextColumn Header="Employee Name" Binding="{Binding EmployeeName}"></DataGridTextColumn>
<DataGridTextColumn Header="Employee Designation" Binding="{Binding Desigantion}"></DataGridTextColumn>
<DataGridTextColumn Header="Employee Organization" Binding="{Binding Organization}"></DataGridTextColumn>
</DataGrid.Columns>
</DataGrid>

f) Add common Class file for Export to Excel functionality and will be reused across the application where ever Export is required.
g) Add "Microsoft.Office.Interop.Excel" dll as reference to the project for using the Excel obejct and add below code which will accpts the GridView Object and retrieve the data from DataGridColumns (DataGridBoundColumn,DataGridTemplateColumn etc)
public static void ExportDataGrid(object sender)
{
DataGrid currentGrid = sender as DataGrid;
if (currentGrid != null)
{
StringBuilder sbGridData = new StringBuilder();
List<string> listColumns = new List<string>();

List<DataGridColumn> listVisibleDataGridColumns = new List<DataGridColumn>();

List<string> listHeaders = new List<string>();

Microsoft.Office.Interop.Excel.Application application = null;

Microsoft.Office.Interop.Excel.Workbook workbook = null;

Microsoft.Office.Interop.Excel.Worksheet worksheet = null;

int rowCount = 1;

int colCount = 1;

try
{
application = new Microsoft.Office.Interop.Excel.Application();
workbook = application.Workbooks.Add(Type.Missing);
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];

if (currentGrid.HeadersVisibility == DataGridHeadersVisibility.Column || currentGrid.HeadersVisibility == DataGridHeadersVisibility.All)
{
foreach (DataGridColumn dataGridColumn in currentGrid.Columns.Where(dataGridColumn => dataGridColumn.Visibility == Visibility.Visible))
{
listVisibleDataGridColumns.Add(dataGridColumn);
if (dataGridColumn.Header != null)
{
listHeaders.Add(dataGridColumn.Header.ToString());
}
worksheet.Cells[rowCount, colCount] = dataGridColumn.Header;
colCount++;
}

// IEnumerable collection = currentGrid.ItemsSource;

foreach (object data in currentGrid.ItemsSource)
{
listColumns.Clear();

colCount = 1;

rowCount++;

foreach (DataGridColumn dataGridColumn in listVisibleDataGridColumns)
{
string strValue = string.Empty;
Binding objBinding = null;
DataGridBoundColumn dataGridBoundColumn = dataGridColumn as DataGridBoundColumn;

if (dataGridBoundColumn != null)
{
objBinding = dataGridBoundColumn.Binding as Binding;
}

DataGridTemplateColumn dataGridTemplateColumn = dataGridColumn as DataGridTemplateColumn;

if (dataGridTemplateColumn != null)
{
// This is a template column...let us see the underlying dependency object

DependencyObject dependencyObject = dataGridTemplateColumn.CellTemplate.LoadContent();

FrameworkElement frameworkElement = dependencyObject as FrameworkElement;
if (frameworkElement != null)
{
FieldInfo fieldInfo = frameworkElement.GetType().GetField("ContentProperty", BindingFlags.Public | BindingFlags.Static | BindingFlags.FlattenHierarchy);
if (fieldInfo == null)
{
if (frameworkElement is System.Windows.Controls.TextBox || frameworkElement is TextBlock || frameworkElement is ComboBox)
{
fieldInfo = frameworkElement.GetType().GetField("TextProperty");
}
else if (frameworkElement is DatePicker)
{
fieldInfo = frameworkElement.GetType().GetField("SelectedDateProperty");
}
}

if (fieldInfo != null)
{
DependencyProperty dependencyProperty = fieldInfo.GetValue(null) as DependencyProperty;
if (dependencyProperty != null)
{
BindingExpression bindingExpression = frameworkElement.GetBindingExpression(dependencyProperty);
if (bindingExpression != null)
{
objBinding = bindingExpression.ParentBinding;
}
}
}
}
}

if (objBinding != null)
{

if (!String.IsNullOrEmpty(objBinding.Path.Path))
{

PropertyInfo pi = data.GetType().GetProperty(objBinding.Path.Path);

if (pi != null)
{

object propValue = pi.GetValue(data, null);

if (propValue != null)
{
strValue = Convert.ToString(propValue);
}

else
{
strValue = string.Empty;
}
}
}

if (objBinding.Converter != null)
{
if (!String.IsNullOrEmpty(strValue))
{
strValue = objBinding.Converter.Convert(strValue, typeof(string), objBinding.ConverterParameter, objBinding.ConverterCulture).ToString();
}

else
{
strValue = objBinding.Converter.Convert(data, typeof(string), objBinding.ConverterParameter, objBinding.ConverterCulture).ToString();
}
}
}

listColumns.Add(strValue);

worksheet.Cells[rowCount, colCount] = strValue;

colCount++;
}
}
}

}

catch (System.Runtime.InteropServices.COMException)
{
}

finally
{
workbook.Close();
application.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(application);
}

}
}
h) Pass the DataGrid object onClicks of a Export button and call the Export method as below
private void Export(object gridExcel)
{
if (gridExcel != null)
{
DataGridExport.ExportDataGrid(gridExcel);
}
}
Download the code sample from below location
Source Code

By Siva Jagan Dhulipalla   Popularity  (6761 Views)