private void CreateTotals(ISheet worksheet, IList <TotalsColumn> totalsColumns, IList <PropertyInfo> properties, int rowCount, StylingOptions stylingOptions, Dictionary <string, ICellStyle> styleCache) { if (totalsColumns == null || !totalsColumns.Any()) { return; } var totalsRowColorId = GetXlColor(stylingOptions.TotalsRowColor); var totalsFontColorId = GetXlColor(stylingOptions.TotalsFontColor); var totalsRow = worksheet.CreateRow(rowCount + 1); foreach (var columnIndex in properties.Select(properties.IndexOf)) { var totalsColumn = totalsColumns.SingleOrDefault(tc => tc.ColumnIndex == columnIndex); var cell = totalsRow.CreateCell(columnIndex); if (totalsColumn == null) { SetCellValue(cell, columnIndex == 0 ? "Totals:" : null, typeof(string)); totalsRow.Cells[columnIndex].CellStyle = GetCellStyle(0, totalsFontColorId, stylingOptions.TotalsFontSize, styleCache, true, totalsRowColorId, HorizontalAlignment.Left); } else { cell.SetCellFormula(GetCellFormula(totalsColumn.TotalType, columnIndex, rowCount)); totalsRow.Cells[columnIndex].CellStyle = GetCellStyle(totalsColumn.Format, totalsFontColorId, stylingOptions.TotalsFontSize, styleCache, true, totalsRowColorId, HorizontalAlignment.Right); } } }
private void ShadeAlternatingRows(ISheet sheet, StylingOptions stylingOptions, int rowCount, int columnCount) { var conditionalFormattingRule = sheet.SheetConditionalFormatting.CreateConditionalFormattingRule("MOD(ROW(),2)"); var patternFormatting = conditionalFormattingRule.CreatePatternFormatting(); patternFormatting.FillBackgroundColor = GetXlColor(stylingOptions.AlternatingDataRowColor); patternFormatting.FillPattern = (short)FillPattern.SolidForeground; CellRangeAddress[] regions = { new CellRangeAddress(2, rowCount, 0, columnCount - 1) }; sheet.SheetConditionalFormatting.AddConditionalFormatting(regions, conditionalFormattingRule); }
public NpoiWorkbook AddWorkSheet <T>(IEnumerable <T> collection, string workSheetName, StylingOptions stylingOptions = null) { GetXlColor(Color.Azure); if (collection == null) { throw new ArgumentNullException(nameof(collection)); } if (string.IsNullOrWhiteSpace(workSheetName)) { throw new ArgumentNullException(nameof(workSheetName)); } if (stylingOptions == null) { stylingOptions = new StylingOptions(); } var enumeratedCollection = collection.ToList(); var type = typeof(T); var properties = type.GetProperties() .Where(p => p.CustomAttributes.All(a => !a.AttributeType.IsSubclassOf(typeof(ExportAttributeBase)) || a.CreateInstance <ExportAttributeBase>().ShouldExport(collection)) ).ToList(); var worksheet = _hssfWorkbook.CreateSheet(workSheetName); var columnFormatters = new Dictionary <int, short>(); var styleCache = new Dictionary <string, ICellStyle>(); if (stylingOptions.ShadeAlternateRows) { ShadeAlternatingRows(worksheet, stylingOptions, enumeratedCollection.Count, properties.Count); } var totalsColumns = CreateHeader(worksheet, type, properties, stylingOptions, columnFormatters, styleCache); CreateDataRows(worksheet, properties, enumeratedCollection, stylingOptions, columnFormatters, styleCache); CreateTotals(worksheet, totalsColumns, properties, enumeratedCollection.Count, stylingOptions, styleCache); return(this); }
private void CreateDataRows <T>(ISheet worksheet, IList <PropertyInfo> properties, IReadOnlyList <T> enumeratedCollection, StylingOptions stylingOptions, Dictionary <int, short> columnFormatters, Dictionary <string, ICellStyle> styleCache) { var dataFontColorId = GetXlColor(stylingOptions.DataFontColor); for (var rowIndex = 0; rowIndex < enumeratedCollection.Count; rowIndex++) { var row = worksheet.CreateRow(rowIndex + 1); foreach (var columnIndex in properties.Select(properties.IndexOf)) { SetCellValue(row.CreateCell(columnIndex), properties[columnIndex].GetValue(enumeratedCollection[rowIndex]), properties[columnIndex].PropertyType); row.Cells[columnIndex].CellStyle = GetCellStyle(columnFormatters[columnIndex], dataFontColorId, stylingOptions.DataFontSize, styleCache); } } for (var i = 0; i < properties.Count; i++) { worksheet.AutoSizeColumn(i); worksheet.SetColumnWidth(i, worksheet.GetColumnWidth(i) + 1000); } }
private Collection <TotalsColumn> CreateHeader(ISheet worksheet, Type type, IList <PropertyInfo> properties, StylingOptions stylingOptions, Dictionary <int, short> columnFormatters, Dictionary <string, ICellStyle> styleCache) { var headerRowColorId = GetXlColor(stylingOptions.HeaderRowColor); var headerFontColorId = GetXlColor(stylingOptions.HeaderFontColor); var headerCellStyle = GetCellStyle(0, headerFontColorId, stylingOptions.HeaderFontSize, styleCache, true, headerRowColorId, HorizontalAlignment.Center); var headerRow = worksheet.CreateRow(0); var totalsColumns = new Collection <TotalsColumn>(); for (var columnIndex = 0; columnIndex < properties.Count; columnIndex++) { var displayNameAttribute = Attribute.GetCustomAttribute(type.GetProperty(properties[columnIndex].Name), typeof(DisplayNameAttribute)) as DisplayNameAttribute; headerRow.CreateCell(columnIndex).SetCellValue(displayNameAttribute == null ? properties[columnIndex].Name : displayNameAttribute.DisplayName); headerRow.Cells[columnIndex].CellStyle = headerCellStyle; var formatAttribute = Attribute.GetCustomAttribute(type.GetProperty(properties[columnIndex].Name), typeof(FormatAttribute)) as FormatAttribute; var format = GetFormatId(formatAttribute); columnFormatters.Add(columnIndex, format); var totalsAttribute = Attribute.GetCustomAttribute(type.GetProperty(properties[columnIndex].Name), typeof(FormulaAttribute)) as FormulaAttribute; if (totalsAttribute != null) { totalsColumns.Add(new TotalsColumn { ColumnIndex = columnIndex, Format = format, TotalType = totalsAttribute.FormulaType }); } } worksheet.CreateFreezePane(0, 1); return(totalsColumns); }
public static NpoiWorkbook ToExcelWorkBook <T>(this IEnumerable <T> collection, string workSheetName, StylingOptions stylingOptions = null) { var workbook = new NpoiWorkbook(); return(workbook.AddWorkSheet(collection, workSheetName, stylingOptions)); }