/// <summary>
/// 读取DataTable到集合中
/// </summary>
/// <param name="sheetTable"></param>
/// <param name="columns"></param>
/// <returns></returns>
public static List <T> ReadDataTable(DataTable sheetTable, Action <IRootReadExcelColumnBuilder <T> > columns, Func <T> createInstance)
{
ExcelReader <T> reader = new ExcelReader <T>();
ReadExcelColumnBuilder <T> columnBuilder = CreateColumnBuilder(columns);
//读取Excel
List <T> result = reader.ReadDataTable(sheetTable, columnBuilder, createInstance);
return(result);
}
/// <summary>
/// 创建信息列构造器
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="columns"></param>
/// <returns></returns>
private static ReadExcelColumnBuilder <T> CreateColumnBuilder(Action <IRootReadExcelColumnBuilder <T> > columns)
{
var builder = new ReadExcelColumnBuilder <T>();
if (columns != null)
{
columns(builder);
}
return(builder);
}
public List <T> ReadDataTable(DataTable sheetTable, ReadExcelColumnBuilder <T> columns, Func <T> createInstance)
{
if (sheetTable == null)
{
throw new SheetTableNullException("工作表中不存在数据");
}
//校验信息列
this.ValidateColumns(columns, sheetTable, "");
List <T> items = new List <T>(sheetTable.Rows.Count);
//循环读取每行Excel信息
for (Int32 rowIndex = 0; rowIndex < sheetTable.Rows.Count; rowIndex++)
{
DataRow row = sheetTable.Rows[rowIndex];
T item = this.CreateInstance(createInstance);
//该行数据为空
if (row.ItemArray.Where(e => null == e).Count() == row.ItemArray.Length)
{
continue;
}
using (DataRowWrapper dataWrapper = new DataRowWrapper(row, rowIndex))
{
foreach (var column in columns)
{
ProcessCellDataException error = this.ProcessCellData(item, dataWrapper, column);
if (null != error)
{//保存错误信息
throw error;
}
}
}
items.Add(item);
}
return(items);
}
/// <summary>
/// 验证单元格是否有效
/// </summary>
/// <param name="columns"></param>
/// <param name="table">信息源</param>
/// <param name="message">验证失败的信息</param>
/// <returns></returns>
private void ValidateColumns(ReadExcelColumnBuilder <T> columns, DataTable table, String sheetName)
{
String message = String.Empty;
if (null == columns)
{
throw new ArgumentException("columns");
}
foreach (var column in columns)
{
if (column.CustomDelegate != null)
{//自定义绑定信息，忽略验证
continue;
}
if (!String.IsNullOrEmpty(column.ColumnName))
{ //信息列名称 优先级大于 索引
if (!table.Columns.Contains(column.ColumnName))
{ //校验信息列是否有效
message += String.Format("工作表中不存在数据列 [{0}] 。{1}", column.ColumnName, "<br>");
}
continue;
}
if (column.ColumnIndex >= 0)
{//校验信息列是否有效
if (table.Columns.Count <= column.ColumnIndex)
{
message += String.Format("工作表中不存在第 [{0}] 列错误。{1}", column.ColumnIndex, "<br>");
}
continue;
}
}
if (!String.IsNullOrEmpty(message))
{//验证失败，抛出异常
throw new ValidateColumnException(message);
}
}