本文介绍如何使用EPPlus库进行Excel文件的高效导出，包括单表头和多表头的数据导出，并提供了一个完整的.NET Core Web API示例。
先来简单介绍下市面上最广泛常见的三种操作excel库的优缺点
1.NPOI
优点:免费开源，无需装Office即可操作excel, 支持处理的文件格式包括xls, xlsx, docx.格式
缺点：对大数据量以及多sheet的导出支持很不友好
2.Aspose.Cells
优点：支持大数据量以及多sheet的导出，提供了应有尽有的文件格式支持，速度快性能佳
缺点：除了收费几乎没有缺点，试用版 限制打开文件数量100个，限制使用Aspose.Cells.GridWeb功能，生成的Excel会有水印
3.EPPlus
优点：开源免费，不需要安装office，支持图表的列印，导入导出速度快，支持高版本Excel格式，可以实现Excel上的各种基本功能
唯一缺点：仅支持xlsx格式，不支持古老的xls
基于业务需求和各大库优缺点对比，尽量选择合适业务需求的库，个人比较推荐的是EPPlus
本文使用的是EPPlus包来实现数据的导出，因为5.0以上的版本需要商业授权码，所以使用的是4.5.3.3的的版本
项目也是基于最新版本的.net 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++)
{
DataColumn 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
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);
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
[HttpGet("ExportExcel")
public FileResult ExportExcel()
{
IList<Gogo> list = new List<Gogo>
{
new Gogo
{
Name = "张三",
Age = 18,
Card = "41234567890",
CreateTime = DateTime.Now,
},
new Gogo
{
Name = "李四",
Age = 20,
Card = "4254645461",
CreateTime = DateTime.Now,
},
};
//导出表头和字段集合
ExportColumnCollective ecc = new ExportColumnCollective();
//导出字段集合
ecc.ExportColumnList = new List<ExportColumn>
{
new ExportColumn{Field = "Name"},
new ExportColumn{Field = "Card"},
new ExportColumn{Field = "Age"},
new ExportColumn{Field = "CreateTime"},
};
//导出表头集合
ecc.HeaderExportColumnList = new List<List<ExportColumn>>
{
//使用list是为了后续可能有多表头合并列的需求，这里只需要单个表头所以一个list就ok了
new List<ExportColumn>
{
new ExportColumn{Title = "姓名"},
new ExportColumn{Title = "身份号"},
new ExportColumn{Title = "年龄"},
new ExportColumn{Title = "添加时间"}
},
//new List<ExportColumn>
//{
// new ExportColumn{Title = "子标题A",ColSpan = 1},
// new ExportColumn{Title = "子标题B",ColSpan = 1}
//},
};
byte[] result = Export2Excel.Export<Gogo>(list, ecc, "测试导出", false);
return File(result, "application/vnd.ms-excel", "导出报表.xlsx");
}
最后通过路由地址访问api,浏览器会自动弹窗下载文件，打开后表格所示如图
导出多表头如下
扫一扫
3376
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
