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

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

1-简单的设置下载文件的控制器方法：

//

        // 摘要:

        //     /// 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, string fileDownloadName)

//这里以下载txt文件为例：

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");

2-本篇文章是对WebAPI项目使用NPOI操作Excel时的帮助类：ExcelHelper的改进优化做下记录：

备注：下面的帮助类代码使用的文件格式为：xlsx文件，xlsx相对xls的优缺点代码里有注释，推荐使用xlsx文件保存数据！

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帮助类

    /// </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>

        /// <param name="fileDirectoryPath">文件路径</param>

        /// <param name="excelName">文件名（必须是英文或数字）</param>

        /// <returns></returns>

        public IWorkbook CreateOrUpdateWorkbook<T>(List<T> tList, Dictionary<string, string> fieldNameAndShowNameDic, IWorkbook workbook = null, 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 == null)

            {

                workbook = new XSSFWorkbook();

                //workbook = new HSSFWorkbook();

            }

            ISheet worksheet = workbook.CreateSheet(sheetName);

            List<string> columnNameList = fieldNameAndShowNameDic.Values.ToList();

            //设置首列显示

            IRow row1 = worksheet.CreateRow();

            ICell cell = null;

            ICellStyle cellHeadStyle = workbook.CreateCellStyle();

            //设置首行字体加粗

            IFont font = workbook.CreateFont();

            font.Boldweight = short.MaxValue;

            cellHeadStyle.SetFont(font);

            int cloumnCount = columnNameList.Count;

            for (var i = ; i < cloumnCount; i++)

            {

                cell = row1.CreateCell(i);

                cell.SetCellValue(columnNameList[i]);

                cell.CellStyle = cellHeadStyle;

            }

            //根据反射创建其他行数据

            var raws = tList.Count;

            Dictionary<string, PropertyInfo> titlePropertyDic = this.GetIndexPropertyDic<T>(fieldNameAndShowNameDic);

            PropertyInfo propertyInfo = null;

            T t = default(T);

            for (int i = ; i < raws; i++)

            {

                if (i %  == )

                {

                    this.Logger.LogInformation($"Excel已创建{i + 1}条数据");

                }

                row1 = worksheet.CreateRow(i + );

                t = tList[i];

                int cellIndex = ;

                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) ?? ));

                    }

                    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++;

                }

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

                //worksheet.AutoSizeColumn(i, true);

            }

            return workbook;

        }

        /// <summary>

        /// 保存Workbook数据为文件

        /// </summary>

        /// <param name="workbook"></param>

        /// <param name="fileDirectoryPath"></param>

        /// <param name="fileName"></param>

        public void SaveWorkbookToFile(IWorkbook workbook, string fileDirectoryPath, string fileName)

        {

            //xls文件格式属于老版本文件，一个sheet最多保存65536行；而xlsx属于新版文件类型；

            //Excel 07 - 2003一个工作表最多可有65536行，行用数字1—65536表示; 最多可有256列，列用英文字母A—Z，AA—AZ，BA—BZ，……，IA—IV表示；一个工作簿中最多含有255个工作表，默认情况下是三个工作表；

            //Excel 2007及以后版本，一个工作表最多可有1048576行，16384列；

            MemoryStream ms = new 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);

        }

        /// <summary>

        /// 保存Workbook数据为下载文件

        /// </summary>

        public FileContentResult SaveWorkbookToDownloadFile(IWorkbook workbook)

        {

            MemoryStream ms = new MemoryStream();

            //这句代码非常重要，如果不加，会报：打开的EXCEL格式与扩展名指定的格式不一致

            ms.Seek(, SeekOrigin.Begin);

            workbook.Write(ms);

            byte[] myByteArray = ms.GetBuffer();

            //对于.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;

　　　　　　　//设置下载文件名
            FileContentResult fileResult= new FileContentResult(myByteArray, mediaType.ToString());

　　　　　　　fileResult.FileDownloadName="xxx.xlsx";

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

        }

        /// <summary>

        /// 读取Excel数据

        /// </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);

            //标题属性字典列表

            Dictionary<string, PropertyInfo> titlePropertyDic = this.GetIndexPropertyDic<T>(fieldNameAndShowNameDic);

            //标题下标列表

            Dictionary<string, int> titleIndexDic = new Dictionary<string, int>();

            PropertyInfo propertyInfo = null;

            using (FileStream fileStream = new FileStream(filePath, FileMode.Open, FileAccess.Read))

            {

                IWorkbook xssfWorkbook = new XSSFWorkbook(fileStream);

                var sheet = xssfWorkbook.GetSheetAt();

                var rows = sheet.GetRowEnumerator();

                tList = new List<T>(sheet.LastRowNum + );

                //第一行数据为标题，

                if (rows.MoveNext())

                {

                    IRow row = (XSSFRow)rows.Current;

                    ICell cell = null;

                    string cellValue = null;

                    for (int i = ; i < row.Cells.Count; i++)

                    {

                        cell = row.Cells[i];

                        cellValue = cell.StringCellValue;

                        if (titlePropertyDic.ContainsKey(cellValue))

                        {

                            titleIndexDic.Add(cellValue, i);

                        }

                    }

                }

                //从第2行数据开始获取

                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>();

        }

        /// <summary>

        /// 根据属性名顺序获取对应的属性对象

        /// </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;

        }

    }

}

巴特西

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

最新文章

热门文章