How do I export a C# object list to an excel spreadsheet?

Question

I have a C# list that I created from an Excel spreadsheet, and I want to export it to Excel. How can I achieve that task? This is just a console project. I do not intend to display the data in a .Net application. I just need the spread sheet.

var fileName = string.Format("C:\\Users\\SGurmu\\Desktop\\Data 091510.xls");
var connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0;", fileName);

var fileName2 = string.Format("C:\\Users\\SGurmu\\Desktop\\Copy of Prototype.xls");
var connectionString2 = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0;", fileName);

var adapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", connectionString);
var ds = new DataSet();

adapter.Fill(ds, "contacts");

var data = ds.Tables["contacts"].AsEnumerable();

List<EmployeeData> query = data.Where(x => x.Field<string>("First_Name") != string.Empty).Select(x =>

new EmployeeData
    {
    empID = x.Field<double>("EMPLOYEE"),
    firstName = x.Field<string>("First_Name"),
    lastName = x.Field<string>("Last_Name"),
    JobCategory = x.Field<string>("Job Title"),
    StartDate = x.Field<Nullable<DateTime>>("Hire Dt"),
    EndDate =   x.Field<Nullable<DateTime>>("Term Dt"),
    TermReason = x.Field<string>("Term Reason"),
    PeggedUID = x.Field<Nullable<double>>("Pegged UserID"),
    UpdateDate = x.Field<Nullable<DateTime>>("Last Updated")
    }).ToList();

can't you export to a simple csv file or an xml file that you import in a precomputed excel file? — Steve B, Sep 24 '12 at 15:48
Did you try this guys approach? goto the section entitled "And Here is the method ported for a List. List — Paul Zahra, Sep 24 '12 at 15:53

score 3 · Accepted Answer · answered Jun 22 '16 at 15:30

maybe you could try use Infodinamica.Framework.Expotable package, hosted in Nuget.

With it, you could do something like this

List<EmployeeData> query = data.Where(x => x.Field<string>("First_Name") != string.Empty).Select(x =>

    new EmployeeData
        {
        empID = x.Field<double>("EMPLOYEE"),
        firstName = x.Field<string>("First_Name"),
        lastName = x.Field<string>("Last_Name"),
        JobCategory = x.Field<string>("Job Title"),
        StartDate = x.Field<Nullable<DateTime>>("Hire Dt"),
        EndDate =   x.Field<Nullable<DateTime>>("Term Dt"),
        TermReason = x.Field<string>("Term Reason"),
        PeggedUID = x.Field<Nullable<double>>("Pegged UserID"),
        UpdateDate = x.Field<Nullable<DateTime>>("Last Updated")
        }).ToList();

    IExportEngine engine = new ExcelExportEngine();
    engine.AddData(EmployeeData);
    MemoryStream memory = engine.Export();

You can install with nuget command:

Install-Package Infodinamica.Framework.Exportable

The only problem, documentation is in spanish.

The project page is here

Export example (in spanish) is here

It also enable import files (in spanish) here

Excelllent solution, it saved my day ! Thx. – JPacheco Aug 09 '22 at 15:05 — JPacheco, Aug 09 '22 at 15:05

score 2 · Answer 2 · answered May 08 '17 at 07:26

Right-click the project and manage nuget packages.
Add the ClosedXML nuget package
Add using ClosedXML.Excel; to the top of the class file.
Add the 'ToExcelFile' method to your class.
Convert your list to a DataTable.

ToExcelFile method

public static bool ToExcelFile(this DataTable dt, string filename)
{
    bool Success = false;
    //try
    //{
        XLWorkbook wb = new XLWorkbook();

        wb.Worksheets.Add(dt, "Sheet 1");

        if (filename.Contains("."))
        {
            int IndexOfLastFullStop = filename.LastIndexOf('.');

            filename = filename.Substring(0, IndexOfLastFullStop) + ".xlsx";

        }

        filename = filename + ".xlsx";

        wb.SaveAs(filename);

        Success = true;

    //}
    //catch (Exception ex)
    //{
        //ex.HandleException();

    //}
    return Success;
}

Convert your list to a DataTable

    public static DataTable ToDataTable(List<string> list)
    {
        DataTable MethodResult = null;

        DataTable dt = new DataTable();
        dt.Columns.Add("Item", );

        foreach(string s in list)
        {
            DataRow dr = dt.NewRow();
            dr[0] = s;
            dt.Rows.Add(dr);

        }

        dt.AcceptChanges();

        MethodResult = dt;

        return MethodResult;

    }

How do I export a C# object list to an excel spreadsheet?

2 Answers2