private Font GetFont(BsGridExcelStyle style, out int fontId)
{
foreach (var item in fonts)
{
if (item.Font.Bold == style.Font.Bold &&
item.Font.Italic == style.Font.Italic &&
item.Font.Color == style.Font.Color &&
item.Font.Size == style.Font.Size &&
string.Compare(item.Font.Family, style.Font.Family) == 0)
{
fontId = fonts.IndexOf(item);
return(null);
}
}
var color = GetColor(style.Font.Color);
var font = ExcelHelpers.CreateFont(
style.Font.Bold ?? false,
style.Font.Italic ?? false,
style.Font.Family,
style.Font.Size,
color);
fonts.Add(style);
fontId = fonts.IndexOf(style);
return(font);
}
/// <summary>
/// Test if two BsGridExcelStyles are equal (based on fontId and fillId reference)
/// </summary>
/// <param name="excelStyle"></param>
/// <param name="otherExcelStyle"></param>
/// <returns></returns>
public static bool AreEqual(this BsGridExcelStyle excelStyle, BsGridExcelStyle otherExcelStyle)
{
return(excelStyle.FontId.HasValue &&
excelStyle.FillId.HasValue &&
excelStyle.FontId == otherExcelStyle.FontId &&
excelStyle.FillId == otherExcelStyle.FillId);
}
public BsGridExcelBuilder <T> ConfigureHeader(Action <BsGridExcelCellFactory <T> > config)
{
var factory = new BsGridExcelCellFactory <T>();
config(factory);
this.headerCells = factory.Cells;
this.headerStyle = factory.Style;
this.order = factory.Positions;
return(this);
}
private CellFormat GetCellFormat(BsGridExcelStyle style, out int formatId)
{
if (style.FillId.HasValue && style.FontId.HasValue)
{
foreach (var item in cellFormats)
{
if (item.AreEqual(style))
{
formatId = cellFormats.IndexOf(item);
return(null);
}
}
var cellFormat = ExcelHelpers.CreateCellFormat((UInt32)style.FontId, (UInt32)style.FillId);
cellFormats.Add(style);
formatId = cellFormats.IndexOf(style);
return(cellFormat);
}
else
{
int fontId;
var font = GetFont(style, out fontId);
if (font != null)
{
styleSheet.Fonts.Append(font);
}
int fillId;
var fill = GetFill(style, out fillId);
if (fill != null)
{
styleSheet.Fills.Append(fill);
}
var formatStyle = new BsGridExcelStyle()
{
FontId = fontId,
FillId = fillId
};
return(GetCellFormat(formatStyle, out formatId));
}
}
private Fill GetFill(BsGridExcelStyle style, out int fillId)
{
foreach (var item in fills)
{
if ((item.FillColor == style.FillColor) && (item.FillPattern == style.FillPattern))
{
fillId = fills.IndexOf(item);
return(null);
}
}
var fillColor = GetColor(style.FillColor);
var fill = ExcelHelpers.CreateFill(fillColor, style.FillPattern);
fills.Add(style);
fillId = fills.IndexOf(style);
return(fill);
}
public BsGridExcelCellFactory()
{
Style = new BsGridExcelStyle();
}
public BsGridExcelCell <TRow> Style(Action <BsGridExcelStyle> style)
{
this.CellStyle = new BsGridExcelStyle();
style(this.CellStyle);
return(this);
}
/// <summary>
/// Processes the list of items, adding them to the worksheet
/// </summary>
/// <param name="worksheet"></param>
private void AddRows(Worksheet worksheet)
{
if (items == null || !items.Any())
{
return;
}
var sheetData = new SheetData();
var columns = new List <string>();
Columns exColumns = new Columns();
var headerRow = new Row();
var type = typeof(T);
#region Header
var index = 0;
var properties = type.GetProperties();
#region Order Columns
if (this.order != null) // the column order from settings has priority
{
properties = properties.OrderBy(x =>
{
if (this.order.ContainsKey(x.Name))
{
return(this.order.FirstOrDefault(y => y.Key == x.Name).Value);
}
else
{
return(Int32.MaxValue);
}
}).ToArray();
}
else if (this.dataCells != null && this.dataCells.Any())
{
properties = properties.OrderBy(x =>
{
var cell = this.dataCells.FirstOrDefault(y => y.PropName == x.Name);
if (cell != null)
{
return(cell.Position ?? Int32.MaxValue);
}
else
{
return(Int32.MaxValue);
}
}).ToArray();
}
#endregion
// create header based on DisplayAttribute and BsGridColumnAttribute
foreach (var property in properties)
{
BsGridColumnAttribute columnAttr = null;
if (ReflectionHelpers.TryGetAttribute(property, out columnAttr))
{
if (columnAttr.Usage != Models.BsGridColumnUsage.Html)
{
index++;
#region Value
string displayName = null;
DisplayAttribute displayAttribute = null;
if (ReflectionHelpers.TryGetAttribute(property, out displayAttribute))
{
displayName = displayAttribute.GetName();
}
else
{
displayName = property.Name;
}
#endregion
#region Style
var width = columnAttr.Width;
BsGridExcelStyle style = null;
if (headerCells != null)
{
var headerCell = headerCells.FirstOrDefault(x => string.Compare(x.PropName, property.Name) == 0);
if (headerCell != null)
{
displayName = headerCell.Name;
style = BsGridExcelHelpers.Concat(headerCell.CellStyle, this.headerStyle);
}
}
columns.Add(property.Name);
exColumns.Append(ExcelHelpers.CreateColumn((UInt32)index, (UInt32)index, width * widthUnit));
int formatId;
if (style == null)
{
style = this.headerStyle ?? new BsGridExcelStyle
{
Font = new BsGridExcelFont
{
Bold = true
}
};
}
var cellFormat = GetCellFormat(style, out formatId);
if (cellFormat != null)
{
styleSheet.CellFormats.Append(cellFormat);
}
#endregion
headerRow.AppendChild(ExcelHelpers.CreateTextCell(displayName, (UInt32)formatId));
}
}
}
sheetData.AppendChild(headerRow);
#endregion
#region Rows
// create data table
foreach (var item in items)
{
var row = new Row();
foreach (var column in columns)
{
var property = type.GetProperty(column);
object value;
var cell = dataCells.FirstOrDefault(x => string.Compare(x.PropName, column) == 0);
#region Style
BsGridExcelStyle style = new BsGridExcelStyle();
if (aConfig != null)
{
aConfig(item, style);
}
else if (fConfig != null)
{
style = fConfig(item);
}
if (cell != null)
{
if (cell.CellStyle != null)
{
style = BsGridExcelHelpers.Concat(style, cell.CellStyle);
}
if (cell.StyleFunc != null)
{
var style2 = new BsGridExcelStyle();
cell.StyleFunc(item, style2);
style = BsGridExcelHelpers.Concat(style, style2);
}
}
int formatId;
var cellFormat = GetCellFormat(style, out formatId);
if (cellFormat != null)
{
styleSheet.CellFormats.Append(cellFormat);
}
#endregion
#region Value
if (cell != null && cell.NameFunc != null)
{
value = cell.NameFunc(item);
}
else
{
value = property.GetValue(item);
}
if (value != null)
{
var strValue = value as string;
if (strValue != null)
{
row.AppendChild(ExcelHelpers.CreateTextCell(strValue, (UInt32)formatId));
}
else
{
DateTime dateValue;
int intValue;
long longValue;
double doubleValue;
if (DateTime.TryParse(value.ToString(), out dateValue))
{
// ToOADate => excel representation of DateTime - TODO: format date
row.AppendChild(ExcelHelpers.CreateTextCell(dateValue.ToShortDateString(), (UInt32)formatId));
}
else if (int.TryParse(value.ToString(), out intValue))
{
row.AppendChild(ExcelHelpers.CreateValueCell(intValue, (UInt32)formatId, CellValues.Number));
}
else if (long.TryParse(value.ToString(), out longValue))
{
row.AppendChild(ExcelHelpers.CreateValueCell(longValue, (UInt32)formatId, CellValues.Number));
}
else if (Double.TryParse(value.ToString(), out doubleValue))
{
row.AppendChild(ExcelHelpers.CreateValueCell(doubleValue, (UInt32)formatId, CellValues.Number));
}
else // not supported type
{
throw new Exception(column + " is not of type string");
}
}
}
else
{
row.AppendChild(ExcelHelpers.CreateTextCell(string.Empty, (UInt32)formatId));
}
#endregion
}
sheetData.AppendChild(row);
}
#endregion
worksheet.Append(exColumns);
worksheet.Append(sheetData);
}