程式師世界 >> 編程語言 >> C語言 >> 關於C語言 >> C#與Excel的交互示例

C#與Excel的交互示例

編輯：關於C語言

//這裡加添加一個Excel對象的包裝器。就是添加一個引用

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

}
}