还是干脆、利索直接上代码最实惠。。。。
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. 使用方法就不用多废话了吧。。。还是贴个图好了~_~