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
– Steve B, Commented Sep 24, 2012 at 15:48
Did you try this guys approach? goto the section entitled "And Here is the method ported for a List<T>. List<object>" codeproject.com/Tips/64127/… — BLoB
– BLoB, Commented Sep 24, 2012 at 15:53

Vladimir Venegas · Accepted Answer · 2016-06-22 15:30:33Z

3

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

answered Jun 22, 2016 at 15:30

Vladimir Venegas

4,2337 gold badges31 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JPacheco Over a year ago

Excelllent solution, it saved my day ! Thx.

WonderWorker · Accepted Answer · 2017-05-08 07:26:21Z

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;

    }

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related