/// <summary>
/// Execl文件转模型
/// </summary>
/// <param name="path"></param>
/// <returns></returns>
public static List <T> ExcelToModel <T>(string path) where T : class, new()
{
try
{
if (!File.Exists(path))
{
throw new ArgumentNullException($"{path}路径的文件不存在！");
}
List <T> list = new List <T>();
Type tType = typeof(T);
string sheetName = tType.Name;
SheetAttribute sheetAttribute = tType.GetCustomAttribute <SheetAttribute>();
if (sheetAttribute != null)
{
sheetName = sheetAttribute.Name;
}
int startIndex = 0;
RowAttribute rowAttribute = tType.GetCustomAttribute <RowAttribute>();
if (rowAttribute != null)
{
startIndex = rowAttribute.StartRow;
}
PropertyInfo[] properties = tType.GetProperties(BindingFlags.Instance | BindingFlags.Public);
using (var fileStream = new FileStream(path, FileMode.Open, FileAccess.Read))
{
IWorkbook workbook = new HSSFWorkbook(fileStream);
ISheet sheet = workbook.GetSheet(sheetName);
for (int j = startIndex; j < sheet.LastRowNum; j++)
{
IRow row = sheet.GetRow(j);
if (row == null || IsRowEmpty(row))
{
continue;
}
else
{
T tModel = new T();
foreach (PropertyInfo property in properties)
{
ColumnAttribute columnAttribute = property.GetCustomAttribute <ColumnAttribute>();
if (columnAttribute != null)
{
ICell cell = row.GetCell(columnAttribute.Index);
property.SetValue(tModel, GetCellValue(cell, property));
}
}
list.Add(tModel);
}
}
return(list);
}
}
catch (Exception ex)
{
System.Diagnostics.Trace.TraceError(ex.Message);
return(null);
}
}
/// <summary>
/// Execl文件转内存表
/// </summary>
/// <param name="path"></param>
/// <returns></returns>
public static DataTable ExcelToTable <T>(string path)
{
if (string.IsNullOrEmpty(path))
{
throw new ArgumentNullException($"{path}路径为空！");
}
if (!File.Exists(path))
{
throw new ArgumentNullException($"{path}路径的文件不存在！");
}
try
{
DataTable dataTable = new DataTable();
Type tType = typeof(T);
string tableName = tType.Name;
TableAttribute tableAttribute = tType.GetCustomAttribute <TableAttribute>();
SheetAttribute sheetAttribute = tType.GetCustomAttribute <SheetAttribute>();
if (tableAttribute != null)
{
tableName = tableAttribute.Name;
}
dataTable.TableName = tableName;
int startIndex = 0;
RowAttribute rowAttribute = tType.GetCustomAttribute <RowAttribute>();
if (rowAttribute != null)
{
startIndex = rowAttribute.StartRow;
}
PropertyInfo[] properties = tType.GetProperties(BindingFlags.Instance | BindingFlags.Public);
dataTable.Columns.AddRange(properties.Select(x =>
{
string columnName = x.Name;
TableColumnAttribute tableColumnAttribute = x.GetCustomAttribute <TableColumnAttribute>();
if (tableColumnAttribute != null)
{
columnName = tableColumnAttribute.Name;
}
return(new DataColumn(columnName, x.PropertyType));
}).ToArray());
var fileStream = new FileStream(path, FileMode.Open, FileAccess.Read);
IWorkbook workbook = null;
HSSFWorkbook hSSFWorkbook2007 = null;
XSSFWorkbook xSSFWorkbook2003 = null;
try
{
hSSFWorkbook2007 = new HSSFWorkbook(fileStream);
workbook = (IWorkbook)hSSFWorkbook2007;
}
catch (Exception ex)
{
fileStream = new FileStream(path, FileMode.Open, FileAccess.Read);
xSSFWorkbook2003 = new XSSFWorkbook(fileStream);
workbook = (IWorkbook)xSSFWorkbook2003;
System.Diagnostics.Trace.TraceError(ex.Message);
}
if (sheetAttribute != null)
{
tableName = sheetAttribute.Name;
}
ISheet sheet = workbook.GetSheet(tableName);
for (int j = startIndex; j < sheet.LastRowNum; j++)
{
IRow row = sheet.GetRow(j);
if (row == null || IsRowEmpty(row))
{
continue;
}
else
{
DataRow newRow = dataTable.NewRow();
foreach (PropertyInfo property in properties)
{
ColumnAttribute columnAttribute = property.GetCustomAttribute <ColumnAttribute>();
TableColumnAttribute tableColumnAttribute = property.GetCustomAttribute <TableColumnAttribute>();
if (tableColumnAttribute != null)
{
newRow[tableColumnAttribute.Name] = GetCellValue(row, property, tableColumnAttribute, columnAttribute);
}
}
dataTable.Rows.Add(newRow);
}
}
workbook.Close();
fileStream.Close();
return(dataTable);
}
catch (Exception ex)
{
System.Diagnostics.Trace.TraceError(ex.Message);
return(null);
}
}