public static ExcelImportOption <T> Add <T, E>(this ExcelImportOption <T> origin, string field, Expression <Func <T, E> > prop, ExcelToEntityType type = ExcelToEntityType.Default)
{
if (origin.FieldOption == null)
{
origin.FieldOption = new List <ExcelCellOption <T> >();
}
origin.FieldOption.Add(GenOption(field, prop, type));
return(origin);
}
public static ExcelImportOption <T> Add <T, E>(this ExcelImportOption <T> origin, string field, Expression <Func <T, E> > prop, Func <string, object> action)
{
if (origin.FieldOption == null)
{
origin.FieldOption = new List <ExcelCellOption <T> >();
}
origin.FieldOption.Add(GenOption(field, prop, action));
return(origin);
}
/// <summary>
/// 将表格数据转换为指定的数据实体(实际上下面的方法高度重合,主要为了Init)
/// </summary>
public static ICollection <T> ExcelToEntity <T>(Stream excelStream, ExcelImportOption <T> options)
{
var header = GenExcelHeaderByOptions(excelStream, options);
using ExcelPackage pack = new(excelStream);
// 合并 FieldOption 和 DefaultOption
var propOptions = options.FieldOption.Concat(options.DefaultOption);
var sheet = pack.Workbook.Worksheets[1];
int rowCount = sheet.Dimension.Rows, colCount = sheet.Dimension.Columns;
// 将excel 的数据转换为 对应实体
List <T> data = new();
for (int r = 2; r <= rowCount; r++)
{
try
{
// 将单行数据转换为 表头:数据 的键值对
var rowData = sheet.Cells[r, 1, r, colCount]
.Where(item => header.Any(title => title.Value == item.End.Column))
.Select(item => new KeyValuePair <string, string>(header.First(title => title.Value == item.End.Column).Key, item.Value?.ToString().Trim()))
.ToDictionary(item => item.Key, item => item.Value);
var obj = Activator.CreateInstance(typeof(T));
// 根据对应传入的设置 为obj赋值
foreach (var option in propOptions)
{
if (!option.ExcelField.IsNullOfEmpty())
{
var value = rowData.ContainsKey(option.ExcelField) ? rowData[option.ExcelField] : string.Empty;
// if (!value.IsNullOfEmpty())
option.Prop.SetValue(obj, option.Action == null ? value : option.Action(value));
}
else
{
option.Prop.SetValue(obj, option.Action == null ? null : option.Action(string.Empty));
}
}
if (options.Init != null)
{
obj = options.Init((T)obj);
}
data.Add((T)obj);
}
catch (Exception ex)
{
throw new Exception($@"ERROR: 第 {r} 行 ; {ex.Message}");
}
}
return(data);
}
public static ExcelImportOption <T> Require <T, E>(this ExcelImportOption <T> origin, string field, Expression <Func <T, E> > prop, ExcelToEntityType type = ExcelToEntityType.Default, Func <string, object> action = null)
{
var option = GenOption(field, prop, type);
var optionAction = option.Action;
option.Action = item =>
{
if (item.IsNullOfEmpty())
{
throw new Exception($@" {field} 不可为空");
}
if (optionAction == null)
{
return(item);
}
return(optionAction(item));
};
origin.FieldOption.Add(option);
return(origin);
}
public static ExcelImportOption <T> Require <T, E>(this ExcelImportOption <T> origin, string field, Expression <Func <T, E> > prop, Func <string, object> action)
{
var member = prop.GetMember();
if (origin.FieldOption == null)
{
origin.FieldOption = new List <ExcelCellOption <T> >();
}
Func <string, object> optionAction = item =>
{
if (string.IsNullOrEmpty(item))
{
throw new Exception($@" {field} 不可为空");
}
if (action == null)
{
return(item);
}
return(action(item));
};
origin.FieldOption.Add(GenOption(field, prop, optionAction));
return(origin);
}
public static Dictionary <string, int> GenExcelHeaderByOptions <T>(Stream excelStream, ExcelImportOption <T> options)
{
using ExcelPackage pack = new(excelStream);
// 合并 FieldOption 和 DefaultOption
var propOptions = options.FieldOption.Concat(options.DefaultOption);
var sheet = pack.Workbook.Worksheets[1];
int rowCount = sheet.Dimension.Rows, colCount = sheet.Dimension.Columns;
// 获取对应设置的 表头 以及其 column
var header = sheet.Cells[1, 1, 1, sheet.Dimension.Columns]
.Where(item => propOptions.Any(opt => opt.ExcelField == item.Value?.ToString().Trim()))
.ToDictionary(item => item.Value?.ToString().Trim(), item => item.End.Column);
return(header);
}
public static ExcelImportOption <T> AddInit <T>(this ExcelImportOption <T> origin, Func <T, T> action = null)
{
origin.Init = action;
return(origin);
}
public static ExcelImportOption <T> Default <T, E>(this ExcelImportOption <T> origin, Expression <Func <T, E> > prop, object defaultValue = null)
{
origin.DefaultOption.Add(GenOption(string.Empty, prop, item => defaultValue));
return(origin);
}
