WPF-将DataGrid控件中的数据导出到Excel

导出至Excel是非常常见，我们可以用很多类库，例如Aspose、NOPI、Interop，在这里我们使用微软自家的工具。我的WPF绑定的ObservableCollection<T>集合。

public string ExcelExport(System.Data.DataTable DT, string title){try{//创建ExcelMicrosoft.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 = 14; //设置字体大小_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 File|*.xlsx|Office 2000- File|*.xls|所有文件|*.*";if (sfd.ShowDialog() == true){if (sfd.FileName != ""){ExcelBook.SaveAs(sfd.FileName); //将其进行保存到指定的路径System.Windows.MessageBox.Show("导出文件已存储为: " + sfd.FileName, "温馨提示");}}//释放可能还没释放的进程ExcelBook.Close();ExcelApp.Quit();return sfd.FileName;}catch{//System.Windows.MessageBox.Show("导出文件保存失败,可能原因该文件已打开！", "警告！");return null;}}

该方法还不止这么多，还涉及到了1-27 = A-Z 的适配，如以下方法。

/// <summary>/// 获取当前列列名,并得到EXCEL中对应的列/// </summary>/// <param name="count"></param>/// <returns></returns>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 = "T";break;default:ascstr = "U";break;}return ascstr;}

最后我们获取数据给方法，下面是设计了数据转换到datatable的一个过程。

private void Export(object sender, RoutedEventArgs e){DataTable newTB = new DataTable();ObservableCollection<XModel.STORE_IN> instore = this.dataGrid.ItemsSource as ObservableCollection<XModel.STORE_IN>;List<XModel.STORE_IN> list = new List<XModel.STORE_IN>(instore.ToList());newTB = XTools.XHelper.Datavalidation.CopyToDataTable<XModel.STORE_IN>(list);ExcelExport(newTB, "入库记录");}

public static class Datavalidation{public static DataTable CopyToDataTable<T>(this IEnumerable<T> array){var ret = new DataTable();foreach (PropertyDescriptor dp in TypeDescriptor.GetProperties(typeof(T)))ret.Columns.Add(dp.Name);foreach (T item in array){var Row = ret.NewRow();foreach (PropertyDescriptor dp in TypeDescriptor.GetProperties(typeof(T)))Row[dp.Name] = dp.GetValue(item);ret.Rows.Add(Row);}return ret;}}