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