[C# .Net] 使用 Microsoft.Office.Interop.Excel 讀 / 寫 Excel 2010
[C# .Net] Microsoft.Office.Interop.Excel 讀 / 寫 Excel 語法概全 這篇當時是使用Excel2003所完成，距離現在2010的版本已經有點久遠了，版本不同寫法也有點不同，今天就來更新一下用法。
首先要先加入Excel.exe參考
再來匯入 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];
接下來實作，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
若有謬誤，煩請告知，新手發帖請多包涵
Microsoft MVP Award 2010~2017 C# 第四季
Microsoft MVP Award 2018~2022 .NET
