C#_.NetCore_Web專案_EXCEL資料匯出（ExcelHelper_第一版）

專案需要參考NPOI的Nuget包：DotNetCore.NPOI-v1.2.2

A-前端觸發下載Excel的方法有三種：

　　1-JS-Url跳轉請求-后臺需要回傳檔案流資料：

window.Location.href = "https://www.cnblogs.com/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")        {            IWorkbook workbook = new 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());            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());                    }                }                //設定行寬度自適應                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;                }            }        }    }

標籤：ASP.NET

上一篇：ASP.NET是什么？

下一篇：時隔一年，你關注的打造一個實用的TXT文本操作及日志框架，我們開源了，不再為程式寫日志發愁（也支持.net core哦）