C#读取Excel文件，转换为list

 

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

 

//读取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;
}