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(0); 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 = 0; 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 = 0; i < raws; i++) { if (i % 10000 == 0) { this.Logger.LogInformation($"Excel已创建{i + 1}条数据"); } 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++; } //重要：设置行宽度自适应(大批量添加数据时，该行代码需要注释，否则会极大减缓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(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数据为下载文件 /// </summary> public FileContentResult SaveWorkbookToDownloadFile(IWorkbook workbook) { MemoryStream ms = new MemoryStream(); //这句代码非常重要，如果不加，会报：打开的EXCEL格式与扩展名指定的格式不一致 ms.Seek(0, 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>(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); //第一行数据为标题， 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行数据开始获取 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> /// 根据属性名顺序获取对应的属性对象 /// </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; } } }
