core使⽤Epplus导出excel（⽀持多表头，⾏列合并）
先来简单介绍下市⾯上最⼴泛常见的三种操作excel库的优缺点
1.NPOI
优点：免费开源，⽆需装Office即可操作excel, ⽀持处理的⽂件格式包括xls, xlsx, docx.格式
缺点：不⽀持⼤数据量以及多sheet的导出
2.Aspose.Cells
优点：⽀持⼤数据量以及多sheet的导出，提供了应有尽有的⽂件格式⽀持，速度快性能佳
缺点：除了收费⼏乎没有缺点，试⽤版 限制打开⽂件数量100个，限制使⽤Aspose.Cells.GridWeb功能，⽣成的Excel会有⽔印
3.EPPlus
优点：开源免费，不需要安装office，⽀持图表的列印，导⼊导出速度快，⽀持⾼版本Excel格式，可以实现Excel上的各种基本功能唯⼀缺点：仅⽀持xlsx格式，不⽀持古⽼的xlsx
基于业务需求和各⼤库优缺点对⽐，尽量选择合适业务需求的库，个⼈⽐较推荐的是EPPlus
本⽂使⽤的是EPPlus包来实现数据的导出，因为5.0以上的版本需要商业授权码，所以使⽤的是4.5.3.3的的版本
项⽬也是基于最新版本的 core 3.1 web api
右键管理NuGet包添加EPPlus 选择版本添加项⽬引⽤
然后代码附上
创建excel导⼊帮助类Export2Excel.cs，为了使所有的地⽅通⽤，通过list泛型参数 传⼊数据源以及需要导出的字段标题，返回byte[]，以便直接写⼊⽂件流，也提供了基于DataTable 的操作
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using Newtonsoft.Json;
using OfficeOpenXml;
using OfficeOpenXml.Style;
namespace Common.Utils
{
public class Export2Excel
{
/// <summary>
/// ⽣成excel
/
// </summary>
/// <param name="dtSource">数据源</param>
/// <param name="title">标题(Sheet名)</param>
/// <param name="showTitle">是否显⽰</param>
/// <returns></returns>
public static MemoryStream Export(DataTable dtSource,string title,bool showTitle =true)
{
using(ExcelPackage package =new ExcelPackage())
{
ExcelWorksheet workSheet = package.Workbook.Worksheets.Add(title);
int maxColumnCount = dtSource.Columns.Count;
int curRowIndex =0;
if(showTitle ==true)
{
{
curRowIndex++;
//主题
workSheet.Cells[curRowIndex,1,1, maxColumnCount].Merge =true;
workSheet.Cells[curRowIndex,1].Value = title;
var headerStyle = workSheet.Workbook.Styles.CreateNamedStyle("headerStyle");
headerStyle.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
headerStyle.Style.Font.Bold =true;
headerStyle.Style.Font.Size =20;
workSheet.Cells[curRowIndex,1].StyleName ="headerStyle";
curRowIndex++;
//导出时间栏
workSheet.Cells[curRowIndex,1,2, maxColumnCount].Merge =true;
workSheet.Cells[curRowIndex,1].Value ="导出时间："+ DateTime.Now.ToString("yyyy-MM-dd HH:mm");
workSheet.Cells[curRowIndex,1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
}
curRowIndex++;
var titleStyle = workSheet.Workbook.Styles.CreateNamedStyle("titleStyle");
titleStyle.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
titleStyle.Style.Font.Bold =true;
//标题
for(var i =0; i < maxColumnCount; i++)
{
param nameDataColumn column = dtSource.Columns[i];
workSheet.Cells[curRowIndex, i +1].Value = column.ColumnName;
workSheet.Cells[curRowIndex, i +1].StyleName ="titleStyle";
}
workSheet.View.FreezePanes(curRowIndex,1);//冻结标题⾏
//内容
for(var i =0; i < dtSource.Rows.Count; i++)
{
curRowIndex++;
for(var j =0; j < maxColumnCount; j++)
{
DataColumn column = dtSource.Columns[j];
var row = dtSource.Rows[i];
object value= row[column];
var cell = workSheet.Cells[curRowIndex, j +1];
var pType = column.DataType;
pType = pType.Name =="Nullable`1"? Nullable.GetUnderlyingType(pType): pType;
if(pType ==typeof(DateTime))
{
cell.Style.Numberformat.Format ="yyyy-MM-dd hh:mm";
cell.Value = Convert.ToDateTime(value);
}
else if(pType ==typeof(int))
{
cell.Value = Convert.ToInt32(value);
}
else if(pType ==typeof(double)|| pType ==typeof(decimal))
{
cell.Value = Convert.ToDouble(value);
}
else
{
cell.Value =value==null?"":value.ToString();
}
workSheet.Cells[curRowIndex, j +1].Value = row[column].ToString();
}
}
workSheet.Cells[workSheet.Dimension.Address].Style.Font.Name ="宋体";
workSheet.Cells[workSheet.Dimension.Address].AutoFitColumns();//⾃动填充
for(var i =1; i <= workSheet.Dimension.End.Column; i++){ workSheet.Column(i).Width = workSheet.Column(i).Width +2;}//在填充的基础上再加2
for(var i =1; i <= workSheet.Dimension.End.Column; i++){ workSheet.Column(i).Width = workSheet.Column(i).Width +2;}//在填充的基础上再加2              MemoryStream ms =new MemoryStream(package.GetAsByteArray());
return ms;
}
}
/// <summary>
/// ⽣成excel
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="dtSource">数据源</param>
/// <param name="columns">导出字段表头合集</param>
/// <param name="title">标题(Sheet名)</param>
/// <param name="showTitle">是否显⽰标题</param>
/// <returns></returns>
public static byte[]Export<T>(IList<T> dtSource, ExportColumnCollective columns,string title,bool showTitle =true)
{
using(ExcelPackage package =new ExcelPackage())
{
ExcelWorksheet workSheet = package.Workbook.Worksheets.Add(title);
int maxColumnCount = columns.ExportColumnList.Count;
int curRowIndex =0;
//Excel标题
if(showTitle ==true)
{
curRowIndex++;
workSheet.Cells[curRowIndex,1,1, maxColumnCount].Merge =true;
workSheet.Cells[curRowIndex,1].Value = title;
var headerStyle = workSheet.Workbook.Styles.CreateNamedStyle("headerStyle");
headerStyle.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
headerStyle.Style.Font.Bold =true;
headerStyle.Style.Font.Size =20;
workSheet.Cells[curRowIndex,1].StyleName ="headerStyle";
curRowIndex++;
/
/导出时间
workSheet.Cells[curRowIndex,1,2, maxColumnCount].Merge =true;
workSheet.Cells[curRowIndex,1].Value ="导出时间："+ DateTime.Now.ToString("yyyy-MM-dd HH:mm");
workSheet.Cells[curRowIndex,1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
}
//数据表格标题(列名)
for(int i =0, rowCount = columns.HeaderExportColumnList.Count; i < rowCount; i++)
{
curRowIndex++;
workSheet.Cells[curRowIndex,1, curRowIndex, maxColumnCount].Style.Font.Bold =true;
var curColSpan =1;
for(int j =0, colCount = columns.HeaderExportColumnList[i].Count; j < colCount; j++)
{
var colColumn = columns.HeaderExportColumnList[i][j];
var colSpan =FindSpaceCol(workSheet, curRowIndex, curColSpan);
if(j ==0) curColSpan = colSpan;
var toColSpan = colSpan + colColumn.ColSpan;
var cell = workSheet.Cells[curRowIndex, colSpan, colColumn.RowSpan + curRowIndex, toColSpan];
cell.Merge =true;
cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
workSheet.Cells[curRowIndex, colSpan].Value = colColumn.Title;
curColSpan += colColumn.ColSpan;
}
}
workSheet.View.FreezePanes(curRowIndex +1,1);//冻结标题⾏
Type type =typeof(T);
Type type =typeof(T);
PropertyInfo[] propertyInfos = type.GetProperties();
if(propertyInfos.Count()==0&& dtSource.Count >0) propertyInfos = dtSource[0].GetType().GetProperties();
//数据⾏
for(int i =0, sourceCount = dtSource.Count(); i < sourceCount; i++)
{
curRowIndex++;
for(var j =0; j < maxColumnCount; j++)
{
var column = columns.ExportColumnList[j];
var cell = workSheet.Cells[curRowIndex, j +1];
foreach(var propertyInfo in propertyInfos)
{
if(column.Field == propertyInfo.Name)
{
object value= propertyInfo.GetValue(dtSource[i]);
var pType = propertyInfo.PropertyType;
pType = pType.Name =="Nullable`1"? Nullable.GetUnderlyingType(pType): pType;
if(pType ==typeof(DateTime))
{
cell.Style.Numberformat.Format ="yyyy-MM-dd hh:mm";
cell.Value = Convert.ToDateTime(value);
}
else if(pType ==typeof(int))
{
cell.Style.Numberformat.Format ="#0";
cell.Value = Convert.ToInt32(value);
}
else if(pType ==typeof(double)|| pType ==typeof(decimal))
{
if(column.Precision !=null) cell.Style.Numberformat.Format ="#,##0.00";//保留两位⼩数
cell.Value = Convert.ToDouble(value);
}
else
{
cell.Value =value==null?"":value.ToString();
}
}
}
}
}
workSheet.Cells[workSheet.Dimension.Address].Style.Font.Name ="宋体";
workSheet.Cells[workSheet.Dimension.Address].AutoFitColumns();//⾃动填充
for(var i =1; i <= workSheet.Dimension.End.Column; i++){ workSheet.Column(i).Width = workSheet.Column(i).Width +2;}//在填充的基础上再加2
return package.GetAsByteArray();
}
}
private static int FindSpaceCol(ExcelWorksheet workSheet,int row,int col)
{
if(workSheet.Cells[row, col].Merge)
{
return FindSpaceCol(workSheet, row, col +1);
}
return col;
}
}
//导出所需要映射的字段和表头集合
public class ExportColumnCollective
{
/// <summary>
/// 字段列集合
/
// 字段列集合
/// </summary>
public List<ExportColumn> ExportColumnList {get;set;}
/// <summary>
/// 表头或多表头集合
/// </summary>
public List<List<ExportColumn>> HeaderExportColumnList {get;set;}
}
//映射excel实体
public class ExportColumn
{
/
// <summary>
/// 标题
/// </summary>
[JsonProperty("title")]
public string Title {get;set;}
/// <summary>
/// 字段
/// </summary>
[JsonProperty("field")]
public string Field {get;set;}
/// <summary>
/
// 精度(只对double、decimal有效)
/// </summary>
[JsonProperty("precision")]
public int? Precision {get;set;}
/// <summary>
/// 跨列
/// </summary>
[JsonProperty("colSpan")]
public int ColSpan {get;set;}
/// <summary>
/// 跨⾏
/
// </summary>
[JsonProperty("rowSpan")]
public int RowSpan {get;set;}
}
}
OK，有了通⽤帮助类库，剩下的就是针对具体业务所需⽽提供相应字段和表头的隐射，既可以实现⽂件的导出别忘了添加引⽤命名空间using Common.Utils;
我们来看⼀下API

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。