WPF DataGrid 的导出到 Excel 功能
5.00/5 (15投票s)
本文介绍了从 WPF datagrid 导出 Excel 表格的功能。
引言
开发人员经常收到业务用户的需求，要求将任何集合项（listview、gridview、listbox）中的数据导出，以便在应用程序未运行时，他们可以根据自己的需要使用这些数据。为此，开发人员通常将数据导出为 PDF、Word、Excel 和 RTF 格式。DataGrid 控件是在 WPFToolkit 中引入的，可以与 VS 2008 一起使用。目前，Visual Studio 2010 本身就捆绑了它。在本文中，我们将探讨如何从 datagrid 创建 Excel 文件。我们将尝试以一种非常简单的方式来解释这个概念。开发人员可以根据需要进行更改。
将 WPF DataGrid 导出到 Microsoft Excel
步骤 1
打开一个 Visual Studio 2008 实例。确保您的机器上安装了 WPF Toolkit。如果没有，请从这个链接下载。请务必添加 WPFToolkit 的引用。
步骤 2
创建一个 WPF 屏幕，如代码片段 XAML 文件所示
<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>
步骤 3
屏幕将如下所示
步骤 4
我们将把两个不同的类绑定到两个 datagrid。第一个 datagrid 与类 Employees 绑定，第二个与 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; }
}
}
步骤 5
向类中添加一些数据，并将它们绑定到 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;
}
步骤 6
导出到 Excel 的操作可以从下面的流程图中理解
步骤 7
现在我们将探讨代码。
/// <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();
}
}
}
强制类 ExportToExcel 获取两个参数，T 和 U。T 参数应该是类，U 应该是 T 对象的列表。这是为了确保该类获得正确的输入。此外，对于 datagrid，我们通常绑定一个对象列表。GenerateReport 方法将创建/初始化 Excel 应用程序，填充 Excel 列和行。
大多数代码是不言自明的，其中 CreateHeader 和 WriteData 方法更加重要。CreateHeader 使用反射来获取我们传递的类的属性。从这些数据中，我们将在 Excel 表格中打印列标题。WriteData 方法知道有多少列（来自 CreateHeader 方法）和总数据（来自 dataToPrint.Count）。形成一个二维数组，并使用反射填充该数组。生成的二维数组被填充到 Excel 表格中。
步骤 8
当我们运行代码时，WPF 窗体将显示给用户。
当我们按下 books 和 Employee datagrid 的“导出”按钮，并使用以下代码时：
/// <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();
}
我们将获得一个包含数据的 Excel 文件，如下面 Book 的截图所示
以及下面 Employee 按钮的截图
完整的代码列表如下
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; }
}
}
}
进一步改进
我们目前正在探索以下可能性
- 如果您移动列的顺序，生成的 Excel 表格数据将与
List中的数据相同。我们希望按照在DataGrid中看到的那样打印。 - 如果您对列进行排序，生成的 Excel 表格数据将与
List中的数据相同。我们希望按照在DataGrid中看到的那样打印。 - 我们正在探索要导出的动态
Datagrid（意味着用户可以添加任意数量的行，并且应该可以导出它们）。