/// <summary>
/// 获取Sheet头部信息
/// </summary>
/// <returns></returns>
public void GetSheetHeadList()
{
//遍历Sheet
foreach (var sheetModel in ExcelGlobalDTO.Sheets)
{
//获取Sheet
ISheet sheet = ExcelGlobalDTO.Workbook.GetSheetAt(sheetModel.SheetIndex);
//获取头部行
IRow headRow = sheet.GetRow(sheetModel.StartRowIndex.Value);
if (headRow == null)
{
continue;
}
#region 合并列：把实体属性+Excel头部列合并(二开扩展列)
//头部实体集合：实体属性+Excel列
List <ExcelHeadDTO> entityHeadDtoList = ExcelAttributeHelper <TEntity> .GetHeads();
List <ExcelHeadDTO> sheetHeadDtoList = GetSheetHead(headRow.Cells);
//获取不在实体属性中的列，并合并至头部集合
IEnumerable <string> headNames = entityHeadDtoList.Select(s => s.HeadName);
IEnumerable <ExcelHeadDTO> otherColumns = sheetHeadDtoList.Where(w => headNames.Contains(w.HeadName) == false);
if (otherColumns != null)
{
entityHeadDtoList.AddRange(otherColumns);
}
#endregion
#region 设置列序号
foreach (var item in entityHeadDtoList)
{
//因为根据实体获取的头部对象还要很多其他的属性值，所以存在同名则以实体为主;
var headDto = sheetHeadDtoList.FirstOrDefault(it => it.HeadName.Equals(item.HeadName));
if (headDto != null)
{
item.ColumnIndex = headDto.ColumnIndex;
}
}
#endregion
//添加头部
sheetModel.SheetHeadList = entityHeadDtoList;
}
}
public static DataTableEntityBuilder <T> CreateBuilder(DataRow dr)
{
DataTableEntityBuilder <T> dynamicBuilder = new DataTableEntityBuilder <T>();
DynamicMethod method = new DynamicMethod("DynamicCreateEntity", typeof(T), new Type[] { typeof(DataRow) }, typeof(T), true);
ILGenerator generator = method.GetILGenerator();
LocalBuilder result = generator.DeclareLocal(typeof(T));
generator.Emit(OpCodes.Newobj, typeof(T).GetConstructor(Type.EmptyTypes));
generator.Emit(OpCodes.Stloc, result);
var attributeList = ExcelAttributeHelper <T> .GetHeads();
for (int i = 0; i < dr.ItemArray.Length; i++)
{
var list = attributeList.Where(w => w.PropertyName.ToLower() == dr.Table.Columns[i].ColumnName.ToLower());
if (list.Count() > 0)
{
PropertyInfo pi = typeof(T).GetProperty(list.First().PropertyName);
Label endIfLabel = generator.DefineLabel();
if (pi != null && pi.GetSetMethod() != null)
{
generator.Emit(OpCodes.Ldarg_0);
generator.Emit(OpCodes.Ldc_I4, i);
generator.Emit(OpCodes.Callvirt, isDBNullMethod);
generator.Emit(OpCodes.Brtrue, endIfLabel);
generator.Emit(OpCodes.Ldloc, result);
generator.Emit(OpCodes.Ldarg_0);
generator.Emit(OpCodes.Ldc_I4, i);
generator.Emit(OpCodes.Callvirt, getValueMethod);
generator.Emit(OpCodes.Unbox_Any, pi.PropertyType);
generator.Emit(OpCodes.Callvirt, pi.GetSetMethod());
generator.MarkLabel(endIfLabel);
}
}
}
generator.Emit(OpCodes.Ldloc, result);
generator.Emit(OpCodes.Ret);
dynamicBuilder.handler = (Load)method.CreateDelegate(typeof(Load));
return(dynamicBuilder);
}
/// <summary>
/// 校验头部
/// </summary>
public virtual void ValidationHead()
{
//遍历Sheet进行头部校验
foreach (var sheetModel in ExcelGlobalDTO.Sheets)
{
ISheet sheet = ExcelGlobalDTO.Workbook.GetSheetAt(sheetModel.SheetIndex);
//获取头部行
IRow row = sheet.GetRow(sheetModel.StartRowIndex.Value);
if (row == null)
{
throw new Exception(ExcelGlobalDTO.ExcelValidationMessage.Clgyl_Common_Import_TempletError);
}
//获取表头信息
List <string> cellValues = row.Cells.Select(s => ExcelHelper.GetCellValue(s)).ToList();
List <ExcelHeadDTO> headDtoList = ExcelAttributeHelper <TEntity> .GetHeads();
//头部校验
foreach (ExcelHeadDTO dto in headDtoList)
{
//校验必填的，判断表头是否在excel中存在
if (dto.IsValidationHead == true && cellValues.Contains(dto.HeadName) == false)
{
throw new Exception(ExcelGlobalDTO.ExcelValidationMessage.Clgyl_Common_Import_TempletError);
}
}
}
//动态列验证
DynamicColumn <TEntity> .ValidationHead(ExcelGlobalDTO, ValidationModelEnum.DynamicColumn);
//头部校验后处理，用于特殊处理
ValidationHeaderAfter();
}
/// <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
}
}