項目需要引用NPOI的Nuget包:DotNetCore.NPOI-v1.2.2 本篇文章是對WebAPI項目使用NPOI操作Excel時的幫助類:ExcelHelper的改進優化做下記錄: 備註:下麵的幫助類代碼使用的文件格式為:xlsx文件,xlsx相對xls的優缺點代碼里有註釋,推薦使用xls ...
項目需要引用NPOI的Nuget包:DotNetCore.NPOI-v1.2.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; 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; } } }