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

Question

我有一个从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();

- Sophonias

---

你不能将数据导出为简单的CSV文件或XML文件，然后将其导入到预先计算的Excel文件中吗？ - Steve B

你试过这个人的方法吗？跳转到名为"And Here is the method ported for a List<T>. List<object>"的部分。http://www.codeproject.com/Tips/64127/Exporting-a-List-of-any-type-to-Excel - Paul Zahra

Answer 1

也许您可以尝试使用在Nuget中托管的Infodinamica.Framework.Expotable包，与此一起，您可以像这样做：

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

您可以使用NuGet命令进行安装：

Install-Package Infodinamica.Framework.Exportable

唯一的问题是，文档是用西班牙语编写的。

该项目页面在此处：here 导出示例（使用西班牙语）在此处：here 它还支持导入文件（使用西班牙语）here

- Vladimir Venegas

---

优秀的解决方案，它救了我的一天！谢谢。 - JPacheco

Answer 2

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

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;
}

将您的列表转换为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;

    }

- WonderWorker