Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

c#

.net

excel

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();
like image 545
Sophonias Avatar asked Oct 06 '22 13:10

Sophonias


2 Answers

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

like image 162
Vladimir Venegas Avatar answered Oct 10 '22 03:10

Vladimir Venegas


  1. Right-click the project and manage nuget packages.
  2. Add the ClosedXML nuget package
  3. Add using ClosedXML.Excel; to the top of the class file.
  4. Add the 'ToExcelFile' method to your class.
  5. 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;

    }
like image 38
WonderWorker Avatar answered Oct 10 '22 01:10

WonderWorker