/// <summary>
/// 获取表头单元格和属性
/// </summary>
/// <param name="headerCellInfo">表头单元格信息集合</param>
private ExcelHeaderCellPropertyInfo GetHeaderCellProperties <TImportDto>(ExcelHeaderCellInfo headerCellInfo) where TImportDto : class, new()
{
if (headerCellInfo.HeaderCells?.Any() != true)
{
throw new Exception($"表头行不能为空");
}
var cellProperties = new ExcelHeaderCellPropertyInfo
{
SheetName = headerCellInfo.SheetName,
SheetIndex = headerCellInfo.SheetIndex
};
//属性名称
var properties = ExcelHelper.GetProperties <TImportDto>();
foreach (var p in properties)
{
var name = p.GetDisplayNameFromProperty()?.Trim();
var cell = headerCellInfo.HeaderCells.FirstOrDefault(a => a.Name.Trim() == name);
if (cell != null)
{
cellProperties.HeaderCells.Add(new ExcelHeaderCellProperty
{
Name = cell.Name,
ColumnIndex = cell.ColumnIndex,
RowIndex = cell.RowIndex,
PropertyInfo = p
});
}
}
return(cellProperties);
}
/// <summary>
/// 获取工作表数据
/// </summary>
/// <typeparam name="TImportDto"></typeparam>
/// <param name="workbook">工作册</param>
/// <param name="sheetIndex">工作表下标（其实下表：0）</param>
/// <param name="options">选项配置</param>
/// <returns></returns>
private ExcelSheetDataOutput <TImportDto> ProcessWorksheet <TImportDto>(TWorkbook workbook, int sheetIndex, ExcelImportOptions options) where TImportDto : class, new()
{
//获取工作表
TSheet worksheet = GetWorksheet(workbook, sheetIndex);
//工作表名称
string sheetName = GetWorksheetName(workbook, worksheet);
try
{
//获取表头行
TRow headerRow = GetHeaderRow(workbook, worksheet, options);
//获取表头单元格集合
List <ExcelHeaderCell> headerCells = GetHeaderCells(workbook, worksheet, headerRow);
//表头单元格信息
ExcelHeaderCellInfo headerCellInfo = new ExcelHeaderCellInfo(sheetName, sheetIndex, headerCells);
//验证表头行
ValidateHeaderRow <TImportDto>(headerCellInfo);
//获取表头单元格属性信息
ExcelHeaderCellPropertyInfo headerCellProperties = GetHeaderCellProperties <TImportDto>(headerCellInfo);
//设置工作表数据
return(new ExcelSheetDataOutput <TImportDto>
{
SheetName = sheetName,
SheetIndex = sheetIndex + 1,
Rows = ProcessWorksheetData <TImportDto>(workbook, worksheet, headerCellInfo, headerCellProperties, options),
});
}
catch (Exception e)
{
throw new Exception($"工作表【{sheetName}】存在以下错误：{e.Message}", e);
}
}
/// <summary>
/// 验证表头行
/// </summary>
/// <param name="headerCellInfo">表头单元格信息集合</param>
private void ValidateHeaderRow <TImportDto>(ExcelHeaderCellInfo headerCellInfo) where TImportDto : class, new()
{
if (headerCellInfo.HeaderCells?.Any() != true)
{
throw new Exception($"表头行不能为空");
}
//属性名称
var propertyNames = ExcelHelper.GetDisplayNameListFromProperty <TImportDto>();
if (!propertyNames.Any())
{
throw new Exception($"类 {typeof(TImportDto).Name} 对应的表头不能为空");
}
var propertyDuplicate = propertyNames.GroupBy(a => a).Where(a => a.Count() > 1).Select(a => a.Key).ToList();
if (propertyDuplicate.Any())
{
throw new Exception($"类 {typeof(TImportDto).Name} 中 Display Name 重复（或与属性名称重复）：{string.Join(",", propertyDuplicate)}");
}
//excel表头名称
var headerNames = headerCellInfo.HeaderCells.Select(a => a.Name).ToList();
var headerDuplicate = headerNames.GroupBy(a => a).Where(a => a.Count() > 1).Select(a => a.Key).ToList();
if (headerDuplicate.Any())
{
throw new Exception($"表头名称重复：{string.Join(",", headerDuplicate)}");
}
var except = propertyNames.Except(headerNames).ToList();
if (except.Any())
{
throw new Exception($"工作表中不存在以下表头名称：{string.Join(",", except)}");
}
}
/// <summary>
/// 处理工作表
/// </summary>
/// <param name="workbook">工作册</param>
/// <param name="worksheet">工作表</param>
/// <param name="headerCellInfo"></param>
/// <param name="headerCellProperties">表头单元格信息集合</param>
/// <param name="options">配置选项</param>
/// <returns></returns>
private List <ExcelImportRowInfo <TImportDto> > ProcessWorksheetData <TImportDto>(TWorkbook workbook, TSheet worksheet, ExcelHeaderCellInfo headerCellInfo, ExcelHeaderCellPropertyInfo headerCellProperties, ExcelImportOptions options) where TImportDto : class, new()
{
var rows = new List <ExcelImportRowInfo <TImportDto> >();
//获取数据行区域索引
var rowRangeIndex = GetDataRowStartAndEndRowIndex(workbook, worksheet, options);
for (var i = rowRangeIndex.StartIndex; i <= rowRangeIndex.EndIndex; i++)
{
//获取数据行
TRow row = GetDataRow(workbook, worksheet, i);
//获取行数据
TImportDto entity = GetRowData <TImportDto>(workbook, worksheet, headerCellProperties, row);
if (entity != null)
{
//验证数据
var valid = ExcelHelper.GetValidationResult(entity);
var rowInfo = new ExcelImportRowInfo <TImportDto>
{
SheetName = headerCellInfo.SheetName,
SheetIndex = headerCellInfo.SheetIndex,
Row = entity,
Errors = valid,
RowNum = i + 1,
IsValid = valid == null
};
rows.Add(rowInfo);
if (!rowInfo.IsValid)
{
if (options.ValidateMode.Equals(ExcelValidateModeEnum.StopRow))
{
break;
}
if (options.ValidateMode.Equals(ExcelValidateModeEnum.ThrowRow))
{
rowInfo.CheckError();
}
}
}
}
return(rows);
}