export IEnumerable<T> 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.