Harish Batchu

Harish Batchu

  • 1.9k
  • 247
  • 47k

Format excel using epplus?

Oct 30 2019 2:33 AM
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
  1. public static DataTable ToDataTable<T>(List<T> items)  
  2. {  
  3. DataTable dataTable = new DataTable(typeof(T).Name);  
  4. dataTable.Columns.AddRange(new DataColumn[3] {  
  5. new DataColumn("Common Fields"),  
  6. new DataColumn("Retail Handyman",typeof(string)),  
  7. new DataColumn("QuickBooks",typeof(string))  
  8. });  
  9. dataTable.Columns.AddRange(new DataColumn[9] {  
  10. new DataColumn("S.No"typeof(int)),  
  11. new DataColumn("Job ID"typeof(string)),  
  12. new DataColumn("Invoice ID",typeof(string)),  
  13. new DataColumn("PO #",typeof(string)),  
  14. new DataColumn("Sign Date",typeof(string)),  
  15. new DataColumn("Store #",typeof(string)),  
  16. new DataColumn("PO ",typeof(string)),  
  17. new DataColumn("SignDate",typeof(string)),  
  18. new DataColumn("Store",typeof(string))});  
  19. //Get all the properties by using reflection  
  20. PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);  
  21. // foreach (PropertyInfo prop in Props)  
  22. // {  
  23. // //Setting column names as Property names  
  24. // dataTable.Columns.Add(prop.Name);  
  25. // }  
  26. foreach (T item in items)  
  27. {  
  28. var values = new object[Props.Length];  
  29. for (int i = 0; i < Props.Length; i++)  
  30. {  
  31. values[i] = Props[i].GetValue(item, null);  
  32. }  
  33. dataTable.Rows.Add(values);  
  34. }  
  35. return dataTable;  
  36. }
For converting excel
  1. public static Attachment GetAttachment(DataTable dataTable)  
  2. {  
  3. MemoryStream outputStream = new MemoryStream();  
  4. using (ExcelPackage package = new ExcelPackage(outputStream))  
  5. {  
  6. ExcelWorksheet facilityWorksheet = package.Workbook.Worksheets.Add("sheetName");  
  7. facilityWorksheet.Cells.LoadFromDataTable(dataTable, true);  
  8. package.Save();  
  9. }  
  10. outputStream.Position = 0;  
  11. Attachment attachment = new Attachment(outputStream, "MisMatchReport.xlsx""application/vnd.ms-excel");  
  12. return attachment;  

Please Help

Answers (1)