C#_.NetCore_EXCEL数据导出_前台生成文件的几种方法

项目需要引用NPOI的Nuget包：DotNetCore.NPOI-v1.2.2

 

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

　　1-JS-Url跳转请求-后台需要返回文件流数据：

window.Location.href = "/ajax/toolhelper.js?action=reBuyExport&beginTime=" + beginTime + "&endTime=" + endTime;

　　2-使用form+iframe请求文件流-后台需要返回文件流数据：

<form target="downLoadIframe" method="post" action="/ajax/toolhelper.js?action=reBuyExport">
        <div class="form-group">
            <label for="datetime">beginTime:</label>
            <input type="date" class="form-control" name="beginTime" placeholder="Enter beginTime" />
        </div>
        <div class="form-group">
            <label for="datetime">endTime:</label>
            <input type="date" class="form-control" name="endTime" placeholder="Enter endTime">
        </div>
        <button type="submit" class="btn btn-primary" id="btnExport">导出Excel</button>
    </form>
    <iframe id="downLoadIframe" name="downLoadIframe" style="display:none;"></iframe>

　　3-JS-Fetch请求使用Blob保存二进制文件流数据，通过A标签下载流文件-后台需要返回文件流数据：

　　领导推荐这种方法，经过检验的，可以应对大文件下载的超时问题

fetch(url).then(function (res) {
                    res.blob().then(function (blob) {
                        var a = document.createElement('a');
                        var url = window.URL.createObjectURL(blob);
                        a.href = url;
                        a.download = fileName;
                        a.click();
                        window.URL.revokeObjectURL(url);
                    });
                });

 

B-后台返回流数据：

 

Core下的Excel帮助类

/// <summary>
/// EXCEL帮助类
/// </summary>
/// <typeparam name="T">泛型类</typeparam>
/// <typeparam name="TCollection">泛型类集合</typeparam>
public class ExcelHelp<T, TCollection> where TCollection : List<T> where T : new()
{
    public 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>
    /// <param name="httpResponse">响应</param>
    /// <param name="excelName">文件名（必须是英文或数字）</param>
    /// <returns></returns>
    public async Task ExportExcelData(TCollection tList, Dictionary<string, string> fieldNameAndShowNameDic, HttpResponse httpResponse, string excelName = "exportResult")
    {
        //xls文件
        //IWorkbook workbook = new HSSFWorkbook();
        //xlsx文件
        IWorkbook workbook = new XSSFWorkbook();
        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());

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

            for (int j = 0; j < fieldNameAndShowNameDic.Count; j++)
            {
                cell = row1.CreateCell(j);
                if (indexPropertyDic[j].PropertyType == typeof(int)
                    || indexPropertyDic[j].PropertyType == typeof(decimal)
                    || indexPropertyDic[j].PropertyType == typeof(double))
                {
                    cell.SetCellValue(Convert.ToDouble(indexPropertyDic[j].GetValue(tList[i])));
                }
                else if (indexPropertyDic[j].PropertyType == typeof(DateTime))
                {
                    cell.SetCellValue(Convert.ToDateTime(indexPropertyDic[j].GetValue(tList[i]).ToString()));
                }
                else if (indexPropertyDic[j].PropertyType == typeof(bool))
                {
                    cell.SetCellValue(Convert.ToBoolean(indexPropertyDic[j].GetValue(tList[i]).ToString()));
                }
                else
                {
                    cell.SetCellValue(indexPropertyDic[j].GetValue(tList[i]).ToString());
                }
            }
        }

        //设置行宽度自适应(大批量添加数据时，该行代码需要注释，否则会极大减缓Excel添加行的速度！)
        //for (int i = 0; i < raws; i++)
        //{
        //    worksheet.AutoSizeColumn(i, 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 = new MemoryStream();
        //这句代码非常重要，如果不加，会报：打开的EXCEL格式与扩展名指定的格式不一致
        ms.Seek(0, SeekOrigin.Begin);
        workbook.Write(ms);
        byte[] myByteArray = ms.GetBuffer();
        httpResponse.Headers.Add("Content-Length", myByteArray.Length.ToString());
        await httpResponse.Body.WriteAsync(myByteArray, 0, myByteArray.Length);
    }

    /// <summary>
    /// 根据属性名顺序获取对应的属性对象
    /// </summary>
    /// <param name="fieldNameList"></param>
    /// <returns></returns>
    private Dictionary<int, PropertyInfo> GetIndexPropertyDic(List<string> fieldNameList)
    {
        Dictionary<int, PropertyInfo> indexPropertyDic = new Dictionary<int, PropertyInfo>(fieldNameList.Count);
        List<PropertyInfo> tPropertyInfoList = typeof(T).GetProperties().ToList();
        PropertyInfo propertyInfo = null;
        for (int i = 0; i < fieldNameList.Count; i++)
        {
            propertyInfo = tPropertyInfoList.Find(m => m.Name.Equals(fieldNameList[i], StringComparison.OrdinalIgnoreCase));
            indexPropertyDic.Add(i, propertyInfo);
        }

        return indexPropertyDic;
    }

}

 

Core的中间件请求方法：

TBDataHelper为提前注入的数据库帮助类，需要改成自己的数据请求类；

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

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

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

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

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

        /// <summary>
        /// 复购数据导出
        /// </summary>
        /// <param name="httpContext"></param>
        /// <returns></returns>
        private async 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 = this.TBDataHelper.SelectReBuyList(beginTime, endTime);

                Dictionary<string, string> fieldNameAndShowNameDic = new Dictionary<string, string>(0);
                fieldNameAndShowNameDic.Add("UserID", "用户ID");
                fieldNameAndShowNameDic.Add("PayCount", "支付数");
                fieldNameAndShowNameDic.Add("BeforeBuyCount", beginTime.ToString("MM/dd") + "之前支付数");

                string fileName = $"{beginTime.ToString("MMdd")}_{endTime.ToString("MMdd")}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>
    /// 中间件基类
    /// </summary>
    public abstract class BaseMiddleware
    {
        /// <summary>
        /// 等同于ASP.NET里面的WebCache(HttpRuntime.Cache)
        /// </summary>
        protected IMemoryCache MemoryCache { get; set; }

        /// <summary>
        /// 获取配置文件里面的配置内容
        /// </summary>
        protected ConfigurationManager ConfigurationManager { get; set; }

        /// <summary>
        /// 下一个中间件
        /// </summary>
        protected RequestDelegate Next { get; set; }

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

    }