SUMMARY This article describes how to automatically run Excel in Visual C # .NET to use the array to populate and retrieve the values in the multi-cell area.
MORE INFORMATION To fill a multi-single area, it is not a cell to fill it.
Range object
The Value property is set to a two-dimensional array. Similarly, it can be used
The Value property retrieves the two-dimensional array of values of multiple cells. The following steps explain the process of setting up and retrieve data using a two-dimensional array.
Generate an automation client for Microsoft Excel
Start Microsoft Visual Studio .NET. On the File menu, click New, and then click Project. Select a Windows application from the Visual C # project type. FORM1 is created by default. Add a reference to the Microsoft Excel object library. To do this, follow these steps:
On the project menu, click Add Reference. On the COM tab, find the Microsoft Excel object library, then click Select. Note: Microsoft Office 2003 contains the main intero set (PIA). Microsoft Office XP does not contain PIA, but you can download PIA. For additional information about Office XP PIA, click the article number below to view the article in the Microsoft Knowledge Base: 328912 Info: Microsoft Office XP PIA is available for download in the Add Reference dialog box Click OK to accept your choice . If the system prompts you to generate a package for the selected library, click Yes. On the View menu, select the Toolbox to display the toolbox. Add two buttons to Form1 and a checkbox. Set the Name and Text Properties of the check box to FillwithStrings. Double click on Button1. The code window of the form appears. In the code window, put the following code private void button1_click (Object Sender, System.EventArgs E)
{
}
Replace with: // 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 objsheet;
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.worksheet;
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 colorn 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");
}
}
Return to the design view of Form1 and double-click Button2. In the code window, put the following code private void button2_click (Object Sender, System.EventArgs E)
{
}
Replace: Private Void Button2_Click (Object Sender, System.Eventargs E)
{
Excel.sheets objsheet;
Excel._Worksheet Objsheet;
Excel.range Range;
Try
{
Try
{
// Get a reason to the first sheet of the workbook.
Objsheets = objbook.worksheet;
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");
}
Scroll to the top of the code window. Add the following code line to the end of the Using instruction list: use system.reflection;
Using Excel = Microsoft.Office.Interop.Excel;
Test the automated client
Press F5 to build and run the sample program. Click Button1. The program will start Microsoft Excel and open a new workbook, and cell A1: E5 of the first worksheet has populated with numerical data from an array. Click Button2. The program will retrieve the data in cell A1: E5 and fill it into a new array and then display the results in a message box. Select FillWithstrings, and then click Button1 with string data to populate cell A1: E5.
Refer to more information, visit the following Microsoft Developer Network (MSDN) Web site:
Microsoft Office Development with Visual Studio (Microsoft Office Developing with Visual Studio) http://msdn.microsoft.com/library/en-us/dnoffdev/html/vsofficeDev.asp Using array settings in lower versions of Visual Studio And search additional information from Excel data, click the article number below to see the article in the Microsoft Knowledge Base:
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
The information in this article applies to:
Microsoft Visual C # .NET (2003) Microsoft Visual C # .NET (2002) Microsoft Office Excel 2003 Microsoft Excel 2002
Recent Updated: 2004-2-13 (6.0) Keywords: Kbautomation Kbhowto KB302096
