/// <summary> /// 创建表头 /// </summary> /// <param name="excelSheet">工作表</param> /// <param name="style">单元格样式</param> /// <param name="execlWorkBookStyle"></param> internal static void CreateHeader(ISheet excelSheet, ICellStyle style, WorkBookStyle execlWorkBookStyle) { List <ExeclColumnStyle> execlCellStyleList = execlWorkBookStyle.ExeclColumnStyleList; int cellIndex = 0, rowIndex; //title所在行 rowIndex = execlWorkBookStyle.TitleRowIndex.HasValue ? execlWorkBookStyle.TitleRowIndex.Value : 0; IRow sheetRow = excelSheet.GetRow(rowIndex); if (sheetRow == null) { sheetRow = excelSheet.CreateRow(rowIndex); } //循环导出列 foreach (var de in execlWorkBookStyle.ColumnsName) { ICell newCell = sheetRow.GetCell(cellIndex); if (newCell == null) { newCell = sheetRow.CreateCell(cellIndex); //赋默认值 newCell.CellStyle = style; //按表头文字的宽度 excelSheet.SetColumnWidth(cellIndex, de.Value.ToString().Length *byteWidth); //获取头部样式 ExcelColumnStyle execlCellStyle = execlCellStyleList.FirstOrDefault(it => it.ColumnsName == de.Key); if (execlCellStyle != null && execlCellStyle.TitleStyle != null) { newCell.CellStyle = execlCellStyle.TitleStyle;//用户自定义表头样式 } //设置头部宽度 if ((execlCellStyle?.Width ?? 0) > 0) { excelSheet.SetColumnWidth(cellIndex, execlCellStyle.Width); } } newCell.SetCellValue(de.Value.ToString()); cellIndex++; } }
/// <summary> /// 插入数据行 /// </summary> /// <param name="entity">来源数据行</param> /// <param name="currentExcelRow">当前数据行</param> /// <param name="execlWorkBookStyle">表头样式</param> /// <param name="rowCount">execl当前行</param> private static void InsertCell <T>(T entity, IRow currentExcelRow, WorkBookStyle execlWorkBookStyle, int rowCount) { Type objType = typeof(T); #region 获取错误信息集合 List <ErrorMessage> errorMessages = new List <ErrorMessage>(); //得到错误信息集合对象 PropertyInfo errorMessagesPro = objType.GetProperty("ErrorMessages"); if (errorMessagesPro != null) { errorMessages = (List <ErrorMessage>)errorMessagesPro.GetValue(entity, null); } #endregion #region 获取未定义的列信息集合 List <ColumnInfo> otherColumns = new List <ColumnInfo>(); //得到未定义的列集合对象 PropertyInfo otherColumnsPro = objType.GetProperty("OtherColumns"); if (otherColumnsPro != null) { otherColumns = (List <ColumnInfo>)otherColumnsPro.GetValue(entity, null); } #endregion int cellIndex = 0; //根据表头开始循环 foreach (var item in execlWorkBookStyle.ColumnsName) { //列名称 string columnsName = item.Key.ToString(); PropertyInfo columnPropertyInfo = objType.GetProperty(columnsName); string propertyValue = string.Empty; System.Type rowType; #region 未定义属性处理 if (columnPropertyInfo == null) { ColumnInfo columnInfo = otherColumns.FirstOrDefault(it => it.Title.Equals(item.Value)); if (columnInfo != null) { propertyValue = columnInfo.Value; } columnsName = item.Value; rowType = typeof(string); } else { propertyValue = columnPropertyInfo.GetValue(entity, null)?.ToString().Trim() ?? string.Empty; rowType = columnPropertyInfo.PropertyType; } #endregion //获取列的样式对象 ExcelColumnStyle execlCellStyle = execlWorkBookStyle.ExeclColumnStyleList?.FirstOrDefault(it => it.ColumnsName == columnsName) ?? null; //获取该列的错误信息 string errMsg = string.Empty; List <ErrorMessage> errorMessageList = errorMessages?.Where(it => (it.Name?.Equals(columnsName) ?? false) || (it.Title?.Equals(columnsName) ?? false))?.ToList(); ICell newCell = currentExcelRow.GetCell(cellIndex); if (newCell == null) { newCell = currentExcelRow.CreateCell(cellIndex); } else { newCell.CellComment = null; } //单元格内容处理 CellCreate(rowType, newCell, propertyValue, execlWorkBookStyle.BaseExcelWorkbook); //设置列样式 ExcelHelper.SetExeclCellStyle(newCell, execlCellStyle); //设置隐藏样式 ExcelHelper.SetColumnStyle(execlWorkBookStyle.WorkSheet, cellIndex, execlCellStyle); //设置批注 SetComment(errorMessageList, currentExcelRow, newCell, execlWorkBookStyle); //设置计算表达式 ExcelHelper.SetCellFormula(execlCellStyle, execlWorkBookStyle.WorkSheet, newCell, rowCount); cellIndex++; } }