private void setHeaders(List <PropertyInfo> properties, ExcelWorksheet ws, ObjectToExcel_PropertyListOptions propertyListOptions) { var count = properties.Count(); for (int i = 0; i < count; i++) { var property = properties[i]; var range = this.getHeadingRange(ws, i, propertyListOptions); range.Value = property.Name; } var headerRange = this.getFullHeadingRange(ws, count, propertyListOptions); switch (propertyListOptions) { case ObjectToExcel_PropertyListOptions.HorizontalPropertyNames: this.ExcelService.Format_Background_Text(headerRange, ExcelFillStyle.Solid, ExcelHorizontalAlignment.Center, Color.LightGray, true); break; case ObjectToExcel_PropertyListOptions.VerticalPropertyNames: this.ExcelService.Format_Background_Text(headerRange, ExcelFillStyle.Solid, ExcelHorizontalAlignment.Left, Color.LightGray, true); break; default: break; } }
private ExcelRange getFullHeadingRange(ExcelWorksheet ws, int count, ObjectToExcel_PropertyListOptions propertyListOptions) { switch (propertyListOptions) { case ObjectToExcel_PropertyListOptions.HorizontalPropertyNames: return(ws.Cells[1, 1, 1, count]); case ObjectToExcel_PropertyListOptions.VerticalPropertyNames: return(ws.Cells[1, 1, count, 1]); default: throw new Exception("Case not matched."); } }
private ExcelRange getHeadingRange(ExcelWorksheet ws, int itemIndex, ObjectToExcel_PropertyListOptions propertyListOptions) { var column = 0; var row = 0; switch (propertyListOptions) { case ObjectToExcel_PropertyListOptions.HorizontalPropertyNames: row = 1; column = itemIndex + 1; break; case ObjectToExcel_PropertyListOptions.VerticalPropertyNames: column = 1; row = itemIndex + 1; break; default: throw new Exception("Case not matched."); } return(ws.Cells[row, column]); }
private Tuple <int, int> getDataRange(ExcelWorksheet ws, int itemIndex, int propertyIndex, ObjectToExcel_PropertyListOptions propertyListOptions, Tuple <int, int> pickupRange) { var column = 0; var row = 0; switch (propertyListOptions) { case ObjectToExcel_PropertyListOptions.HorizontalPropertyNames: if (pickupRange.Item1 == 0) { row = itemIndex + 2; } else { row = pickupRange.Item1; } if (pickupRange.Item2 == 0) { column = itemIndex + 1; } else { column = pickupRange.Item2 + 1; } break; case ObjectToExcel_PropertyListOptions.VerticalPropertyNames: if (pickupRange.Item1 == 0) { row = propertyIndex + 1; } else { row = pickupRange.Item1 + 1; } if (pickupRange.Item2 == 0) { column = itemIndex + 2; } else { column = pickupRange.Item2; } break; default: throw new Exception("Case not matched."); } return(Tuple.Create <int, int>(row, column)); }
public void Save_Object_To_Excel(string filename, string outputDirectory, IEnumerable <object> data, ObjectToExcel_PropertyListOptions propertyListOptions) { var result = this.ObjectList_To_ExcelPackage(data, propertyListOptions); this.ExcelService.SaveExcel(filename, outputDirectory, result); }
public ExcelPackage ObjectList_To_ExcelPackage(IEnumerable <object> data, ExcelPackage package, string worksheetName, ObjectToExcel_PropertyListOptions propertyListOptions) { if (data == null || !data.Any()) { throw new Exception("Object_To_ExcelPackage: Null or empty input table!\n"); } var properties = getProperties(data.First()); var ws = package.Workbook.Worksheets.Add(worksheetName); setHeaders(properties, ws, propertyListOptions); setData(properties, data.ToList(), ws, propertyListOptions); this.ExcelService.AutoFit_All_Columns(ws); return(package); }
public ExcelPackage ObjectList_To_ExcelPackage(IEnumerable <object> data, string worksheetName, ObjectToExcel_PropertyListOptions propertyListOptions) { var package = new ExcelPackage(); return(this.ObjectList_To_ExcelPackage(data, package, worksheetName, propertyListOptions)); }
public void Object_To_Excel(object data, ExcelPackage package, string worksheetName, ObjectToExcel_PropertyListOptions propertyListOptions) { if (data == null) { throw new Exception("Object_To_ExcelPackage: Null or empty input table!\n"); } var properties = getProperties(data); var ws = package.Workbook.Worksheets.Add(worksheetName); var json = JsonConvert.SerializeObject(data, Formatting.Indented, new JsonConverter[] { new StringEnumConverter() }); var range = ws.Cells[1, 1]; range.Value = json; range.Style.WrapText = true; range.Style.VerticalAlignment = ExcelVerticalAlignment.Top; this.ExcelService.AutoFit_All_Columns(ws); ws.Column(1).Width = 200; }
private Tuple <int, int> setField(List <PropertyInfo> properties, object data, ExcelWorksheet ws, ObjectToExcel_PropertyListOptions propertyListOptions, int itemIndex, int propertyIndex, Tuple <int, int> pickupRange) { var value = properties[propertyIndex].GetValue(data, null); var isValueTypeOrString = true; Type valueType = null; var valueTypeName = ""; if (value != null) { valueType = value.GetType(); valueTypeName = valueType.Name; if (valueTypeName == "String") { isValueTypeOrString = true; } else { isValueTypeOrString = valueType.IsValueType; } } if (isValueTypeOrString) { var range = this.getDataRange(ws, itemIndex, propertyIndex, propertyListOptions, pickupRange); var excelRange = ws.Cells[range.Item1, range.Item2]; excelRange.Value = value; this.ExcelService.AlignRight(excelRange); return(range); } else { var newProperties = getProperties(value); var range = this.getDataRange(ws, itemIndex, propertyIndex, propertyListOptions, pickupRange); var excelRange = ws.Cells[range.Item1, range.Item2]; excelRange.Value = valueTypeName; this.ExcelService.AlignRight(excelRange); switch (propertyListOptions) { case ObjectToExcel_PropertyListOptions.HorizontalPropertyNames: this.setData(newProperties, value, ws, ObjectToExcel_PropertyListOptions.VerticalPropertyNames, range.Item1 + 1, range.Item2); return(Tuple.Create <int, int>(range.Item1, range.Item2)); case ObjectToExcel_PropertyListOptions.VerticalPropertyNames: this.setData(newProperties, value, ws, ObjectToExcel_PropertyListOptions.HorizontalPropertyNames, range.Item1, range.Item2 + 1); return(Tuple.Create <int, int>(range.Item1, range.Item2)); default: throw new Exception("Case not matched."); } } }
private void setData(List <PropertyInfo> properties, List <object> data, ExcelWorksheet ws, ObjectToExcel_PropertyListOptions propertyListOptions) { var pickupRange = Tuple.Create <int, int>(0, 0); for (int itemIndex = 0; itemIndex < data.Count(); itemIndex++) { for (int propertyIndex = 0; propertyIndex < properties.Count(); propertyIndex++) { pickupRange = setField(properties, data[itemIndex], ws, propertyListOptions, itemIndex, propertyIndex, pickupRange); } } }
private Tuple <int, int> setData(List <PropertyInfo> properties, object data, ExcelWorksheet ws, ObjectToExcel_PropertyListOptions propertyListOptions, int pickupRow = 0, int pickupColumn = 0) { var itemIndex = 0; var pickupRange = Tuple.Create <int, int>(pickupRow, pickupColumn); for (int propertyIndex = 0; propertyIndex < properties.Count(); propertyIndex++) { pickupRange = setField(properties, data, ws, propertyListOptions, itemIndex, propertyIndex, pickupRange); } return(pickupRange); }