[C# .Net] 使用 Microsoft.Office.Interop.Excel 讀 / 寫 Excel 2010

[C# .Net] 使用 Microsoft.Office.Interop.Excel 讀 / 寫 Excel 2010

[C# .Net] Microsoft.Office.Interop.Excel 讀 / 寫 Excel 語法概全 這篇當時是使用Excel2003所完成,距離現在2010的版本已經有點久遠了,版本不同寫法也有點不同,今天就來更新一下用法。

首先要先加入Excel.exe參考

image

 

再來匯入 using Excel = Microsoft.Office.Interop.Excel;

http://www.dotblogs.com.tw/yc421206/archive/2009/12/06/12297.aspx

 

再來瞭解一下我們會用到以下類別,這已經跟舊版的Excel名稱不同了。

Excel.Application:Excel應用程式

Excel.Workbook:應用程式裡的活頁簿,預設情況下,不管你開幾個Excel檔案,在工作管理員裡只會出現一個Excel.exe

Excel.Worksheet:活頁簿裡的工作表

Excel.Range:工作表裡的儲存格,一格也是Range,多格也是Range,用法Excel.Range[“A1”];

Excel.Range.Cells:這是儲存格的最小單位,代表一格的Range,用法Excel.Range.Cells[1,1];

 

image

 image

 

接下來實作,initailExcel方法會檢查有無Excel.exe執行,若有則引用舊有Excel

Excel.Application _Excel = null;

void initailExcel()
{
    //檢查PC有無Excel在執行
    bool flag = false;
    foreach (var item in Process.GetProcesses())
    {
        if (item.ProcessName == "EXCEL")
        {
            flag = true;
            break;
        }
    }

    if (!flag)
    {
        this._Excel = new Excel.Application();
    }
    else
    {
        object obj = Marshal.GetActiveObject("Excel.Application");//引用已在執行的Excel
        _Excel = obj as Excel.Application;
    }

    this._Excel.Visible = true;//設false效能會比較好
}
 

初始化Excel.Application在.NET裡沒有版本之分,但下列Method就有了


@VS2010以前的版本VS2005 C#2.0~VS2008 C#3.0

private void button1_Click(object sender, EventArgs e)
{
    initailExcel();
    operExcel();
}

private void button2_Click(object sender, EventArgs e)
{
    this._Excel.Quit();
    this._Excel = null;
    //確認已經沒有excel工作再回收
    GC.Collect();
}

void operExcel()
{
    Excel.Workbook book = null;
    Excel.Worksheet sheet = null;
    Excel.Range range = null;
    string path = Application.StartupPath + "\\test.xlsx";
    try
    {
        book = _Excel.Workbooks.Open(path, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);//開啟舊檔案
        sheet = (Excel.Worksheet)book.Sheets[1];

        range = sheet.get_Range("A5", "D23");
        foreach (Excel.Range item in range)
        {
            Console.WriteLine(item.Cells.Formula);
            Console.WriteLine(item.Cells.Value2);
            Console.WriteLine(item.Cells.Text);
        }

        book.Sheets.Add(Type.Missing, sheet, 1, Type.Missing);
        sheet = (Excel.Worksheet)book.Worksheets[2];
        sheet.Name = "新的工作表";
        //另存活頁簿
        book.SaveAs(Application.StartupPath + "\\test1.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
    }
    finally
    {
        book.Close(Type.Missing, Type.Missing, Type.Missing);
        book = null;
    }
}

@VS2010 C# 4.0 版本:因為C# 4.0新增了選擇性參數,不在像舊版那樣必須強制代入無用的參數Type.Missing

另外:

在.NET4.0的專案中可以不必轉型

sheet = book.Sheets["Sheet1"];

在.NET2.0~3.5的專案中要轉型

sheet = (Excel.Worksheet)book.Sheets["Sheet1"];

private void button1_Click(object sender, EventArgs e)
{
    initailExcel();
    operExcel();
}

private void button2_Click(object sender, EventArgs e)
{
    this._Excel.Quit();
    this._Excel = null;
    //確認已經沒有excel工作再回收
    GC.Collect();
}


void operExcel()
{
    Excel.Workbook book = null;
    Excel.Worksheet sheet = null;
    Excel.Range range = null;
    string path = Application.StartupPath + \\test.xlsx;
    try
    {
        book = _Excel.Workbooks.Open(path);//開啟舊檔案
        //sheet = (Excel.Worksheet)book.Sheets[1];//指定活頁簿,代表Sheet1 
        sheet = (Excel.Worksheet)book.Sheets["Sheet1"];//也可以直接指定工作表名稱 

        range = sheet.get_Range("A5", "D23");
        foreach (Excel.Range item in range)
        {
            Console.WriteLine(item.Cells.Formula);
            Console.WriteLine(item.Cells.Value);
            Console.WriteLine(item.Cells.Value2);
            Console.WriteLine(item.Cells.Text);
        }

        book.Sheets.Add(After: sheet, Count: 1);
        sheet = (Excel.Worksheet)book.Worksheets[2];
        sheet.Name = "新的工作表";
        //另存活頁簿
        book.SaveAs(Filename: Application.StartupPath + "\\test1.xls", FileFormat: Excel.XlFileFormat.xlXMLSpreadsheet, AccessMode: Excel.XlSaveAsAccessMode.xlNoChange);
    }
    finally
    {
        book.Close();
        book = null;
    }
}

 

延伸閱讀

http://www.dotblogs.com.tw/yc421206/archive/2008/12/20/6470.aspx

-----------------------------------------------------------------------------------------

2013/10/17 補充:

以下內容出自黑大

『結束前都建議使用Marshal.FinalReleaseComObject()清掉COM+物件的Reference Counter,確保Excel程式能順利關閉。』

http://blog.darkthread.net/post-2013-05-14-excel-interop-notes.aspx

若有謬誤,煩請告知,新手發帖請多包涵

2010~2015 C# 第四季