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

项目须要引用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资助类

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;
13. /// <summary>
14. /// 将数据导出EXCEL
15. /// </summary>
16. /// <param name="tList">要导出的数据集</param>
17. /// <param name="fieldNameAndShowNameDic">键值对聚集（键：字段名，值：表现名称）</param>
18. /// <param name="httpResponse">相应</param>
19. /// <param name="excelName">文件名（必须是英文或数字）</param>
20. /// <returns></returns>
21. public async Task ExportExcelData(TCollection tList, Dictionary<string, string> fieldNameAndShowNameDic, HttpResponse httpResponse, string excelName = "exportResult")
22. {
23. IWorkbook workbook = new HSSFWorkbook();
24. ISheet worksheet = workbook.CreateSheet("sheet1");
26. List<string> columnNameList = fieldNameAndShowNameDic.Values.ToList();
27. //设置首列表现
28. IRow row1 = worksheet.CreateRow(0);
29. ICell cell = null;
30. ICellStyle cellHeadStyle = workbook.CreateCellStyle();
31. //设置首行字体加粗
32. IFont font = workbook.CreateFont();
33. font.Boldweight = short.MaxValue;
34. cellHeadStyle.SetFont(font);
35. for (var i = 0; i < columnNameList.Count; i++)
36. {
37. cell = row1.CreateCell(i);
38. cell.SetCellValue(columnNameList[i]);
39. cell.CellStyle = cellHeadStyle;
40. }
42. //根据反射创建其他行数据
43. var raws = tList.Count;
44. Dictionary<int, PropertyInfo> indexPropertyDic = this.GetIndexPropertyDic(fieldNameAndShowNameDic.Keys.ToList());
46. for (int i = 0; i < raws; i++)
47. {
48. row1 = worksheet.CreateRow(i + 1);
50. for (int j = 0; j < fieldNameAndShowNameDic.Count; j++)
51. {
52. cell = row1.CreateCell(j);
53. if (indexPropertyDic[j].PropertyType == typeof(int)
54. || indexPropertyDic[j].PropertyType == typeof(decimal)
55. || indexPropertyDic[j].PropertyType == typeof(double))
56. {
57. cell.SetCellValue(Convert.ToDouble(indexPropertyDic[j].GetValue(tList[i])));
58. }
59. else if (indexPropertyDic[j].PropertyType == typeof(DateTime))
60. {
61. cell.SetCellValue(Convert.ToDateTime(indexPropertyDic[j].GetValue(tList[i]).ToString()));
62. }
63. else if (indexPropertyDic[j].PropertyType == typeof(bool))
64. {
65. cell.SetCellValue(Convert.ToBoolean(indexPropertyDic[j].GetValue(tList[i]).ToString()));
66. }
67. else
68. {
69. cell.SetCellValue(indexPropertyDic[j].GetValue(tList[i]).ToString());
70. }
71. }
72. //设置行宽度自顺应
73. worksheet.AutoSizeColumn(i, true);
74. }
76. MediaTypeHeaderValue mediaType = new MediaTypeHeaderValue("application/vnd.ms-excel");
77. mediaType.Encoding = System.Text.Encoding.UTF8;
79. httpResponse.ContentType = mediaType.ToString();
80. //设置导出文件名
81. httpResponse.Headers.Add("content-disposition", $"attachment;filename={excelName}.xls");
82. MemoryStream ms = new MemoryStream();
83. workbook.Write(ms);
84. //这句代码非常告急，如果不加，会报：打开的EXCEL格式与扩展名指定的格式不同等
85. ms.Seek(0, SeekOrigin.Begin);
86. byte[] myByteArray = ms.GetBuffer();
87. httpResponse.Headers.Add("Content-Length", myByteArray.Length.ToString());
88. await httpResponse.Body.WriteAsync(myByteArray, 0, myByteArray.Length);
89. }
91. /// <summary>
92. /// 根据属性名序次获取对应的属性对象
93. /// </summary>
94. /// <param name="fieldNameList"></param>
95. /// <returns></returns>
96. private Dictionary<int, PropertyInfo> GetIndexPropertyDic(List<string> fieldNameList)
97. {
98. Dictionary<int, PropertyInfo> indexPropertyDic = new Dictionary<int, PropertyInfo>(fieldNameList.Count);
99. List<PropertyInfo> tPropertyInfoList = typeof(T).GetProperties().ToList();
100. PropertyInfo propertyInfo = null;
101. for (int i = 0; i < fieldNameList.Count; i++)
102. {
103. propertyInfo = tPropertyInfoList.Find(m => m.Name.Equals(fieldNameList[i], StringComparison.OrdinalIgnoreCase));
104. indexPropertyDic.Add(i, propertyInfo);
105. }
107. return indexPropertyDic;
108. }
110. }

复制代码

View Code

Core的中心件哀求方法：

TBDataHelper为提前注入的数据库资助类，须要改成本身的数据哀求类；

自定义的导出文件名，不能输入中文，临时还没有找到办理办法；

BaseMiddleware为基类，牢记基类中只能存常态化的数据，如：下一中心件，配置，缓存。不能存放Request，Response等！！！

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. }
9. public async Task Invoke(HttpContext httpContext)
10. {
11. var query = httpContext.Request.Query;
12. var queryAction = query["action"];
14. switch (queryAction)
15. {
16. case "reBuyExport":
17. await this.ReBuyExport(httpContext);
18. break;
19. }
20. }
22. /// <summary>
23. /// 复购数据导出
24. /// </summary>
25. /// <param name="httpContext"></param>
26. /// <returns></returns>
27. private async Task ReBuyExport(HttpContext httpContext)
28. {
29. var request = httpContext.Request;
30. var response = httpContext.Response;
31. var requestForm = request.Form;
33. try
34. {
35. DateTime beginTime = Convert.ToDateTime(requestForm["beginTime"]);
36. DateTime endTime = Convert.ToDateTime(requestForm["endTime"]);
38. List<RebuyModel> rebuyModelList = this.TBDataHelper.SelectReBuyList(beginTime, endTime);
40. Dictionary<string, string> fieldNameAndShowNameDic = new Dictionary<string, string>(0);
41. fieldNameAndShowNameDic.Add("UserID", "用户ID");
42. fieldNameAndShowNameDic.Add("PayCount", "付出数");
43. fieldNameAndShowNameDic.Add("BeforeBuyCount", beginTime.ToString("MM/dd") + "之前付出数");
45. string fileName = $"{beginTime.ToString("MMdd")}_{endTime.ToString("MMdd")}ReBuyExport_{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}";
46. await ExcelHelp<RebuyModel, List<RebuyModel>>.INSTANCE.ExportExcelData(rebuyModelList, fieldNameAndShowNameDic, response, fileName);
47. }
48. catch (Exception e)
49. {
50. throw e;
51. }
52. }
54. }

复制代码

View Code

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; }
11. /// <summary>
12. /// 获取配置文件内里的配置内容
13. /// </summary>
14. protected ConfigurationManager ConfigurationManager { get; set; }
16. /// <summary>
17. /// 下一个中心件
18. /// </summary>
19. protected RequestDelegate Next { get; set; }
21. public BaseMiddleware(RequestDelegate next, params object[] @params)
22. {
23. this.Next = next;
24. foreach (var item in @params)
25. {
26. if (item is IMemoryCache)
27. {
28. this.MemoryCache = (IMemoryCache)item;
29. }
30. else if (item is ConfigurationManager)
31. {
32. this.ConfigurationManager = (ConfigurationManager)item;
33. }
34. }
35. }
37. }

复制代码

View Code