Export 之 简单导出excel

选择第三方库

• 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; } }