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