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