WPF 導出DataGrid內容到CV文件

上一篇講到了如何將DataGrid數據導出到csv 文件，這一章將如何將DataGrid中的數據導出到Excel文件中。

導出的Excel文件如下：

實現代碼如下：

創建類 ExportToExcel.cs

using Microsoft.Office;

using System.Windows.Controls;

public class ExportToExcel

{

public bool Export_to_Excel(DataGrid dataGrid, string excelFileName)

{

System.Data.DataTable dt = new System.Data.DataTable();

for (int i = 0; i < dataGrid.Columns.Count; i++)

{

//只導出可見列

if (dataGrid.Columns[i].Visibility == System.Windows.Visibility.Visible)

{

//構建錶頭

dt.Columns.Add(dataGrid.Columns[i].Header.ToString());

}

}

for (int i = 0; i < dataGrid.Items.Count; i++)

{

int columnsIndex = 0;

System.Data.DataRow row = dt.NewRow();

for (int j = 0; j < dataGrid.Columns.Count; j++)

{

if (dataGrid.Columns[j].Visibility == System.Windows.Visibility.Visible)

{

//var r = dataGrid.Items[i];

//var c = dataGrid.Items[i] as Member;

//var v = dataGrid.Columns[j].GetCellContent(dataGrid.Items[i] as Member);

//填充可見列數據

//if ((dataGrid.Items[i] != null) && (dataGrid.Columns[j].GetCellContent(dataGrid.Items[i] as TextBlock) != null))

if (dataGrid.Items[i] != null && ( dataGrid.Items[i] as Member) !=null)

{

var M = dataGrid.Items[i] as Member;

// row[columnsIndex] = (dataGrid.Columns[j].GetCellContent(dataGrid.Items[i]) as TextBlock).Text.ToString();

if (columnsIndex == 0)

row[columnsIndex] = M.Name;

else if(columnsIndex==1)

row[columnsIndex] = M.Age;

else if (columnsIndex == 2)

row[columnsIndex] = M.Sex;

else if (columnsIndex == 3)

row[columnsIndex] = M.Pass;

else if (columnsIndex == 4)

row[columnsIndex] = M.Email;

}

else

{

row[columnsIndex] = “”;

}

columnsIndex++;

}

}

dt.Rows.Add(row);

}

if (Export_to_Table(dt, excelFileName) != null)

{

return true;

}

else

{

return false;

}

}

private string Export_to_Table(System.Data.DataTable DT, string title)

{

try

{

//創建Excel

Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();

Microsoft.Office.Interop.Excel.Workbook ExcelBook = ExcelApp.Workbooks.Add(System.Type.Missing);

//創建工作錶（即Excel裏的子錶sheet） 1錶示在子錶sheet1裏進行數據導出

Microsoft.Office.Interop.Excel.Worksheet ExcelSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelBook.Worksheets[1];

//如果數據中存在數字類型 可以讓它變文本格式顯示

ExcelSheet.Cells.NumberFormat = “@”;

//設置工作錶名

ExcelSheet.Name = title;

//設置Sheet標題

string start = “A1”;

string end = ChangeASC(DT.Columns.Count) + “1”;

Microsoft.Office.Interop.Excel.Range _Range = (Microsoft.Office.Interop.Excel.Range)ExcelSheet.get_Range(start, end);

_Range.Merge(0);                    //單元格合並動作(要配合上面的get_Range()進行設計)

_Range = (Microsoft.Office.Interop.Excel.Range)ExcelSheet.get_Range(start, end);

_Range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;

_Range.Font.Size = 22; //設置字體大小

_Range.Font.Name = “宋體”; //設置字體的種類

ExcelSheet.Cells[1, 1] = title;    //Excel單元格賦值

_Range.EntireColumn.AutoFit(); //自動調整列寬

//寫錶頭

for (int m = 1; m <= DT.Columns.Count; m++)

{

ExcelSheet.Cells[2, m] = DT.Columns[m - 1].ColumnName.ToString();

start = “A2”;

end = ChangeASC(DT.Columns.Count) + “2”;

_Range = (Microsoft.Office.Interop.Excel.Range)ExcelSheet.get_Range(start, end);

_Range.Font.Size = 15; //設置字體大小

_Range.Font.Bold = true;//加粗

_Range.Font.Name = “宋體”; //設置字體的種類

_Range.EntireColumn.AutoFit(); //自動調整列寬

_Range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;

}

//寫數據

for (int i = 0; i < DT.Rows.Count; i++)

{

for (int j = 1; j <= DT.Columns.Count; j++)

{

//Excel單元格第一個從索引1開始

// if (j == 0) j = 1;

ExcelSheet.Cells[i + 3, j] = DT.Rows[i][j - 1].ToString();

}

}

//錶格屬性設置

for (int n = 0; n < DT.Rows.Count + 1; n++)

{

start = “A” + (n + 3).ToString();

end = ChangeASC(DT.Columns.Count) + (n + 3).ToString();

//獲取Excel多個單元格區域

_Range = (Microsoft.Office.Interop.Excel.Range)ExcelSheet.get_Range(start, end);

_Range.Font.Size = 12; //設置字體大小

_Range.Font.Name = “宋體”; //設置字體的種類

_Range.EntireColumn.AutoFit(); //自動調整列寬

_Range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //設置字體在單元格內的對其方式 _Range.EntireColumn.AutoFit(); //自動調整列寬

}

ExcelApp.DisplayAlerts = false; //保存Excel的時候，不彈出是否保存的窗口直接進行保存

//彈出保存對話框,並保存文件

Microsoft.Win32.SaveFileDialog sfd = new Microsoft.Win32.SaveFileDialog();

sfd.DefaultExt = “.xlsx”;

sfd.Filter = “Office 2007 File|*.xlsx|Office 2000-2003 File|*.xls|所有文件|*.*”;

sfd.FileName = “ExportDemo”;

if (sfd.ShowDialog() == true)

{

if (sfd.FileName != “”)

{

ExcelBook.SaveAs(sfd.FileName);  //將其進行保存到指定的路徑

//    MessageBox.Show("導出文件已存儲為: " + sfd.FileName, “溫馨提示”);

}

}

//釋放可能還沒釋放的進程

ExcelBook.Close();

ExcelApp.Quit();

// PubHelper.Instance.KillAllExcel(ExcelApp);

return sfd.FileName;

}

catch (Exception)

{

//  MessageBox.Show(“導出文件保存失敗！”, “警告！”);

return null;

}

}

///

/// 獲取當前列列名,並得到EXCEL中對應的列

///

///

///

private string ChangeASC(int count)

{

string ascstr = “”;

switch (count)

{

case 1:

ascstr = “A”;

break;

case 2:

ascstr = “B”;

break;

case 3:

ascstr = “C”;

break;

case 4:

ascstr = “D”;

break;

case 5:

ascstr = “E”;

break;

case 6:

ascstr = “F”;

break;

case 7:

ascstr = “G”;

break;

case 8:

ascstr = “H”;

break;

case 9:

ascstr = “I”;

break;

case 10:

ascstr = “J”;

break;

case 11:

ascstr = “K”;

break;

case 12:

ascstr = “L”;

break;

case 13:

ascstr = “M”;

break;

case 14:

ascstr = “N”;

break;

case 15:

ascstr = “O”;

break;

case 16:

ascstr = “P”;

break;

case 17:

ascstr = “Q”;

break;

case 18:

ascstr = “R”;

break;

case 19:

ascstr = “S”;

break;

case 20:

ascstr = “Y”;

break;

default:

ascstr = “U”;

break;

}

return ascstr;

}

}

在UI的後臺代碼：

private void btnExport2_Click(object sender, RoutedEventArgs e)

{

try

{

//實例方法導出到Excel

ExportToExcel exportTool = new ExportToExcel();

exportTool.Export_to_Excel(this.dg, “MyExcel”);

}

catch (Exception ex)

{

MessageBox.Show(ex.Message);

}

}

測試驗證導出成功，樣式設置如上圖。