WPF的DataGrid导出到Excel

笔记记录，设计有一定缺陷，同行指正，另：转载请注明

WPF的DataGrid导出到Excel

工作上用到，将wpf界面datagrid导出未Excel表格，观摩前人代码，改进了一下：如果属性是decimal、int等数值类型，在导入到Excel中时，都转换成了相应的格式类型，如有大神看见，请帮忙指点优化思路。
第一次写博客记录之前的代码，很多地方都不是很仔细，欢迎讨论！

1.界面通过按钮控制导出事件

//前端导出按钮触发事件
private void BtnExcel_Click(object sender, RoutedEventArgs e)
{
	    if(Data == null)
	    {
	        MessageBoxBase.ShowMessage("导出的数据不能为空！");
	        return;
	    }
	    using (ExcelHelper excel = new ExcelHelper())
	    {
	        string sheetName = "datagrid数据导出";
	        string filename = string.Format("{0}_{1}.xls", sheetName, DateTime.Now.ToString("yyyyMMddHHmmss"));
	        if(excel.SaveToExcel<Model>(Data, filename, sheetName))
	            MessageBox.Show("导出成功！");
	    }
}

2.具体的导出方法

	public class ExcelHelper : IDisposable
    {
        private string fileName = null; //文件名
        private IWorkbook workbook = null;
        private FileStream fs = null;
        private bool disposed;
        public ExcelHelper()//构造函数，读入文件名
        {
            disposed = false;
        }
		/// <summary>
		/// 将泛型集合保存到EXCEL
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="collection"></param>
		/// <param name="filename">EXCEL文件名(包括路径)</param>
		/// <param name="sheetName">标签名称</param>
		/// <param name="isColumnWritten">是否写列名称</param>
		public bool SaveToExcel<T>(IEnumerable<T> collection,string filename,string sheetName, bool isColumnWritten = true)
		{
		    DataTable data = ToDataTable<T>(collection);
		    if (data != null)
		    {
		        data.TableName = sheetName;
		        if (SaveFileDialog(filename))
		        {
		            return DataTableListToExcel(new List<DataTable>(new DataTable[] { data }),isColumnWritten);
		        }
		    }
   			return false;
		}

        /// <summary>
        /// 将集合转换成DataTable，通过特性标志获取需要转换的数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="collection">转换数据</param>
        /// <param name="tableName">表名（用于生成Execl的Sheet名字）</param>
        /// <returns></returns>
        public DataTable ToDataTable<T>(IEnumerable<T> collection, string tableName = null)
        {
            var props = typeof(T).GetProperties();
            var dt = new DataTable();
            var propList = props.Where(p =>
                p.GetCustomAttributes(typeof(ExportExcelObjectSignAttribute), true).Length > 0);
            props = propList.OrderBy(p =>
                ((ExportExcelObjectSignAttribute)
                    (p.GetCustomAttributes(typeof(ExportExcelObjectSignAttribute), true)[0])).Index).ToArray();
            if (props.Length <= 0)
            {
                throw new Exception(typeof(T).FullName +
                                    "中的属性缺少特性ExportExcelObjectSignAttribute(注：只针对需要导出的属性添加ExportExcelObjectSignAttribute特性)");
            }

            foreach (var property in props)
            {
                object[] objs = property.GetCustomAttributes(typeof(ExportExcelObjectSignAttribute), true);
                if (objs.Length > 0)
                {
                    dt.Columns.Add(new DataColumn(((ExportExcelObjectSignAttribute) objs[0]).Display,
                        property.PropertyType));
                }
            }
            if (collection == null || collection.Count() == 0)
            {
                return dt;
            }
            if (collection.Count() > 0)
            {
                for (int i = 0; i < collection.Count(); i++)
                {
                    ArrayList tempList = new ArrayList();
                    foreach (PropertyInfo pi in props)
                    {
                        object obj = pi.GetValue(collection.ElementAt(i), null);
                        tempList.Add(obj);
                    }

                    object[] array = tempList.ToArray();
                    dt.LoadDataRow(array, true);
                }
            }

            if (!string.IsNullOrEmpty(tableName))
            {
                dt.TableName = tableName;
            }

            return dt;
        }
        
        /// <summary>
        /// 选择保存路径
        /// </summary>
        /// <param name="fileName"></param>
        /// <returns></returns>
        public bool SaveFileDialog(string fileName)
        {
            Microsoft.Win32.SaveFileDialog dlg = new Microsoft.Win32.SaveFileDialog();
            dlg.FileName = fileName; // Default file name
            dlg.DefaultExt = ".xls"; // Default file extension
            dlg.Filter = "Excel2003及以下版本|*.xls|Excel2007及以上版本|*.xlsx"; 
            var dir = System.IO.Directory.GetCurrentDirectory(); //何志凌 2018-10-27
            dlg.InitialDirectory = dir;
            // Show save file dialog box
            Nullable<bool> result = dlg.ShowDialog();
            // Process save file dialog box results
            if (result == true)
            {
                // Save document
                this.fileName = dlg.FileName;
            }
            return !string.IsNullOrWhiteSpace(this.fileName);
        }

		/// <summary>
        /// 将DataTableList导入到Excel中，用于生成多个sheet
        /// </summary>
        /// <param name="dataList"></param>
        /// <param name="isColumnWritten"></param>
        /// <returns></returns>
        public bool DataTableListToExcel(List<DataTable> dataList, bool isColumnWritten = true)
        {
            MemoryStream ms = new MemoryStream();

            try
            {
                if (string.IsNullOrEmpty(fileName))
                {
                    return false;
                }

                // 兼容2003版本
                if (fileName.IndexOf(".xlsx", StringComparison.Ordinal) > 0)
                {
                    fileName = fileName.Replace(".xlsx", ".xls");
                }

                HSSFWorkbook workbook = new HSSFWorkbook();


                for (int i = 0; i < dataList.Count; i++)
                {
                    int count;

                    //Sheet名
                    string sheetName = string.IsNullOrEmpty(dataList[i].TableName) ? string.Format("sheet{0}", i + 1) : dataList[i].TableName;
                    ISheet sheet = workbook.CreateSheet(sheetName);
                    if (isColumnWritten) //写入DataTable的列名
                    {
                        IRow row = sheet.CreateRow(0);
                        for (int j = 0; j < dataList[i].Columns.Count; ++j)
                        {
                            row.CreateCell(j).SetCellValue(dataList[i].Columns[j].ColumnName);
                        }

                        count = 1;
                    }
                    else
                    {
                        count = 0;
                    }
                   for (int r = 0; r < dataList[i].Rows.Count; ++r)
                    {
                        IRow row = sheet.CreateRow(count);
                        for (int j = 0; j < dataList[i].Columns.Count; ++j)
                        {
                       	    //找到对应列数据类型，通过反射获取数据类型，switch 判断表走不同分支，控制Excel表格导出数据类型
                            //SetCellValue：写入数据只支持bool、double、string、DateTime、IRichTextString类型
                            var type = dataList[i].Columns[j].DataType.Name;
                            switch (type)
                            {
                                case "Decimal":
                                    row.CreateCell(j).SetCellValue(Convert.ToDouble(dataList[i].Rows[r][j]));
                                    break;
                                case "Int":
                                    row.CreateCell(j).SetCellValue(Convert.ToDouble(dataList[i].Rows[r][j]));
                                    break;
                                case "Float":
                                    row.CreateCell(j).SetCellValue(Convert.ToDouble(dataList[i].Rows[r][j]));
                                    break;
                                case "Double":
                                    row.CreateCell(j).SetCellValue(Convert.ToDouble(dataList[i].Rows[r][j]));
                                    break;
                                case "DateTime":
                                    row.CreateCell(j).SetCellValue(Convert.ToDateTime(dataList[i].Rows[r][j]));
                                    break;
                                case "Bool":
                                    row.CreateCell(j).SetCellValue(Convert.ToBoolean(dataList[i].Rows[r][j]));
                                    break;
                                default:
                                    row.CreateCell(j).SetCellValue(dataList[i].Rows[r][j].ToString());
                                    break;
                            }
                            //row.CreateCell(j).SetCellValue(dataList[i].Rows[r][j].ToString());
                        }

                        ++count;
                    }
                }

                // 写入 
                workbook.Write(ms); //写入到excel
                using (FileStream fs2 = new FileStream(fileName, FileMode.Create, FileAccess.Write))
                {
                    byte[] data2 = ms.ToArray();
                    fs2.Write(data2, 0, data2.Length);
                    fs2.Flush();
                }
                ms.Close();
                ms.Dispose();
                return true;
            }
            catch (Exception ex)
            {
                ms.Close();
                ms.Dispose();
                return false;
            }
        }
	}

3.使用特性标注实体属性，方便选择需要导出的数据列表

	namespace Models.AttributeModel
	{
    /// <summary>
    /// 作用：对导出excel的实体标记需要导出的属性，并指定在excel表格中的顺序
    /// </summary>
    [AttributeUsage(AttributeTargets.Property)]
    public class ExportExcelObjectSignAttribute : Attribute
    {
        /// <summary>
        /// 列显示名称
        /// </summary>
        public string Display { get; set; }
        /// <summary>
        /// 显示顺序
        /// </summary>
        public int Index { get; set; }
        public ExportExcelObjectSignAttribute(string display, int index)
        {
            this.Display = display;
            this.Index = index;
        }
    }
}