I am trying to export my DataGrid to Excel (Office 2019/365).
My method is using the Microsoft.Office.Interop.Excel extension, but it throws an exception, and I learned it will only work for Office 2013.
So my question is, are there any extensions that will work for Office 2019 that are interchangeable with Microsoft.Office.Interop.Excel? Would be great to have as little refactoring as possible.
My method:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using Microsoft.Office.Interop.Excel;
namespace My.NameSpace {
class ExcelExport {
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;
Workbook workbook = null;
Worksheet worksheet = null;
int rowCount = 1;
int colCount = 1;
try {
application = new Microsoft.Office.Interop.Excel.Application();
workbook = application.Workbooks.Add(Type.Missing);
worksheet = (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's 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("TextProeprty");
} 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);
}
}
}
}
}