使用EPPlus实现Excel导出
引言
使用EPPlus导出Excel我们在封装之前,需要明白怎么去根据一个对象(类)获取到其属性和属性的值,当我们提前弄清楚这个东东的话,对我们后续的工作就好开展了!因为EPPlus导出Excel是需要进行行列对应的,不能每导出一个列表,我们都要手动去控制这个对象所需多少行,多少列吧,明显太费事也行不通。所以获取对象(类)获取到其属性和属性的值是我们前提必备的要件!
一、如何获取对象(类)属性和值?
1.1 创建对象(类)
public class FyLogDto
{
/// <summary>
/// 标识
/// </summary>
public string ID { get; set; }
/// <summary>
/// IP
/// </summary>
public string IP { get; set; }
/// <summary>
/// 机器名称
/// </summary>
public string MachineName { get; set; }
/// <summary>
/// 日志类型
/// </summary>
public string Level { get; set; }
/// <summary>
/// 日志内容
/// </summary>
public string Message { get; set; }
/// <summary>
/// 记录器名称
/// </summary>
public string Logger { get; set; }
/// <summary>
/// 调用站点
/// </summary>
public string Callsite { get; set; }
/// <summary>
/// Url
/// </summary>
public string Url { get; set; }
/// <summary>
/// 控制器
/// </summary>
public string Controller { get; set; }
/// <summary>
/// 控制器的的方法
/// </summary>
public string Action { get; set; }
/// <summary>
/// 请求方式:GET,POST,PUT...
/// </summary>
public string Method { get; set; }
/// <summary>
/// 日志记录时间
/// </summary>
public string Logged { get; set; }
}
1.2 获取对象(类)的属性
class Program
{
static void Main(string[] args)
{
FyLog fyLog = new FyLog();
PropertyInfo[] properties = fyLog.GetType().GetProperties();
Console.WriteLine("============对象(类)的属性如下==============");
foreach (var property in properties)
{
Console.WriteLine(property.Name);
}
Console.ReadKey();
}
}
1.3 获取对象(类)的属性值
class Program
{
static void Main(string[] args)
{
FyLog fyLog = new FyLog()
{
Id="ID",
Ip="IP",
Logged=DateTime.Now,
Machinename= "Machinename",
Level="Info",
Message= "Message",
Logger= "Logger",
Callsite= "Callsite",
Module= "Module",
Url="http://42.193.5.236",
Controller= "Controller",
Action="Index",
Method="GET",
Exception= "Exception"
};
PropertyInfo[] properties = fyLog.GetType().GetProperties();
Console.WriteLine("==============获取对象(类)的属性值==============");
foreach (var item in properties)
{
var value = fyLog.GetType().GetProperty(item.Name).GetValue(fyLog);
Console.WriteLine(value);
}
Console.ReadKey();
}
}
二、通过对象(类)的属性和值进行导出
2.1 NuGet:EPPlus
2.2 创建EPPlus导出Excel的辅助类
这里需要注意理解一下的是:EPPlus导出的一个行列顺序,表头:(1,1),(1,2),…,即:第一行第一列,第一行第二列,…,然后呢,第一行数据的起始位置:(2,1),(2,2)…,即:第二行第一列,第二行第二列,接着(3,1),(3,2)…,递推!!!把这个结构理解清楚,使用起EPPlus就显得相当简单了!
public class ExcelHelper<T> where T:class,new()
{
/// <summary>
/// 获取类的属性值
/// </summary>
/// <param name="obj">类</param>
/// <param name="property">属性</param>
/// <returns></returns>
private static object GetPropertyValue(object obj, string property)
{
return obj.GetType().GetProperty(property).GetValue(obj);
}
/// <summary>
/// 获取类的全部属性
/// </summary>
/// <param name="t"></param>
/// <returns></returns>
private static PropertyInfo[] GetProperties(T t)
{
PropertyInfo[] properties = t.GetType().GetProperties();
return properties;
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="fileInfo">FileInfo</param>
/// <param name="tList">数据</param>
/// <returns></returns>
public static async Task OutPutExcel(FileInfo fileInfo, List<T> tList)
{
//指定EPPlus使用非商业化许可证
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using (ExcelPackage package = new ExcelPackage(fileInfo))
{
//工作簿
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
//实体属性
PropertyInfo[] properties = GetProperties(new T());
//填充表头
for (int i = 1; i < properties.Length+1; i++)
{
worksheet.Cells[1, i].Value = properties[i - 1].Name;
}
//填充行(从第二行开始)
for (int i = 2; i < tList.Count + 2; i++)
{
//填充行内列
for (int j = 1; j < properties.Length + 1; j++)
{
var property = properties[j - 1].Name;
worksheet.Cells[i, j].Value = GetPropertyValue(tList[i - 2], property);
}
}
//列宽自适应
worksheet.Cells.AutoFitColumns();
//保存
await package.SaveAsync();
}
}
}
2.3 Web层调用导出
[HttpGet("out")]
public async Task<FileResult> OutPut()
{
string filePath = Directory.GetCurrentDirectory() + "/excels";
if (!Directory.Exists(filePath))
{
Directory.CreateDirectory(filePath);
}
string fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
FileInfo fileInfo = new FileInfo(Path.Combine(filePath, fileName));
var tlist = await _fyLog.GetListAsync();
await ExcelHelper<FyLogDto>.OutPutExcel(fileInfo, tlist);
var stream = System.IO.File.OpenRead(Path.Combine(filePath, fileName));
var provider = new FileExtensionContentTypeProvider();
string contentType = provider.Mappings[Path.GetExtension(fileName)];
return File(stream, contentType, fileName);
}