//
        // 摘要:
        //     /// Returns a file with the specified fileContents as content (Microsoft.AspNetCore.Http.StatusCodes.Status200OK),
        //     the /// specified contentType as the Content-Type and the specified fileDownloadName
        //     as the suggested file name. /// This supports range requests (Microsoft.AspNetCore.Http.StatusCodes.Status206PartialContent
        //     or /// Microsoft.AspNetCore.Http.StatusCodes.Status416RangeNotSatisfiable if
        //     the range is not satisfiable). ///
        //
        // 參數(shù):
        //   fileContents:
        //     The file contents.
        //
        //   contentType:
        //     The Content-Type of the file.
        //
        //   fileDownloadName:
        //     The suggested file name.
        //
        // 返回結(jié)果:
        //     The created Microsoft.AspNetCore.Mvc.FileContentResult for the response.
        [NonAction]
        public FileContentResult File(byte[] fileContents, string contentType, string fileDownloadName)

byte[] logByte = System.IO.File.ReadAllBytes(dateLogFilePath);
                MediaTypeHeaderValue mediaTypeHeaderValue = new MediaTypeHeaderValue("text/plain");
                mediaTypeHeaderValue.Encoding = Encoding.UTF8;
                return File(logByte, mediaTypeHeaderValue.ToString(), date.ToString("yyyy-MM-dd") + ".log");

using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Logging;
using Microsoft.Net.Http.Headers;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;

namespace PaymentAccountAPI.Helper
{
    /// <summary>
    /// EXCEL幫助類(lèi)
    /// </summary>
    /// <typeparam name="T">泛型類(lèi)</typeparam>
    /// <typeparam name="TCollection">泛型類(lèi)集合</typeparam>
    public class ExcelHelp
    {
        private ILogger Logger = null;

        public ExcelHelp(ILogger<ExcelHelp> logger)
        {
            this.Logger = logger;
        }

        /// <summary>
        /// 將數(shù)據(jù)導(dǎo)出EXCEL
        /// </summary>
        /// <param name="tList">要導(dǎo)出的數(shù)據(jù)集</param>
        /// <param name="fieldNameAndShowNameDic">鍵值對(duì)集合（鍵：字段名,，值：顯示名稱(chēng)）</param>
        /// <param name="fileDirectoryPath">文件路徑</param>
        /// <param name="excelName">文件名（必須是英文或數(shù)字）</param>
        /// <returns></returns>
        public IWorkbook CreateOrUpdateWorkbook<T>(List<T> tList, Dictionary<string, string> fieldNameAndShowNameDic, IWorkbook workbook = null, string sheetName = "sheet1") where T : new()
        {
            //xls文件格式屬于老版本文件，一個(gè)sheet最多保存65536行,；而xlsx屬于新版文件類(lèi)型,；
            //Excel 07 - 2003一個(gè)工作表最多可有65536行，行用數(shù)字1—65536表示; 最多可有256列,，列用英文字母A—Z,，AA—AZ，BA—BZ,，……,，IA—IV表示；一個(gè)工作簿中最多含有255個(gè)工作表,，默認(rèn)情況下是三個(gè)工作表,；
            //Excel 2007及以后版本，一個(gè)工作表最多可有1048576行,，16384列；
            if (workbook == null)
            {
                workbook = new XSSFWorkbook();
                //workbook = new HSSFWorkbook();
            }
            ISheet worksheet = workbook.CreateSheet(sheetName);

            List<string> columnNameList = fieldNameAndShowNameDic.Values.ToList();
            //設(shè)置首列顯示
            IRow row1 = worksheet.CreateRow(0);

            ICell cell = null;
            ICellStyle cellHeadStyle = workbook.CreateCellStyle();
            //設(shè)置首行字體加粗
            IFont font = workbook.CreateFont();
            font.Boldweight = short.MaxValue;
            cellHeadStyle.SetFont(font);
            int cloumnCount = columnNameList.Count;
            for (var i = 0; i < cloumnCount; i++)
            {
                cell = row1.CreateCell(i);
                cell.SetCellValue(columnNameList[i]);
                cell.CellStyle = cellHeadStyle;
            }

            //根據(jù)反射創(chuàng)建其他行數(shù)據(jù)
            var raws = tList.Count;
            Dictionary<string, PropertyInfo> titlePropertyDic = this.GetIndexPropertyDic<T>(fieldNameAndShowNameDic);

            PropertyInfo propertyInfo = null;
            T t = default(T);
            for (int i = 0; i < raws; i++)
            {
                if (i % 10000 == 0)
                {
                    this.Logger.LogInformation($"Excel已創(chuàng)建{i + 1}條數(shù)據(jù)");
                }
                row1 = worksheet.CreateRow(i + 1);
                t = tList[i];

                int cellIndex = 0;
                foreach (var titlePropertyItem in titlePropertyDic)
                {
                    propertyInfo = titlePropertyItem.Value;
                    cell = row1.CreateCell(cellIndex);

                    if (propertyInfo.PropertyType == typeof(int)
                        || propertyInfo.PropertyType == typeof(decimal)
                        || propertyInfo.PropertyType == typeof(double))
                    {
                        cell.SetCellValue(Convert.ToDouble(propertyInfo.GetValue(t) ?? 0));
                    }
                    else if (propertyInfo.PropertyType == typeof(DateTime))
                    {
                        cell.SetCellValue(Convert.ToDateTime(propertyInfo.GetValue(t)?.ToString()).ToString("yyyy-MM-dd HH:mm:ss"));
                    }
                    else if (propertyInfo.PropertyType == typeof(bool))
                    {
                        cell.SetCellValue(Convert.ToBoolean(propertyInfo.GetValue(t).ToString()));
                    }
                    else
                    {
                        cell.SetCellValue(propertyInfo.GetValue(t)?.ToString() ?? "");
                    }
                    cellIndex++;
                }

                //重要：設(shè)置行寬度自適應(yīng)(大批量添加數(shù)據(jù)時(shí),，該行代碼需要注釋?zhuān)駝t會(huì)極大減緩Excel添加行的速度,！)
                //worksheet.AutoSizeColumn(i, true);
            }

            return workbook;
        }

        /// <summary>
        /// 保存Workbook數(shù)據(jù)為文件
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="fileDirectoryPath"></param>
        /// <param name="fileName"></param>
        public void SaveWorkbookToFile(IWorkbook workbook, string fileDirectoryPath, string fileName)
        {
            //xls文件格式屬于老版本文件,，一個(gè)sheet最多保存65536行；而xlsx屬于新版文件類(lèi)型,；
            //Excel 07 - 2003一個(gè)工作表最多可有65536行,，行用數(shù)字1—65536表示; 最多可有256列，列用英文字母A—Z,，AA—AZ,，BA—BZ，……,，IA—IV表示,；一個(gè)工作簿中最多含有255個(gè)工作表，默認(rèn)情況下是三個(gè)工作表,；
            //Excel 2007及以后版本,，一個(gè)工作表最多可有1048576行，16384列,；

            MemoryStream ms = new MemoryStream();
            //這句代碼非常重要,，如果不加，會(huì)報(bào)：打開(kāi)的EXCEL格式與擴(kuò)展名指定的格式不一致
            ms.Seek(0, SeekOrigin.Begin);
            workbook.Write(ms);
            byte[] myByteArray = ms.GetBuffer();

            fileDirectoryPath = fileDirectoryPath.TrimEnd('\\') + "\\";
            if (!Directory.Exists(fileDirectoryPath))
            {
                Directory.CreateDirectory(fileDirectoryPath);
            }

            string filePath = fileDirectoryPath + fileName;
            if (File.Exists(filePath))
            {
                File.Delete(filePath);
            }
            File.WriteAllBytes(filePath, myByteArray);
        }

        /// <summary>
        /// 保存Workbook數(shù)據(jù)為下載文件
        /// </summary>
        public FileContentResult SaveWorkbookToDownloadFile(IWorkbook workbook)
        {
            MemoryStream ms = new MemoryStream();
            //這句代碼非常重要,，如果不加,，會(huì)報(bào)：打開(kāi)的EXCEL格式與擴(kuò)展名指定的格式不一致
            ms.Seek(0, SeekOrigin.Begin);
            workbook.Write(ms);
            byte[] myByteArray = ms.GetBuffer();

            //對(duì)于.xls文件
            //application/vnd.ms-excel
            //用于.xlsx文件。
            //application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
            MediaTypeHeaderValue mediaType = new MediaTypeHeaderValue("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            mediaType.Encoding = System.Text.Encoding.UTF8;
　　　　　　　//設(shè)置下載文件名
            FileContentResult fileResult= new FileContentResult(myByteArray, mediaType.ToString());
　　　　　　　fileResult.FileDownloadName="xxx.xlsx";

            return new FileContentResult(myByteArray, mediaType.ToString());
        }


        /// <summary>
        /// 讀取Excel數(shù)據(jù)
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="fieldNameAndShowNameDic"></param>
        /// <returns></returns>
        public List<T> ReadDataList<T>(string filePath, Dictionary<string, string> fieldNameAndShowNameDic) where T : new()
        {
            List<T> tList = null;
            T t = default(T);

            //標(biāo)題屬性字典列表
            Dictionary<string, PropertyInfo> titlePropertyDic = this.GetIndexPropertyDic<T>(fieldNameAndShowNameDic);
            //標(biāo)題下標(biāo)列表
            Dictionary<string, int> titleIndexDic = new Dictionary<string, int>(0);

            PropertyInfo propertyInfo = null;

            using (FileStream fileStream = new FileStream(filePath, FileMode.Open, FileAccess.Read))
            {
                IWorkbook xssfWorkbook = new XSSFWorkbook(fileStream);
                var sheet = xssfWorkbook.GetSheetAt(0);

                var rows = sheet.GetRowEnumerator();
                tList = new List<T>(sheet.LastRowNum + 1);
                //第一行數(shù)據(jù)為標(biāo)題,，
                if (rows.MoveNext())
                {
                    IRow row = (XSSFRow)rows.Current;
                    ICell cell = null;
                    string cellValue = null;
                    for (int i = 0; i < row.Cells.Count; i++)
                    {
                        cell = row.Cells[i];
                        cellValue = cell.StringCellValue;
                        if (titlePropertyDic.ContainsKey(cellValue))
                        {
                            titleIndexDic.Add(cellValue, i);
                        }
                    }
                }
                //從第2行數(shù)據(jù)開(kāi)始獲取
                while (rows.MoveNext())
                {
                    IRow row = (XSSFRow)rows.Current;
                    t = new T();

                    foreach (var titleIndexItem in titleIndexDic)
                    {
                        var cell = row.GetCell(titleIndexItem.Value);
                        if (cell != null)
                        {
                            propertyInfo = titlePropertyDic[titleIndexItem.Key];
                            if (propertyInfo.PropertyType == typeof(int))
                            {
                                propertyInfo.SetValue(t, Convert.ToInt32(cell.NumericCellValue));
                            }
                            else if (propertyInfo.PropertyType == typeof(decimal))
                            {
                                propertyInfo.SetValue(t, Convert.ToDecimal(cell.NumericCellValue));
                            }
                            else if (propertyInfo.PropertyType == typeof(double))
                            {
                                propertyInfo.SetValue(t, Convert.ToDouble(cell.NumericCellValue));
                            }
                            else if (propertyInfo.PropertyType == typeof(bool))
                            {
                                propertyInfo.SetValue(t, Convert.ToBoolean(cell.StringCellValue));
                            }
                            else if (propertyInfo.PropertyType == typeof(DateTime))
                            {
                                propertyInfo.SetValue(t, Convert.ToDateTime(cell.StringCellValue));
                            }
                            else
                            {
                                propertyInfo.SetValue(t, cell.StringCellValue);
                            }

                        }
                    }
                    tList.Add(t);
                }
            }
            return tList ?? new List<T>(0);
        }

        /// <summary>
        /// 根據(jù)屬性名順序獲取對(duì)應(yīng)的屬性對(duì)象
        /// </summary>
        /// <param name="fieldNameList"></param>
        /// <returns></returns>
        private Dictionary<string, PropertyInfo> GetIndexPropertyDic<T>(Dictionary<string, string> fieldNameAndShowNameDic)
        {
            Dictionary<string, PropertyInfo> titlePropertyDic = new Dictionary<string, PropertyInfo>(fieldNameAndShowNameDic.Count);

            List<PropertyInfo> tPropertyInfoList = typeof(T).GetProperties().ToList();
            PropertyInfo propertyInfo = null;

            foreach (var item in fieldNameAndShowNameDic)
            {
                propertyInfo = tPropertyInfoList.Find(m => m.Name.Equals(item.Key, StringComparison.OrdinalIgnoreCase));
                titlePropertyDic.Add(item.Value, propertyInfo);
            }
            return titlePropertyDic;
        }

    }
}