不需要開其他 Attribute 來做到，這邊我就寫一隻自動透過 Header 來對應的 function 方便我之後 複製貼上
private static List<T> GetWorkSheetToList<T>(ExcelWorksheet sheet)
{
List<T> list = new List<T>();
//取得第一欄的資料
var columnInfo = Enumerable.Range(1, sheet.Dimension.Columns).ToList().Select(n =>
new { Index = n, ColumnName = sheet.Cells[1, n].Value.ToString() }
);
//從第二欄位開始
for (int row = 2; row < sheet.Dimension.Rows; row++)
{
T obj = (T)Activator.CreateInstance(typeof(T));//generic object
foreach (var prop in typeof(T).GetProperties())
{
//取得 EpplusTableColumnAttribute 並且判斷一樣加入該 Property
var properName = prop.GetCustomAttribute<EpplusTableColumnAttribute>();
if (properName != null)
{
int col = columnInfo.SingleOrDefault(c => c.ColumnName == properName.Header).Index;
var val = sheet.Cells[row, col].Value;
var propType = prop.PropertyType;
prop.SetValue(obj, Convert.ChangeType(val, propType));
}
else
{
continue;
}
}
list.Add(obj);
}
return list;
}
使用方法:
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
var excelPackage = new ExcelPackage(AppDomain.CurrentDomain.BaseDirectory + "sample1.xlsx");
var worksheet = excelPackage.Workbook.Worksheets[0];
var result = GetWorkSheetToList<Staff>(worksheet);
Console.WriteLine(JsonConvert.SerializeObject(result));
