经常会用到,废话不多说直接贴代码

 

//读取Excel文件

public static DataTable ReadExcelToTable(string path)//excel存放的路径

{

try

{

//连接字符串

string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';";

using (OleDbConnection conn = new OleDbConnection(connstring))

{

conn.Open();

//读取sheetName

DataTable sheetsName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" }); 

string firstSheetName = sheetsName.Rows[0][2].ToString();

string sql = string.Format("SELECT * FROM [{0}]", firstSheetName); 

OleDbDataAdapter ada = new OleDbDataAdapter(sql, connstring);

DataSet set = new DataSet();

ada.Fill(set);

return set.Tables[0];

}

}

catch (Exception)

{

return null;

}

}

 

//把读取到的数据转换为 list

public class ModelConvertHelper<T> where T : new()

{

public static IList<T> ConvertToModel(DataTable dt)

{

// 定义集合 

IList<T> ts = new List<T>();

// 获得此模型的类型 

Type type = typeof(T);

string tempName = "";

foreach (DataRow dr in dt.Rows)

{

T t = new T();

// 获得此模型的公共属性 

PropertyInfo[] propertys = t.GetType().GetProperties();

foreach (PropertyInfo p in propertys)

{

tempName = p.Name; // 检查DataTable是否包含此列

if (dt.Columns.Contains(tempName))

{

// 判断此属性是否有Setter 

if (!pi.CanWrite) continue;

object value = dr[tempName];

if (value != DBNull.Value)

p.SetValue(t, value, null);

}

}

ts.Add(t);

}

return ts;

}