请选择 进入手机版 | 继续访问电脑版

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

C#教程 ASP.NET教程 C#视频教程程序源码享受不尽 C#问题入口 ASP.NET问题入口

【C#问题提交】 社群合作 申请版主 程序开发 【远程协助】 每天乐一乐 每日签到 【承接毕业设计】 面试-葵花宝典下载

官方一群:

官方二群:

查看: 382|回复: 0

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

[复制链接]
  • TA的每日心情
    开心
    前天 21:32
  • 签到天数: 1610 天

    [LV.Master]伴坛终老

    4251

    主题

    6177

    帖子

    11万

    积分

    管理员

    IBC编程社区-原道楠

    Rank: 9Rank: 9Rank: 9

    积分
    111382

    推广达人突出贡献优秀版主荣誉管理论坛元老

    发表于 2019-11-8 09:50:43 | 显示全部楼层 |阅读模式

    马上加入IBC,查看更多教程

    您需要 登录 才可以下载或查看,没有帐号?立即注册

    x

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

    095507dwu8pkb6we77bkge.gif
    095507fqxf7i3qaofjmjai.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等!!!

    095507u3qoqcscb1ij3zjc.gif
    095507mqhwekx8dz75wbpz.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
    095507vxzdecumuo0evyso.gif
    095508rmlbmal9yb9lybp3.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

    C#论坛 www.ibcibc.com IBC编程社区
    C#
    C#论坛
    IBC编程社区
    您需要登录后才可以回帖 登录 | 立即注册

    本版积分规则