excel导入导出操作类 利用泛型 反射 导入excel

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Reflection;
using System.Data.Linq.Mapping;
using System.Windows.Forms;
using System.Drawing;

namespace ToExcel
{
    class ExcelHelper
    {
        /// <summary>
        /// 读取Excel到DataTable
        /// </summary>
        /// <param name="fileName">Excel文件路径</param>
        /// <returns></returns>
        public static DataTable ExcelToDataTable(string fileName)
        {
            OleDbConnection conn = null;
            DataTable dt = new DataTable();
            string strCon = "";

            //string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" + fileName;

            if (fileName.Substring(fileName.LastIndexOf(".") + 1).ToLower().Equals("xlsx"))
            {
                strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" + fileName;
            }
            else if (fileName.Substring(fileName.LastIndexOf(".") + 1).ToLower().Equals("xls"))
            {
                strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" + fileName;
            }
            else
            {
                MessageBox.Show("选择的文件格式不对！","错误");
                return null;
            }

           
           
            try
            {
                conn = new OleDbConnection(strCon);
                string strCom = "Select * From [Sheet1$]";
                conn.Open();
                OleDbDataAdapter adp = new OleDbDataAdapter(strCom, conn);
                adp.Fill(dt);
                return dt;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                if (conn != null && conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// 将一个泛型导入Excel表格，表头为实体类中的属性的Storage
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="excelList"></param>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public static bool SaveListToExcel<T>(List<T> excelList, string filePath)
        {
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook wBook = null;
            Microsoft.Office.Interop.Excel.Worksheet wSheet = null;
            try
            {
                app.Visible = false;
                wBook = app.Workbooks.Add(true);
                wSheet = wBook.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet;

                Microsoft.Office.Interop.Excel.Range RanSheet = (Microsoft.Office.Interop.Excel.Range)wSheet.Cells;
                RanSheet.Font.Size = 9;
                RanSheet.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;

                //int ticks = DateTime.Now.Second;
                PropertyInfo[] propertys = typeof(T).GetProperties();
                if (propertys.Length > 0 && excelList.Count > 0)
                {
                    for (int colnum = 0; colnum < propertys.Length; colnum++)
                    {
                        for (int row = 0; row < excelList.Count; row++)
                        {
                            //判断是否是工号？处理：不处理
                            if (propertys[colnum].Name == "Number")
                            {
                                wSheet.Cells[row + 2, colnum + 1] = "'" + GetPropertyValue(propertys[colnum], excelList[row]);
                            }
                            else
                            {
                                wSheet.Cells[row + 2, colnum + 1] = GetPropertyValue(propertys[colnum], excelList[row]);
                            }
                        }
                    }
                }

                for (int colnum = 0; colnum < propertys.Length; colnum++)
                {
                    wSheet.Cells[1, colnum + 1] = ParseAttribute(propertys[colnum]);
                    Microsoft.Office.Interop.Excel.Range RanHead = (Microsoft.Office.Interop.Excel.Range)wSheet.Cells[1, colnum + 1];
                    RanHead.Font.Bold = true;
                }

                //int ticks2 = DateTime.Now.Second - ticks;
                //MessageBox.Show(ticks2.ToString());

                //整表单元格自动行宽
                RanSheet.Cells.Columns.AutoFit();
                //RanSheet.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].Weight = 1;

                app.DisplayAlerts = false;
                app.AlertBeforeOverwriting = false;
                wBook.SaveAs(filePath);
                //wBook.Save();
                //app.SaveWorkspace(filePath);

                MessageBox.Show("导出成功！");
                return true;

            }
            catch (Exception ex)
            {
                MessageBox.Show("导出Excel出错！错误原因：" + ex.Message, "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return false;
            }
            finally
            {
                #region"销毁Excel进程"
                wBook.Close(false, Type.Missing, Type.Missing);
                app.Workbooks.Close();
                app.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(wBook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
                wBook = null;
                app = null;
                GC.Collect();
                #endregion
            }
        }

        /// <summary>
        /// 将一个DataRow换成一个实体类
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dr"></param>
        /// <returns></returns>
        public static T DataRowToEntity<T>(DataRow dr)
        {
            T entity = default(T);
            if (null == dr) return entity;
            Type type = typeof(T);
            entity = (T)Activator.CreateInstance<T>();
            PropertyInfo[] properties = type.GetProperties();
            if (properties == null) return entity;
            foreach (var property in properties)
            {
                string storage = ParseAttribute(property);
                if (string.IsNullOrEmpty(storage)) continue;
                if (!dr.Table.Columns.Contains(storage)) continue;
                if (property.PropertyType != typeof(DateTime))
                {
                    if (dr[storage] != DBNull.Value)
                        property.SetValue(entity, dr[storage], null);
                    else
                        property.SetValue(entity, null, null);
                }
                else
                {
                    object value = dr[storage] != DBNull.Value ? dr[storage] : null;
                    DateTime valueTime = DateTime.MinValue;
                    bool transferResult = DateTime.TryParse(value.ToString(), out valueTime);
                    property.SetValue(entity, valueTime, null);
                }
            }
            return entity;
        }

        /// <summary>
        /// 返回实体类中属性对应的Storage的值
        /// </summary>
        /// <param name="member"></param>
        /// <returns></returns>
        public static string ParseAttribute(PropertyInfo member)
        {
            object[] attributes = member.GetCustomAttributes(false);
            if (null == attributes) return string.Empty;
            foreach (var attribute in attributes)
            {
                if (attribute is ColumnAttribute)
                {
                    ColumnAttribute filed = (ColumnAttribute)attribute;
                    return filed.Storage;
                }
            }
            return string.Empty;
        }

        /// <summary>
        /// 返回属性的值
        /// </summary>
        /// <param name="property"></param>
        /// <param name="entity"></param>
        /// <returns></returns>
        public static string GetPropertyValue(PropertyInfo property, object entity)
        {
            string result = string.Empty;
            object value = property.GetValue(entity, null);
            if (value == null)
            {
                return string.Empty;
            }

            Type typeOfValue = value.GetType();
            if (typeOfValue == typeof(string) || typeOfValue == typeof(DateTime))
            {
                result = value.ToString();
            }
            else if (typeOfValue.IsValueType)
            {
                result = value.ToString();
            }
            return result;
        }
    }
}