如何使用 Visual C# 2005 或 Visual C# .NET 向 Excel 工作簿传输数据

原文网址：http://support.microsoft.com/kb/306023/zh-cn

本文分步介绍了多种从 Microsoft Visual C# 2005 或 Microsoft Visual C# .NET 程序向 Microsoft Excel 2002 传输数据的方法。本文还提供了每种方法的优点和缺点，以便您可以选择最适合您的情况的解决方案。

回到顶端

概述

1. 创建一个名为 C:/ExcelData 的新文件夹。示例程序将在此文件夹中存储 Excel 工作簿。
2. 创建一个新工作簿，以供示例向其中写入数据：
   1. 在 Excel 中启动一个新工作簿。
   2. 在新工作簿的 Sheet1 上，在单元格 A1 中键入 FirstName，在单元格 B1 中键入 LastName。
   3. 选择 A1:B1。
   4. 在“插入”菜单上，指向“名称”，然后单击“定义”。键入名称 MyTable，然后单击“确定”。
   5. 将该工作簿另存为 C:/Exceldata/Book7.xls。
   6. 退出 Excel。
3. 启动 Microsoft Visual Studio 2005 或 Microsoft Visual Studio .NET。在“文件”菜单上，指向“新建”，然后单击“项目”。在“Visual C# 项目”或“Visual C#”下，选择“Windows 应用程序”。默认情况下会创建 Form1。
4. 添加对 Excel 对象库和 ADODB 主互操作程序集的引用。为此，请按照下列步骤操作：
   1. 在“项目”菜单上，单击“添加引用”。
   2. 在“NET”选项卡上，找到“ADODB”，然后单击“选择”。
      
      注意：在 Visual Studio 2005 中，您不需要单击“选择”。
   3. 在“COM”选项卡上，找到“Microsoft Excel 10.0 对象库或 Microsoft Excel 11.0 对象库”，然后单击“选择”。
      
      注意：在 Visual Studio 2005 中，您不需要单击“选择”。
      
      注意：如果您正在使用 Microsoft Excel 2002，并且尚未这样做，Microsoft 建议您下载并安装 Microsoft Office XP 主互操作 程序集 (PIA)。 有关 Office XP PIA 的更多信息，请单击下面的文章编号，以查看 Microsoft 知识库中相应的文章：
      328912  (http://support.microsoft.com/kb/328912/ ) Microsoft Office XP主互操作程序集 (PIA) 可供下载
   4. 在“添加引用”对话框中，单击“确定”以接受您的选择。
5. 向 Form1 中添加一个“Combo Box”控件和一个“Button”控件。
6. 为该窗体的“Load”事件和 Button 控件的“Click”事件添加事件处理程序：
   1. 在 Form1.cs 的设计视图中，双击“Form1”。
      
      此时将创建该窗体的“Load”事件的处理程序，该处理程序出现在 Form1.cs 中。
   2. 在“视图”菜单上，单击“设计器”以切换到设计视图。
   3. 双击“Button1”。
      
      此时将创建该按钮的“Click”事件的处理程序，该处理程序出现在 Form1.cs 中。
7. 在 Form1.cs 中，将以下代码：

   private void Form1_Load(object sender, System.EventArgs e)
   {
   
   }
   
   private void button1_Click(object sender, System.EventArgs e)
   {
   
   }
   					

   替换为：

           // 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 object m_strSampleFolder = "C://ExcelData//";
           private string m_strNorthwind = "C://Program Files//Microsoft Office//Office10//Samples//Northwind.mdb";
   
           private void Form1_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(m_strSampleFolder + "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(m_strSampleFolder + "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(m_strSampleFolder + "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(m_strSampleFolder + "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(m_strSampleFolder + "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(
                   m_strSampleFolder + "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(m_strSampleFolder + "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(m_strSampleFolder + "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=" + m_strSampleFolder +
                   "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
   }// End namespace
   					

   注意：您必须更改 Visual Studio 2005 中的代码。默认情况下，当创建一个 Windows 窗体项目时，Visual C# 会向该项目中添加一个窗体。该窗体被命名为 Form1。代表该窗体的两个文件被命名为 Form1.cs 和 Form1.designer.cs。您在 Form1.cs 中编写代码。Form1.designer.cs 文件是 Windows 窗体设计器编写代码的地方，这些代码可以实现通过拖放工具箱中的控件而执行的所有操作。
   
   有关 Visual C# 2005 中的 Windows 窗体设计器的更多信息，请访问下面的 Microsoft Developer Network (MSDN) 网站：
   http://msdn2.microsoft.com/zh-cn/library/ms173077.aspx (http://msdn2.microsoft.com/zh-cn/library/ms173077.aspx)
   注意：如果您没有将 Office 安装到默认文件夹 (C:/Program Files/Microsoft Office) 中，请修改代码示例中的“m_strNorthwind”常量以匹配 Northwind.mdb 的安装路径。
8. 将下面的代码添加到 Form1.cs 中的“Using”指令中：

   	using System.Reflection;
   	using System.Runtime.InteropServices;
   	using Excel = Microsoft.Office.Interop.Excel;
   					

9. 按 F5 生成并运行该示例。