private static IEnumerable <T> GetCollectionFromSheets <T>(List <ExcelWorksheet> sheets, string excelName) where T : ExcelBase, new()
{
var list = new List <T>();
if (sheets == null || sheets.Count == 0)
{
return(list);
}
Type type = typeof(T);
var importSetting = new ImportExcelSetting(type);
if (importSetting.ImportColumns.Count > 0)
{
foreach (var item in sheets)
{
var sheetList = GetListFromWorksheet <T>(item, importSetting, excelName);
if (sheetList.Count > 0)
{
list.AddRange(sheetList);
}
}
}
return(list);
}
private static List <T> GetListFromWorksheet <T>(ExcelWorksheet sheet, ImportExcelSetting importSetting, string excelName) where T : ExcelBase, new()
{
List <T> list = new List <T>();
if (sheet == null || sheet.Dimension == null)
{
return(list);
}
Type entityType = typeof(T);
List <string> errorMessages = new List <string>();
int maxColumnNum = sheet.Dimension.End.Column;
int maxRowNum = sheet.Dimension.End.Row;
var mergeList = sheet.MergedCells;
for (int m = 1; m <= maxColumnNum; m++)
{
int cellRow = importSetting.ImportStyle.DataRowIndex - 1;
var cell = sheet.Cells[cellRow, m];
if (cell.Merge)
{
var index = sheet.GetMergeCellId(cellRow, m);
var range = sheet.MergedCells[index - 1]?.Split(new char[1] {
':'
}, StringSplitOptions.RemoveEmptyEntries);
if (range != null && range.Length > 0)
{
cell = sheet.Cells[range[0]];
}
}
var importColumn = importSetting.ImportColumns.Where(o => o.Text == cell.Text?.Trim()).FirstOrDefault();
if (importColumn != null)
{
importColumn.ColumnIndex = m;
}
}
importSetting.ImportColumns = importSetting.ImportColumns.Where(o => o.ColumnIndex > 0).ToList();
for (int n = importSetting.ImportStyle.DataRowIndex; n <= maxRowNum; n++)
{
var entity = Activator.CreateInstance <T>();
//??
entity.ExcelName = excelName;
entity.SheetName = sheet.Name;
entity.RowIndex = n;
foreach (var item in importSetting.ImportColumns)
{
entity.ColumnIndex = item.ColumnIndex;
var property = entityType.GetProperty(item.Name);
#region 数据校验
try
{
//??sheet.Cells[n, item.ColumnIndex].Text
var value = sheet.Cells[n, item.ColumnIndex].Value;
if (value != null && value.ToString() != "")
{
if (value is string)
{
value = ((string)value).TrimStart(item.Prefix.ToArray()).TrimEnd(item.Suffix.ToArray());
}
if (item.IsUseTempField)
{
var tempProperty = entityType.GetProperty(item.TempField);
if (tempProperty != null)
{
tempProperty.SetValue(entity, value, null);
}
else
{
var tempField = entityType.GetField(item.TempField);
if (tempField != null)
{
tempField.SetValue(entity, value);
}
}
continue;
}
if (!string.IsNullOrEmpty(item.DataVerReg))
{
var isMatch = Regex.IsMatch((string)value, item.DataVerReg);
if (!isMatch && item.IsRegFailThrowException)
{
throw new ImportFormatException();
}
}
if (property.PropertyType.IsGenericType && property.PropertyType.GetGenericTypeDefinition().Equals(typeof(Nullable <>)))
{
if (value != null && value.ToString().Length > 0)
{
var tempValue = Convert.ChangeType(value, property.PropertyType.GetGenericArguments()[0]);
property.SetValue(entity, tempValue, null);
}
}
else
{
var tempValue = Convert.ChangeType(value, property.PropertyType);
property.SetValue(entity, tempValue, null);
}
}
}
catch (ImportFormatException ex)
{
errorMessages.Add(string.Format("Excel{0}sheet:{1}第{2}行第{3}列【{4}】的值【{5}】不合法", entity.ExcelName, entity.SheetName, n, item.ColumnIndex, item.Text, sheet.Cells[n, item.ColumnIndex].Value));
}
catch (Exception ex)
{
errorMessages.Add(string.Format("Excel{0}sheet:{1}第{2}行第{3}列【{4}】的值【{5}】转换异常", entity.ExcelName, entity.SheetName, n, item.ColumnIndex, item.Text, sheet.Cells[n, item.ColumnIndex].Value));
}
#endregion
}
list.Add(entity);
}
if (errorMessages.Count > 0)
{
throw new ImportFormatException(errorMessages.ToArray());
}
return(list);
}
private static List <T> GetListFromWorksheet <T>(ExcelWorksheet sheet, ImportExcelSetting importSetting) where T : ExcelBase, new()
{
List <T> list = new List <T>();
if (sheet == null || sheet.Dimension == null)
{
return(list);
}
Type entityType = typeof(T);
List <string> errorMessages = new List <string>();
int maxColumnNum = sheet.Dimension.End.Column;
int maxRowNum = sheet.Dimension.End.Row;
for (int m = 1; m <= maxColumnNum; m++)
{
var cell = sheet.Cells[importSetting.ImportStyle.HeadRowIndex, m];
var importColumn = importSetting.ImportColumns.Where(o => o.Text == cell.Text?.Trim()).FirstOrDefault();
if (importColumn != null)
{
importColumn.ColumnIndex = m;
}
}
for (int n = importSetting.ImportStyle.DataRowIndex; n <= maxRowNum; n++)
{
var entity = Activator.CreateInstance <T>();
//??
entity.ExcelName = sheet.Workbook.Properties.Title;
entity.SheetName = sheet.Name;
entity.RowIndex = n;
foreach (var item in importSetting.ImportColumns)
{
entity.ColumnIndex = item.ColumnIndex;
var property = entityType.GetProperty(item.Name);
#region 数据校验
try
{
//??sheet.Cells[n, item.ColumnIndex].Text
var value = sheet.Cells[n, item.ColumnIndex].Value;
if (value != null && value.ToString() != "")
{
if (item.IsConvert)
{
var tempProperty = entityType.GetProperty(item.TempField);
if (tempProperty != null)
{
tempProperty.SetValue(entity, value, null);
}
else
{
var tempField = entityType.GetField(item.TempField);
if (tempField != null)
{
tempField.SetValue(entity, value);
}
}
continue;
}
if (property.PropertyType.IsGenericType && property.PropertyType.GetGenericTypeDefinition().Equals(typeof(Nullable <>)))
{
if (value != null && value.ToString().Length > 0)
{
var tempValue = Convert.ChangeType(value, property.PropertyType.GetGenericArguments()[0]);
property.SetValue(entity, tempValue, null);
}
}
else
{
var tempValue = Convert.ChangeType(value, property.PropertyType);
property.SetValue(entity, tempValue, null);
}
}
}
catch (Exception ex)
{
errorMessages.Add(string.Format("Excel{0}sheet:{1}第{2}行第{3}列【{4}】的值【{5}】不合法", entity.ExcelName, entity.SheetName, n, item.ColumnIndex, item.Text, sheet.Cells[n, item.ColumnIndex].Value));
}
#endregion
}
list.Add(entity);
}
if (errorMessages.Count > 0)
{
throw new ImportFormatException(errorMessages.ToArray());
}
return(list);
}
