首頁 C# Read/Write Excel
文章
Cancel

C# Read/Write Excel

發表於 27/11/2022 更新於 15/02/2023
作者 jay
12 分鐘閱讀

前言

拿到了一份有作業上使用VBA Button Click的Excel，在其Click的商業邏輯之下，
手動Click 1次要跑完至少要花費30秒
所以想使用C#建立簡單的Winform模仿其商業邏輯來降低「需要多次Click情況」時的耗費時間
以下我將測試期間使用過的Excel套件都封裝成一樣的Class，並記錄其特點
以便我日後若有讀寫Excel的其他需求可以在這邊直接參考或Copy

Excel套件整理

Microsoft.Office.Interop.Excel

Desktop View

1

NuGet\Install-Package Microsoft.Office.Interop.Excel -Version 15.0.4795.1001

特點 1.每次執行 xlApp.Workbooks.Open(“FilePath”) 都會真的開起檔案(非背景運行)
2.需要額外使用Dispose來釋放記憶體
3.運作效率明顯較其他套件來的慢
4.帶有VBA的xls使用SavaAs Method結果失敗

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Excel = Microsoft.Office.Interop.Excel;
namespace ReadExcel
{
    internal class Program
    {
        static void Main(string[] args)
        {
            Excel.Application xlApp = new Excel.Application();
            Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"xls File Path");
            Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
            Excel.Range xlRange = xlWorksheet.UsedRange;
            int rowCount = xlRange.Rows.Count;
            int colCount = xlRange.Columns.Count;

            //iterate over the rows and columns and print to the console as it appears in the file
            //excel is not zero based!!
            for (int i = 1; i <= rowCount; i++)
            {
                for (int j = 1; j <= colCount; j++)
                {
                    //new line
                    if (j == 1)
                        Console.Write("\r\n");

                    //write the value to the console
                    if (xlRange.Cells[i, j] != null && xlRange.Cells[i, j].Value2 != null)
                    {
                        var test = xlRange.Cells[i, j].Value2.ToString();
                        Console.Write(xlRange.Cells[i, j].Value2.ToString() + "\t");
                    }
                    
                }
            }

            Excel excel = new Excel(@"C:\temp\VIS3_DEMO.xls", "ProdVIS4");
            int i = 3;
            int j = 3;
            if (excel.xlRange.Cells[i, j] != null && excel.xlRange.Cells[i, j].Value2 != null)
            {
                var test = excel.xlRange.Cells[i, j].Value2.ToString();
                Console.Write(excel.xlRange.Cells[i, j].Value2.ToString() + "\t");
            }

        }
    }
    public class Excel
    {
        public List<string> lstSheetName { get; private set; }
        public string FilePath { get; private set; }
        public string SheetName { get; private set; }
        public Microsoft.Office.Interop.Excel.Application xlApp { private set; get; }
        public Microsoft.Office.Interop.Excel.Workbook workbook { get; private set; }
        public Microsoft.Office.Interop.Excel.Sheets sheet { get; private set; }
        public Microsoft.Office.Interop.Excel.Range xlRange { private set; get; }
        public Microsoft.Office.Interop.Excel.Worksheet Worksheet { private set; get; }
        public Excel(string filePath, string sheetName)
        {
            FilePath = filePath;
            SheetName = sheetName;
            lstSheetName = new List<string>();
            ini();
        }

        public Microsoft.Office.Interop.Excel.Sheets SetSheet(string sheetName)
        {
            SheetName = sheetName;
            int iIndex = lstSheetName.IndexOf(sheetName);
            sheet = workbook.Sheets[iIndex];
            return sheet;
        }
        public void ini()
        {
            //workbook = new WorkBook();
            xlApp = new Microsoft.Office.Interop.Excel.Application();
            xlApp.Visible = false;
            workbook = xlApp.Workbooks.Open(FilePath);
            xlApp.Visible = false;

            foreach (Microsoft.Office.Interop.Excel.Worksheet wSheet in workbook.Worksheets)
            {
                lstSheetName.Add(wSheet.Name);
            }
            int iIndex = lstSheetName.IndexOf(this.SheetName);
            Worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[iIndex];
            xlRange = Worksheet.UsedRange;
        }
        public void Dispose()
        {
            if (workbook != null) workbook.Close(false, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
            if (xlApp != null) xlApp.Quit();
            GC.Collect();
            GC.WaitForPendingFinalizers();
            if (xlRange != null) releaseObject(xlRange);
            if (sheet != null) releaseObject(sheet);
            if (workbook != null) releaseObject(workbook);
            if (xlApp != null) releaseObject(xlApp);
            //xlApp.Quit();
        }
        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Unable to release the Object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        }
    }
}

IronXL

Desktop View

1

NuGet\Install-Package IronXL.Excel -Version 2022.11.10251

特點
1.我不太確定License中提及的版本及使用Nuget直接載來的版本差在哪
2.可以良好的讀寫透過Excel公式所產生的Value
3.讀取「帶有VBA的xls」時，使用SavaAs Method結果失敗
4.測試中出現過我無法成功Debug的Error

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

using IronXL;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace ReadExcel
{
    internal class Program
    {
        static void Main(string[] args)
        {
          //初始化時，設定要讀取的Excel路徑跟工作表名稱
          Excel excel = new Excel(@"File Path", "SheetName");
          var result=excel.sheet["A3"].StringValue;
          Console.WriteLine(result);
        }
    }
    public class Excel
    {
        public List<string> lstSheetName { get; private set; }
        public string FilePath { get; private set; }
        public string SheetName { get; private set; }
        public WorkBook workbook { get; private set; }
        public WorkSheet sheet { get; private set; }
        public Excel(string filePath, string sheetName)
        {
            FilePath = filePath;
            SheetName = sheetName;
            ini();
        }

        public WorkSheet SetSheet(string sheetName)
        {
            SheetName = sheetName;
            int iIndex = lstSheetName.IndexOf(sheetName);
            sheet = workbook.WorkSheets[iIndex];
            return sheet;
        }
        public void ini()
        {
            workbook = WorkBook.Load(FilePath);
            lstSheetName = workbook.WorkSheets.Select(c => c.Name).ToList();
            int iIndex = lstSheetName.IndexOf(this.SheetName);
            sheet = workbook.WorkSheets[iIndex];
        }
    }
}

FreeSpire.XLS

Desktop View

1

NuGet\Install-Package FreeSpire.XLS -Version 12.7.0

特點
1.由於FreeSpire.XLS是免費版，所以限制僅能讀取前200個Cell的內容
由於FreeSpire.XLS是免費版，所以限制僅能讀取前200個Cell的內容
以範例程式碼來說
excel.sheet[1 , 3].Value 到 excel.sheet[200, 3].Value 會有資料
excel.sheet[201, 3].Value 以後的資料為「”“」

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53

using Spire.Xls;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace ReadExcel
{
    internal class Program
    {
        static void Main(string[] args)
        {
          //初始化時，設定要讀取的Excel路徑跟工作表名稱
          Excel excel = new Excel(@"File Path", "SheetName");

          //設定要讀取的儲存格
          var Cell_Value=excel.sheet[3, 3].Value;

          //使用SetSheet("SheetName")可以變更讀取的工作表
          //SetSheet("SheetName")
          Console.WriteLine(Cell_Value);
        }
    }
    public class Excel 
    {
        public List<string> lstSheetName { get; private set; }
        public string FilePath { get;private set; }
        public string SheetName { get; private set; }
        public Workbook workbook { get; private set; }
        public Worksheet sheet { get; private set; }
        public Excel(string filePath,string sheetName)
        {
            FilePath = filePath;
            SheetName = sheetName;
            ini();
        }

        public Worksheet SetSheet(string sheetName)
        {
            int iIndex = lstSheetName.IndexOf(sheetName);
            sheet = workbook.Worksheets[iIndex];
            return sheet;
        }
        public void ini()
        {
            workbook = new Workbook(); 
            workbook.LoadFromFile(FilePath);
            lstSheetName = workbook.Worksheets.Select(c => c.Name).ToList();
            int iIndex = lstSheetName.IndexOf(this.SheetName);
             sheet = workbook.Worksheets[iIndex];
        }
    }
}

ExcelLibrary.SpreadSheet

Desktop View

1

NuGet\Install-Package ExcelLibrary -Version 1.2011.7.31

特點
1.沒有內建Excel Insert的Method
2.帶有VBA的xls使用SavaAs Method 成功
3.帶有VBA的xls使用SavaAs產生的檔案，其透過C#寫入的公式，儲存格會以公式本身的字串呈現，儲存格顯示的不是公式的運算結果

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58

using ExcelLibrary.SpreadSheet;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;

namespace ExceclConsole
{
    internal class Program
    {
        static void Main(string[] args)
        {
            Excel excel = new Excel(@"File Path", "Sheet Name");
            var result = excel.sheet.Cells[3, 3].Value;
            Console.WriteLine(result);

        }
    }
    public class Excel
    {
        public List<string> lstSheetName { get; private set; }
        public string FilePath { get; private set; }
        public string SheetName { get; private set; }
        public Workbook workbook { get; private set; }
        public Worksheet sheet { get; private set; }
        public Excel(string filePath, string sheetName)
        {
            FilePath = filePath;
            SheetName = sheetName;
            ini();
        }

        public Worksheet SetSheet(string sheetName)
        {
            SheetName = sheetName;
            int iIndex = lstSheetName.IndexOf(sheetName);
            sheet = workbook.Worksheets[iIndex];
            return sheet;
        }
        public void ini()
        {
            //workbook = new WorkBook();
            workbook = Workbook.Load(FilePath);
            lstSheetName = workbook.Worksheets.Select(c => c.Name).ToList();
            int iIndex = lstSheetName.IndexOf(this.SheetName);
            sheet = workbook.Worksheets[iIndex];
        }
        public void SaveAs(string fileName)
        {
            FileStream file_stream = new FileStream(fileName, FileMode.Create);
            workbook.SaveToStream(file_stream);
            file_stream.Close();
        }
    }
}

Epplus

Desktop View

1

NuGet\Install-Package EPPlus -Version 4.5.3.3

特點
1.不支援xls
2.「EPPlus 4.5.3.3」之前的版本可以免費使用,後面的版本要錢
備註
1.由於我需要讀取「帶有VBA的xls」所以我參考了這個
先透過Microsoft.Office.Interop.Excel轉檔成Xlsx，在進行讀寫
2.為了解決轉檔時，另存新檔會跑出詢問畫面的問題 我參考了這裡 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

using OfficeOpenXml;
using System.IO;

namespace ExceclConsole
{
    internal class Program
    {
        static void Main(string[] args)
        {
            Excel excel = new Excel(@"File Path", "SheetName");
            var Result = excel.sheet.Cells[3, 3].Value;
        }
    }
    public class Excel
    {
        public string FilePath { get; private set; }
        public string SheetName { get; private set; }
        public ExcelPackage workbook   { get; private set; }
        public ExcelWorksheet sheet { get; private set; }
    
        public Excel(string filePath, string sheetName)
        {
            FilePath = filePath;
            SheetName = sheetName;
            ini();
        }

        public ExcelWorksheet SetSheet(string sheetName)
        {
            sheet = workbook.Workbook.Worksheets[sheetName]; // 可以使用頁籤名稱
            return sheet;
        }
        public void ini()
        {
            workbook = new ExcelPackage(new FileInfo(FilePath));
            sheet =workbook.Workbook.Worksheets[SheetName];
        }
        public void Dispose() 
        {
            workbook.Dispose();
            sheet.Dispose();
        }
    }
}

Microsoft.Office.Interop.Excel的轉檔Function 

1
2
3
4
5
6
7
8
9
10
11
12
13

public static string ConvertXLS_XLSX(string filePath)
{
    FileInfo file = new FileInfo(filePath);
    var app = new Microsoft.Office.Interop.Excel.Application();
    var xlsFile = file.FullName;
    var wb = app.Workbooks.Open(xlsFile);
    var xlsxFile = xlsFile + "x";
    app.DisplayAlerts = false;
    wb.SaveAs(Filename: xlsxFile, FileFormat: Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook);
    wb.Close();
    app.Quit();
    return xlsxFile;
}

可導向到本地下載網址的套件 

1

NuGet\Install-Package Syroot.Windows.IO.KnownFolders -Version 1.2.3

刻製新的Excel檔案的範例寫法 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

using OfficeOpenXml;
using System;
using System.Diagnostics;
using System.IO;

namespace ConsoleApp7
{
    internal class Program
    {
        public static readonly string Downloads = new Syroot.Windows.IO.KnownFolder(Syroot.Windows.IO.KnownFolderType.Downloads).Path;
        static void Main(string[] args)
        {
            CreatNewExcel("test1");
        }
        public static void CreatNewExcel(string FileName)
        {
            ExcelPackage excel = new ExcelPackage();
            ExcelWorksheet workSheet = excel.Workbook.Worksheets.Add("Sheet1");
            workSheet.Cells[1, 1].Value = "Col_A";
            string strPath = Path.Combine(Downloads, $"{FileName}_{DateTime.Now.ToString("yymmddhhmmss")}.xlsx");
            FileStream objFileStrm = File.Create(strPath);
            objFileStrm.Close();
            File.WriteAllBytes(strPath, excel.GetAsByteArray());
            excel.Dispose();
            Process.Start(strPath);
        }
    }
}

備註 取得Rows最大值的方式 

1

workSheet.Dimension.End.Row

新增Excel的方式 

1
2
3
4
5
6
7
8
9
10
11
12
13
14

public void CreatNewExcel()
{
    ExcelPackage excel = new ExcelPackage();
    var workSheet = excel.Workbook.Worksheets.Add("Sheet1");
    /*
      * 詳細內容
      */
    string p_strPath = Path.Combine("FilePath", $"{DateTime.Now.ToString("yymmddhhmmss")}.xlsx");
    FileStream objFileStrm = File.Create(p_strPath);
    objFileStrm.Close();
    File.WriteAllBytes(p_strPath, excel.GetAsByteArray());
    excel.Dispose();
    Process.Start(p_strPath);
}

小記

Desktop View
如果在C#有代入跟相對位置有關的公式，例如上圖這種的公式
要使用的是FormulaR1C1然後進行Calculate才會有資料 

1
2

excel.sheet.Cells[1, 2].FormulaR1C1 = "=工作表2!A1";
excel.sheet.Cells[1, 2].Calculate();

如果使用的是Formula，下中斷點會發現資料為Null， Desktop View

其他套件

SpreadsheetLight

特點
1.非Open Source 所以Google資料偏少
2.由於我光是讀取「帶有VBA的xls」就失敗了，然後就沒有然後了

GitHub

https://github.com/digamana/ExceclRepo.git

本文由作者按照 CC BY 4.0 進行授權
最近更新
文章內容