.NET Core使用NPOI導出複雜，美觀的Excel詳解
- 2020 年 3 月 29 日
- 筆記
前言：
這段時間一直專註於數據報表的開發，當然涉及到相關報表的開發數據導出肯定是一個不可避免的問題啦。客戶要求要導出優雅，美觀的Excel文檔格式的來展示數據，當時的第一想法就是使用NPOI開源庫來做數據導出Excel文檔（當時想想真香，網上隨便搜一搜教程一大堆），但是當自己真正的實踐起來才知道原來想要給不同的單元格設置相關的字體樣式、邊框樣式以及單元格樣式一個簡單的樣式需要寫這麼多行程式碼來實現。作為一個喜歡編寫簡潔程式碼的我而言肯定是受不了的，於是乎提起袖子說干就干，我自己根據網上的一些資料自己封裝了一個通用的NPOI導出Excel幫助類，主要包括行列創建，行內單元格常用樣式封裝（如：字體樣式，字體顏色，字體大小，單元格背景顏色，單元格邊框，單元格內容對齊方式等常用屬性），希望在以後的開發中能夠使用到，並且也希望能夠幫助到更多有需要的同學。
一、引入NPOI NuGet：
NPOI GitHub源碼地址：
版本說明：
NPOI 2.4.1 （注意不同版本可能使用的姿勢有點小差別）
程式包管理器控制台輸入一下命令安裝：
Install-Package NPOI -Version 2.4.1
通過NuGet管理解決方案安裝：
選擇=>工具=>NuGet包管理器=>程式包管理器控制台：
搜索：NPOI進行安裝：
二、導出Excel文檔內容展示格式如下如所示：
三、CellStyle單元格常用樣式概述：
HSSFCellStyle cellStyle = (HSSFCellStyle)workbook.CreateCellStyle(); //創建列頭單元格實例樣式 cellStyle.Alignment = hAlignment; //水平布局方式（HorizontalAlignment hAlignment） cellStyle.VerticalAlignment = vAlignment; //垂直布局方式（VerticalAlignment vAlignment） cellStyle.WrapText =false;//是否自動換行 //TODO:十分注意，要設置單元格背景色必須是FillForegroundColor和FillPattern兩個屬性同時設置，否則是不會顯示背景顏色 //如下設置黃色背景色 cellStyle.FillForegroundColor = cellBackgroundColor;//單元格背景顏色（short cellBackgroundColor = HSSFColor.Yellow.Index） cellStyle.FillPattern = fillPattern;//填充圖案樣式(FineDots 細點，SolidForeground立體前景，isAddFillPattern=true時存在（FillPattern fillPattern = FillPattern.SolidForeground） //設置單元格邊框樣式 //常用的邊框樣式 None(沒有),Thin(細邊框，瘦的),Medium(中等),Dashed(虛線),Dotted(星羅棋布的),Thick(厚的),Double(雙倍),Hair(頭髮)[上右下左順序設置] cellStyle.BorderBottom = BorderStyle.Thin; cellStyle.BorderRight = BorderStyle.Thin; cellStyle.BorderTop = BorderStyle.Thin; cellStyle.BorderLeft = BorderStyle.Thin; //設置單元格邊框顏色[上右下左順序設置] cellStyle.TopBorderColor = HSSFColor.DarkGreen.Index;//DarkGreen(黑綠色) cellStyle.RightBorderColor = HSSFColor.DarkGreen.Index; cellStyle.BottomBorderColor = HSSFColor.DarkGreen.Index; cellStyle.LeftBorderColor = HSSFColor.DarkGreen.Index;
四、Font字體常用屬性概述：
var cellStyleFont = (HSSFFont)workbook.CreateFont();//創建字體對象實例 //假如字體大小只需要是粗體的話直接使用下面該屬性即可 cellStyleFont.IsBold = true; //或者通過下面屬性，設置字體weight來設置字體是否加粗 cellStyleFont.Boldweight = boldWeight; //字體加粗（字體加粗 (None = 0,Normal = 400，Bold = 700） cellStyleFont.FontHeightInPoints = fontHeightInPoints; //字體大小（short fontHeightInPoints） cellStyleFont.FontName = fontName;//字體（仿宋，楷體，宋體 ） cellStyleFont.Color = fontColor;//設置字體顏色（short fontColor = HSSFColor.Black.Index） cellStyleFont.IsItalic =true;//是否將文字變為斜體（true是，false否） cellStyleFont.Underline = underlineStyle;//字體下劃線（下劃線樣式（無下劃線[None],單下劃線[Single],雙下劃線[Double],會計用單下劃線[SingleAccounting],會計用雙下劃線[DoubleAccounting]）） cellStyleFont.TypeOffset = typeOffset;//字體上標下標（字體上標下標(普通默認值[None],上標[Sub],下標[Super]),即字體在單元格內的上下偏移量） cellStyleFont.IsStrikeout =true;//是否顯示刪除線（true顯示，false不顯示）
五、NPOI HSSFColor 顏色索引對照表:
說明：通過對相關屬性的介紹，我們可以清楚的知道無論是字體顏色，表格邊框顏色，還是單元格背景顏色我們都需要用到HSSFColor對象中的顏色屬性索引（該索引的字元類型為short類型）。
HSSFColor顏色索引對照表如下表所示：
表格來源：https://www.cnblogs.com/Brainpan/p/5804167.html （潘小博1992）
| 顏色 | Class名稱(注意可能給由於NPOI的版本原因，我們在使用的時候首字母可能需要小寫) | short |
| Black | 8 | |
| Brown | 60 | |
| Olive_Green | 59 | |
| Dark_Green | 58 | |
| Dark_Teal | 56 | |
| Dark_Blue | 18 | |
| Indigo | 62 | |
| Grey_80_PERCENT | 63 | |
| Dark_Red | 16 | |
| Orange | 53 | |
| DARK_YELLOW | 19 | |
| Green | 17 | |
| Teal | 21 | |
| Blue | 12 | |
| Blue_Grey | 54 | |
| Grey_50_PERCENT | 23 | |
| Red | 10 | |
| LIGHT_ORANGE | 52 | |
| LIME | 50 | |
| SEA_GREEN | 57 | |
| AQUA | 49 | |
| LIGHT_BLUE | 48 | |
| VIOLET | 20 | |
| GREY_40_PERCENT | 55 | |
| Pink | 14 | |
| Gold | 51 | |
| Yellow | 13 | |
| BRIGHT_GREEN | 11 | |
| TURQUOISE | 15 | |
| SKY_BLUE | 40 | |
| Plum | 61 | |
| GREY_25_PERCENT | 22 | |
| Rose | 45 | |
| Tan | 47 | |
| LIGHT_YELLOW | 43 | |
| LIGHT_GREEN | 42 | |
| LIGHT_TURQUOISE | 41 | |
| PALE_BLUE | 44 | |
| LAVENDER | 46 | |
| White | 9 | |
| CORNFLOWER_BLUE | 24 | |
| LEMON_CHIFFON | 26 | |
| MAROON | 25 | |
| ORCHID | 28 | |
| CORAL | 29 | |
| ROYAL_BLUE | 30 | |
| LIGHT_CORNFLOWER_BLUE | 31 | |
| AUTOMATIC | 64 |
六、關於NPOI對Excel中的行列常用操作方法概述：
創建Excel工作表，給工作表賦一個名稱(Excel底部名稱)：
var sheet = workbook.CreateSheet("人才培訓課程表");
創建Excel中指定的行:
/// <summary> /// TODO:先創建行，然後在創建對應的列 /// 創建Excel中指定的行 /// </summary> /// <param name="sheet">Excel工作表對象</param> /// <param name="rowNum">創建第幾行(從0開始)</param> /// <param name="rowHeight">行高</param> public HSSFRow CreateRow(ISheet sheet, int rowNum, float rowHeight) { HSSFRow row = (HSSFRow)sheet.CreateRow(rowNum); //創建行 row.HeightInPoints = rowHeight; //設置列頭行高 return row; }
創建行內指定的單元格:
/// <summary> /// 創建行內指定的單元格 /// </summary> /// <param name="row">需要創建單元格的行</param> /// <param name="cellStyle">單元格樣式</param> /// <param name="cellNum">創建第幾個單元格(從0開始)</param> /// <param name="cellValue">給單元格賦值</param> /// <returns></returns> public HSSFCell CreateCells(HSSFRow row, HSSFCellStyle cellStyle, int cellNum, string cellValue) { HSSFCell cell = (HSSFCell)row.CreateCell(cellNum); //創建單元格 cell.CellStyle = cellStyle; //將樣式綁定到單元格 if (!string.IsNullOrWhiteSpace(cellValue)) { //單元格賦值 cell.SetCellValue(cellValue); } return cell; }
指定合併的行列:
//TODO:關於Excel行列單元格合併問題(注意：合併單元格後，只需對第一個位置賦值即可) /** 第一個參數：從第幾行開始合併 第二個參數：到第幾行結束合併 第三個參數：從第幾列開始合併 第四個參數：到第幾列結束合併 **/ CellRangeAddress region = new CellRangeAddress(0, 0, 0, 5); sheet.AddMergedRegion(region); cell.SetCellValue("人才培訓課程表");//TODO:頂部標題
設置單元格的列寬：
sheet.SetColumnWidth(單元格索引,1000);//設置對應列寬（單元格索引從0開始，後面接寬度）
七、NPOI生成Excel文檔完整程式碼：
NPOI之Excel數據導出幫助類（創建Excel表格行列，設置行高，設置字體樣式，單元格邊框樣式，單元格背景顏色和樣式，單元格內容對齊方式等常用屬性和樣式封裝）:
/** * Author:追逐時光 * Description：Npoi之Excel數據導出幫助類（創建Excel表格行列，設置行高，設置字體樣式，單元格邊框樣式，單元格背景顏色和樣式，單元格內容對齊方式等常用屬性和樣式封裝） * Description：2020年3月29日 */ using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using NPOI.HSSF.Util; namespace YY_Utility { public class NpoiExcelExportHelper { private static NpoiExcelExportHelper _exportHelper; public static NpoiExcelExportHelper _ { get => _exportHelper ?? (_exportHelper = new NpoiExcelExportHelper()); set => _exportHelper = value; } /// <summary> /// TODO:先創建行，然後在創建對應的列 /// 創建Excel中指定的行 /// </summary> /// <param name="sheet">Excel工作表對象</param> /// <param name="rowNum">創建第幾行(從0開始)</param> /// <param name="rowHeight">行高</param> public HSSFRow CreateRow(ISheet sheet, int rowNum, float rowHeight) { HSSFRow row = (HSSFRow)sheet.CreateRow(rowNum); //創建行 row.HeightInPoints = rowHeight; //設置列頭行高 return row; } /// <summary> /// 創建行內指定的單元格 /// </summary> /// <param name="row">需要創建單元格的行</param> /// <param name="cellStyle">單元格樣式</param> /// <param name="cellNum">創建第幾個單元格(從0開始)</param> /// <param name="cellValue">給單元格賦值</param> /// <returns></returns> public HSSFCell CreateCells(HSSFRow row, HSSFCellStyle cellStyle, int cellNum, string cellValue) { HSSFCell cell = (HSSFCell)row.CreateCell(cellNum); //創建單元格 cell.CellStyle = cellStyle; //將樣式綁定到單元格 if (!string.IsNullOrWhiteSpace(cellValue)) { //單元格賦值 cell.SetCellValue(cellValue); } return cell; } /// <summary> /// 行內單元格常用樣式設置 /// </summary> /// <param name="workbook">Excel文件對象</param> /// <param name="hAlignment">水平布局方式</param> /// <param name="vAlignment">垂直布局方式</param> /// <param name="fontHeightInPoints">字體大小</param> /// <param name="isAddBorder">是否需要邊框</param> /// <param name="boldWeight">字體加粗 (None = 0,Normal = 400，Bold = 700</param> /// <param name="fontName">字體（仿宋，楷體，宋體，微軟雅黑...與Excel主題字體相對應）</param> /// <param name="isAddBorderColor">是否增加邊框顏色</param> /// <param name="isItalic">是否將文字變為斜體</param> /// <param name="isLineFeed">是否自動換行</param> /// <param name="isAddCellBackground">是否增加單元格背景顏色</param> /// <param name="fillPattern">填充圖案樣式(FineDots 細點，SolidForeground立體前景，isAddFillPattern=true時存在)</param> /// <param name="cellBackgroundColor">單元格背景顏色（當isAddCellBackground=true時存在）</param> /// <param name="fontColor">字體顏色</param> /// <param name="underlineStyle">下劃線樣式（無下劃線[None],單下劃線[Single],雙下劃線[Double],會計用單下劃線[SingleAccounting],會計用雙下劃線[DoubleAccounting]）</param> /// <param name="typeOffset">字體上標下標(普通默認值[None],上標[Sub],下標[Super]),即字體在單元格內的上下偏移量</param> /// <param name="isStrikeout">是否顯示刪除線</param> /// <returns></returns> public HSSFCellStyle CreateStyle(HSSFWorkbook workbook, HorizontalAlignment hAlignment, VerticalAlignment vAlignment, short fontHeightInPoints, bool isAddBorder, short boldWeight, string fontName = "宋體", bool isAddBorderColor = true, bool isItalic = false, bool isLineFeed = false, bool isAddCellBackground = false, FillPattern fillPattern = FillPattern.NoFill, short cellBackgroundColor = HSSFColor.Yellow.Index, short fontColor = HSSFColor.Black.Index, FontUnderlineType underlineStyle = FontUnderlineType.None, FontSuperScript typeOffset = FontSuperScript.None, bool isStrikeout = false) { HSSFCellStyle cellStyle = (HSSFCellStyle)workbook.CreateCellStyle(); //創建列頭單元格實例樣式 cellStyle.Alignment = hAlignment; //水平居中 cellStyle.VerticalAlignment = vAlignment; //垂直居中 cellStyle.WrapText = isLineFeed;//自動換行 //背景顏色，邊框顏色，字體顏色都是使用 HSSFColor屬性中的對應調色板索引，關於 HSSFColor 顏色索引對照表，詳情參考：https://www.cnblogs.com/Brainpan/p/5804167.html //TODO：引用了NPOI後可通過ICellStyle 介面的 FillForegroundColor 屬性實現 Excel 單元格的背景色設置，FillPattern 為單元格背景色的填充樣式 //TODO:十分注意，要設置單元格背景色必須是FillForegroundColor和FillPattern兩個屬性同時設置，否則是不會顯示背景顏色 if (isAddCellBackground) { cellStyle.FillForegroundColor = cellBackgroundColor;//單元格背景顏色 cellStyle.FillPattern = fillPattern;//填充圖案樣式(FineDots 細點，SolidForeground立體前景) } //是否增加邊框 if (isAddBorder) { //常用的邊框樣式 None(沒有),Thin(細邊框，瘦的),Medium(中等),Dashed(虛線),Dotted(星羅棋布的),Thick(厚的),Double(雙倍),Hair(頭髮)[上右下左順序設置] cellStyle.BorderBottom = BorderStyle.Thin; cellStyle.BorderRight = BorderStyle.Thin; cellStyle.BorderTop = BorderStyle.Thin; cellStyle.BorderLeft = BorderStyle.Thin; } //是否設置邊框顏色 if (isAddBorderColor) { //邊框顏色[上右下左順序設置] cellStyle.TopBorderColor = HSSFColor.DarkGreen.Index;//DarkGreen(黑綠色) cellStyle.RightBorderColor = HSSFColor.DarkGreen.Index; cellStyle.BottomBorderColor = HSSFColor.DarkGreen.Index; cellStyle.LeftBorderColor = HSSFColor.DarkGreen.Index; } /** * 設置相關字體樣式 */ var cellStyleFont = (HSSFFont)workbook.CreateFont(); //創建字體 //假如字體大小只需要是粗體的話直接使用下面該屬性即可 //cellStyleFont.IsBold = true; cellStyleFont.Boldweight = boldWeight; //字體加粗 cellStyleFont.FontHeightInPoints = fontHeightInPoints; //字體大小 cellStyleFont.FontName = fontName;//字體（仿宋，楷體，宋體 ） cellStyleFont.Color = fontColor;//設置字體顏色 cellStyleFont.IsItalic = isItalic;//是否將文字變為斜體 cellStyleFont.Underline = underlineStyle;//字體下劃線 cellStyleFont.TypeOffset = typeOffset;//字體上標下標 cellStyleFont.IsStrikeout = isStrikeout;//是否有刪除線 cellStyle.SetFont(cellStyleFont); //將字體綁定到樣式 return cellStyle; } } }
生成並保存指定的Excel文檔內容：
using System; using System.IO; using Microsoft.AspNetCore.Hosting; using NPOI.HSSF.UserModel; using NPOI.HSSF.Util; using NPOI.SS.UserModel; using NPOI.SS.Util; using YY_Utility; namespace YY_Services { /// <summary> /// Excel文檔生成並保存操作類 /// </summary> public class NpoiExcelOperationService { private static IHostingEnvironment _environment; public NpoiExcelOperationService(IHostingEnvironment iEnvironment) { _environment = iEnvironment; } /// <summary> /// Excel數據導出簡單示例 /// </summary> /// <param name="resultMsg">導出結果</param> /// <param name="excelFilePath">保存excel文件路徑</param> /// <returns></returns> public bool ExcelDataExport(out string resultMsg, out string excelFilePath) { var result = true; excelFilePath = ""; resultMsg = "successfully"; //Excel導出名稱 string excelName = "人才培訓課程表"; try { //首先創建Excel文件對象 var workbook = new HSSFWorkbook(); //創建工作表，也就是Excel中的sheet，給工作表賦一個名稱(Excel底部名稱) var sheet = workbook.CreateSheet("人才培訓課程表"); //sheet.DefaultColumnWidth = 20;//默認列寬 sheet.ForceFormulaRecalculation = true;//TODO:是否開始Excel導出後公式仍然有效（非必須） #region table 表格內容設置 #region 標題樣式 //設置頂部大標題樣式 var cellStyleFont = NpoiExcelExportHelper._.CreateStyle(workbook, HorizontalAlignment.Center, VerticalAlignment.Center, 20, true, 700, "楷體", true, false, false, true, FillPattern.SolidForeground, HSSFColor.Coral.Index, HSSFColor.White.Index, FontUnderlineType.None, FontSuperScript.None, false); //第一行表單 var row = NpoiExcelExportHelper._.CreateRow(sheet, 0, 28); var cell = row.CreateCell(0); //合併單元格 例： 第1行到第2行 第3列到第4列圍成的矩形區域 //TODO:關於Excel行列單元格合併問題 /** 第一個參數：從第幾行開始合併 第二個參數：到第幾行結束合併 第三個參數：從第幾列開始合併 第四個參數：到第幾列結束合併 **/ CellRangeAddress region = new CellRangeAddress(0, 0, 0, 5); sheet.AddMergedRegion(region); cell.SetCellValue("人才培訓課程表");//合併單元格後，只需對第一個位置賦值即可（TODO:頂部標題） cell.CellStyle = cellStyleFont; //二級標題列樣式設置 var headTopStyle = NpoiExcelExportHelper._.CreateStyle(workbook, HorizontalAlignment.Center, VerticalAlignment.Center, 15, true, 700, "楷體", true, false, false, true, FillPattern.SolidForeground, HSSFColor.Grey25Percent.Index, HSSFColor.Black.Index, FontUnderlineType.None, FontSuperScript.None, false); //表頭名稱 var headerName = new[] { "課程類型", "序號", "日期", "課程名稱", "內容概要", "講師簡介" }; row = NpoiExcelExportHelper._.CreateRow(sheet, 1, 24);//第二行 for (var i = 0; i < headerName.Length; i++) { cell = NpoiExcelExportHelper._.CreateCells(row, headTopStyle, i, headerName[i]); //設置單元格寬度 if (headerName[i] == "講師簡介" || headerName[i] == "內容概要") { sheet.SetColumnWidth(i, 10000); } else { sheet.SetColumnWidth(i, 5000); } } #endregion #region 單元格內容資訊 //單元格邊框樣式 var cellStyle = NpoiExcelExportHelper._.CreateStyle(workbook, HorizontalAlignment.Center, VerticalAlignment.Center, 10, true, 400); //左側列單元格合併 begin //TODO:關於Excel行列單元格合併問題（合併單元格後，只需對第一個位置賦值即可） /** 第一個參數：從第幾行開始合併 第二個參數：到第幾行結束合併 第三個參數：從第幾列開始合併 第四個參數：到第幾列結束合併 **/ CellRangeAddress leftOne = new CellRangeAddress(2, 7, 0, 0); sheet.AddMergedRegion(leftOne); CellRangeAddress leftTwo = new CellRangeAddress(8, 11, 0, 0); sheet.AddMergedRegion(leftTwo); //左側列單元格合併 end var currentDate = DateTime.Now; string[] curriculumList = new[] { "藝術學", "設計學", "材料學", "美學", "心理學", "中國近代史", "管理人員的情緒修鍊", "高效時間管理", "有效的目標管理", "溝通與協調" }; int number = 1; for (var i = 0; i < 10; i++) { row = NpoiExcelExportHelper._.CreateRow(sheet, i + 2, 20); //sheet.CreateRow(i+2);//在上面表頭的基礎上創建行 switch (number) { case 1: cell = NpoiExcelExportHelper._.CreateCells(row, cellStyle, 0, "公共類課程"); break; case 7: cell = NpoiExcelExportHelper._.CreateCells(row, cellStyle, 0, "管理類課程"); break; } //創建單元格列公眾類課程 cell = NpoiExcelExportHelper._.CreateCells(row, cellStyle, 1, number.ToString()); cell = NpoiExcelExportHelper._.CreateCells(row, cellStyle, 2, currentDate.AddDays(number).ToString("yyyy-MM-dd")); cell = NpoiExcelExportHelper._.CreateCells(row, cellStyle, 3, curriculumList[i]); cell = NpoiExcelExportHelper._.CreateCells(row, cellStyle, 4, "提升，充實，拓展自己綜合實力"); cell = NpoiExcelExportHelper._.CreateCells(row, cellStyle, 5, "追逐時光_" + number + "號金牌講師！"); number++; } #endregion #endregion string folder = DateTime.Now.ToString("yyyyMMdd"); //保存文件到靜態資源文件夾中（wwwroot）,使用絕對路徑 var uploadPath = _environment.WebRootPath + "/UploadFile/" + folder + "/"; //excel保存文件名 string excelFileName = excelName + "_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"; //創建目錄文件夾 if (!Directory.Exists(uploadPath)) { Directory.CreateDirectory(uploadPath); } //Excel的路徑及名稱 string excelPath = uploadPath + excelFileName; //使用FileStream文件流來寫入數據（傳入參數為：文件所在路徑，對文件的操作方式，對文件內數據的操作） var fileStream = new FileStream(excelPath, FileMode.OpenOrCreate, FileAccess.ReadWrite); //向Excel文件對象寫入文件流，生成Excel文件 workbook.Write(fileStream); //關閉文件流 fileStream.Close(); //釋放流所佔用的資源 fileStream.Dispose(); //excel文件保存的相對路徑，提供前端下載 var relativePositioning = "/UploadFile/" + folder + "/" + excelFileName; excelFilePath = relativePositioning; } catch (Exception e) { result = false; resultMsg = e.Message; } return result; } } }
總結：
學習，工作，生活都一樣，很多東西都需要我們自己動手去嘗試我們才能真正的品嘗到其中的味道，收穫不一樣的驚喜。本文主要是個人在工作和學習中的一些總結，希望能夠幫助有需要的同學，別忘了給我star喲。
相關實例鏈接地址：
GitHub完整實例地址：
https://github.com/YSGStudyHards/NPOI-ExportWordAndExcel-ImportExcelData
.NET Core使用NPOI導出複雜，美觀的Excel詳解：
.NET Core使用NPOI導出複雜Word詳解：
.NET Core使用NPOI將Excel中的數據批量導出到MySQL：
