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));
}