本文首发于[《.NET 5/.NET Core中使用EPPlus导出自定义列标题、忽略列、列排序的静态扩展方法》](https://codedefault.com/p/epplus-export-custom-colum-name-excel)

EPPlus是一个.NET/.NET Core中操作Excel表格数据的第三方组件。使用EPPlus你可以在不依赖其它第三方组件的情况下完成对Excel的导入、导出等操作，而且步骤非常简单。相信不少.NET玩家已经体验过EPPlus带来的便捷和高效，所以本文就不再为大家分享EPPlus的具体使用方法(如何使用EPPlus请参考[《C#/.NET使用EPPlus组件导出数据到EXCEL表格并下载到本地的示例》][1])，而是介绍一个为EPPlus定制自定义导出列标题、忽略列以及列排序的静态扩展方法。

## 创建解决方案

首先，创建一个示例解决方案[EpplusDemo]，再分别创建一个共享的.NET 5的类库[EpplusDemo.Shared]和一个测试用的基于.NET 5的Web Api项目[EpplusDemo.WebApi]。

EPPlus的常规导出泛型集合到Excel的方法如下：

```csharp
/// <summary>
/// 导出列表到Excel,返回文件流格式
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sheetName"></param>
/// <param name="list"></param>
/// <returns></returns>
public static Stream ExportToExcel<T>(this List<T> list) where T : class
{
    ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
    using var pck = new ExcelPackage();
    //创建工作表
    var ws = pck.Workbook.Worksheets.Add("sheetName");

//填充数据
    if (list.Count > 0)
    {
        ws.Cells["A1"].LoadFromCollection(list);
    }
    ws.Cells.AutoFitColumns();
    var stream = new MemoryStream();
    pck.SaveAs(stream);
    stream.Position = 0;
    return stream;
}
```

但这个导出Excel的泛型方法功能相对简单，无法自定义一些配置，比如：

* 不能自定义泛型对象的列名，对象的属性名称是什么，导出的列名就是什么；
* 不能忽略不想导出的列，比如不想导出一个泛型对象的某些属性，那我们还得创建一个只包含需要导出列的属性的实体；
* 无法对导出Excel表格的列顺序进行排序

## 创建公共扩展类和方法

下面，我们在项目[EpplusDemo.Shared]中创建一些共享的帮助类，如下：

CdExcelHeader.cs

```csharp
/// <summary>
/// Excel的表头信息
/// </summary>
public class CdExcelHeader
{
    /// <summary>
    /// 列头名称
    /// </summary>
    public string ColumnName { get; set; }
    /// <summary>
    /// 属性名称
    /// </summary>
    public string PropertyName { get; set; }
    /// <summary>
    /// 列的顺序
    /// </summary>
    public int Order { get; set; }
}
```

CdExcelMemberInfo.cs

```c#
public class CdExcelMemberInfo
{
    /// <summary>
    /// 成员信息
    /// </summary>
    public MemberInfo MemberInfo { get; set; }
    /// <summary>
    /// 属性信息
    /// </summary>
    public PropertyInfo PropertyInfo { get; set; }
    /// <summary>
    /// 列名
    /// </summary>
    public string ColumnName { get; set; }
    /// <summary>
    /// 排序
    /// </summary>
    public int Order { get; set; }
}
```

CdExportAttribute.cs

```csharp
/// <summary>
/// 导出表格的特性类
/// </summary>
[AttributeUsage(AttributeTargets.All)]
public class CdExportAttribute : Attribute
{
    /// <summary>
    /// 列名
    /// </summary>
    public string ColumnName { get; set; }
    /// <summary>
    /// 是否忽略此属性
    /// </summary>
    public bool Ignore { get; set; }
    /// <summary>
    /// 列的顺序
    /// </summary>
    public int ColumnOrder { get; set; }
}
```

CdExcelInfo.cs

```csharp
/// <summary>
/// EPPlus导出信息初始化类
/// </summary>
/// <typeparam name="T"></typeparam>
public class CdExcelInfo<T> where T : class
{
    public CdExcelInfo(List<T> list)
    {
        IncludeMembers = new List<MemberInfo>();
        Headers = new List<CdExcelHeader>();
        Init(list);
    }
    /// <summary>
    /// 需要输出的实体对象的成员集合
    /// </summary>
    public List<MemberInfo> IncludeMembers { get; private set; }
    /// <summary>
    /// 表头列的信息集合
    /// </summary>
    public List<CdExcelHeader> Headers { get; private set; }

/// <summary>
    /// 初始化表头和成员集合信息，调用此方法可以过滤掉(不导出)CdExportAttribute中忽略的属性
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="list"></param>
    private void Init(List<T> list) 
    {
        var props = typeof(T).GetProperties(BindingFlags.Instance | BindingFlags.Public);
        IncludeMembers = new List<MemberInfo>();
        Headers = new List<CdExcelHeader>();
        var epcMemberInfoList = new List<CdExcelMemberInfo>();
        foreach (var pi in props)
        {
            var info = new CdExcelMemberInfo
            {
                MemberInfo = pi,
                PropertyInfo = pi,
                Order = 0,
                ColumnName = pi.Name
            };
            var attrs = pi.GetCustomAttributes(typeof(CdExportAttribute), true);
            if (attrs.Length <= 0)
            {
                epcMemberInfoList.Add(info);
                continue;
            }
            var attr = (CdExportAttribute)attrs.FirstOrDefault();
            if (attr == null)
            {
                epcMemberInfoList.Add(info);
                continue;
            }
            if (attr.Ignore)
            {
                continue;
            }

if (!string.IsNullOrEmpty(attr.ColumnName))
            {
                info.ColumnName = attr.ColumnName;
            }
            info.Order = attr.ColumnOrder;
            epcMemberInfoList.Add(info);
        }
        IncludeMembers = epcMemberInfoList.OrderBy(x => x.Order).Select(x => x.MemberInfo).ToList();
        var headers = epcMemberInfoList
            .Select(m => new CdExcelHeader
                    {
                        Order = m.Order,
                        PropertyName = m.PropertyInfo.Name,
                        ColumnName = m.ColumnName
                    }).OrderBy(x => x.Order)
            .ToList();
        Headers.AddRange(headers);
    }
}
```

最后，创建一个EPPlus的静态扩展类:EPPlusExportHelper.cs

```csharp
/// <summary>
/// EPPlus导出帮助扩展类
/// </summary>
public static class EPPlusExportHelper
{
    /// <summary>
    /// 导出列表到Excel,返回文件流格式
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="sheetName"></param>
    /// <param name="list"></param>
    /// <returns></returns>
    public static Stream ExportToExcel<T>(this List<T> list, string sheetName) where T : class
    {
        ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
        using var pck = new ExcelPackage();
        //创建工作表
        var ws = pck.Workbook.Worksheets.Add(sheetName);

var epcExcelInfo = new CdExcelInfo<T>(list);
        for (var i = 0; i < epcExcelInfo.Headers.Count(); i++)
        {
            var header = epcExcelInfo.Headers[i];
            ws.Cells[1, i + 1].Value = header.ColumnName;
        }

//填充数据
        if (list.Count > 0)
        {
            ws.Cells["A2"].LoadFromCollection(list
                                              , false
                                              , OfficeOpenXml.Table.TableStyles.None
                                              , BindingFlags.Instance | BindingFlags.Public
                                              , epcExcelInfo.IncludeMembers.ToArray());
        }
        ws.Cells.AutoFitColumns();
        //格式化表头
        using (var rng = ws.Cells["A1:BZ1"])
        {
            rng.Style.Font.Bold = true;
            rng.Style.Fill.PatternType = ExcelFillStyle.Solid;
        }
        var stream = new MemoryStream();
        pck.SaveAs(stream);
        stream.Position = 0;
        return stream;
    }

/// <summary>
    /// 导出列表到Excel,返回文件流格式
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="sheetName"></param>
    /// <param name="list"></param>
    /// <returns></returns>
    public static Stream ExportToExcel<T>(this List<T> list) where T : class
    {
        ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
        using var pck = new ExcelPackage();
        //创建工作表
        var ws = pck.Workbook.Worksheets.Add("sheetName");

//填充数据
        if (list.Count > 0)
        {
            ws.Cells["A1"].LoadFromCollection(list);
        }
        ws.Cells.AutoFitColumns();
        var stream = new MemoryStream();
        pck.SaveAs(stream);
        stream.Position = 0;
        return stream;
    }

/// <summary>
    /// 从泛型集合中加载Excel数据集的扩展方法
    /// </summary>
    /// <param name="excelRange">EPPlus的ExcelRange实例</param>
    /// <param name="collection">泛型集合</param>
    /// <param name="includedMemberInfoList"></param>
    /// <typeparam name="T"></typeparam>
    /// <returns></returns>
    public static ExcelRangeBase LoadFromCollectionFiltered<T>(this ExcelRangeBase excelRange, IEnumerable<T> collection, MemberInfo[] includedMemberInfoList) where T : class
    {
        return excelRange.LoadFromCollection<T>(collection, false,
                                                OfficeOpenXml.Table.TableStyles.None,
                                                BindingFlags.Instance | BindingFlags.Public,
                                                includedMemberInfoList.ToArray());
    }
    public static ExcelRangeBase LoadFromCollection<T>(this ExcelRangeBase excelRange,
                                                       IEnumerable<T> collection, string[] propertyNames, bool printHeaders) where T : class
    {
        MemberInfo[] membersToInclude = typeof(T)
            .GetProperties(BindingFlags.Instance | BindingFlags.Public)
            .Where(p => propertyNames.Contains(p.Name))
            .ToArray();

return excelRange.LoadFromCollection<T>(collection, printHeaders,
                                                OfficeOpenXml.Table.TableStyles.None,
                                                BindingFlags.Instance | BindingFlags.Public,
                                                membersToInclude);
    }
}
```

## 在WebApi中测试EPPlus的导出功能

在项目[EpplusDemo.WebApi]中创建一个DownloadController.cs控制器，并分别创建两个导出测试，如下：

```csharp
public class DownloadController : ControllerBase
{
    /// <summary>
    /// 不使用扩展方法的原始导出方法
    /// </summary>
    /// <returns></returns>
    [HttpGet]
    [Route("download/export_raw_excel")]
    public IActionResult ExportRawExcel()
    {
        var personService = new PersonService();
        var data = personService.GetPersonList();
        var stream = EPPlusExportHelper.ExportToExcel(data, "个人信息");
        var contentType = "application/octet-stream";
        var fileName = "个人信息表.xlsx";
        return File(stream, contentType, fileName);
    }

/// <summary>
    /// 使用扩展方法的自定义导出
    /// </summary>
    /// <returns></returns>
    [HttpGet]
    [Route("download/export_mapper_excel")]
    public IActionResult ExportExcel()
    {
        var personService = new PersonService();
        var data = personService.GetPersonList();
        var models = data.Select(x => new PersonExportModel
                                 {
                                     Age = x.Age,
                                     Created = x.Created.ToString("yyyy-MM-dd HH:mm:ss"),
                                     Id = x.Id,
                                     FirstName = x.FirstName,
                                     LastName = x.LastName
                                 }).ToList();
        var stream = EPPlusExportHelper.ExportToExcel(models, "个人信息");
        var contentType = "application/octet-stream";
        var fileName = "个人信息表.xlsx";
        return File(stream, contentType, fileName);
    }
}
```

测试用的Person.cs模型

```csharp
public class Person
{
    public Person(int id)
    {
        Id = id;
    }
    /// <summary>
    /// ID
    /// </summary>
    public int Id { get; private set; }
    /// <summary>
    /// First name
    /// </summary>
    public string FirstName { get; set; }
    /// <summary>
    /// Last name
    /// </summary>
    public string LastName { get; set; }
    /// <summary>
    /// Age
    /// </summary>
    public int Age { get; set; }
    /// <summary>
    /// Created date
    /// </summary>
    public DateTime Created { get; set; }
}
```

测试用的自定义导出模型PersonExportModel.cs

```csharp
public class PersonExportModel
{
    /// <summary>
    /// ID
    /// </summary>
    [CdExport(Ignore = true)]
    public int Id { get; set; }
    /// <summary>
    /// First name
    /// </summary>
    [CdExport(ColumnName = "名",ColumnOrder = 1)]
    public string FirstName { get; set; }
    /// <summary>
    /// Last name
    /// </summary>
    [CdExport(ColumnName = "姓",ColumnOrder = 0)]
    public string LastName { get; set; }
    /// <summary>
    /// Age
    /// </summary>
    [CdExport(ColumnName = "年龄",ColumnOrder = 2)]
    public int Age { get; set; }
    /// <summary>
    /// Created date
    /// </summary>
    [CdExport(ColumnName = "创建时间",ColumnOrder = 3)]
    public string Created { get; set; }
}
```

如你所见，我们只需要在导出模型中配置`CdExportAttribute`特性的参数即可，导出的Excel效果对比如下：

![](https://statics.codedefault.com/uploads/u/2020/12/v3nl0pg9st.png)

[1]: https://codedefault.com/p/csharp-dotnet-export-data-to-excel-and-download-to-local

.NET 5/.NET Core中使用EPPlus导出自定义列标题、忽略列、列排序的静态扩展方法

总共已有 0 条评论