.NET导出示例【代码可直接使用】

话不多说，上码

查询结果集中对Model中的字段加属性

[Description("字段中文注释")]
public string Name { get; set; }

调用代码

 1 //导出EXCEL
 2         public MemoryStream ExportList()
 3         {
 4             var list = new List<T>();
 5             try
 6             {
 7                 Model input = new Model();
 8                 list = GetList(input);
 9                 return ExportHelper<T>.Export(list);
10             }
11             catch (Exception e)
12             {
13 
14                 throw;
15             }
16         }

类源码

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace Example
{
    /// <summary>
    /// 导出
    /// </summary>
    public static class ExportHelper<T>
    {
        /// <summary>
        /// 导出实现类
        /// </summary>
        /// <param name="stream"></param>
        /// <param name="dataList"></param>
        /// <param name="keyValues"></param>
        /// <returns></returns>
        public static MemoryStream Export(List<T> dataList)
        {
            MemoryStream stream = new MemoryStream();
            var keyValues = GetColsInformation(dataList.FirstOrDefault());//获取列信息
            using (var oxExt = new ExcelExport(stream))
            {
                oxExt.Write<T>(dataList, keyValues);
            }
            stream.Position = 0;
            return stream;
        }

        /// <summary>
        /// 获取列信息
        /// </summary>
        /// <returns></returns>
        public static Dictionary<string, string> GetColsInformation(object obj)
        {
            Dictionary<string, string> result = new Dictionary<string, string>();
            Type t = obj.GetType();//获取对象类型
            PropertyInfo[] info = t.GetProperties();//获取对象的属性，属性必须带有set和get，否则获取不到
            foreach (PropertyInfo item in info)
            {
                var description = item.GetCustomAttribute<DescriptionAttribute>()?.Description;
                if (!string.IsNullOrWhiteSpace(description))
                    result.Add(item.Name, item.GetCustomAttribute<DescriptionAttribute>()?.Description);
            }
            return result;
        }

        public class ColsInformation
        {
            //public ColsInformation();

            public string ColName { get; set; }
            public string ColShowName { get; set; }
            public Dictionary<string, string> ColValues { get; set; }
        }


        /// 导出Excel
        /// </summary>
        public class ExcelExport : IDisposable
        {

            string sheetName = "sheet1";
            SpreadsheetDocument xl;
            OpenXmlWriter oxw;
            WorksheetPart wsp;
            /// <summary>
            /// 导出Excel
            /// </summary>
            /// <param name="path">Excel文件名称(全路径)</param>
            /// <param name="rowCount">表格列数量</param>
            /// <param name="sheetName">表格名称</param>

            public ExcelExport(MemoryStream memoryStream, string sheetName = "sheet1")
            {
                this.sheetName = sheetName ?? "sheet1";
                xl = SpreadsheetDocument.Create(memoryStream, SpreadsheetDocumentType.Workbook);
                xl.AddWorkbookPart();
                wsp = xl.WorkbookPart.AddNewPart<WorksheetPart>();
                oxw = OpenXmlWriter.Create(wsp);
                oxw.WriteStartElement(new Worksheet());
                oxw.WriteStartElement(new SheetData());
            }
            /// <summary>
            /// 写入表格数据
            /// </summary>
            /// <typeparam name="T"></typeparam>
            /// <param name="items">数据</param>
            /// <param name="keyValues">表头对应数据的列名</param>
            public void Write<T>(List<T> items, Dictionary<string, string> keyValues)
            {

                if (items == null || items.Count == 0) return;
                oxw.WriteStartElement(new Row());

                var tb = new DataTable(typeof(T).Name);
                PropertyInfo[] props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
                // 表头
                foreach (PropertyInfo prop in props)
                {
                    if (string.IsNullOrWhiteSpace(prop.Name)) continue;
                    Type t = GetCoreType(prop.PropertyType);
                    tb.Columns.Add(prop.Name, t);

                    if (!keyValues.ContainsKey(prop.Name)) continue;

                    string name = keyValues[prop.Name];

                    var oxa = new List<OpenXmlAttribute>();

                    oxa.Add(new OpenXmlAttribute("t", null, "str"));

                    oxw.WriteStartElement(new Cell() { DataType = CellValues.InlineString }, oxa);

                    oxw.WriteElement(new CellValue($"{name}"));

                    oxw.WriteEndElement();
                }
                oxw.WriteEndElement();
                // 内容
                foreach (T item in items)
                {
                    oxw.WriteStartElement(new Row());

                    foreach (PropertyInfo prop in props)
                    {
                        if (!keyValues.ContainsKey(prop.Name)) continue;
                        var oxa = new List<OpenXmlAttribute>();

                        oxa.Add(new OpenXmlAttribute("t", null, "str"));

                        oxw.WriteStartElement(new Cell() { DataType = CellValues.InlineString }, oxa);

                        oxw.WriteElement(new CellValue($"{ prop.GetValue(item)}"));

                        oxw.WriteEndElement();
                    }

                    oxw.WriteEndElement();
                }
            }


            /// <summary>
            /// Return underlying type if type is Nullable otherwise return the type
            /// </summary>
            private static Type GetCoreType(Type t)
            {
                if (t != null && IsNullable(t))

                {
                    if (!t.IsValueType)
                    {
                        return t;
                    }
                    else
                    {
                        return Nullable.GetUnderlyingType(t);
                    }
                }
                else
                {
                    return t;
                }
            }
            /// <summary>
            /// Determine of specified type is nullable
            /// </summary>
            private static bool IsNullable(Type t)
            {
                return !t.IsValueType || (t.IsGenericType && t.GetGenericTypeDefinition() == typeof(Nullable<>));
            }
            void Close()
            {
                // this is for SheetData
                oxw.WriteEndElement();
                // this is for Worksheet
                oxw.WriteEndElement();
                oxw.Close();
                oxw = OpenXmlWriter.Create(xl.WorkbookPart);
                oxw.WriteStartElement(new Workbook());
                oxw.WriteStartElement(new Sheets());
                // you can use object initialisers like this only when the properties
                // are actual properties. SDK classes sometimes have property-like properties
                // but are actually classes. For example, the Cell class has the CellValue
                // "property" but is actually a child class internally.
                // If the properties correspond to actual XML attributes, then you're fine.
                oxw.WriteElement(new Sheet()
                {
                    Name = sheetName,
                    SheetId = 1,
                    Id = xl.WorkbookPart.GetIdOfPart(wsp)
                });
                // this is for Sheets
                oxw.WriteEndElement();
                // this is for Workbook
                oxw.WriteEndElement();
                oxw.Close();
                xl.Close();
            }
            #region IDisposable Support
            private bool disposedValue = false; // 要检测冗余调用
            protected virtual void Dispose(bool disposing)
            {
                if (!disposedValue)
                {
                    if (disposing)
                    {
                        // TODO: 释放托管状态(托管对象)。
                        Close();
                    }
                    // TODO: 释放未托管的资源(未托管的对象)并在以下内容中替代终结器。
                    // TODO: 将大型字段设置为 null。
                    disposedValue = true;
                }
            }
            // TODO: 仅当以上 Dispose(bool disposing) 拥有用于释放未托管资源的代码时才替代终结器。
            // ~OpenXmlExt() {
            //   // 请勿更改此代码。将清理代码放入以上 Dispose(bool disposing) 中。
            //   Dispose(false);
            // }
            // 添加此代码以正确实现可处置模式。
            void IDisposable.Dispose()
            {
                // 请勿更改此代码。将清理代码放入以上 Dispose(bool disposing) 中。
                Dispose(true);
                // TODO: 如果在以上内容中替代了终结器，则取消注释以下行。
                // GC.SuppressFinalize(this);
            }
            #endregion
        }
    }
}