[C#] EPPlus 讀寫(read/write) Excel檔案 懶人包範例程式碼
聽說EPPlus讀寫Excel檔寫法較簡潔，用了後真的感覺不錯
抄個筆記…
.ddl檔下載(Visual Studio要加入參考)：http://epplus.codeplex.com/
※2014-07-16 修正第一個範例程式碼，使用using語法釋放物件，for迴圈走訪Excel Row Range時略過空白列，所謂空白列就是紅框處(任何無填寫資料的列)↓
因為實務上我碰過，使用者刪除資料是選擇一個列，然後按Delete鍵，結果變成，它是一個空白資料列，但endRowNumber仍然會指到該地方，造成程式抓資料有問題
※正常User操作刪除Excel列應該是↓點選列編號右鍵=>刪除
為了配合User操作習慣也為了讓程式更完美一點，請見以下範例程式碼：
using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
namespace ConsoleReadEmptyRow
{
class Program
{
static void Main(string[] args)
{
//開檔
using (FileStream fs = new FileStream(@"C:\Read.xlsx", FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
//載入Excel檔案
using (ExcelPackage ep = new ExcelPackage(fs))
{
ExcelWorksheet sheet = ep.Workbook.Worksheets[1];//取得Sheet1
int startRowNumber = sheet.Dimension.Start.Row;//起始列編號，從1算起
int endRowNumber = sheet.Dimension.End.Row;//結束列編號，從1算起
int startColumn = sheet.Dimension.Start.Column;//開始欄編號，從1算起
int endColumn = sheet.Dimension.End.Column;//結束欄編號，從1算起
bool isHeader = true;
if (isHeader)//有包含標題
{
startRowNumber += 1;
}
////寫入標題文字
sheet.Cells[1, 1].Value = "第1欄";
sheet.Cells[1, 2].Value = "第2欄";
for (int currentRow = startRowNumber; currentRow <= endRowNumber; currentRow++)
{
ExcelRange range = sheet.Cells[currentRow, startColumn, currentRow, endColumn];//抓出目前的Excel列
if (range.Any(c=> !string.IsNullOrEmpty(c.Text))==false)//這是一個完全空白列(使用者用Delete鍵刪除動作)
{
continue;//略過此列
}
//讀值
string cellValue = sheet.Cells[currentRow, 1].Text;//讀取格式化過後的文字(讀取使用者看到的文字)
//寫值
sheet.Cells[currentRow, 1].Value = cellValue + "test";
}
//建立檔案
using (FileStream createStream = new FileStream(@"C:\output.xlsx", FileMode.Create, FileAccess.Write, FileShare.ReadWrite))
{
ep.SaveAs(createStream);//存檔
}//end using
}//end using
}//end using
}
}
}
2014/04/29 追記說明為何使用Cells[i,i].Text讀值
如果使用者在Cell裡輸入一個字串「2014/01/01」，Excel程式會很雞婆地把該字串轉成日期格式的「2014/1/1」
這時候用
Convert.ToString(Cells[i,i].Value)或Cells[i,i].Text 方法
來讀值的話就會有差異
用Convert.ToString(Cells[i,i].Value)讀日期的話會包含時間資訊(但使用者不會在Excel上看到)，用Cells[i,i].Text讀日期的話則是讀取使用者看到的文字
2013.5.8 追記如何建立Excel檔案
/// <summary>
/// 寫入Excel
/// </summary>
/// <param name="strFilePath">寫檔路徑</param>
static void SheetWrite(string strFilePath, DataTableCollection objDTC1,DataTableCollection objDTC2 ,DataTableCollection objDTC3)
{
//檔案已存在
if (File.Exists(strFilePath))
{
//刪除檔案
File.Delete(strFilePath);
}
//在記憶體中建立一個Excel物件
ExcelPackage ep = new ExcelPackage();
//加入一個Sheet
ep.Workbook.Worksheets.Add("MySheet");
//注意sheetName不可包含特殊字元，否則Exception
//sheetName只能31個字，超過部份會自動被截斷，且無Exception拋出
//取得剛剛加入的Sheet(實體Sheet就叫MySheet)
ExcelWorksheet sheet1 = ep.Workbook.Worksheets["MySheet"];//取得Sheet1
sheet1.Cells[1, 1].Value = "欄位1";//加入標頭
sheet1.Cells[1, 2].Value = "欄位2";//同上
//迴圈部份自由料理
if (objDTC1.Count>0 && objDTC1[0].Rows.Count>0)
{
for (int i = 0; i < objDTC1[0].Rows.Count;i++ )
{
sheet1.Cells[i+2, 1].Value = objDTC1[0].Rows[i][0].ToString();
sheet1.Cells[i+2, 2].Value = objDTC1[0].Rows[i][1].ToString();
}
}
//建立檔案串流
FileStream OutputStream = new FileStream(strFilePath, FileMode.Create, FileAccess.Write, FileShare.ReadWrite);
//把剛剛的Excel物件真實存進檔案裡
ep.SaveAs(OutputStream);
//關閉串流
OutputStream.Close();
}
2013.8.13追記：如果想在記憶體中建立Excel並輸出給Client下載的話
ASP.net MVC範例
[HttpGet]
public ActionResult Export()
{
//在記憶體中建立一個Excel物件
ExcelPackage ep = new ExcelPackage();
//加入一個Sheet
ep.Workbook.Worksheets.Add("MySheet");
//取得剛剛加入的Sheet(實體Sheet就叫MySheet)
ExcelWorksheet sheet1 = ep.Workbook.Worksheets["MySheet"];//取得Sheet1
sheet1.Cells[1, 1].Value = "欄位1";//加入標頭
sheet1.Cells[1, 2].Value = "欄位2";//隨便寫值，型別最好都給字串，如果給數字的話，在Excel有可能被自動轉成科學記號
byte[] file = ep.GetAsByteArray();
return File(file, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Export.xlsx");
}ASP.net WebForm匯出下載範例
/// <summary>
/// 匯出Excel報表
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnExport_Click(object sender, EventArgs e)
{
//在記憶體中建立一個Excel物件
ExcelPackage ep = new ExcelPackage();
//加入一個Sheet，並取得剛剛加入的Sheet
ExcelWorksheet sheet1 = ep.Workbook.Worksheets.Add("MySheet");
sheet1.Cells[1, 1].Value = "欄位1";//加入標頭
sheet1.Cells[1, 2].Value = "欄位2";//隨便寫值
//將檔案轉成byte陣列
byte[] file = ep.GetAsByteArray();
//先清除Response中雜七雜八資料
Response.Clear();
//設定MIME類型
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
//設定預設檔名
Response.AddHeader("content-disposition", "attachment;filename=" + "Export.xlsx");
//用 BinaryWrite 輸出byte[]檔案
Response.BinaryWrite(file);
Response.End();
}//end click
2013.11.6 讓每個Column靠左對齊，Cell寬度自動調整
int startColumn = sheet1.Dimension.Start.Column;
int endColumn = sheet1.Dimension.End.Column;
for (int i = startColumn; i <= endColumn; i++)
{
sheet1.Column(i).Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;//靠左對齊
// sheet1.Column(i).Width = 80;//固定寬度寫法
sheet1.Column(i).AutoFit();//依內容fit寬度
}//End for
2013.11.19
Cell斷行的寫法
sheet1.Cells[1, 1].Value = "店名\n(公司名稱)";
sheet1.Cells[1, 1].Style.WrapText = true;
2014.3.18追記，如果cell為數字，讀取日期的方法：Reading Datetime value From Excel sheet
2014.3.24追記，設定儲存格背景顏色方法：Set custom BackgroundColor of a Excel sheet cell using epplus c#
2020.11.13追記 字體&儲存格背景色程式碼如下：
ExcelRange range = sheet.Cells[1, 1, 1, 8];
//粗體
range.Style.Font.Bold = true;
//字顏色
range.Style.Font.Color.SetColor(System.Drawing.Color.White);//白字
//背景色
range.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;//必填
range.Style.Fill.BackgroundColor.SetColor(0, 128, 128, 0);//綠底2021-05-24追記：設定儲存格格式為文字字串
sheet.Cells[0, 0].Style.Numberformat.Format = "@";