如何将C＃对象列表导出到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.

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

2 个解决方案

#1

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

它还可以在此处启用导入文件(西班牙语)

#2

1. Right-click the project and manage nuget packages.

右键单击项目并管理nuget包。

2. Add the ClosedXML nuget package

添加ClosedXML nuget包

3. Add using ClosedXML.Excel; to the top of the class file.

使用ClosedXML.Excel添加;到类文件的顶部。

4. Add the 'ToExcelFile' method to your class.

将“ToExcelFile”方法添加到您的班级。

5. Convert your list to a DataTable.

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

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

    }