使用反射技术实现的导入Excel文件到数据库的公共方法

来源：互联网发布：淘宝上的万斯是真的吗编辑：程序博客网时间：2024/05/18 18:43

还是干脆、利索直接上代码最实惠。。。。

1. 定义接口类IImportService

public interface IImportService    {        /// <summary>        ///  从XLSX导入信息到数据库        /// </summary>        /// <param name="filePath">导入文件的全路径</param>        /// <param name="propertyDic">Excel文档表头和数据库表字段的对应关系（例如Excel文件的"姓名"——》对应数据库的"Name"）</param>        List<T> ImportEntityListFromXlsx<T>(string filePath, Dictionary<string, string> propertyDic) where T : class,new();    }

2. 提供默认实现类 IImportService

public class ImportService : IImportService    {        private const string cmdText = "Provider=Microsoft.Ace.OleDb.12.0;Data Source={0};Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'";        public List<T> ImportEntityListFromXlsx<T>(string filePath, Dictionary<string, string> propertyDic) where T : class, new()        {            List<T> tempList = new List<T>();            using (OleDbConnection conn = new OleDbConnection(string.Format(cmdText, filePath)))            {                conn.Open();                DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);                for (int i = 0; i < schemaTable.Rows.Count; i++)                {                    string sheetName = schemaTable.Rows[i]["TABLE_NAME"].ToString().Trim();                    //查询sheet中的数据                    string strSql = "select * from [" + sheetName + "]";                    OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn);                    DataSet ds = new DataSet();                    da.Fill(ds);                    var dt = ds.Tables[0];                    foreach (DataRow dr in dt.Rows)                    {                        T tempItem = (T)Activator.CreateInstance(typeof(T));                        foreach (PropertyInfo pop in typeof(T).GetProperties())                        {                            if (propertyDic.HasKey(pop.Name))                            {                                if (!pop.PropertyType.IsGenericType)                                {                                    pop.SetValue(tempItem, Convert.ChangeType(dr[propertyDic[pop.Name]], pop.PropertyType, null));                                }                                else                                {                                    //泛型Nullable<>                                    Type genericTypeDefinition = pop.PropertyType.GetGenericTypeDefinition();                                    if (genericTypeDefinition == typeof(Nullable<>))                                    {                                        pop.SetValue(tempItem, string.IsNullOrEmpty(dr[propertyDic[pop.Name]].TryString()) ? null : Convert.ChangeType(dr[propertyDic[pop.Name]], Nullable.GetUnderlyingType(pop.PropertyType)), null);                                    }                                }                            }                        }                        tempList.Add(tempItem);                    }                }            }            return tempList;        }    }

3. 使用方法就不用多废话了吧。。。还是贴个图好了~_~

0 0