Format excel using epplus?

I would like to format excel using epplus like below

but when i do that i am getting following

The following code I am using

public static DataTable ToDataTable(List items)
{
DataTable dataTable = new DataTable(typeof(T).Name);
dataTable.Columns.AddRange(new DataColumn[3] {
new DataColumn("Common Fields"),
new DataColumn("Retail Handyman",typeof(string)),
new DataColumn("QuickBooks",typeof(string))
});
dataTable.Columns.AddRange(new DataColumn[9] {
new DataColumn("S.No", typeof(int)),
new DataColumn("Job ID", typeof(string)),
new DataColumn("Invoice ID",typeof(string)),
new DataColumn("PO #",typeof(string)),
new DataColumn("Sign Date",typeof(string)),
new DataColumn("Store #",typeof(string)),
new DataColumn("PO ",typeof(string)),
new DataColumn("SignDate",typeof(string)),
new DataColumn("Store",typeof(string))});
//Get all the properties by using reflection
PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
// foreach (PropertyInfo prop in Props)
// {
// //Setting column names as Property names
// dataTable.Columns.Add(prop.Name);
// }
foreach (T item in items)
{
var values = new object[Props.Length];
for (int i = 0; i < Props.Length; i++)
{
values[i] = Props[i].GetValue(item, null);
}
dataTable.Rows.Add(values);
}
return dataTable;
}

For converting excel

public static Attachment GetAttachment(DataTable dataTable)
{
MemoryStream outputStream = new MemoryStream();
using (ExcelPackage package = new ExcelPackage(outputStream))
{
ExcelWorksheet facilityWorksheet = package.Workbook.Worksheets.Add("sheetName");
facilityWorksheet.Cells.LoadFromDataTable(dataTable, true);
package.Save();
}
outputStream.Position = 0;
Attachment attachment = new Attachment(outputStream, "MisMatchReport.xlsx", "application/vnd.ms-excel");
return attachment;
}

Please Help

1 Reply

Know the answer? Post it — somebody with the same question will find it here.