1. 首页
  2. 文章中心
  3. c#

.Net webapi MiniExcel生成Excel

50 阅读 0 评论
我是靠谱客的博主 单纯身影，最近开发中收集的这篇文章主要介绍.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,
}

最后

以上就是单纯身影为你收集整理的.Net webapi MiniExcel生成Excel的全部内容，希望文章能够帮你解决.Net webapi MiniExcel生成Excel所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。

本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
点赞(33)

相关文章

手把手教你从零搭建一个 ASP.NET Core 程序
手把手教你从零搭建一个 ASP.NET Core 程序
ASP.NET CORE 从零开始1.工欲善其事，必先利其器之必备工具2.开局一部宝典送给你3.搭建一个项目：使用Blazor WebAssembly或Blazor Server；了解目录结构4.制作一个页面布局5.数据库相关6.使用数据库进行数据存储，操作。
ASP.NET CORE 从零开始1.工欲善其事，必先利其器之必备工具2.开局一部宝典送给你3.搭建一个项目：使用Blazor WebAssembly或Blazor Server；了解目录结构4.制作一个页面布局5.数据库相关6.使用数据库进行数据存储，操作。
VB.NET中获取串口列表
VB.NET中获取串口列表
ASP.NET Core 之学习（一）
ASP.NET Core 之学习（一）
.Net webapi MiniExcel生成Excel
.Net webapi MiniExcel生成Excel
vb.net利用SerialPort进行读取串口操作
vb.net利用SerialPort进行读取串口操作
.NET7之MiniAPI(特别篇)：.NET7 Preview3
.NET7之MiniAPI(特别篇)：.NET7 Preview3
web api实现多个Get方法
web api实现多个Get方法

评论列表共有 0 条评论

立即
投稿
返回
顶部