c# - 如何从列表中导出 C# 中的数据<object> or IEnumerable to Excel (MS OpenXml)? <div id="body"><p>I'm trying to export a <code>List<dynamic></code> or <code>IEnumerable</code> to an Excel file using the library Open XML of Microsoft.</p> <p>Library: <a href="https:

仅供参考，这是我的实现，使用@mikesknowledgebase 的代码（http://www.mikesknowledgebase.com - 网页不起作用......但至少要给予学分：D）

在此处发布所有信息

所以，我将它用于 .Net Core 2.2；目的是使用该方法将 a 导出List<dynamic>到Excel.

最终结果（在最简单的例子中）：

[HttpGet("[action]")]
public FileResult exportExample()
{
    List<dynamic> data = new List<dynamic>();

    data.Add(new { a = 1, b = "HELLO WORLD", c = DateTime.Now });
    data.Add(new { a = 2, b = "TEST", c = 34 });

    // Implementation of Dictionary to limit the columns and the type of the List
    // Works with some standard, and flat files (Not with Dynamic Objects that have multiples levels - Indentation of a dynamic into a property)
    Dictionary<string, Type> columns = new Dictionary<string, Type>();
    columns.Add("a", typeof(int));
    columns.Add("b", typeof(string));
    columns.Add("c", typeof(object)); // Accepts any (Numbers or DateTime)

    string excelContentType;
    var excelStream = CreateExcelFile.CreateExcelStream(data, columns, out excelContentType);

    return File(excelStream, excelContentType, "report.xlsx");
}

我在 Mike 的类中创建了一些其他方法......

获取列的方法List...Dictionary<string, type>和另一个返回的参数ContentType：

public static byte[] CreateExcelStream<T>(List<T> list, Dictionary<string, Type> columns, out string contentType )
{
    DataSet ds = new DataSet();

    ds.Tables.Add(ListToDataTable(list, columns));
    contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; // "application/vnd.ms-excel";

    return CreateExcelStream(ds).fwToByteArray();
}

另一种将转换List为 a 的方法DataTable：

public static DataTable ListToDataTable<dynamic>(List<dynamic> list, Dictionary<string, Type> columns)
{
    DataTable dt = new DataTable();

    foreach (var column in columns)
        dt.Columns.Add(new DataColumn(column.Key, GetNullableType(column.Value)));

    foreach (var t in list)
    {
        DataRow row = dt.NewRow();

        ((object)t)
        .GetType()
        .GetProperties()
        .ToList()
        .ForEach(p =>
        {
            if (!IsNullableType(p.PropertyType))
                row[p.Name] = p.GetValue(t, null);
            else
                row[p.Name] = (p.GetValue(t, null) ?? DBNull.Value);
        });
        dt.Rows.Add(row);
    }
    return dt;
}

我包括一个将 a 转换stream为 a的扩展byteArray：

public static byte[] fwToByteArray(this Stream stream)
{
    stream.Position = 0;
    byte[] buffer = new byte[stream.Length];

    for (int totalBytesCopied = 0; totalBytesCopied < stream.Length;)
        totalBytesCopied += stream.Read(buffer, totalBytesCopied, Convert.ToInt32(stream.Length) - totalBytesCopied);

    return buffer;
}

其余代码仍然相同......结果如下：

c# - 如何从列表中导出 C# 中的数据

问题描述

How to find an empty cell in a row and return corresponding column header?

解决方案

推荐阅读