/// <summary>
/// 获取头部集合
/// </summary>
/// <returns></returns>
public static List <ExcelHeadDTO> GetHeads()
{
//结果集
List <ExcelHeadDTO> result = new List <ExcelHeadDTO>();
PropertyInfo[] propertyInfos = typeof(TEntity).GetProperties();
//遍历属性
foreach (PropertyInfo info in propertyInfos)
{
//获取头部特性
ExcelHeadAttribute attribute = info.GetCustomAttribute <ExcelHeadAttribute>();
if (attribute != null)
{
//头部实体赋值
ExcelHeadDTO dto = new ExcelHeadDTO();
dto.PropertyName = info.Name;
dto.HeadName = attribute.HeadName;
dto.IsSetHeadColor = attribute.IsSetHeadColor;
dto.IsLocked = attribute.IsLocked;
dto.SortValue = attribute.SortValue;
dto.ColumnType = attribute.ColumnType;
dto.IsHiddenColumn = attribute.IsHiddenColumn;
dto.ColumnWidth = attribute.ColumnWidth;
dto.Format = attribute.Format;
dto.BackgroundColor = attribute.BackgroundColor;
dto.HeaderBackgroundColor = attribute.HeaderBackgroundColor;
//获取属性
RequiredAttribute requiredAttribute = info.GetCustomAttribute <RequiredAttribute>();
List <string> disableAttrs = new RequiredAttributeValidation <TEntity>().GetDisableAttributes();
//启用必填属性
if (requiredAttribute != null && (disableAttrs == null || disableAttrs.Contains(info.Name) == false))
{
dto.IsValidationHead = true;
}
else
{
dto.IsValidationHead = false;
}
//启用必填属性
Dictionary <string, BaseAttribute> enableAttrs = new RequiredAttributeValidation <TEntity>().GetEnableAttributes();
if (enableAttrs != null && enableAttrs.Keys.Contains(info.Name) == true)
{
dto.IsValidationHead = true;
}
//添加至结果集
result.Add(dto);
}
}
int columnIndex = 0;
//返回结果集
result = result.OrderBy(it => it.SortValue).ToList();
result.ForEach(it => { it.ColumnIndex = columnIndex++; });
return(result);
}
/// <summary>
/// 实体属性赋值
/// </summary>
/// <param name="entity"></param>
/// <param name="prop"></param>
/// <param name="headDto"></param>
/// <param name="cell"></param>
private bool EntityPropertySetValue(TEntity entity, PropertyInfo prop, ExcelHeadDTO headDto, ICell cell)
{
//变量申明
string value = ExcelHelper.GetCellValue(cell);//获取单元格的值，设置属性值
//日期
if (headDto.ColumnType == Attribute.Enum.ColumnTypeEnum.Date && cell.CellType == CellType.Numeric)
{
//赋值、设置
prop.SetValue(entity, cell.DateCellValue, null);
return(true);
}
//Decimal保留两位
if (headDto.ColumnType == Attribute.Enum.ColumnTypeEnum.Decimal && string.IsNullOrEmpty(headDto.Format) == false)
{
//赋值、设置
string newValue = decimal.Parse(value).ToString(GetNewFormatString(headDto.Format));
prop.SetValue(entity, decimal.Parse(newValue), null);
return(true);
}
//GUID类型转换
if (prop.PropertyType == typeof(Guid?))
{
//赋值、设置
prop.SetValue(entity, new Guid(value), null);
return(true);
}
//GUID类型转换
if (prop.PropertyType == typeof(Guid))
{
//赋值、设置
prop.SetValue(entity, new Guid(value), null);
return(true);
}
if (prop.PropertyType == typeof(decimal) && string.IsNullOrEmpty(headDto.Format) == false)
{
prop.SetValue(entity, Math.Round(Convert.ToDecimal(value), int.Parse(headDto.Format), MidpointRounding.AwayFromZero), null);
return(true);
}
//类型是泛型类型且为空类型
if (prop.PropertyType.IsGenericType && prop.PropertyType.GetGenericTypeDefinition() == typeof(Nullable <>))
{
//赋值、设置
prop.SetValue(entity, Convert.ChangeType(value, Nullable.GetUnderlyingType(prop.PropertyType)), null);
return(true);
}
return(false);
}
/// <summary>
/// 异常处理
/// </summary>
/// <param name="entity"></param>
/// <param name="prop"></param>
/// <param name="headDto"></param>
/// <param name="ex"></param>
private void EntityPropertyErrorSet(TEntity entity, PropertyInfo prop, ExcelHeadDTO headDto, Exception ex)
{
#region 异常处理
//获取属性转换异常信息
string convertErrorMsg = ConvertErrorAttributeHelper <TEntity> .GetPropertyConvertErrorInfo(prop.Name);
if (string.IsNullOrEmpty(convertErrorMsg) == true)
{
convertErrorMsg = ex.Message;
}
//设置异常信息
ColumnErrorMessage errorMsg = new ColumnErrorMessage
{
PropertyName = prop.Name,
ErrorMessage = convertErrorMsg,
ColumnName = headDto.HeadName
};
entity.ColumnErrorMessage.Add(errorMsg);
#endregion
}
/// <summary>
/// 获取工作簿的表头
/// </summary>
/// <param name="cells"></param>
protected List <ExcelHeadDTO> GetSheetHead(List <ICell> cells)
{
if (cells == null)
{
return(null);
}
//返回
List <ExcelHeadDTO> result = new List <ExcelHeadDTO>();
//遍历
foreach (ICell cell in cells)
{
ExcelHeadDTO headDTO = new ExcelHeadDTO
{
HeadName = ExcelHelper.GetCellValue(cell),
ColumnIndex = cell.ColumnIndex,
};
result.Add(headDTO);
}
return(result);
}
/// <summary>
/// 初始化Excel
/// </summary>
/// <param name="excelGlobalDTO"></param>
private void GlobalExcelInitByData(ExcelGlobalDTO <TEntity> excelGlobalDTO)
{
//工作簿为空判断
if (excelGlobalDTO.Workbook == null)
{
excelGlobalDTO.Workbook = ExcelHelper.CreateWorkbook(excelGlobalDTO.ExcelVersionEnum);
}
//遍历Sheet，创建头部、数据行
foreach (var item in excelGlobalDTO.Sheets)
{
item.SheetHeadList = ExcelAttributeHelper <TEntity> .GetHeads();
//获取动态列
List <ColumnModel> dynamicColumns = System.Activator.CreateInstance <TEntity>().GetDynamicColumns();
if (dynamicColumns != null && dynamicColumns.Count > 0)
{
if (item.SheetHeadList == null)
{
item.SheetHeadList = new List <ExcelHeadDTO>();
}
foreach (ColumnModel columnModel in dynamicColumns)
{
//头部实体赋值
ExcelHeadDTO excelHeadDTO = new ExcelHeadDTO();
excelHeadDTO.ColumnIndex = columnModel.ColumnIndex;
excelHeadDTO.SortValue = columnModel.SortValue;
excelHeadDTO.HeadName = columnModel.ColumnName;
excelHeadDTO.IsValidationHead = columnModel.IsValidationHead;
excelHeadDTO.IsSetHeadColor = columnModel.IsSetHeadColor;
excelHeadDTO.IsLocked = columnModel.IsLocked;
excelHeadDTO.ColumnType = columnModel.ColumnType;
excelHeadDTO.IsHiddenColumn = columnModel.IsHiddenColumn;
excelHeadDTO.ColumnWidth = columnModel.ColumnWidth;
excelHeadDTO.Format = columnModel.Format;
excelHeadDTO.BackgroundColor = columnModel.BackgroundColor;
item.SheetHeadList.Add(excelHeadDTO);
}
//排序
item.SheetHeadList = item.SheetHeadList.OrderBy(o => o.SortValue).ToList();
int order = 0;
item.SheetHeadList.ForEach(it => { it.ColumnIndex = order++; });
}
//创建Sheet
ISheet sheet = null;
#region 创建Sheet
//判断Sheet是否存在，不存在则创建
if (string.IsNullOrEmpty(item.SheetName))
{
sheet = excelGlobalDTO.Workbook.CreateSheet();
item.SheetName = sheet.SheetName;
}
else
{
//如果存在则使用存在
ISheet existSheet = excelGlobalDTO.Workbook.GetSheet(item.SheetName);
if (existSheet == null)
{
//处理特殊字符
string sheetName = item.SheetName.Replace("\\", "").Replace("/", "").Replace(":", "").Replace("?", "")
.Replace("*", "").Replace("[", "").Replace("]", "");
sheet = excelGlobalDTO.Workbook.CreateSheet(sheetName);
item.SheetName = sheet.SheetName;
}
else
{
sheet = existSheet;
}
}
#endregion
item.SheetIndex = excelGlobalDTO.Workbook.GetSheetIndex(sheet.SheetName);
//初始化sheet页表头和单元格样式
InitCellStyle(item.SheetHeadList, excelGlobalDTO.Workbook);
#region 创建头部
//创建头部
bool isExistHead = sheet.GetRow(item.StartRowIndex.Value) == null ? false : true;//是否存在头部
IRow row = sheet.GetRow(item.StartRowIndex.Value) ?? sheet.CreateRow(item.StartRowIndex.Value);
Dictionary <string, ICellStyle> styleDic = new Dictionary <string, ICellStyle>();
//头部
foreach (var head in item.SheetHeadList)
{
//获取单元格对象
ICell cell = row.GetCell(head.ColumnIndex) ?? row.CreateCell(head.ColumnIndex);
//设置样式
if (cell.CellStyle.IsNullOrEmpty())
{
cell.CellStyle = HeadCellStyleDic[head.HeadName];
}
//设置值
cell.SetCellValue(head.HeadName);
//设置列宽
if (head.ColumnWidth == 0 && head.HeadName != null)
{
head.ColumnWidth = Encoding.Default.GetBytes(head.HeadName).Length;
}
if (isExistHead == false)
{
head.ColumnWidth = head.ColumnWidth + 1;//多留一个字符的宽度
sheet.SetColumnWidth(head.ColumnIndex, head.ColumnWidth * 256);
}
}
#endregion
//如果没有实体集合则跳出
if (item.SheetEntityList == null)
{
continue;
}
//设置行号
int rowNumber = item.StartRowIndex.Value;
foreach (var entity in item.SheetEntityList)
{
//设置实体的行号
rowNumber++;
entity.RowNumber = rowNumber;
}
#region 创建行、列及设置值
foreach (var entity in item.SheetEntityList)
{
//创建行
IRow dataRow = sheet.GetRow(entity.RowNumber) ?? sheet.CreateRow(entity.RowNumber);
//循环创建列
foreach (var head in item.SheetHeadList)
{
//获取单元格对象
ICell cell = dataRow.GetCell(head.ColumnIndex) ?? dataRow.CreateCell(head.ColumnIndex);
//获取单元格样式
if (cell.CellStyle.IsNullOrEmpty())
{
cell.CellStyle = CellStyleDic[head.HeadName];
}
object value = null;
if (string.IsNullOrEmpty(head.PropertyName) == false)
{
value = entity.GetType().GetProperty(head.PropertyName).GetValue(entity);
}
else
{
if (entity.OtherColumns != null)
{
ColumnModel columnModel = entity.OtherColumns.Where(w => w.ColumnName == head.HeadName).FirstOrDefault();
value = columnModel?.ColumnValue;
}
}
if (value != null)
{
if (head.ColumnType == Attribute.Enum.ColumnTypeEnum.Date && string.IsNullOrEmpty(head.Format) == false)
{
cell.SetCellValue(((DateTime)value).ToString(head.Format));
}
else if (head.ColumnType == Attribute.Enum.ColumnTypeEnum.Decimal && string.IsNullOrEmpty(head.Format) == false)
{
string cellValue = ((decimal)value).ToString(GetNewFormatString(head.Format));
cell.SetCellValue(cellValue);
}
else if (head.ColumnType == Attribute.Enum.ColumnTypeEnum.Option && string.IsNullOrEmpty(head.Format) == false)
{
string cellValue = ((decimal)value).ToString(GetNewFormatString(head.Format));
cell.SetCellValue(cellValue);
}
else
{
cell.SetCellValue(value.ToString());
}
}
}
}
#endregion
}
}
/// <summary>
/// 实体其他列设置
/// </summary>
/// <param name="entity"></param>
/// <param name="sheetModel"></param>
/// <param name="headDto"></param>
/// <param name="value"></param>
public virtual void EntityOtherColumnsSet(TEntity entity, ExcelSheetModel <TEntity> sheetModel, ExcelHeadDTO headDto, string value)
{
//其他不在属性内的列
ColumnModel column = new ColumnModel
{
ColumnIndex = headDto.ColumnIndex,
ColumnName = headDto.HeadName,
ColumnValue = value
};
entity.OtherColumns.Add(column);
}
/// <summary>
/// 设置批注
/// </summary>
/// <param name="excelGlobalDTO"></param>
public void SetComment(ExcelGlobalDTO <TEntity> excelGlobalDTO)
{
foreach (var item in excelGlobalDTO.Sheets)
{
//值判断
if (item.SheetEntityList == null)
{
continue;
}
//获取Excel的Sheet，对Excel设置批注
ISheet sheet = excelGlobalDTO.Workbook.GetSheetAt(item.SheetIndex);
//批注总数
int commentCount = 0;
foreach (var entity in item.SheetEntityList)
{
if (entity.ColumnErrorMessage == null)
{
continue;
}
//NPOI目前最大批注个数设置的默认值是1024个,如果超出默认值就会报错
if (commentCount >= 1000)
{
break;
}
//获取行对象
IRow row = sheet.GetRow(entity.RowNumber);
//基于属性设置批注
var groupPropertyName = entity.ColumnErrorMessage.Where(w => w.PropertyName != null).GroupBy(g => g.PropertyName);
foreach (var groupItem in groupPropertyName)
{
//NPOI目前最大批注个数设置的默认值是1024个,如果超出默认值就会报错
if (commentCount >= 1000)
{
break;
}
//获取单元格，设置批注
ExcelHeadDTO headDto = item.SheetHeadList.Where(n => n.PropertyName == groupItem.Key).FirstOrDefault();
ICell cell = row.GetCell(headDto.ColumnIndex);
if (cell == null)
{
cell = row.CreateCell(headDto.ColumnIndex);
}
//设置批注
string errorMsg = string.Join(";", groupItem.Select(s => s.ErrorMessage).Distinct().ToArray());
SetCellComment(cell, errorMsg, excelGlobalDTO);
commentCount++;
}
//基于列头设置批注，适用于动态列
var groupColumnName = entity.ColumnErrorMessage.Where(w => w.PropertyName == null).GroupBy(g => g.ColumnName);
foreach (var groupItem in groupColumnName)
{
//NPOI目前最大批注个数设置的默认值是1024个,如果超出默认值就会报错
if (commentCount >= 1000)
{
break;
}
//获取单元格，设置批注
ExcelHeadDTO headDto = item.SheetHeadList.Where(n => n.HeadName == groupItem.Key).FirstOrDefault();
ICell cell = row.GetCell(headDto.ColumnIndex);
if (cell == null)
{
cell = row.CreateCell(headDto.ColumnIndex);
}
//设置批注
string errorMsg = string.Join(";", groupItem.Select(s => s.ErrorMessage).Distinct().ToArray());
SetCellComment(cell, errorMsg, excelGlobalDTO);
commentCount++;
}
}
}
}
