.net core下對於Excel的一些操作及使用

在上一篇[.net core下配置、資料庫訪問等操作實現]主要介紹了讀取配置，資料庫操作的一些方法實例，本篇主要介紹下 .net core下針對Excel的相關操作。

對於後台相關的管理系統，Excel導出是基本的功能，下面就簡單說下實現該功能的代碼實現吧

EPPlus與NPOI的選擇

相對於大名鼎鼎的NPOI來說，EPPlus的API更加友好，導出數據的能力也比NPOI更強大點，但在操作Excel的功能上還是NPOI強一點，如果你想導出比較複雜的Excel的話可以使用NPOI，但對於常規需求的話EPPlus基本滿足了。

網上也有些兩者對比的文章，可以參考下，比如[C# NPOI導出Excel和EPPlus導出Excel比較]

NPOI和EPPlus均已支持 .net core，看不同需求自行選擇，這裡主要講下EPPlus的使用。

EPPlus的基本介紹

EPPlus是一個使用Open Office XML(xlsx)文件格式，能讀寫Excel 2007/2010 文件的開源組件，在導出Excel的時候不需要電腦上安裝office。官網地址：http://epplus.codeplex.com/

使用的話直接NuGet上獲取對應的dll即可。

但有一點注意，EPPlus不支持2003版本的Excel。

創建保存Excel

using (ExcelPackage package=new ExcelPackage()){ ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Sheet1");//創建worksheet package.Save();}

單元格賦值

單元格賦值很簡單，指定對應的單元格就可以直接賦值，價格遍歷循環就可以進行批量的操作了

worksheet.Cells[1, 1].Value = "測試";//直接指定行列數進行賦值worksheet.Cells["A1"].Value = "賦值";//直接指定單元格進行賦值

設置單元格樣式

worksheet.Cells[1, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中worksheet.Cells[1, 1].Style.VerticalAlignment = ExcelVerticalAlignment.Center;//垂直居中worksheet.Cells[1, 4, 1, 5].Merge = true;//合併單元格worksheet.Cells.Style.WrapText = true;//自動換行

設置字體

worksheet.Cells[1, 1].Style.Font.Bold = true;//字體為粗體worksheet.Cells[1, 1].Style.Font.Color.SetColor(Color.White);//字體顏色worksheet.Cells[1, 1].Style.Font.Name = "微軟雅黑";//字體worksheet.Cells[1, 1].Style.Font.Size = 12;//字體大小

設置單元格邊框

worksheet.Cells[1, 1].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));//設置單元格所有邊框worksheet.Cells[1, 1].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;//單獨設置單元格底部邊框樣式和顏色（上下左右均可分開設置）worksheet.Cells[1, 1].Style.Border.Bottom.Color.SetColor(Color.FromArgb(191, 191, 191));

設置單元格高和寬

worksheet.Cells.Style.ShrinkToFit = true;//單元格自動適應大小worksheet.Row(1).Height = 15;//設置行高worksheet.Row(1).CustomHeight = true;//自動調整行高worksheet.Column(1).Width = 15;//設置列寬

設置單元格格式

worksheet.Cells[1, 1].Style.Numberformat.Format = "#,##0.00";//這是保留兩位小數

設置sheet背景

worksheet.View.ShowGridLines = false;//去掉sheet的網格線worksheet.Cells.Style.Fill.PatternType = ExcelFillStyle.Solid;worksheet.Cells.Style.Fill.BackgroundColor.SetColor(Color.LightGray);//設置背景色worksheet.BackgroundImage.Image = Image.FromFile(@"firstbg.jpg");//設置背景圖片

隱藏sheet

worksheet.Hidden = eWorkSheetHidden.Hidden;//隱藏sheetworksheet.Column(1).Hidden = true;//隱藏某一列worksheet.Row(1).Hidden = true;//隱藏某一行

圖片操作

有時候需求會將某個圖片保存至Excel中，代碼如下：

ExcelPicture picture = worksheet.Drawings.AddPicture("picture", Image.FromFile(@"firstbg.jpg"));//插入圖片picture.SetPosition(100, 100);//設置圖片的位置picture.SetSize(100, 100);//設置圖片的大小

Excel加密和鎖定

有時候導出的Excel不希望別人修改，可對Excel進行加密，代碼如下：

worksheet.Protection.IsProtected = true;//設置是否進行鎖定worksheet.Protection.SetPassword("yk");//設置密碼worksheet.Protection.AllowAutoFilter = false;//下面是一些鎖定時許可權的設置worksheet.Protection.AllowDeleteColumns = false;worksheet.Protection.AllowDeleteRows = false;worksheet.Protection.AllowEditScenarios = false;worksheet.Protection.AllowEditObject = false;worksheet.Protection.AllowFormatCells = false;worksheet.Protection.AllowFormatColumns = false;worksheet.Protection.AllowFormatRows = false;worksheet.Protection.AllowInsertColumns = false;worksheet.Protection.AllowInsertHyperlinks = false;worksheet.Protection.AllowInsertRows = false;worksheet.Protection.AllowPivotTables = false;worksheet.Protection.AllowSelectLockedCells = false;worksheet.Protection.AllowSelectUnlockedCells = false;worksheet.Protection.AllowSort = false;

實際demo

首先定義一個通用創建ExcelPackage的方法：

/// <summary>/// 創建ExcelPackage/// </summary>/// <typeparam name="T"></typeparam>/// <param name="datas">數據實體</param>/// <param name="columnNames">列名</param>/// <param name="outOfColumns">排除列</param>/// <param name="sheetName">sheet名稱</param>/// <param name="title">標題</param>/// <param name="isProtected">是否加密</param>/// <returns></returns>private static ExcelPackage CreateExcelPackage<T>(List<T> datas, Dictionary<string, string> columnNames, List<string> outOfColumns, string sheetName = "Sheet1",string title="",int isProtected=0){var package = new ExcelPackage();var worksheet = package.Workbook.Worksheets.Add(sheetName);if(isProtected==1){ worksheet.Protection.IsProtected = true;//設置是否進行鎖定 worksheet.Protection.SetPassword("xiangzhidaomimama");//設置密碼 worksheet.Protection.AllowAutoFilter = false;//下面是一些鎖定時許可權的設置 worksheet.Protection.AllowDeleteColumns = false; worksheet.Protection.AllowDeleteRows = false; worksheet.Protection.AllowEditScenarios = false; worksheet.Protection.AllowEditObject = false; worksheet.Protection.AllowFormatCells = false; worksheet.Protection.AllowFormatColumns = false; worksheet.Protection.AllowFormatRows = false; worksheet.Protection.AllowInsertColumns = false; worksheet.Protection.AllowInsertHyperlinks = false; worksheet.Protection.AllowInsertRows = false; worksheet.Protection.AllowPivotTables = false; worksheet.Protection.AllowSelectLockedCells = false; worksheet.Protection.AllowSelectUnlockedCells = false; worksheet.Protection.AllowSort = false;}var titleRow = 0;if(!string.IsNullOrWhiteSpace(title)){ titleRow = 1; worksheet.Cells[1, 1, 1, columnNames.Count()].Merge = true;//合併單元格 worksheet.Cells[1, 1].Value = title; worksheet.Cells.Style.WrapText = true; worksheet.Cells[1, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中 worksheet.Cells[1, 1].Style.VerticalAlignment = ExcelVerticalAlignment.Center;//垂直居中 worksheet.Row(1).Height = 30;//設置行高 worksheet.Cells.Style.ShrinkToFit = true;//單元格自動適應大小}//獲取要反射的屬性,載入首行Type myType = typeof(T);List<PropertyInfo> myPro = new List<PropertyInfo>();int i = 1;foreach (string key in columnNames.Keys){ PropertyInfo p = myType.GetProperty(key); myPro.Add(p); worksheet.Cells[1+ titleRow, i].Value = columnNames[key]; i++;}int row = 2+ titleRow;foreach (T data in datas){ int column = 1; foreach (PropertyInfo p in myPro.Where(info => !outOfColumns.Contains(info.Name))) { worksheet.Cells[row, column].Value = p == null ? "" : Convert.ToString(p.GetValue(data, null)); column++; } row++;}return package;}

然後將ExcelPackage轉換成Byte類型，以流的方式進行導出：

public static Byte[] GetByteToExportExcel<T>(List<T> datas, Dictionary<string, string> columnNames, List<string> outOfColumn, string sheetName = "Sheet1",string title="",int isProtected=0){ using (var fs = new MemoryStream()) { using (var package = CreateExcelPackage(datas, columnNames, outOfColumn, sheetName, title, isProtected)) { package.SaveAs(fs); return fs.ToArray(); } }}

最後就可以直接進行導出了：

public async Task<IActionResult> GetExcel(UserModel entity,int isProtected=0){ var result = await ReportServices.GetAttendance(entity); var columns = new Dictionary<string, string>() { { "Id","序號"}, { "UserName","用戶名"}, { "Remark","備註"} }; var fs = ExcelHelper.GetByteToExportExcel(result.Collection.ToList(), columns, new List<string>(),"Sheet1","", isProtected); return File(fs, "application/vnd.android.package-archive", $"ExcelDemo.xlsx");}

總結

EPPlus總的來說還是比較好用的，也能滿足基本需求，導出效率也不錯，大家可以嘗試下

推薦閱讀：