如何将 C# 对象列表导出到 Excel 电子表格？

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

提问by Sophonias

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.

我有一个从 Excel 电子表格创建的 C# 列表，我想将其导出到 Excel。我怎样才能完成这个任务？这只是一个控制台项目。我不打算在 .Net 应用程序中显示数据。我只需要电子表格。

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();

采纳答案by Vladimir Venegas

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

也许您可以尝试使用托管在 Nuget 中的 Infodinamica.Framework.Expotable 包。

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:

您可以使用 nuget 命令安装：

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

它还在此处启用导入文件（西班牙语）

回答by WonderWorker

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.

1. 右键单击项目并管理 nuget 包。
2. 添加 ClosedXML nuget 包
3. 添加using ClosedXML.Excel;到类文件的顶部。
4. 将“ToExcelFile”方法添加到您的类中。
5. 将您的列表转换为数据表。

ToExcelFile method

ToExcelFile 方法

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;

    }