WPF-将DataGrid控件中的数据导出到Excel
导出至Excel是非常常见，我们可以用很多类库，例如Aspose、NOPI、Interop，在这里我们使用微软自家的工具。我的WPF绑定的ObservableCollection<T>集合。
public string ExcelExport(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 = 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 2007 File|*.xlsx|Office 2000-2003 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; } }
