概要
本文阐述如何在 Visual C# .NET 使 Excel 自动运行以使用数组填充和检索多单元格区域中的值。更多信息
要填充一个多单元格区域而又不是一次一个单元格地进行填充,可以将 Range 对象的 Value 属性设置为二维数组。同样,可通过使用 Value 属性一次检索多个单元格的值的二维数组。下面的步骤阐述了这一使用二维数组设置和检索数据的过程。为 Microsoft Excel 生成自动化客户端
- 启动 Microsoft Visual Studio .NET。
- 在文件菜单上,单击新建,然后单击项目。从 Visual C# 项目类型中选择 Windows 应用程序。默认情况下会创建 Form1。
- 添加对 Microsoft Excel 对象库的引用。为此,请按照下列步骤操作:
- 在项目菜单上,单击添加引用。
- 在 COM 选项卡上,找到 Microsoft Excel 对象库,然后单击选择。
注意:Microsoft Office 2003 包含主 Interop 程序集 (PIA)。Microsoft Office XP 不包含 PIA,但您可以下载 PIA。 有关 Office XP PIA 的其他信息,请单击下面的文章编号,以查看 Microsoft 知识库中相应的文章:328912 INFO:Microsoft Office XP PIA 可供下载
- 在添加引用对话框中单击确定以接受您的选择。如果系统提示您为选定的库生成包装,请单击是。
- 在视图菜单上,选择工具箱以显示工具箱。向 Form1 添加两个按钮和一个复选框。
- 将复选框的 Name 和 Text 属性设置为 FillWithStrings。
- 双击 Button1。出现该窗体的代码窗口。
- 在代码窗口中,将以下代码
private void button1_Click(object sender, System.EventArgs e) { }
替换为://Declare these two variables globally so you can access them from both //Button1 and Button2. Excel.Application objApp; Excel._Workbook objBook; private void button1_Click(object sender, System.EventArgs e) { Excel.Workbooks objBooks; Excel.Sheets objSheets; Excel._Worksheet objSheet; Excel.Range range; try { // Instantiate Excel and start a new workbook. objApp = new Excel.Application(); objBooks = objApp.Workbooks; objBook = objBooks.Add( Missing.Value ); objSheets = objBook.Worksheets; objSheet = (Excel._Worksheet)objSheets.get_Item(1); //Get the range where the starting cell has the address //m_sStartingCell and its dimensions are m_iNumRows x m_iNumCols. range = objSheet.get_Range("A1", Missing.Value); range = range.get_Resize(5, 5); if (this.FillWithStrings.Checked == false) { //Create an array. double[,] saRet = new double[5, 5]; //Fill the array. for (long iRow = 0; iRow < 5; iRow++) { for (long iCol = 0; iCol < 5; iCol++) { //Put a counter in the cell. saRet[iRow, iCol] = iRow * iCol; } } //Set the range value to the array. range.set_Value(Missing.Value, saRet ); } else { //Create an array. string[,] saRet = new string[5, 5]; //Fill the array. for (long iRow = 0; iRow < 5; iRow++) { for (long iCol = 0; iCol < 5; iCol++) { //Put the row and column address in the cell. saRet[iRow, iCol] = iRow.ToString() + "|" + iCol.ToString(); } } //Set the range value to the array. range.set_Value(Missing.Value, saRet ); } //Return control of Excel to the user. objApp.Visible = true; objApp.UserControl = true; } catch( Exception theException ) { String errorMessage; errorMessage = "Error: "; errorMessage = String.Concat( errorMessage, theException.Message ); errorMessage = String.Concat( errorMessage, " Line: " ); errorMessage = String.Concat( errorMessage, theException.Source ); MessageBox.Show( errorMessage, "Error" ); } }
- 返回到 Form1 的设计视图并双击 Button2。
- 在代码窗口中,将以下代码
private void button2_Click(object sender, System.EventArgs e) { }
替换为:private void button2_Click(object sender, System.EventArgs e) { Excel.Sheets objSheets; Excel._Worksheet objSheet; Excel.Range range; try { try { //Get a reference to the first sheet of the workbook. objSheets = objBook.Worksheets; objSheet = (Excel._Worksheet)objSheets.get_Item(1); } catch( Exception theException ) { String errorMessage; errorMessage = "Can't find the Excel workbook. Try clicking Button1 " + "to create an Excel workbook with data before running Button2."; MessageBox.Show( errorMessage, "Missing Workbook?"); //You can't automate Excel if you can't find the data you created, so //leave the subroutine. return; } //Get a range of data. range = objSheet.get_Range("A1", "E5"); //Retrieve the data from the range. Object[,] saRet; saRet = (System.Object[,])range.get_Value( Missing.Value ); //Determine the dimensions of the array. long iRows; long iCols; iRows = saRet.GetUpperBound(0); iCols = saRet.GetUpperBound(1); //Build a string that contains the data of the array. String valueString; valueString = "Array Data/n"; for (long rowCounter = 1; rowCounter <= iRows; rowCounter++) { for (long colCounter = 1; colCounter <= iCols; colCounter++) { //Write the next value into the string. valueString = String.Concat(valueString, saRet[rowCounter, colCounter].ToString() + ", "); } //Write in a new line. valueString = String.Concat(valueString, "/n"); } //Report the value of the array. MessageBox.Show(valueString, "Array Values"); } catch( Exception theException ) { String errorMessage; errorMessage = "Error: "; errorMessage = String.Concat( errorMessage, theException.Message ); errorMessage = String.Concat( errorMessage, " Line: " ); errorMessage = String.Concat( errorMessage, theException.Source ); MessageBox.Show( errorMessage, "Error" ); } }
- 滚动到代码窗口的顶部。将下面的代码行添加到 using 指令列表的末尾:
using System.Reflection; using Excel = Microsoft.Office.Interop.Excel;
对自动化客户端进行测试
- 按 F5 键生成并运行该示例程序。
- 单击 Button1。该程序将启动 Microsoft Excel 并打开一个新工作簿,而且第一个工作表的单元格 A1:E5 已填充了来自某个数组的数值数据。
- 单击 Button2。该程序将检索单元格 A1:E5 中的数据并将其填充到一个新的数组中,然后将结果显示在一个消息框中。
- 选择 FillWithStrings,然后单击 Button1 用字符串数据填充单元格 A1:E5。
参考
有关更多信息,请访问下面的 Microsoft Developer Network (MSDN) Web 站点:Microsoft Office Development with Visual Studio(使用 Visual Studio 进行 Microsoft Office 开发)
http://msdn.microsoft.com/library/en-us/dnoffdev/html/vsofficedev.asp
186120 HOWTO:Use MFC to Automate Excel and Fill a Range with an Array
186122 HOWTO:Use MFC to Automate Excel and Obtain an Array from a Range
247412 INFO:Methods for Transferring Data to Excel from Visual Basic
这篇文章中的信息适用于:
- Microsoft Visual C# .NET (2003)
- Microsoft Visual C# .NET (2002)
- Microsoft Office Excel 2003
- Microsoft Excel 2002
最近更新: | 2004-2-13 (6.0) |
关键字: | kbAutomation kbhowto KB302096 |