C#_.NetCore_WebAPI项目_EXCEL数据导出（ExcelHelper_第二版_优

//
        // 摘要:
        //     / 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). /
         参数:
           fileContents:
             The file contents.
           contentType:
             The Content-Type of the file.
           fileDownloadName:
             The suggested file name.
         返回结果:
             The created Microsoft.AspNetCore.Mvc.FileContentResult for the response.
        [NonAction]
        public FileContentResult File(byte[] fileContents,string contentType,1)">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;
 Microsoft.Extensions.Logging;
 Microsoft.Net.Http.Headers;
 NPOI.SS.UserModel;
 NPOI.XSSF.UserModel;
 System;
 System.Collections.Generic;
 System.IO;
 System.Linq;
 System.Reflection;

namespace PaymentAccountAPI.Helper
{
    /// <summary>
    /// EXCEL帮助类
    </summary>
    <typeparam name="T">泛型类</typeparam>
    <typeparam name="TCollection">泛型类集合</typeparam>
    public class ExcelHelp
    {
        private ILogger Logger = null;

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

        <summary>
         将数据导出EXCEL
        </summary>
        <param name="tList">要导出的数据集</param>
        <param name="fieldNameAndShowNameDic">键值对集合（键：字段名，值：显示名称）<param name="fileDirectoryPath">文件路径<param name="excelName">文件名（必须是英文或数字）<returns></returns>
        public IWorkbook CreateOrUpdateWorkbook<T>(List<T> tList,Dictionary<string,1)">string> fieldNameAndShowNameDic,IWorkbook workbook = null,1)">string sheetName = sheet1") where T : new()
        {
            xls文件格式属于老版本文件，一个sheet最多保存65536行；而xlsx属于新版文件类型；
            Excel 07 - 2003一个工作表最多可有65536行，行用数字1—65536表示; 最多可有256列，列用英文字母A—Z，AA—AZ，BA—BZ，……，IA—IV表示；一个工作簿中最多含有255个工作表，默认情况下是三个工作表；
            Excel 2007及以后版本，一个工作表最多可有1048576行，16384列；
            if (workbook == )
            {
                workbook =  XSSFWorkbook();
                workbook = new HSSFWorkbook();
            }
            ISheet worksheet = workbook.CreateSheet(sheetName);

            List<string> columnNameList = fieldNameAndShowNameDic.Values.ToList();
            设置首列显示
            IRow row1 = worksheet.CreateRow(0);

            ICell cell = ;
            ICellStyle cellHeadStyle = workbook.CreateCellStyle();
            设置首行字体加粗
            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;
            }

            根据反射创建其他行数据
            var raws = tList.Count;
            Dictionary<this.GetIndexPropertyDic<T>(fieldNameAndShowNameDic);

            PropertyInfo propertyInfo = ;
            T t = default(T);
            int i = 0; i < raws; i++)
            {
                if (i % 10000 == )
                {
                    this.Logger.LogInformation($Excel已创建{i + 1}条数据);
                }
                row1 = worksheet.CreateRow(i + 1);
                t = tList[i];

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

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

                重要：设置行宽度自适应(大批量添加数据时，该行代码需要注释，否则会极大减缓Excel添加行的速度！)
                worksheet.AutoSizeColumn(i,true);
            }

            return workbook;
        }

         保存Workbook数据为文件
        <param name="workbook"></param>
        <param name="fileDirectoryPath"></param>
        <param name="fileName"></param>
        void SaveWorkbookToFile(IWorkbook workbook,1)">string fileDirectoryPath,1)">string fileName)
        {
            Excel 2007及以后版本，一个工作表最多可有1048576行，16384列；

            MemoryStream ms =  MemoryStream();
            这句代码非常重要，如果不加，会报：打开的EXCEL格式与扩展名指定的格式不一致
            ms.Seek(,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);
        }

         保存Workbook数据为下载文件
        </summary>
        public FileContentResult SaveWorkbookToDownloadFile(IWorkbook workbook)
        {
            MemoryStream ms =  ms.GetBuffer();

            对于.xls文件
            application/vnd.ms-excel
            用于.xlsx文件。
            application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
            MediaTypeHeaderValue mediaType = application/vnd.openxmlformats-officedocument.spreadsheetml.sheet);
            mediaType.Encoding = System.Text.Encoding.UTF8;
　　　　　　　//设置下载文件名
            FileContentResult fileResult= new FileContentResult(myByteArray,mediaType.ToString());
　　　　　　　fileResult.FileDownloadName="xxx.xlsx";
return  FileContentResult(myByteArray,mediaType.ToString());
        }


         读取Excel数据
        <param name="filePath"></param>
        <param name="fieldNameAndShowNameDic"></param>
        public List<T> ReadDataList<T>(string filePath,1)">string> fieldNameAndShowNameDic) ()
        {
            List<T> tList = (T);

            标题属性字典列表
            Dictionary<(fieldNameAndShowNameDic);
            标题下标列表
            Dictionary<int> titleIndexDic = new Dictionary<int>();

            PropertyInfo propertyInfo = ;

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

                var rows = sheet.GetRowEnumerator();
                tList = new List<T>(sheet.LastRowNum + );
                第一行数据为标题，
                 (rows.MoveNext())
                {
                    IRow row = (XSSFRow)rows.Current;
                    ICell cell = ;
                    string cellValue = 0; i < row.Cells.Count; i++)
                    {
                        cell = row.Cells[i];
                        cellValue = cell.StringCellValue;
                         (titlePropertyDic.ContainsKey(cellValue))
                        {
                            titleIndexDic.Add(cellValue,i);
                        }
                    }
                }
                从第2行数据开始获取
                while (XSSFRow)rows.Current;
                    t =  T();

                    var titleIndexItem  titleIndexDic)
                    {
                        var cell = row.GetCell(titleIndexItem.Value);
                        if (cell != )
                        {
                            propertyInfo = titlePropertyDic[titleIndexItem.Key];
                            ))
                            {
                                propertyInfo.SetValue(t,Convert.ToInt32(cell.NumericCellValue));
                            }
                            (DateTime))
                            {
                                propertyInfo.SetValue(t,Convert.ToDateTime(cell.StringCellValue));
                            }
                            
                            {
                                propertyInfo.SetValue(t,cell.StringCellValue);
                            }

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

         根据属性名顺序获取对应的属性对象
        <param name="fieldNameList"></param>
        private Dictionary<string> fieldNameAndShowNameDic)
        {
            Dictionary<(fieldNameAndShowNameDic.Count);

            List<PropertyInfo> tPropertyInfoList = (T).GetProperties().ToList();
            PropertyInfo propertyInfo = var item  fieldNameAndShowNameDic)
            {
                propertyInfo = tPropertyInfoList.Find(m => m.Name.Equals(item.Key,StringComparison.OrdinalIgnoreCase));
                titlePropertyDic.Add(item.Value,propertyInfo);
            }
             titlePropertyDic;
        }

    }
}