透過 EPPlus 來存取 EXCEL 檔案

  • 8045
  • 0
  • C#
  • 2014-08-22

透過 EPPlus 來存取 EXCEL 檔案

其實在 EPPlus 的官網上面,就已經有不少的範例說明,所以就不多做說明,因此只介紹常用的操作。

 

1. 建立 EXCEL 檔案


FileInfo FileInfoXLS = new FileInfo(ExportFileName);
using (ExcelPackage pkgXLS = new ExcelPackage(FileInfoXLS))
{
      pkgXLS.Workbook.Worksheets.Add(NewSheetName);
      ExcelWorksheet CurrentSheet = pkgXLS.Workbook.Worksheets[NewSheetName];
      CurrentSheet.Cells.LoadFromDataTable(ExportDataTable, true);
      pkgXLS.Save();
      pkgXLS.Dispose();
}

ExportFileName:要匯出的 EXCEL 檔案,如果檔案存在則會開啟,不存在則會建立檔案。

ExcelPackage 支援 FileInfo 與 Stream,在此建議使用 FileInfo 物件會比較沒有問題。(個人切身感受)

使用 ExcelPakage.Worksheet.Add 方法來新增一個工作表,然後再使用 Cells.LoadFromDataTable 將資料由 Datatable 轉入。除了這方法之外,還可以使用 LoadFromArrays、LoadFromText 與 LoadFromCollection。

當然也可以指定 Row 與 Cell 來塞入資料。

例如:

xlsSheet.Cells["A1"].Value = "AA";  A1 欄位

xlsSheet.Cells[1,2].Value = "BB";    第1行第1欄

xlsSheet.Cells[1,1,4,5].Value = "CC";    第1行第1欄到第4行第5欄

 

當然也可以使用 EXCEL 的公式

xlsSheet.Cells.FormulaR1C1 = "EXCEL 的公式,例:SUM(A1:B6)"

 

最後再使用 Save 就可以將資料存檔。

 

另外如果要使用範本來產生 EXCEL 檔時,在 ExcelPackage 建立時就帶入範本物件。

 


FileInfo FileInfoXLS = new FileInfo(ExportFileName);
FileInfo FileInfoXLSTemplate = new FileInfo(TemplateFileName);
ExcelPackage pkgXLS = new ExcelPackage(FileInfoXLS, FileInfoXLSTemplate);

ExportFileName:要匯出的 EXCEL 檔案,如果檔案存在則會開啟,不存在則會建立檔案。

TemplateFileName:要套用的範本檔案。

 

2. 讀取 EXCEL 檔案


FileInfo FileInfoXLS = new FileInfo(ExcelFileName);
ExcelPackage pkgXLS = new ExcelPackage(FileInfoXLS);
ExcelWorksheet CurrentSheet = pkgXLS.Workbook.Worksheets[SheetName];

列出所有 Sheet 名稱


foreach (ExcelWorksheet tmpSheet in pkgXLS.Workbook.Worksheets)
{
    MessageBox.Show(tmpSheet.Name);
}

取回不重覆的工作表名稱

讀取目前 EXCEL 檔案,並且來比對所有的工作表名稱,當有重覆名稱的時後,自動在名稱後加1。例:Sheet1 就會傳回 Sheet2、「工作表3」就會傳回「工作表4」或是 Temp_1 就回傳回 TemP_2 。


private string GetUpdateSheetName(string SheetName, ExcelWorkbook wkbXLS)
{
    string NewSteetName = SheetName;
    int tmpCount;
    foreach (ExcelWorksheet tmpSheet in wkbXLS.Worksheets)
    {
        if (tmpSheet.Name == NewSteetName)
        {
            if (tmpSheet.Name.ToUpper().StartsWith("SHEET"))
            {
                if (tmpSheet.Name.ToUpper().Replace("SHEET", "") == "")
                {
                    NewSteetName += "1";
                }
                else
                {
                    if (int.TryParse(tmpSheet.Name.ToUpper().Replace("SHEET", ""), out tmpCount))
                    {
                        tmpCount++;
                        NewSteetName = "SHEET" + tmpCount.ToString();
                    }
                    else
                    {
                        NewSteetName += "1";
                    }
                }
            }
            else if (tmpSheet.Name.StartsWith("工作表"))
            {
                if (tmpSheet.Name.ToUpper().Replace("工作表", "") == "")
                {
                    NewSteetName += "1";
                }
                else
                {
                    if (int.TryParse(tmpSheet.Name.ToUpper().Replace("工作表", ""), out tmpCount))
                    {
                        tmpCount++;
                        NewSteetName = "工作表" + tmpCount.ToString();
                    }
                    else
                    {
                        NewSteetName += "1";
                    }
                }
            }
            else if (tmpSheet.Name.Split('_').Length > 1)
            {
                string[] tmpS = tmpSheet.Name.Split('_');
                if (int.TryParse(tmpS[tmpS.Length - 1], out tmpCount))
                {
                    NewSteetName = tmpSheet.Name.Substring(0, tmpSheet.Name.Length - tmpCount.ToString().Length);
                    tmpCount++;
                    NewSteetName += tmpCount.ToString();
                }
                else
                {
                    NewSteetName += "_1";
                }
            }
            else
            {
                NewSteetName = SheetName + "_1";
            }
        }
    }
    return NewSteetName;
}

EPPlus 真的是一個存取 EXCEL 檔案不錯的元件。唯一小缺撼的是,它僅支援 XLSX 的格式,也就是說 Office 2007 以上的版本。

所以如果要存取 XLS Office 2003 的 EXCEL 檔案的話,那麼就去使用 NPOI 或是直接透過 OLEDB 來存取。

 

不過在 OLEDB 下無法建立超過 255 個欄位的 EXCEL 檔案,這點需要注意一下。


程式是運氣與直覺堆砌而成的奇蹟。
若不具備這兩者,不可能以這樣的工時實現這樣的規格。
修改規格是對奇蹟吐槽的褻瀆行為。
而追加修改則是相信奇蹟還會重現的魯莽行動。