马上加入IBC程序猿 各种源码随意下,各种教程随便看! 注册 每日签到 加入编程讨论群

C#教程 ASP.NET教程 C#视频教程程序源码享受不尽 C#技术求助 ASP.NET技术求助

【源码下载】 社群合作 申请版主 程序开发 【远程协助】 每天乐一乐 每日签到 【承接外包项目】 面试-葵花宝典下载

官方一群:

官方二群:

C#_.NetCore_Web项目_EXCEL数据导出

[复制链接]
查看3990 | 回复0 | 2019-11-8 09:54:19 | 显示全部楼层 |阅读模式

项目须要引用NPOI的Nuget包:DotNetCore.NPOI-v1.2.2

A-前端触发下载Excel的方法有三种:

  1-JS-Url跳转请求-背景须要返回文件流数据:

  1. window.Location.href = "/ajax/toolhelper.js?action=reBuyExport&beginTime=" + beginTime + "&endTime=" + endTime;
复制代码

  2-使用form+iframe请求文件流-背景须要返回文件流数据:

  1. <form target="downLoadIframe" method="post" action="/ajax/toolhelper.js?action=reBuyExport">
  2. <div class="form-group">
  3. <label for="datetime">beginTime:</label>
  4. <input type="date" class="form-control" name="beginTime" placeholder="Enter beginTime" />
  5. </div>
  6. <div class="form-group">
  7. <label for="datetime">endTime:</label>
  8. <input type="date" class="form-control" name="endTime" placeholder="Enter endTime">
  9. </div>
  10. <button type="submit" class="btn btn-primary" id="btnExport">导出Excel</button>
  11. </form>
  12. <iframe id="downLoadIframe" name="downLoadIframe" style="display:none;"></iframe>
复制代码

  3-JS-Fetch请求使用Blob生存二进制文件流数据,通过A标签下载流文件-背景须要返回文件流数据:

  向导推荐这种方法,颠末查验的,可以应对大文件下载的超时标题

  1. fetch(url).then(function (res) {
  2. res.blob().then(function (blob) {
  3. var a = document.createElement('a');
  4. var url = window.URL.createObjectURL(blob);
  5. a.href = url;
  6. a.download = fileName;
  7. a.click();
  8. window.URL.revokeObjectURL(url);
  9. });
  10. });
复制代码

B-背景返回流数据:

Core下的Excel资助类

095503heuce3evt3zgykey.gif
095503jjijp0ua233zijze.gif
  1. /// <summary>
  2. /// EXCEL资助类
  3. /// </summary>
  4. /// <typeparam name="T">泛型类</typeparam>
  5. /// <typeparam name="TCollection">泛型类聚集</typeparam>
  6. public class ExcelHelp<T, TCollection> where TCollection : List<T> where T : new()
  7. {
  8. public static ExcelHelp<T, TCollection> INSTANCE = new ExcelHelp<T, TCollection>();
  9. //获取httpResponse对象原位置,放在这里不知道会报错:服务器无法在发送 HTTP 标头之后追加标头
  10. //大概是这里拿到的httpResponse对象不是最新请求的对象导致的,将其放到方法内部即可
  11. //HttpResponse baseResponse = HttpContext.Current.Response;
  12. /// <summary>
  13. /// 将数据导出EXCEL
  14. /// </summary>
  15. /// <param name="tList">要导出的数据集</param>
  16. /// <param name="fieldNameAndShowNameDic">键值对聚集(键:字段名,值:表现名称)</param>
  17. /// <param name="httpResponse">相应</param>
  18. /// <param name="excelName">文件名(必须是英文或数字)</param>
  19. /// <returns></returns>
  20. public async Task ExportExcelData(TCollection tList, Dictionary<string, string> fieldNameAndShowNameDic, HttpResponse httpResponse, string excelName = "exportResult")
  21. {
  22. IWorkbook workbook = new HSSFWorkbook();
  23. ISheet worksheet = workbook.CreateSheet("sheet1");
  24. List<string> columnNameList = fieldNameAndShowNameDic.Values.ToList();
  25. //设置首列表现
  26. IRow row1 = worksheet.CreateRow(0);
  27. ICell cell = null;
  28. ICellStyle cellHeadStyle = workbook.CreateCellStyle();
  29. //设置首行字体加粗
  30. IFont font = workbook.CreateFont();
  31. font.Boldweight = short.MaxValue;
  32. cellHeadStyle.SetFont(font);
  33. for (var i = 0; i < columnNameList.Count; i++)
  34. {
  35. cell = row1.CreateCell(i);
  36. cell.SetCellValue(columnNameList[i]);
  37. cell.CellStyle = cellHeadStyle;
  38. }
  39. //根据反射创建其他行数据
  40. var raws = tList.Count;
  41. Dictionary<int, PropertyInfo> indexPropertyDic = this.GetIndexPropertyDic(fieldNameAndShowNameDic.Keys.ToList());
  42. for (int i = 0; i < raws; i++)
  43. {
  44. row1 = worksheet.CreateRow(i + 1);
  45. for (int j = 0; j < fieldNameAndShowNameDic.Count; j++)
  46. {
  47. cell = row1.CreateCell(j);
  48. if (indexPropertyDic[j].PropertyType == typeof(int)
  49. || indexPropertyDic[j].PropertyType == typeof(decimal)
  50. || indexPropertyDic[j].PropertyType == typeof(double))
  51. {
  52. cell.SetCellValue(Convert.ToDouble(indexPropertyDic[j].GetValue(tList[i])));
  53. }
  54. else if (indexPropertyDic[j].PropertyType == typeof(DateTime))
  55. {
  56. cell.SetCellValue(Convert.ToDateTime(indexPropertyDic[j].GetValue(tList[i]).ToString()));
  57. }
  58. else if (indexPropertyDic[j].PropertyType == typeof(bool))
  59. {
  60. cell.SetCellValue(Convert.ToBoolean(indexPropertyDic[j].GetValue(tList[i]).ToString()));
  61. }
  62. else
  63. {
  64. cell.SetCellValue(indexPropertyDic[j].GetValue(tList[i]).ToString());
  65. }
  66. }
  67. //设置行宽度自适应
  68. worksheet.AutoSizeColumn(i, true);
  69. }
  70. MediaTypeHeaderValue mediaType = new MediaTypeHeaderValue("application/vnd.ms-excel");
  71. mediaType.Encoding = System.Text.Encoding.UTF8;
  72. httpResponse.ContentType = mediaType.ToString();
  73. //设置导出文件名
  74. httpResponse.Headers.Add("content-disposition", $"attachment;filename={excelName}.xls");
  75. MemoryStream ms = new MemoryStream();
  76. workbook.Write(ms);
  77. //这句代码非常告急,假如不加,会报:打开的EXCEL格式与扩展名指定的格式不划一
  78. ms.Seek(0, SeekOrigin.Begin);
  79. byte[] myByteArray = ms.GetBuffer();
  80. httpResponse.Headers.Add("Content-Length", myByteArray.Length.ToString());
  81. await httpResponse.Body.WriteAsync(myByteArray, 0, myByteArray.Length);
  82. }
  83. /// <summary>
  84. /// 根据属性名顺序获取对应的属性对象
  85. /// </summary>
  86. /// <param name="fieldNameList"></param>
  87. /// <returns></returns>
  88. private Dictionary<int, PropertyInfo> GetIndexPropertyDic(List<string> fieldNameList)
  89. {
  90. Dictionary<int, PropertyInfo> indexPropertyDic = new Dictionary<int, PropertyInfo>(fieldNameList.Count);
  91. List<PropertyInfo> tPropertyInfoList = typeof(T).GetProperties().ToList();
  92. PropertyInfo propertyInfo = null;
  93. for (int i = 0; i < fieldNameList.Count; i++)
  94. {
  95. propertyInfo = tPropertyInfoList.Find(m => m.Name.Equals(fieldNameList[i], StringComparison.OrdinalIgnoreCase));
  96. indexPropertyDic.Add(i, propertyInfo);
  97. }
  98. return indexPropertyDic;
  99. }
  100. }
复制代码
View Code

Core的中心件请求方法:

TBDataHelper为提前注入的数据库资助类,须要改成本身的数据请求类;

自界说的导出文件名,不能输入中文,临时还没有找到解决办法;

BaseMiddleware为基类,牢记基类中只能存常态化的数据,如:下一中心件,设置,缓存。不能存放Request,Response等!!!

095504ts6r2oxms2woshfm.gif
095504rbys8qz540394bc8.gif
  1. public class ToolHelperMiddleware : BaseMiddleware
  2. {
  3. public TBDataHelper TBDataHelper { get; set; }
  4. public ToolHelperMiddleware(RequestDelegate next, ConfigurationManager configurationManager, IMemoryCache memoryCache, TBDataHelper tBDataHelper) : base(next, configurationManager, memoryCache)
  5. {
  6. this.TBDataHelper = tBDataHelper;
  7. }
  8. public async Task Invoke(HttpContext httpContext)
  9. {
  10. var query = httpContext.Request.Query;
  11. var queryAction = query["action"];
  12. switch (queryAction)
  13. {
  14. case "reBuyExport":
  15. await this.ReBuyExport(httpContext);
  16. break;
  17. }
  18. }
  19. /// <summary>
  20. /// 复购数据导出
  21. /// </summary>
  22. /// <param name="httpContext"></param>
  23. /// <returns></returns>
  24. private async Task ReBuyExport(HttpContext httpContext)
  25. {
  26. var request = httpContext.Request;
  27. var response = httpContext.Response;
  28. var requestForm = request.Form;
  29. try
  30. {
  31. DateTime beginTime = Convert.ToDateTime(requestForm["beginTime"]);
  32. DateTime endTime = Convert.ToDateTime(requestForm["endTime"]);
  33. List<RebuyModel> rebuyModelList = this.TBDataHelper.SelectReBuyList(beginTime, endTime);
  34. Dictionary<string, string> fieldNameAndShowNameDic = new Dictionary<string, string>(0);
  35. fieldNameAndShowNameDic.Add("UserID", "用户ID");
  36. fieldNameAndShowNameDic.Add("PayCount", "付出数");
  37. fieldNameAndShowNameDic.Add("BeforeBuyCount", beginTime.ToString("MM/dd") + "之前付出数");
  38. string fileName = $"{beginTime.ToString("MMdd")}_{endTime.ToString("MMdd")}ReBuyExport_{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}";
  39. await ExcelHelp<RebuyModel, List<RebuyModel>>.INSTANCE.ExportExcelData(rebuyModelList, fieldNameAndShowNameDic, response, fileName);
  40. }
  41. catch (Exception e)
  42. {
  43. throw e;
  44. }
  45. }
  46. }
复制代码
View Code
095504jy5d7v59dbi777wj.gif
095504fz2gj9eyh0o1gssl.gif
  1. /// <summary>
  2. /// 中心件基类
  3. /// </summary>
  4. public abstract class BaseMiddleware
  5. {
  6. /// <summary>
  7. /// 等同于ASP.NET内里的WebCache(HttpRuntime.Cache)
  8. /// </summary>
  9. protected IMemoryCache MemoryCache { get; set; }
  10. /// <summary>
  11. /// 获取设置文件内里的设置内容
  12. /// </summary>
  13. protected ConfigurationManager ConfigurationManager { get; set; }
  14. /// <summary>
  15. /// 下一个中心件
  16. /// </summary>
  17. protected RequestDelegate Next { get; set; }
  18. public BaseMiddleware(RequestDelegate next, params object[] @params)
  19. {
  20. this.Next = next;
  21. foreach (var item in @params)
  22. {
  23. if (item is IMemoryCache)
  24. {
  25. this.MemoryCache = (IMemoryCache)item;
  26. }
  27. else if (item is ConfigurationManager)
  28. {
  29. this.ConfigurationManager = (ConfigurationManager)item;
  30. }
  31. }
  32. }
  33. }
复制代码
View Code

*滑块验证:
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则