/// <summary> /// 生成数据节 /// </summary> /// <returns></returns> private byte[] GenerateExcel() { //排除在属性上打了ExcelIgnoreAttribute标签的属性,ExcelIgnoreAttribute:不在excel中生成列名,和行数据 PropertyInfo[] propertys = typeof(TModel).GetProperties().Where(p => !p.IsDefined(typeof(ExcelIgnoreAttribute))).ToArray(); //创建excel包对象 using (ExcelPackage excel = new ExcelPackage()) { //获取用户自定义的工作表的属性设置【行高、列宽、名字】 ExcelSheetAttribute excelSheetAttribute = typeof(TModel).GetCustomAttribute <ExcelSheetAttribute>() ?? new ExcelSheetAttribute();; //将新工作表添加到空工作簿中、指定工作表的名称 ExcelWorksheet sheet = excel.Workbook.Worksheets.Add(excelSheetAttribute.Name); //sheet.Row(excelSheetAttribute.RowHeight); //sheet.Column(excelSheetAttribute.ColumnWeight); //添加页眉 this.GenerateExcelHandler(sheet, propertys, excelSheetAttribute); //添加body this.GenerateExcelBody(sheet, propertys, excelSheetAttribute); return(excel.GetAsByteArray()); } }
/// <summary> /// 添加页眉 /// </summary> /// <param name="sheet"></param> /// <param name="propertyInfos"></param> private void GenerateExcelHandler(ExcelWorksheet sheet, PropertyInfo[] propertyInfos, ExcelSheetAttribute excelSheetAttribute) { //设置行高、列宽 sheet.Row(1).Height = excelSheetAttribute.RowHeight; sheet.Column(1).Width = excelSheetAttribute.ColumnWeight; sheet.TabColor = Color.Yellow; //单元格自适应,设置此项,则不能设置单元格的宽高。否则跑出异常 sheet.Cells.Style.ShrinkToFit = true; for (int col = 1; col <= propertyInfos.Length; col++) { //获得当前属性 PropertyInfo currentPropertyInfo = propertyInfos[col - 1]; //找到属性上的标签 DisplayNameAttribute displayNameAttribute = currentPropertyInfo.GetCustomAttribute <DisplayNameAttribute>(); DisplayAttribute displayAttribute = currentPropertyInfo.GetCustomAttribute <DisplayAttribute>(); //判断标签是存在 string displayName = displayNameAttribute?.DisplayName ?? displayAttribute?.GetName() ?? currentPropertyInfo.Name; //给第一行的当前列,赋值 ExcelRange currentCell = sheet.Cells[1, col]; currentCell.Value = displayName; //标头加粗 currentCell.Style.Font.Bold = true; //标头水平方向居中 currentCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; //垂直方向居中 currentCell.Style.VerticalAlignment = ExcelVerticalAlignment.Center; //设置背景颜色 currentCell.Style.Fill.PatternType = ExcelFillStyle.Solid; currentCell.Style.Fill.BackgroundColor.SetColor(Color.Red); } }
/// <summary> /// 添加body /// </summary> /// <param name="sheet"></param> /// <param name="propertyInfos"></param> private void GenerateExcelBody(ExcelWorksheet sheet, PropertyInfo[] propertyInfos, ExcelSheetAttribute excelSheetAttribute) { TModel[] modelArray = model.ToArray(); //第一行。是标头,故,从第二行开始 for (int row = 2; row <= modelArray.Length + 1; row++) { TModel model = modelArray[row - 2]; for (int i = 1; i <= propertyInfos.Length; i++) { ExcelRange excelCell = sheet.Cells[row, i]; PropertyInfo currentPropertyInfo = propertyInfos[i - 1]; DisplayFormatAttribute displayFormatAttribute = currentPropertyInfo.GetCustomAttribute <DisplayFormatAttribute>(); //如果属性上有此标签,并且当前属性类型,能不能被格式化 if (displayFormatAttribute != null && typeof(IFormattable).IsAssignableFrom(currentPropertyInfo.PropertyType)) { excelCell.Value = ((IFormattable)currentPropertyInfo.GetValue(model)).ToString(displayFormatAttribute.DataFormatString, CultureInfo.InvariantCulture); } else { excelCell.Value = currentPropertyInfo.GetValue(model); } ExcelColumnAttribute excelColumnAttribute = currentPropertyInfo.GetCustomAttribute <ExcelColumnAttribute>() ?? new ExcelColumnAttribute(); excelCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; excelCell.Style.VerticalAlignment = ExcelVerticalAlignment.Center; if (!string.IsNullOrEmpty(excelColumnAttribute.Format)) { excelCell.Style.Numberformat.Format = excelColumnAttribute.Format; } //设置行高、列宽[优先使用列设置的宽高,否则使用标头设置的宽高] sheet.Column(i).Width = excelColumnAttribute.Width > 0 ? excelColumnAttribute.Width : excelSheetAttribute.ColumnWeight; } sheet.Row(row).Height = excelSheetAttribute.RowHeight; } }