// Excel usage demo

using System; 
using System.Drawing; 
using System.Collections; 
using System.ComponentModel; 
using System.Windows.Forms; 

namespace ExcelTest 
{ 
/// <summary> 
/// Form3 的摘要說明。 
/// </summary> 
public class Form3 : System.Windows.Forms.Form 
{ 
private System.Windows.Forms.Button button1; 
private System.Windows.Forms.ComboBox comboBox1; 
/// <summary> 
/// 必需的設計器變量。 
/// </summary> 
private System.ComponentModel.Container components = null; 
// Excel object references. 
private Excel.Application m_objExcel = null; 
private Excel.Workbooks m_objBooks = null; 
private Excel._Workbook m_objBook = null; 
private Excel.Sheets m_objSheets = null; 
private Excel._Worksheet m_objSheet = null; 
private Excel.Range m_objRange = null; 
private Excel.Font m_objFont = null; 
private Excel.QueryTables m_objQryTables = null; 
private Excel._QueryTable m_objQryTable = null; 

// Frequenty-used variable for optional arguments. 
private object m_objOpt = System.Reflection.Missing.Value; 

// Paths used by the sample code for accessing and storing data. 
private string m_strNorthwind = @"C:\Program Files\Microsoft Visual Studio\VB98\NWIND.MDB"; 

public Form3() 
{ 
// 
// Windows 窗體設計器支持所必需的 
// 
InitializeComponent(); 

// 
// TODO: 在 InitializeComponent 調用後添加任何構造函數代碼 
// 
} 

/// <summary> 
/// 清理所有正在使用的資源。 
/// </summary> 
protected override void Dispose( bool disposing ) 
{ 
if( disposing ) 
{ 
if(components != null) 
{ 
components.Dispose(); 
} 
} 
base.Dispose( disposing ); 
} 

#region Windows 窗體設計器生成的代碼 
/// <summary> 
/// 設計器支持所需的方法 - 不要使用代碼編輯器修改 
/// 此方法的內容。 
/// </summary> 
private void InitializeComponent() 
{ 
this.button1 = new System.Windows.Forms.Button(); 
this.comboBox1 = new System.Windows.Forms.ComboBox(); 
this.SuspendLayout(); 
// 
// button1 
// 
this.button1.Location = new System.Drawing.Point(208, 136); 
this.button1.Name = "button1"; 
this.button1.Size = new System.Drawing.Size(128, 32); 
this.button1.TabIndex = 0; 
this.button1.Text = "button1"; 
this.button1.Click += new System.EventHandler(this.button1_Click); 
// 
// comboBox1 
// 
this.comboBox1.Location = new System.Drawing.Point(112, 40); 
this.comboBox1.Name = "comboBox1"; 
this.comboBox1.Size = new System.Drawing.Size(376, 20); 
this.comboBox1.TabIndex = 1; 
this.comboBox1.Text = "comboBox1"; 
// 
// Form3 
// 
this.AutoScaleBaseSize = new System.Drawing.Size(6, 14); 
this.ClientSize = new System.Drawing.Size(544, 333); 
this.Controls.Add(this.comboBox1); 
this.Controls.Add(this.button1); 
this.Name = "Form3"; 
this.Text = "Form3"; 
this.Load += new System.EventHandler(this.Form3_Load); 
this.ResumeLayout(false); 

} 
#endregion 

[STAThread] 
static void Main() 
{ 
Application.Run(new Form3()); 
} 

private void Form3_Load(object sender, System.EventArgs e) 
{ 
comboBox1.DropDownStyle = ComboBoxStyle.DropDownList; 

comboBox1.Items.AddRange(new object[]{ 
"Use Automation to Transfer Data Cell by Cell ", 
"Use Automation to Transfer an Array of Data to a Range on a Worksheet ", 
"Use Automation to Transfer an ADO Recordset to a Worksheet Range ", 
"Use Automation to Create a QueryTable on a Worksheet", 
"Use the Clipboard", 
"Create a Delimited Text File that Excel Can Parse into Rows and Columns", 
"Transfer Data to a Worksheet Using ADO.NET "}); 
comboBox1.SelectedIndex = 0; 
button1.Text = "Go!"; 

} 

private void button1_Click(object sender, System.EventArgs e) 
{ 
switch (comboBox1.SelectedIndex) 
{ 
case 0 : Automation_CellByCell(); break; 
case 1 : Automation_UseArray(); break; 
case 2 : Automation_ADORecordset(); break; 
case 3 : Automation_QueryTable(); break; 
case 4 : Use_Clipboard(); break; 
case 5 : Create_TextFile(); break; 
case 6 : Use_ADONET(); break; 
} 

//Clean-up 
m_objFont = null; 
m_objRange = null; 
m_objSheet = null; 
m_objSheets = null; 
m_objBooks = null; 
m_objBook = null; 
m_objExcel = null; 
GC.Collect(); 

} 
private void Automation_CellByCell() 
{ 
// Start a new workbook in Excel. 
m_objExcel = new Excel.Application(); 
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; 
m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt)); 

// Add data to cells of the first worksheet in the new workbook. 
m_objSheets = (Excel.Sheets)m_objBook.Worksheets; 
m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1)); 
m_objRange = m_objSheet.get_Range("A1", m_objOpt); 
m_objRange.set_Value(m_objOpt,"Last Name"); 
m_objRange = m_objSheet.get_Range("B1", m_objOpt); 
m_objRange.set_Value(m_objOpt,"First Name"); 
m_objRange = m_objSheet.get_Range("A2", m_objOpt); 
m_objRange.set_Value(m_objOpt,"Doe"); 
m_objRange = m_objSheet.get_Range("B2", m_objOpt); 
m_objRange.set_Value(m_objOpt,"John"); 

// Apply bold to cells A1:B1. 
m_objRange = m_objSheet.get_Range("A1", "B1"); 
m_objFont = m_objRange.Font; 
m_objFont.Bold=true; 

// Save the workbook and quit Excel. 
m_objBook.SaveAs(Application.StartupPath + "\\Book1.xls", m_objOpt, m_objOpt, 
m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange, 
m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt); 
m_objBook.Close(false, m_objOpt, m_objOpt); 
m_objExcel.Quit(); 

} 

private void Automation_UseArray() 
{ 
// Start a new workbook in Excel. 
m_objExcel = new Excel.Application(); 
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; 
m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt)); 
m_objSheets = (Excel.Sheets)m_objBook.Worksheets; 
m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1)); 

// Create an array for the headers and add it to cells A1:C1. 
object[] objHeaders = {"Order ID", "Amount", "Tax"}; 
m_objRange = m_objSheet.get_Range("A1", "C1"); 
m_objRange.set_Value(m_objOpt,objHeaders); 
m_objFont = m_objRange.Font; 
m_objFont.Bold=true; 

// Create an array with 3 columns and 100 rows and add it to 
// the worksheet starting at cell A2. 
object[,] objData = new Object[100,3]; 
Random rdm = new Random((int)DateTime.Now.Ticks); 
double nOrderAmt, nTax; 
for(int r=0;r<100;r++) 
{ 
objData[r,0] = "ORD" + r.ToString("0000"); 
nOrderAmt = rdm.Next(1000); 
objData[r,1] = nOrderAmt.ToString("c"); 
nTax = nOrderAmt*0.07; 
objData[r,2] = nTax.ToString("c"); 
} 
m_objRange = m_objSheet.get_Range("A2", m_objOpt); 
m_objRange = m_objRange.get_Resize(100,3); 
m_objRange.set_Value(m_objOpt,"objData"); 

// Save the workbook and quit Excel. 
m_objBook.SaveAs(Application.StartupPath + "\\Book2.xls", m_objOpt, m_objOpt, 
m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange, 
m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt); 
m_objBook.Close(false, m_objOpt, m_objOpt); 
m_objExcel.Quit(); 

} 

private void Automation_ADORecordset() 
{ 
// Create a Recordset from all the records in the Orders table. 
ADODB.Connection objConn = new ADODB.Connection(); 
ADODB._Recordset objRS = null; 
objConn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + 
m_strNorthwind + ";", "", "", 0); 
objConn.CursorLocation = ADODB.CursorLocationEnum.adUseClient; 
object objRecAff; 
objRS = (ADODB._Recordset)objConn.Execute("Orders", out objRecAff, 
(int)ADODB.CommandTypeEnum.adCmdTable); 

// Start a new workbook in Excel. 
m_objExcel = new Excel.Application(); 
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; 
m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt)); 
m_objSheets = (Excel.Sheets)m_objBook.Worksheets; 
m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1)); 

// Get the Fields collection from the recordset and determine 
// the number of fields (or columns). 
System.Collections.IEnumerator objFields = objRS.Fields.GetEnumerator(); 
int nFields = objRS.Fields.Count; 

// Create an array for the headers and add it to the 
// worksheet starting at cell A1. 
object[] objHeaders = new object[nFields]; 
ADODB.Field objField = null; 
for(int n=0;n<nFields;n++) 
{ 
objFields.MoveNext(); 
objField = (ADODB.Field)objFields.Current; 
objHeaders[n] = objField.Name; 
} 
m_objRange = m_objSheet.get_Range("A1", m_objOpt); 
m_objRange = m_objRange.get_Resize(1, nFields); 
m_objRange.set_Value(m_objOpt,objHeaders); 
m_objFont = m_objRange.Font; 
m_objFont.Bold=true; 

// Transfer the recordset to the worksheet starting at cell A2. 
m_objRange = m_objSheet.get_Range("A2", m_objOpt); 
m_objRange.CopyFromRecordset(objRS, m_objOpt, m_objOpt); 

// Save the workbook and quit Excel. 
m_objBook.SaveAs(Application.StartupPath + "\\Book3.xls", m_objOpt, m_objOpt, 
m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange, 
m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt); 
m_objBook.Close(false, m_objOpt, m_objOpt); 
m_objExcel.Quit(); 

//Close the recordset and connection 
objRS.Close(); 
objConn.Close(); 

} 

private void Automation_QueryTable() 
{ 
// Start a new workbook in Excel. 
m_objExcel = new Excel.Application(); 
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; 
m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt)); 

// Create a QueryTable that starts at cell A1. 
m_objSheets = (Excel.Sheets)m_objBook.Worksheets; 
m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1)); 
m_objRange = m_objSheet.get_Range("A1", m_objOpt); 
m_objQryTables = m_objSheet.QueryTables; 
m_objQryTable = (Excel._QueryTable)m_objQryTables.Add( 
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + 
m_strNorthwind + ";", m_objRange, "Select * From Orders"); 
m_objQryTable.RefreshStyle = Excel.XlCellInsertionMode.xlInsertEntireRows; 
m_objQryTable.Refresh(false); 

// Save the workbook and quit Excel. 
m_objBook.SaveAs(Application.StartupPath + "\\Book4.xls", m_objOpt, m_objOpt, 
m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt, 
m_objOpt, m_objOpt, m_objOpt); 
m_objBook.Close(false, m_objOpt, m_objOpt); 
m_objExcel.Quit(); 

} 

private void Use_Clipboard() 
{ 
// Copy a string to the clipboard. 
string sData = "FirstName\tLastName\tBirthdate\r\n" + 
"Bill\tBrown\t2/5/85\r\n" + 
"Joe\tThomas\t1/1/91"; 
System.Windows.Forms.Clipboard.SetDataObject(sData); 

// Start a new workbook in Excel. 
m_objExcel = new Excel.Application(); 
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; 
m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt)); 

// Paste the data starting at cell A1. 
m_objSheets = (Excel.Sheets)m_objBook.Worksheets; 
m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1)); 
m_objRange = m_objSheet.get_Range("A1", m_objOpt); 
m_objSheet.Paste(m_objRange, false); 

// Save the workbook and quit Excel. 
m_objBook.SaveAs(Application.StartupPath + "\\Book5.xls", m_objOpt, m_objOpt, 
m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt, 
m_objOpt, m_objOpt, m_objOpt); 
m_objBook.Close(false, m_objOpt, m_objOpt); 
m_objExcel.Quit(); 

} 

private void Create_TextFile() 
{ 
// Connect to the data source. 
System.Data.OleDb.OleDbConnection objConn = new System.Data.OleDb.OleDbConnection( 
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + m_strNorthwind + ";"); 
objConn.Open(); 

// Execute a command to retrieve all records from the Employees table. 
System.Data.OleDb.OleDbCommand objCmd = new System.Data.OleDb.OleDbCommand( 
"Select * From Employees", objConn); 
System.Data.OleDb.OleDbDataReader objReader; 
objReader = objCmd.ExecuteReader(); 


// Create the FileStream and StreamWriter object to write 
// the recordset contents to file. 
System.IO.FileStream fs = new System.IO.FileStream( 
Application.StartupPath + "\\Book6.txt", System.IO.FileMode.Create); 
System.IO.StreamWriter sw = new System.IO.StreamWriter( 
fs, System.Text.Encoding.Unicode); 

// Write the field names (headers) as the first line in the text file. 
sw.WriteLine(objReader.GetName(0) + "\t" + objReader.GetName(1) + 
"\t" + objReader.GetName(2) + "\t" + objReader.GetName(3) + 
"\t" + objReader.GetName(4) + "\t" + objReader.GetName(5)); 

// Write the first six columns in the recordset to a text file as 
// tab-delimited. 
while(objReader.Read()) 
{ 
for(int i=0;i<=5;i++) 
{ 
if(!objReader.IsDBNull(i)) 
{ 
string s; 
s = objReader.GetDataTypeName(i); 
if(objReader.GetDataTypeName(i)=="DBTYPE_I4") 
{ 
sw.Write(objReader.GetInt32(i).ToString()); 
} 
else if(objReader.GetDataTypeName(i)=="DBTYPE_DATE") 
{ 
sw.Write(objReader.GetDateTime(i).ToString("d")); 
} 
else if (objReader.GetDataTypeName(i)=="DBTYPE_WVARCHAR") 
{ 
sw.Write(objReader.GetString(i)); 
} 
} 
if(i<5) sw.Write("\t"); 
} 
sw.WriteLine(); 
} 
sw.Flush(); // Write the buffered data to the FileStream. 

// Close the FileStream. 
fs.Close(); 

// Close the reader and the connection. 
objReader.Close(); 
objConn.Close(); 

// ================================================================== 
// Optionally, automate Excel to open the text file and save it in the 
// Excel workbook format. 

// Open the text file in Excel. 
m_objExcel = new Excel.Application(); 
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; 
m_objBooks.OpenText(Application.StartupPath + "\\Book6.txt", Excel.XlPlatform.xlWindows, 1, 
Excel.XlTextParsingType.xlDelimited, Excel.XlTextQualifier.xlTextQualifierDoubleQuote, 
false, true, false, false, false, false, m_objOpt, m_objOpt, 
m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt); 

m_objBook = m_objExcel.ActiveWorkbook; 

// Save the text file in the typical workbook format and quit Excel. 
m_objBook.SaveAs(Application.StartupPath + "\\Book6.xls", Excel.XlFileFormat.xlWorkbookNormal, 
m_objOpt, m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt, 
m_objOpt, m_objOpt, m_objOpt); 
m_objBook.Close(false, m_objOpt, m_objOpt); 
m_objExcel.Quit(); 

} 

private void Use_ADONET() 
{ 
// Establish a connection to the data source. 
System.Data.OleDb.OleDbConnection objConn = new System.Data.OleDb.OleDbConnection( 
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Application.StartupPath + "\\Book7.xls;Extended Properties=Excel 8.0;"); 
objConn.Open(); 

// Add two records to the table named 'MyTable'. 
System.Data.OleDb.OleDbCommand objCmd = new System.Data.OleDb.OleDbCommand(); 
objCmd.Connection = objConn; 
objCmd.CommandText = "Insert into MyTable (FirstName, LastName)" + 
" values ('Bill', 'Brown')"; 

objCmd.ExecuteNonQuery(); 
objCmd.CommandText = "Insert into MyTable (FirstName, LastName)" + 
" values ('Joe', 'Thomas')"; 
objCmd.ExecuteNonQuery(); 


// Close the connection. 
objConn.Close(); 

} 

// End Class 

} 
}