引言
在完成将DataGrid的内容導出到Excel的時候，參考了一個印度小夥的文章http://www.codeproject.com/Articles/120480/Export-to-Excel-Functionality-in-WPF-DataGrid，很清晰，結合着自己的實際項目寫了這篇文章，分享給大家。
步驟
一：建立窗體，繪制datagrid
這裡不多講，直接拖控件就行。
二：為datagrid綁定資料
這裡查詢結果傳回的是泛型，
在XAML檔案中需要綁定資料：
<DataGrid x:Name="dgtOnlineRecord" Grid.ColumnSpan="4" Grid.Column="1" HorizontalAlignment="Left" Height="432.842" Grid.Row="4" VerticalAlignment="Top" Width="703.722"
ItemsSource="{Binding}" AutoGenerateColumns="False">
<DataGrid.Columns>
<DataGridTextColumn Header="卡号" Binding="{Binding cardID}"/>
<DataGridTextColumn Header="上機時間" Binding="{Binding onLineDateTime}"/>
<DataGridTextColumn Header="下機時間" Binding="{Binding offLineDateTime}"/>
<DataGridTextColumn Header="消費金額" Binding="{Binding costCash}"/>
<DataGridTextColumn Header="上機機器" Binding="{Binding onLineComputer}"/>
</DataGrid.Columns>
</DataGrid>
背景代碼：
JFFacade.CardManagerFacade cardmanagerfacade = new JFFacade.CardManagerFacade();
//執行個體化外觀層的CardManagerFacade類
dgtOnlineRecord.ItemsSource = cardmanagerfacade.QueryStuOnlineRecord(combinedquery);
//使用QueryStuOnlineRecord（查詢學生上機記錄）方法，并将傳回值賦給datagrid的ItemSource
效果如下所示：
三：Excel檔案生成類
這裡把它封裝成了一個類，友善複用，放到了Common層
#region ExportToExcel 導出為Excel
/// <summary>
/// 導出Excel類
/// </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 對象執行個體.
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;
// 可選 參數
private object _optionalValue = Missing.Value;
/// <summary>
/// 生成報表，和其他功能
/// </summary>
/// <returns></returns>
public int GenerateReport()
{
int result = ;
try
{
if (DataToPrint != null)
{
if (DataToPrint.Count != )
{
CreateExcelRef();
FillSheet();
OpenReport();
}
}
}
catch (Exception e)
{
result = ;
//("Excel導出失敗！\n", e.Message);
}
finally
{
ReleaseObject(_sheet);
ReleaseObject(_sheets);
ReleaseObject(_book);
ReleaseObject(_books);
ReleaseObject(_excelApp);
}
return result;
}
/// <summary>
/// 展示 Excel 程式
/// </summary>
private void OpenReport()
{
_excelApp.Visible = true;
}
/// <summary>
/// 填充 Excel sheet
/// </summary>
private void FillSheet()
{
object[] header = CreateHeader();
WriteData(header);
}
/// <summary>
/// 将資料寫入 Excel sheet
/// </summary>
/// <param name="header"></param>
private void WriteData(object[] header)
{
object[,] objData = new object[DataToPrint.Count, header.Length];
for (int j = ; j < DataToPrint.Count; j++)
{
var item = DataToPrint[j];
for (int i = ; 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 + , header.Length);
}
/// <summary>
/// 根據資料拟合 列
/// </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>
/// 根據屬性名建立列标題
/// </summary>
/// <returns></returns>
private object[] CreateHeader()
{
PropertyInfo[] headerInfo = typeof(T).GetProperties();
// 為 标頭 建立 Array
// 開始從 A1 處添加
List<object> objHeaders = new List<object>();
for (int n = ; n < headerInfo.Length; n++)
{
objHeaders.Add(headerInfo[n].Name);
}
var headerToAdd = objHeaders.ToArray();
AddExcelRows("A1", , headerToAdd.Length, headerToAdd);
SetHeaderStyle();
return headerToAdd;
}
/// <summary>
/// 列标題設定為加粗字型
/// </summary>
private void SetHeaderStyle()
{
_font = _range.Font;
_font.Bold = true;
}
/// <summary>
/// 添加行
/// </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>
/// 建立 Excel 傳遞的參數執行個體
/// </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());
}
/// <summary>
/// 釋放未使用的對象
/// </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();
}
}
}
#endregion
四：導出按鈕單擊事件
/// <summary>
/// 導出為Excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnExport_Click(object sender, RoutedEventArgs e)
{
Common.ExportToExcel<JFEntity.LineInfo, List<JFEntity.LineInfo>> exporttoexcel =
new Common.ExportToExcel<JFEntity.LineInfo, List<JFEntity.LineInfo>>();
//執行個體化exporttoexcel對象
exporttoexcel.DataToPrint = (List<JFEntity.LineInfo>)dgtOnlineRecord.ItemsSource;
exporttoexcel.GenerateReport();
}
導出的Excel：
小結
可能出現的問題及解決方案
需要添加引用：
使用命名空間：
using System.Reflection;
using Excel = Microsoft.Office.Interop.Excel;