export IEnumerable<T> to Excel [closed]

Closed. This question does not meet Stack Overflow guidelines. It is not currently accepting answers.

---

We don’t allow questions seeking recommendations for books, tools, software libraries, and more. You can edit the question so it can be answered with facts and citations.

Closed 7 months ago.

Improve this 开发者_如何学Cquestion

anybody knows how or some library to use for this ?

---

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.

---

Take a look here for the library part:

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

Creating Excel spreadsheets .XLS and .XLSX in C#

---

I have solved a problem similar to what you asked. Like you have an 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.