Thursday, July 1, 2010
Posted by
Grady
0
comments
Monday, March 15, 2010
利用程式將資料寫入Excel已經不是第一次了,
如果有時間,應該把這個功能寫成可重復使用的類別。
底下是記錄使用Microsoft.Office.Interop.Excel的過程。
在寫程式之前,要將Microsoft.Office.Interop.Excel加入參考。
Microsoft.Office.Interop.Excel的架構是要先有Application,
然後開啟Workbook,再使用Workbook產生Worksheet。
Worksheet則包含Range,可以存取Cell。
這個架構在下列的網址有更詳細的描述,
http://msdn.microsoft.com/en-us/library/wss56bz7%28v=VS.80%29.aspx
底下是範例程式,
[sourcecode language="csharp"]
using System.IO;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
string fileName = @"C:\XYZ.XLS";
string[] header = { //...字串陣列 }
string firstLineTitle = "First Line";
string secondLineTitle = "Second Line";
//設定必要的物件
Application oXL = new Excel.Application();
Excel.Workbook oWB;
Excel.Worksheet oSheet;
Excel.Range oRng;
oXL.Visible = true;
//產生一個Workbook物件,並加入Application
oWB = oXL.Workbooks.Add(Missing.Value);
//設定工作表
oSheet = (Excel.Worksheet)oWB.ActiveSheet;
//在工作表的特定儲存格,設定內容
oSheet.Cells[1, 1] = firstLineTitle;
oSheet.Cells[2, 1] = secondLineTitle;
//在特定範圍設定儲存格內容
oSheet.get_Range("A4", "I4").Value2 = header;
DataTable dt = //....取得資料表
//從第五行開始,設定第一欄和第二欄的內容
//資料是從某資料表取出
int row = 5;
for (int counter = 0; counter < dt.Rows.Count; counter++)
{
oSheet.Cells[row, 1] = dt.Rows[counter]["XYZ"].ToString();
oSheet.Cells[row, 2] = dt.Rows[counter]["ABC"].ToString();
row++;
}
//設定為按照內容自動調整欄寬
oRng = oSheet.get_Range("A5", "A" + row.ToString());
oRng.EntireColumn.AutoFit();
//設定為置中
oRng = oSheet.get_Range("B5", "B" + row.ToString());
oRng.EntireColumn.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//設定為讓使用者從程式的開始執行後
//就可以操作Excel,例如另存新檔到不同路徑
oXL.Visible = true;
oXL.UserControl = true;
//存檔
//在這裡只設定檔案名稱(含路徑)即可
oWB.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Excel.XlSaveAsAccessMode.xlShared, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
[/sourcecode]
底下是參考文件,
1. http://support.microsoft.com/kb/302084
2. http://msdn.microsoft.com/en-us/library/y1xatbkd%28v=VS.80%29.aspx
Posted by
Grady
0
comments