Tutorial :export IEnumerable to Excel



Question:

anybody knows how or some library to use for this ?


Solution:1

Take a look here for the library part:

Create Excel (.XLS and .XLSX) file from C#

Creating Excel spreadsheets .XLS and .XLSX in C#


Solution:2

The code below is code I use to convert an IEnumerable<T> to a DataTable containing columns named for each property, along with the values of each item in the IEnumerable as rows. It's a very small hop from this to saving it as a CSV file

public class IEnumerableToDataTable  {      public  static DataTable CreateDataTableForPropertiesOfType<T>()      {          DataTable dt = new DataTable();          PropertyInfo[] piT = typeof(T).GetProperties();            foreach (PropertyInfo pi in piT)          {              Type propertyType = null;              if (pi.PropertyType.IsGenericType)              {                  propertyType = pi.PropertyType.GetGenericArguments()[0];              }              else              {                  propertyType = pi.PropertyType;              }              DataColumn dc = new DataColumn(pi.Name, propertyType);                if (pi.CanRead)              {                  dt.Columns.Add(dc);              }          }            return dt;      }        public static DataTable ToDataTable<T>(IEnumerable<T> items)      {          var table = CreateDataTableForPropertiesOfType<T>();          PropertyInfo[] piT = typeof(T).GetProperties();            foreach (var item in items)          {              var dr = table.NewRow();                for (int property = 0; property < table.Columns.Count; property++)              {                  if (piT[property].CanRead)                  {                      dr[property] = piT[property].GetValue(item, null);                  }              }                table.Rows.Add(dr);          }          return table;      }  }  

So, you could write the following to convert your data to a datatable:

IEnumerable<Thing> values = GetMyIEnumerableValues();    var tableOfData = IEnumerableToDataTable.ToDataTable(values);  

Once it's there, it's fairly trivial to write it out to a CSV file, or any other format of your choice, as all the "tricky" reflection work to extract the data from the IEnumerable<T> has been done.


Solution:3

I have solved a problem similar like you asked. Like you have a IEnumerable

IEnumerable<TaskSheetHead> taskSheetHead = new TaskSheetHeadService().GetEmployeeTimesheet(entity);  

So if you want to make a excel from this just create a workbook

var workbook = new HSSFWorkbook();  var sheet = workbook.CreateSheet();  

And iterate with a looping and then add it to the worksheet

foreach (TaskSheetHead taskSheethead in taskSheetHead)              {                  //decimal totalTask = decimal.Zero;                  //double totalTime = 0;                    foreach (TaskSheet taskSheet in taskSheethead.TaskSheets)                  {                      var row2 = sheet.CreateRow(rowNumber++);                      row2.CreateCell(0).SetCellValue(taskSheethead.Date.ToString("MMMM dd, yyyy"));                      row2.CreateCell(1).SetCellValue(taskSheet.Task.Project.ProjectName + ":" + taskSheet.Task.Title + "-" + taskSheet.Comment);                      row2.CreateCell(2).SetCellValue(taskSheet.ExpendedHour);                      row2.CreateCell(3).SetCellValue(taskSheet.IsBilledToClient);                      //totalTask += 1;                      //totalTime += taskSheet.ExpendedHour;                  }                  var row3 = sheet.CreateRow(rowNumber++);                  row3.CreateCell(0).SetCellValue("");                  row3.CreateCell(1).SetCellValue("");                  row3.CreateCell(2).SetCellValue("");                  row3.CreateCell(3).SetCellValue("");                }  

Here I have another child list, therefore I have iterated using a inner loop. Do as your choice.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »