Source: https://igouist.github.io/post/2020/04/epplus/

前陣子工作需要匯出一些資料表，因此用到了 EPPlus 這套工具來把資料匯出成 Excel。由於這需求似乎會挺常遇到的，決定記一下。

目標：稍微紀錄一下這次碰到 EPPlus 的用法。

EPPlus 是在 .NET Framework 或 .NET Core 上提供控制 Excel 的元件，操作簡單好懂，當有需要在網頁上讓人將表格資料下載成 Excel 的功能時就可以試著使用。常常一併被提起的還有另一個一樣老牌的相似功能元件 NPOI，不過我個人只用過 EPPlus 便不再贅述。關於更詳細的介紹，可以參閱黑暗執行緒的這篇 比 NPOI 更討喜的 Excel 元件 - EPPlus!，儘管是有些久遠的文章但仍能迅速了解 EEplus 的賣點和差異。

我在使用 EPPlus 時主要的參考來自於 EPPlus 的 Wiki，每個功能都有說明及範例，同時也有範例專案可以下載，寫得相當詳細。另外還找了如 導出 Excel 之 Epplus 使用教程 - Wico’s Blog、使用 EPPLUS 操控 Excel - 碼農的學習日誌 這些有逐步說明的文章，在此感謝。

首先從建立一個 Excel 開始；要注意的是開啟檔案之後也要記得建分頁出來。後續的寫入資料等都是對分頁去做動作。

我們使用 new ExcelPackage() 來開一個新的 Excel 的處理工作，而結束之後一定要記得 SaveAs。

ExcelPackage.LicenseContext = LicenseContext.NonCommercial; // 關閉新許可模式通知
// 沒設置的話會跳出 Please set the excelpackage.licensecontext property

var file = new FileInfo(@"D:\ExampleExcel.xlsx"); // 檔案路徑
using (var excel = new ExcelPackage())
{
    // 建立分頁
    var ws = excel.Workbook.Worksheets.Add("MySheet");

    // 寫入資料試試
    ws.Cells[2, 1].Value = "測試測試";

    // 儲存 Excel
    excel.SaveAs(file);
}

從這邊也可以認出 ws.Cells[2, 1].Value 的第一個數字是從 ↓ 數下來的，第二個是往 → 數過去的（很抱歉這樣表示，因為我常弄反行跟列，這樣之後參考比較好理解）

另外眼尖的朋友也可以察覺到一個重點，儲存格是從 1 開始數的：不是 0 ！ 也就是說 A1 這一格是 [1, 1]，不要打成 [0, 0] 了。當然，對於困擾的朋友，EEPlus 也提供了 ws.Cells[“B1”] 的寫法，可以用字串傳入的方式直接指定在 Excel 的儲存格位置，比起數格子方便多了，後續也會在示範標註。

編輯時和建立一樣，從 ExcelPackage() 開始動作，這次我們將檔案直接傳入，如此 Save 的時候就可以不用再傳一次。關於各種操作直接在程式碼上的註解進行說明。

// 特別注意，頁籤和儲存格等操作 是由 1 開始而非 0
// 打開存在的 Excel 檔案
var excelFile = new FileInfo(@"D:\ExampleExcel.xlsx");
using (var excel = new ExcelPackage(excelFile))
{
    // 指定頁籤
    //ExcelWorksheet sheet1 = excel.Workbook.Worksheets[1]; // 這邊用是 1 在 Core 用是 0 = =
    ExcelWorksheet sheet1 = excel.Workbook.Worksheets["MySheet"]; // 可以使用頁籤名稱

    #region -- 儲存格讀寫 --

    // 寫入資料，[行－，列｜] 或直接指定 ["儲存格"]
    sheet1.Cells[2, 1].Value = "開啟測試"; // 嚴謹一點可以用 GetValue 和 SetValue 來操作
    //sheet1.Cells["B1"].Value = "開啟測試"; // 此兩行等價

    sheet1.Cells[3, 3, 5, 5].Value = "多格操作測試"; // 從 (3, 3) 一路框到 (5, 5)，包含頭尾
    //sheet1.Cells["C3:E5"].Value = "多格操作測試"; // 此兩行等價

    sheet1.Cells[3, 1].LoadFromText("LoadFromText Test"); // 從字串讀入資料，可用於寫入 CSV 之類的場合

    var coll = new List<string> { "LoadFromCollTest1", "LoadFromCollTest2", "LoadFromCollTest3" };
    sheet1.Cells[4, 1].LoadFromCollection(coll); // 從集合類型的參數讀入資料，會按照行（＝ D1 E1 F1...）依序排列
    // 可從 LoadFromCollection 推測 LoadFromDataReader, LoadFromDataTable, LoadFromArrays 等函式的行為，故省略

    #endregion -- 儲存格讀寫 --

    #region -- 儲存格樣式 --

    // 可以用宣告的方式一併操作指定區域內的儲存格
    using (var range = sheet1.Cells[1, 1, 1, 5])  // 直接選取 A1 到 A5
    {
        range.Value = "樣式測試";
        range.Style.Font.Bold = true; // 粗體
        range.Style.Font.Color.SetColor(Color.White); // 字體顏色

        range.Style.Fill.PatternType = ExcelFillStyle.Solid; // 設定背景填色方法，沒有這一行就上背景色會報錯
                                                             // Solid = 填滿；另外還有斜線、交叉線、條紋等
        range.Style.Fill.BackgroundColor.SetColor(Color.DarkBlue); // 儲存格顏色
    }

    #endregion -- 儲存格樣式 --

    excel.Save(); // 儲存變更
}

執行完的結果會像這樣，可以和上面的程式碼對照看是哪一部份的效果

以上部份嘗試了 資料的讀寫（如寫入單格的資料內容、寫入一個串列等）以及樣式的設定（字體顏色、儲存格顏色等）等。由於其中的操作能選擇的樣式相當繁多，例如背景填色就有好幾種，因此列出來並不是明智的做法，先知道基本的語法之後再自己開編譯器看有哪些選項、或是將對應的操作拿到官方文檔找看有哪些會是比較實際的做法。

這篇只示範資料的寫入，當然 Excel 的處理沒有那麼簡單，可以參照 官方Wiki 的這兩個部份：公式的計算 及 圖表的處理

Epplus 的官方文檔範例寫得挺不錯的，有更進一步需求的朋友可以翻一下範例，抓下來跑跑看，可以解決大部分的問題。

這邊紀錄一下工作需求時用到的做法，由於匯出資料的型別繁多，唯一的共通點就是都很多筆。為了方便寫了一個針對多筆的泛型匯出函式，特別在這裡記錄下來。 其實這篇也是為了把這個部份記錄下來方便以後可以回來抄才開的坑…

void Main()
{
	var data = new List<testClass>
	{
		new testClass{ name = "香蕉" },
		new testClass{ name = "番茄" },
		new testClass{ name = "蘋果" },
		new testClass{ name = "鳳梨" }
	};
	var excel = ExportExcel(data);
	excel.Dump();
}

public FileInfo ExportExcel<T>(IEnumerable<T> data) where T: class
{
	//var output = new MemoryStream();
	var output = new FileInfo("D:\\ExportExcelTest-" + DateTime.Now.ToString("yyyy-MM-dd-hh-mm-ss") + ".xlsx");
	using (var excel = new ExcelPackage(output))
	{
		var ws = excel.Workbook.Worksheets.Add("Sheet1"); // 建立分頁

		// 用反射拿出有 DisplayName 的屬性
		var properties = typeof(T)
            .GetProperties()
            .Where(prop => prop.IsDefined(typeof(DisplayNameAttribute)));

		var rows = data.Count() + 1;   // 直：資料筆數（記得加標題列）
		var cols = properties.Count(); // 橫：類別中有別名的屬性數量

		if(rows > 0 && cols > 0)
		{
			ws.Cells[1, 1].LoadFromCollection(data, true); // 寫入資料

			// 儲存格格式
			var colNumber = 1;
			foreach (var prop in properties)
			{
				// 時間處理，如果沒指定儲存格格式會變成 通用格式，就會以 int＝時間戳 的方式顯示
				if (prop.PropertyType.Equals(typeof(DateTime)) ||
				   prop.PropertyType.Equals(typeof(DateTime?)))
				{
					ws.Cells[2, colNumber, rows, colNumber].Style.Numberformat.Format = "mm-dd-yy hh:mm:ss";
				}
				colNumber += 1;
			}

			// 樣式準備
			using (var range = ws.Cells[1, 1, rows, cols])
			{
				ws.Cells.Style.Font.Name = "新細明體";
				ws.Cells.Style.Font.Size = 12;
				ws.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; // 置中
				ws.Cells.AutoFitColumns(); // 欄寬

				// 框線
				range.Style.Border.Top.Style = ExcelBorderStyle.Thin;
				range.Style.Border.Left.Style = ExcelBorderStyle.Thin;
				range.Style.Border.Right.Style = ExcelBorderStyle.Thin;
				range.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;

				// 標題列
				var title = ws.Cells[1, 1, 1, cols];
				title.Style.Fill.PatternType = ExcelFillStyle.Solid; // 設定背景填色方法
				title.Style.Fill.BackgroundColor.SetColor(Color.LightGray);
			}
		}
		else
		{
			Debug.WriteLine("未列印資料，請檢查是否傳入資料為空，或指定類別未具有公開且加上 DisplayName 的屬性。");
		}
		excel.Save(); // 儲存 Excel
	}
	//output.Position = 0; // 如果是使用 stream 的方式讓人下載，請記得將指標移回資料起始

	return output;
}

public class testClass
{
	[DisplayName("編號")]
	public Guid id { set; get; } = Guid.NewGuid();

	[DisplayName("名稱")]
	public string name { set; get; }
}

這次是為了把小工具記下來，方便以後在外可以直接開網站起來左手抄右手所以硬是丟了一篇出來；關於基本操作的部份說明實在有點偷懶，以後有機會再進行補充和功能示範。現在還請海涵，幸虧官方的 Github 頁面 Wiki 實在相當完善，對操作有疑問的朋友可以先按照官方 Wiki 跑一次，相信可以非常快上手。

EPPlus Wiki

比 NPOI 更討喜的 Excel 元件 - EPPlus! - 黑暗執行緒

導出 Excel 之 Epplus 使用教程 - Wico’s Blog

使用 EPPLUS 操控 Excel - 碼農的學習日誌

.Net Excel 匯出圖表Demo(柱狀圖，多標籤頁) - IT閱讀

Asp.net MVC: Entity Framework 連線資料庫

Asp.net MVC: 連線資料庫、簡單實作 CRUD

我要訂便當 (3): 用 Python + Line Notify 傳送通知

Visual studio 環境設定 —— 字型、套件、快捷鍵

SikuliX —— 針對圖形介面寫自動化腳本的小幫手

worksheet.Cells[FromRow, FromColumn, ToRow, ToColumn].Merge = true;

ws.Column(1).Width = 50;

或是

ws.Cells["A1:K20"].AutoFitColumns();

Back

3 person(s) visited this page until now.