.Net webapi MiniExcel生成Excel
百里泓
MiniExcel 生成 Excel 并且控制器导出
本文章使用.net6 涉及反射 ORM框架使用SqlSugar
/// <summary>
/// Excel帮助类
/// </summary>
public class ExcelHelper
{
/// <summary>
/// 生成Excel 已知类型
/// </summary>
/// <typeparam name="T">数据库查询实体得类型</typeparam>
/// <param name="sheetName">sheet名</param>
/// <param name="lists">数据源</param>
public static string GenerateExcel<T>(List<T> lists) where T : class
{
//如果当前得泛型集合为0 说明数据库中没有数据 直接返回
//获取随机文件名
string fileName = $"{Path.GetRandomFileName()}.xlsx";
var path = $@"{AppDomain.CurrentDomain.BaseDirectory}TempExcel\{fileName}";
//MiniExcel.SaveAs(path, lists);
if (lists.Count == 0)
{
MiniExcel.SaveAs(path, null, true, "data", ExcelType.XLSX);
return path;
}
//判断可以写几个工作表 按一个65536行算
int count = lists.Count;
var page = Math.Ceiling((double)count / 65536);
var sheets = new Dictionary<string, object>();
//生成工作表和数据得字典
for (int i = 0; i < page; i++)
{
var data = lists.GetRange(i * 65535, 65535 - 1 * (i + 1));
sheets.Add($"sheet{i}", data);
}
//循环便利生成字典
var dictionarys = new List<Dictionary<string, object>>();
foreach (var item in lists)
{
Type type = item.GetType();
//获取T得所有字段名
var properties = type.GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);
var dictionary = new Dictionary<string, object>();
//循环 properties 拿出他得字段名和value
foreach (var property in properties)
{
//当前得属性名
var name = property.Name;
//根据名字获取到当前循环得item(属性名) 得值
var vao = property.GetValue(item);
dictionary.Add(name, vao);
}
dictionarys.Add(dictionary);
}
//生成EXCEL
var config = new OpenXmlConfiguration()
{
TableStyles = TableStyles.Default
};
MiniExcel.SaveAsAsync(path, sheets, configuration: config);
return path;
}
/// <summary>
/// 生成备份模板 已知类型
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public static string GenerateExcelTemp<T>() where T : class
{
string fileName = $"{Path.GetRandomFileName()}.xlsx";
var path = $@"{AppDomain.CurrentDomain.BaseDirectory}TempExcel\{fileName}";
Type type = typeof(T);
var properties = type.GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);
var dictionary = new Dictionary<string, object>();
foreach (var property in properties)
{
var name = property.Name;
dictionary.Add(name, "");
}
var dictionarys = new List<Dictionary<string, object>>();
dictionarys.Add(dictionary);
MiniExcel.SaveAs(path, dictionarys, true, "sheet1", ExcelType.XLSX);
return path;
}
/// <summary>
/// 不落地生成Excel模板
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public static MemoryStream GenerateExcelTempStream<T>() where T : class
{
Type type = typeof(T);
var properties = type.GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);
var dictionary = new Dictionary<string, object>();
foreach (var property in properties)
{
var name = property.Name;
dictionary.Add(name, "由此往下填充即可");
}
var dictionarys = new List<Dictionary<string, object>>();
dictionarys.Add(dictionary);
var memoryStream = new MemoryStream();
memoryStream.SaveAs(dictionarys, sheetName: "temp");
memoryStream.Seek(0, SeekOrigin.Begin);
return memoryStream;
}
/// <summary>
/// 不落地生成Excel备份文件
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public static MemoryStream GenerateExcelStream<T>(List<T> lists) where T : class
{
var type = typeof(T);
var properties = type.GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);
var memoryStream = new MemoryStream();
if (lists.Count == 0)
{
var _dictionarys = new List<Dictionary<string, object>>();
var dictionary = new Dictionary<string, object>();
foreach (var property in properties)
{
//当前得属性名
var name = property.Name;
//根据名字获取到当前循环得item(属性名) 得值
dictionary.Add(name, "无数据");
}
_dictionarys.Add(dictionary);
memoryStream.SaveAs(_dictionarys, sheetName: "data0");
memoryStream.Seek(0, SeekOrigin.Begin);
return memoryStream;
}
//判断可以写几个工作表 按一个65536行算
int count = lists.Count;
var page = Math.Ceiling((double)count / 65536);
var sheets = new Dictionary<string, object>();
//生成工作表和数据得字典
for (int i = 0; i < page; i++)
{
if (count >= 65535 - 1 * (i + 1))
{
var data = lists.GetRange(i * 65535, 65535 - 1 * (i + 1));
sheets.Add($"sheet{i}", data);
}
else
{
var data = lists.GetRange(i * 65535, count );
sheets.Add($"sheet{i}", data);
}
}
//循环便利生成字典
var dictionarys = new List<Dictionary<string, object>>();
foreach (var item in lists)
{
//获取T得所有字段名
var dictionary = new Dictionary<string, object>();
//循环 properties 拿出他得字段名和value
foreach (var property in properties)
{
//当前得属性名
var name = property.Name;
//根据名字获取到当前循环得item(属性名) 得值
var vao = property.GetValue(item);
dictionary.Add(name, vao);
}
dictionarys.Add(dictionary);
}
//生成EXCEL
var config = new OpenXmlConfiguration()
{
TableStyles = TableStyles.Default
};
//MiniExcel.SaveAsAsync(path, sheets, configuration: config);
memoryStream.SaveAs(sheets, configuration: config);
memoryStream.Seek(0, SeekOrigin.Begin);
return memoryStream;
}
/// <summary>
/// 根据type 生成Excel 未知类型
/// </summary>
/// <param name="type"></param>
/// <param name="sheetName"></param>
/// <param name="list">必须是集合类型</param>
/// <returns></returns>
public static string GenerateExcel(Type type, string sheetName, List<object> lists)
{
//获取随机文件名
string fileName = $"{Path.GetRandomFileName()}.xlsx";
var path = $@"{AppDomain.CurrentDomain.BaseDirectory}TempExcel\{fileName}";
//MiniExcel.SaveAs(path, lists);
if (lists.Count == 0)
{
MiniExcel.SaveAs(path, null, true, sheetName, ExcelType.XLSX);
return path;
}
//循环便利生成字典
var dictionarys = new List<Dictionary<string, object>>();
foreach (var item in lists)
{
//获取T得所有字段名
var properties = type.GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);
var dictionary = new Dictionary<string, object>();
//循环 properties 拿出他得字段名和value
foreach (var property in properties)
{
//当前得属性名
var name = property.Name;
var typeValue = (dynamic)item;
// dynamic.InvokeGet(typeValue, name);
var vao = Microsoft.VisualBasic.CompilerServices.Versioned.CallByName(typeValue, name, CallType.Get);
//根据名字获取到当前循环得item(属性名) 得值
dictionary.Add(name, vao);
}
dictionarys.Add(dictionary);
}
//生成EXCEL
MiniExcel.SaveAs(path, dictionarys, true, sheetName, ExcelType.XLSX);
return path;
}
/// <summary>
/// 根据type 生成Excel 未知类型
/// </summary>
/// <param name="type"></param>
/// <returns></returns>
public static string GenerateExcelTemp(Type type)
{
string fileName = $"{Path.GetRandomFileName()}.xlsx";
var path = $@"{AppDomain.CurrentDomain.BaseDirectory}TempExcel\{fileName}";
var properties = type.GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);
var dictionary = new Dictionary<string, object>();
foreach (var property in properties)
{
var name = property.Name;
dictionary.Add(name, "");
}
var dictionarys = new List<Dictionary<string, object>>();
dictionarys.Add(dictionary);
MiniExcel.SaveAs(path, dictionarys, true, "sheet1", ExcelType.XLSX);
return path;
}
}
- 控制器中调用
- 如果是知道T是什么类型得时候可以直接使用泛型得方法创建
- 这种使用方式是知道类得字符串得时候创建
- 已知类型得方式可以使用 public static string GenerateExcel(List lists) where T : class方法
/// <summary>
/// 备份
/// </summary>
/// <param name="backUpEnum">
/// 0. UsersInfo,
/// 1. TaskScheduled,
/// 2. SystemPosition,
/// 3. SystemMenu,
/// 4. Room,
/// 5. PositionSystemMenu,
/// 6. OperationAudit,
/// 7. LeaveDetails,
/// 8. CcDetail,
/// 9. AuditFlowDetail,
/// 10. AuditFlow,
/// 11. MapCoordinatesInfo,
/// 12. Community,
/// 13. Building,
/// 14. Resident,
/// 15. NucleicAcidSiteStaff,
/// 16. NucleicAcidSite,
/// 17. NucleicAcid,
/// 18. Infected,
/// </param>
/// <returns></returns>
[HttpGet("UserBackUp")]
public async Task<IActionResult> UserBackUp(BackUpEnum backUpEnum)
{
using (var conn = _connectFactory.GetOpenConn())
{
var typeName = backUpEnum.ToString();
// var type = typeof(typeName);
var type = CreateTypeHelper.typen(typeName);
//数据源
var data = await conn.Connection.SqlQueryable<object>($"select * from {typeName}").ToListAsync();
var result = ExcelHelper.GenerateExcel(type, "data", data);
FileStream fs = new FileStream(result, FileMode.Open);
this.HttpContext.Response.Headers.Add("Content-Length", fs.Length.ToString());
this.HttpContext.Response.Headers.Add("Content-Type", "charset=UTF-8");
return File(fs, "application/octet-stream;charset=UTF-8", Path.GetFileName(result));
}
}
/// <summary>
/// 下载备份模板
/// </summary>
/// <param name="backUpEnum">
/// 0. UsersInfo,
/// 1. TaskScheduled,
/// 2. SystemPosition,
/// 3. SystemMenu,
/// 4. Room,
/// 5. PositionSystemMenu,
/// 6. OperationAudit,
/// 7. LeaveDetails,
/// 8. CcDetail,
/// 9. AuditFlowDetail,
/// 10. AuditFlow,
/// 11. MapCoordinatesInfo,
/// 12. Community,
/// 13. Building,
/// 14. Resident,
/// 15. NucleicAcidSiteStaff,
/// 16. NucleicAcidSite,
/// 17. NucleicAcid,
/// 18. Infected,
/// </param>
/// <returns></returns>
[HttpGet("DownloadTemp")]
public IActionResult DownloadTemp(BackUpEnum backUpEnum)
{
var typeName = backUpEnum.ToString();
// var type = typeof(typeName);
var type = CreateTypeHelper.typen(typeName);
var result = ExcelHelper.GenerateExcelTemp(type);
FileStream fs = new FileStream(result, FileMode.Open);
this.HttpContext.Response.Headers.Add("Content-Length", fs.Length.ToString());
this.HttpContext.Response.Headers.Add("Content-Type", "charset=UTF-8");
return File(fs, "application/octet-stream;charset=UTF-8", Path.GetFileName(result));
}
以上得使用方式会在文件中保存Excel文件如需要删除可以添加拦截器
拦截器：
/// <summary>
/// 用户在处理完接口之后格式化返回Json格式数据
/// </summary>
public class ReturnResultProcessingFilerAttribute : Attribute, IAsyncResultFilter
{
public async Task OnResultExecutionAsync(ResultExecutingContext context, ResultExecutionDelegate next)
{
//特殊处理：对有ApiIgnoreAttribute标签的，不进行返回结果包装，原样输出
var controllerActionDescriptor = context.ActionDescriptor as ControllerActionDescriptor;
if (controllerActionDescriptor != null)
{
var isDefined = controllerActionDescriptor.EndpointMetadata.Any(a => a.GetType().Equals(typeof(ApiIgnoreAttribute)));
if (isDefined)
{
return;
}
}
// 返回结果为JsonResult的请求进行Result包装
if (context.Result != null)
{
if (context.Result is ObjectResult)
{
var result = context.Result as ObjectResult;
context.Result = new JsonResult(new { code = 200, msg = "success", data = result!.Value });
}
else if (context.Result is EmptyResult)
{
context.Result = new JsonResult(new { code = 200, msg = "success", data = new { } });
}
else if (context.Result is ContentResult)
{
var result = context.Result as ContentResult;
context.Result = new JsonResult(new { code = result!.StatusCode, msg = result.Content });
}
else if (context.Result is JsonResult)
{
var result = context.Result as JsonResult;
context.Result = new JsonResult(result?.Value);
}
else if (context.Result is FileStreamResult)
{
//当返回类型为FileStreamResult直接返回
}
else
{
throw new Exception($"未经处理的Result类型：{context.Result.GetType().Name}");
}
}
await next.Invoke();
//返回完数据把文件干掉
if (context.Result is FileStreamResult)
{
//获取到文件得绝对路径
var path = ((System.IO.FileStream)((Microsoft.AspNetCore.Mvc.FileStreamResult)context.Result).FileStream).Name;
if (Path.GetExtension(path) == ".xlsx")
{
File.Delete(path);
}
}
}
不落地生成EXCEL
/// <summary>
/// 备份
/// </summary>
/// <returns></returns>
[HttpGet("UserBackUp")]
public async Task<IActionResult> UserBackUp()
{
using (var conn = _connectFactory.GetOpenConn())
{
var data = await conn.Connection.SqlQueryable<UsersInfo>($"select * from UsersInfo").ToListAsync();
var result = ExcelHelper.GenerateExcelStream<UsersInfo>(data);
this.HttpContext.Response.Headers.Add("Content-Length", result.Length.ToString());
this.HttpContext.Response.Headers.Add("Content-Type", "charset=UTF-8");
return File(result, "application/octet-stream;charset=UTF-8", Path.GetRandomFileName()+".xlsx");
}
}
/// <summary>
/// 下载备份模板
/// </summary>
/// <returns></returns>
[HttpGet("DownloadTemp")]
public IActionResult DownloadTemp()
{
var result = ExcelHelper.GenerateExcelTempStream<UsersInfo>();
//FileStream fs = new FileStream(result, FileMode.Open);
this.HttpContext.Response.Headers.Add("Content-Length", result.Length.ToString());
this.HttpContext.Response.Headers.Add("Content-Type", "charset=UTF-8");
return File(result, "application/octet-stream;charset=UTF-8", Path.GetFileName(Path.GetRandomFileName()+".xlsx"));
}
- BackUpEnum
public enum BackUpEnum
{
UsersInfo,
TaskScheduled,
SystemPosition,
SystemMenu,
Room,
PositionSystemMenu,
OperationAudit,
LeaveDetails,
CcDetail,
AuditFlowDetail,
AuditFlow,
MapCoordinatesInfo,
Community,
Building,
Resident,
NucleicAcidSiteStaff,
NucleicAcidSite,
NucleicAcid,
Infected,
}
类似资料：