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

Bug生活2048發表於2019-02-16
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;//隱藏sheet
worksheet.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總的來說還是比較好用的，也能滿足基本需求，匯出效率也不錯，大家可以嘗試下

相關文章