C#_.NetCore_Web项目_EXCEL数据导出（ExcelHelper_第一版）

/// <summary>
    /// EXCEL帮助类
    </summary>
    <typeparam name="T">泛型类</typeparam>
    <typeparam name="TCollection">泛型类集合</typeparam>
    public class ExcelHelp<T,TCollection> where TCollection : List<T> where T : new()
    {
        static ExcelHelp<T,TCollection> INSTANCE = new ExcelHelp<T,TCollection>();
        //获取httpResponse对象原位置，放在这里不知道会报错：服务器无法在发送 HTTP 标头之后追加标头
        可能是这里拿到的httpResponse对象不是最新请求的对象导致的，将其放到方法内部即可
        HttpResponse baseResponse = HttpContext.Current.Response;

        <summary>
         将数据导出EXCEL
        </summary>
        <param name="tList">要导出的数据集</param>
        <param name="fieldNameAndShowNameDic">键值对集合（键：字段名，值：显示名称）<param name="httpResponse">响应<param name="excelName">文件名（必须是英文或数字）<returns></returns>
        async Task ExportExcelData(TCollection tList,Dictionary<string,string> fieldNameAndShowNameDic,HttpResponse httpResponse,1)">string excelName = "exportResult")
        {
            IWorkbook workbook =  HSSFWorkbook();
            ISheet worksheet = workbook.CreateSheet(sheet1);

            List<string> columnNameList = fieldNameAndShowNameDic.Values.ToList();
            设置首列显示
            IRow row1 = worksheet.CreateRow(0);
            ICell cell = null;
            ICellStyle cellHeadStyle = workbook.CreateCellStyle();
            设置首行字体加粗
            IFont font = workbook.CreateFont();
            font.Boldweight = short.MaxValue;
            cellHeadStyle.SetFont(font);
            for (var i = 0; i < columnNameList.Count; i++)
            {
                cell = row1.CreateCell(i);
                cell.SetCellValue(columnNameList[i]);
                cell.CellStyle = cellHeadStyle;
            }

            根据反射创建其他行数据
            var raws = tList.Count;
            Dictionary<int,PropertyInfo> indexPropertyDic = this.GetIndexPropertyDic(fieldNameAndShowNameDic.Keys.ToList());

            int i = 0; i < raws; i++)
            {
                row1 = worksheet.CreateRow(i + 1);

                int j = 0; j < fieldNameAndShowNameDic.Count; j++)
                {
                    cell = row1.CreateCell(j);
                    if (indexPropertyDic[j].PropertyType == typeof(int)
                        || indexPropertyDic[j].PropertyType == decimaldouble))
                    {
                        cell.SetCellValue(Convert.ToDouble(indexPropertyDic[j].GetValue(tList[i])));
                    }
                    else typeof(DateTime))
                    {
                        cell.SetCellValue(Convert.ToDateTime(indexPropertyDic[j].GetValue(tList[i]).ToString()));
                    }
                    bool))
                    {
                        cell.SetCellValue(Convert.ToBoolean(indexPropertyDic[j].GetValue(tList[i]).ToString()));
                    }
                    else
                    {
                        cell.SetCellValue(indexPropertyDic[j].GetValue(tList[i]).ToString());
                    }
                }
                设置行宽度自适应
                worksheet.AutoSizeColumn(i,1)">true);
            }

            对于.xls文件
            application/vnd.ms-excel
            用于.xlsx文件。
            application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
            MediaTypeHeaderValue mediaType = new MediaTypeHeaderValue(application/vnd.ms-excel);
            mediaType.Encoding = System.Text.Encoding.UTF8;

            httpResponse.ContentType = mediaType.ToString();
            设置导出文件名
            httpResponse.Headers.Add(content-disposition",$attachment;filename={excelName}.xls);
            MemoryStream ms =  MemoryStream();
           这句代码非常重要，如果不加，会报：打开的EXCEL格式与扩展名指定的格式不一致
            ms.Seek(,SeekOrigin.Begin);
            workbook.Write(ms);
            byte[] myByteArray = ms.GetBuffer();
            httpResponse.Headers.Add(Content-Lengthawait httpResponse.Body.WriteAsync(myByteArray, 根据属性名顺序获取对应的属性对象
        <param name="fieldNameList"></param>
        private Dictionary<string> fieldNameList)
        {
            Dictionary<new Dictionary<(fieldNameList.Count);
            List<PropertyInfo> tPropertyInfoList = (T).GetProperties().ToList();
            PropertyInfo propertyInfo = ;
            0; i < fieldNameList.Count; i++)
            {
                propertyInfo = tPropertyInfoList.Find(m => m.Name.Equals(fieldNameList[i],StringComparison.OrdinalIgnoreCase));
                indexPropertyDic.Add(i,propertyInfo);
            }

            return indexPropertyDic;
        }

    }

 ToolHelperMiddleware : BaseMiddleware
    {
        public TBDataHelper TBDataHelper { get; set; }
        public ToolHelperMiddleware(RequestDelegate next,ConfigurationManager configurationManager,IMemoryCache memoryCache,TBDataHelper tBDataHelper) : base(next,configurationManager,memoryCache)
        {
            this.TBDataHelper = tBDataHelper;
        }

        async Task Invoke(HttpContext httpContext)
        {
            var query = httpContext.Request.Query;
            var queryAction = query[action];

            switch (queryAction)
            {
                case reBuyExport:
                    await .ReBuyExport(httpContext);
                    break;
            }
        }

         复购数据导出
        <param name="httpContext"></param>
        private  Task ReBuyExport(HttpContext httpContext)
        {
            var request = httpContext.Request;
            var response = httpContext.Response;
            var requestForm = request.Form;

            try
            {
                DateTime beginTime = Convert.ToDateTime(requestForm[beginTime]);
                DateTime endTime = Convert.ToDateTime(requestForm[endTime]);

                List<RebuyModel> rebuyModelList = .TBDataHelper.SelectReBuyList(beginTime,endTime);

                Dictionary<string> fieldNameAndShowNameDic = string>();
                fieldNameAndShowNameDic.Add(UserID用户IDPayCount支付数BeforeBuyCountMM/dd") + 之前支付数string fileName = ${beginTime.ToString("MMdd)}_{endTime.ToString()}ReBuyExport_{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss)};
                await ExcelHelp<RebuyModel,List<RebuyModel>>.INSTANCE.ExportExcelData(rebuyModelList,fieldNameAndShowNameDic,response,fileName);
            }
            catch (Exception e)
            {
                throw e;
            }
        }

    }

 中间件基类
    </summary>
    abstract  BaseMiddleware
    {
         等同于ASP.NET里面的WebCache(HttpRuntime.Cache)
        </summary>
        protected IMemoryCache MemoryCache { ; }

         获取配置文件里面的配置内容
        protected ConfigurationManager ConfigurationManager {  下一个中间件
        protected RequestDelegate Next { ; }

        public BaseMiddleware(RequestDelegate next,1)">params object[] @params)
        {
            this.Next = next;
            foreach (var item in @params)
            {
                if (item is IMemoryCache)
                {
                    this.MemoryCache = (IMemoryCache)item;
                }
                 ConfigurationManager)
                {
                    this.ConfigurationManager = (ConfigurationManager)item;
                }
            }
        }

    }