C#,動態建立Excel檔案
記得從References選COM匯入Microsoft.Excel.XX.X.Object.Library
//需要Using的命名
using Microsoft.Office.Interop.Excel;
using System.IO;
using System.Reflection;
------------------------------------------------------
////需要使用的變數。Workbook表示Excel檔,Worksheet表示一個Excel檔裡面的sheet(一個Excel檔可以有很多sheet),Range表示Excel裡面單元格的範圍。
Microsoft.Office.Interop.Excel.Application xlApp = null;
Workbook wb = null;
Worksheet ws = null;
Range aRange = null;
Range aRange2 = null;
object mObj_opt = System.Reflection.Missing.Value;
--------------------------------------------------------
private void WriteXls()
{
Console.WriteLine("WriteXls");
//啟動Excel應用程式
xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
Console.WriteLine("Error! xlApp");
return;
}
//用Excel應用程式建立一個Excel物件,也就是Workbook。並取得Workbook中的第一個sheet。這就是我們要操作資料的地方。
wb = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
ws = (Worksheet)wb.Worksheets[1];
if (ws == null)
{
Console.WriteLine("Error! ws");
}
//要在Excel儲存資料,有三種方式,以下分別介紹。利用Range物件,設定要儲存資料的儲存格範圍。
// Select the Excel cells, in the range c1 to c7 in the worksheet.
Range aRange = ws.get_Range("C1", "C7");
if (aRange == null)
{
Console.WriteLine("Could not get a range. Check to be sure you have the correct versions of the office DLLs.");
}
// Fill the cells in the C1 to C7 range of the worksheet with the number 6.
Object[] args = new Object[1];
args[0] = 6;
aRange.Value2 = args;
//衍生自上面方法,但是在儲存資料的時候,可以用InvokeMember呼叫aRange的資料成員(成員函式?)。
//aRange.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, aRange, args);
//利用Cells屬性,取得單一儲存格,並進行操作。
string[] number = { "A", "B", "C", "D", "E" };
foreach (string s in number)
{
Range aRange2 = (Range)ws.Cells["1", s];
Object[] args2 = new Object[1];
args2[0] = s;
aRange2.Value2 = args2;
}
//最後,呼叫SaveAs function儲存這個Excel物件到硬碟。
wb.SaveAs(@"C:\test.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlXMLSpreadsheet, mObj_opt, mObj_opt, mObj_opt, mObj_opt, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, mObj_opt, mObj_opt, mObj_opt, mObj_opt, mObj_opt);
Console.WriteLine("save");
wb.Close(false, mObj_opt, mObj_opt);
xlApp.Workbooks.Close();
xlApp.Quit();
//刪除 Windows工作管理員中的Excel.exe 進程,
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);
System.Runtime.InteropServices.Marshal.ReleaseComObject(aRange2);
System.Runtime.InteropServices.Marshal.ReleaseComObject(aRange3);
xlApp = null;
wb = null;
ws = null;
aRange2 = null;
aRange3 = null;
//呼叫垃圾回收
GC.Collect();
}