[C#] EPPlus 讀寫(read/write) Excel檔案懶人包範例程式碼

2012-07-13

80820
0
.Net Framework
2021-05-24

聽說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 = "@";

回首頁