选择第三方库

• Epplus : 5.0后出现授权问题 out
• Magicodes.IE : 基于Epplush 4.x , IExporterHeaderFilter 这个的性能很渣，而且动态调整order会出现顺序错乱 out
• NPOI : 个人不是太喜欢
• ClosedXML : 基于 Document.OpenXML ,性能不错但是需要自己封装一些业务功能 i like this

简单导出功能

//导出
protected override void Action()
{
    //创建workbook
    var wb = new XLWorkbook(XLEventTracking.Disabled);
    wb.Worksheets.Add("test", Datas);

    wb.SaveAs($"{Guid.NewGuid()}.xlsx");
}


internal static class ClosedXMLExtension
{
    //添加IXLWorksheet扩展  你也可以基于函数封装
    public static IXLWorksheet Add<T>(this IXLWorksheets wss, string sheetName, IEnumerable<T> datas)
    {
        var ws = wss.Add(sheetName);
        var props = typeof(T).GetProperties();
        int row = 1, col = 1;
        
        foreach (var data in datas)
        {
            foreach (var prop in props)
            {
                ws.Cell(row, col).Value = prop.GetValue(data);
                col++;
            }
            col = 1;
            row++;
        }
        return ws;
    }
}

进一步封装

说明

• 整体使用反射获取值向cell中填充
• header可以动态控制，但是控制流实现的比较简单
• 使用model字段的小写与动态header进行匹配

public static class ClosedXMLExtension
{
    /// <summary>
    /// 向单个sheet填充数据
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="wss">IXLWorksheets</param>
    /// <param name="sheetName">sheet 名称(需要唯一)</param>
    /// <param name="dataSource">数据源</param>
    /// <returns></returns>
    public static IXLWorksheet Add<T>(this IXLWorksheets wss, string sheetName, IEnumerable<T> dataSource, Dictionary<string, DynamicHeader> headers = null)
    {
        if (dataSource == null)
            throw new ArgumentNullException(nameof(dataSource), "datasource cant be null");
        if (string.IsNullOrEmpty(sheetName))
            throw new ArgumentNullException(nameof(dataSource), "sheet's name cant be null");

        //创建sheet
        var ws = wss.Add(sheetName);

        //如果header中没有数据，直接返回
        if (headers != null && !headers.Any())
            return ws;

        var props = typeof(T).GetProperties();
        headers = ChangeDynamicHeadersOrder(headers);

        AddHeader(ws, headers == null ?
            props.Select(x => x.Name) :
            headers.Select(x => x.Value.DisplayName));

        int row = 2, col = 1;

        foreach (var data in dataSource)
        {
            foreach (var prop in props)
            {
                if (headers == null)
                {
                    ws.Cell(row, col).Value = prop.GetValue(data);
                    col++;
                }
                else if (headers.TryGetValue(prop.Name.ToLower(), out var dynamicHeader))
                    ws.Cell(row, dynamicHeader.Index).Value = prop.GetValue(data);
            }

            col = 1;
            row++;
        }

        return ws;
    }

    /// <summary>
    /// 添加表格头
    /// </summary>
    /// <param name="ws"></param>
    /// <param name="headerNames"></param>
    private static void AddHeader(IXLWorksheet ws, IEnumerable<string> headerNames)
    {
        int row = 1, col = 1;
        foreach (var name in headerNames)
        {
            var cell = ws.Cell(row, col);
            cell.Value = name;
            cell.Style.Font.Bold = true;
            cell.Style.Fill.SetBackgroundColor(XLColor.LightBlue);

            col++;
        }
    }

    /// <summary>
    /// 修改header的顺序防止数据重叠
    /// </summary>
    /// <param name="headers"></param>
    /// <returns></returns>
    private static Dictionary<string, DynamicHeader> ChangeDynamicHeadersOrder(Dictionary<string, DynamicHeader> headers)
    {
        if (headers == null) return null;

        var temp = headers
            .OrderBy(x => x.Value.Index)
            .Select((x, index) =>
                new KeyValuePair<string, DynamicHeader>(
                    x.Key.ToLower(),
                    new DynamicHeader() { Index = index + 1, DisplayName = x.Value.DisplayName }
                ));

        return temp.ToDictionary(x => x.Key, x => x.Value);
    }
}

public class DynamicHeader
{
    /// <summary>
    /// 序号
    /// </summary>
    public int Index { get; set; }

    /// <summary>
    /// Header显示名称
    /// </summary>
    public string DisplayName { get; set; }
}