SUMMARY This article describes how to use Microsoft Visual C # .NET to create an automated client for Microsoft Excel.
MORE INFORMATION By the automation process, applications written in languages such as Visual C # .NET can be programmed to control other applications. With Excel's automation, you can perform operations such as new workbooks, add data to workbooks or create charts. For Excel and other Microsoft Office applications, almost all operations that can be manually executed over the user interface can also be performed by using the Automation feature.
Excel discloses this program function through an object model. The object model is a collection of classes and methods, these classes and methods act as the logical component of Excel. For example, there is
Application object,
Workbook object and
Worksheet objects, each of which contains the functions of those components in Excel. To access the object model from Visual C # .NET, you can set item references to the type library.
This article will explain how to set the appropriate project reference to the Excel type library for Visual C # .NET and provide example examples that enable Excel automatically.
Create an automated 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, then add a button to Form1. 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: Private Void Button1_Click (Object Sender, System.EventArgs E)
{
Excel.Application OXL;
Excel._Workbook OWB;
Excel._Worksheet Osheet;
Excel.range Orng;
Try
{
// Start Excel and get Application Object.
OXL = New Excel.Application ();
oxl.visible = true;
// Get a new workbook.
OWB = (Excel._Workbook) (OXL.Workbooks.Add (missing.value));
Osheet = (Excel._Worksheet) OWB.Activesheet;
// Add Table Headers Going Cell By Cell.
Osheet.cells [1, 1] = "first name"; Osheet.cells [1, 2] = "last name";
Osheet.cells [1, 3] = "Full Name";
Osheet.cells [1, 4] = "Salary";
// Format A1: D1 as bold, Vertical Alignment = center.
Osheet.get_Range ("A1", "D1"). font.bold = true;
Osheet.get_Range ("A1", "D1"). VerticalAlignment =
Excel.xlvalign.xlvalignCenter;
// Create an Array to Multiple Values at Once.
String [,] SANAMES = New String [5, 2];
SANAMES [0, 0] = "john";
SANAMES [0, 1] = "smith";
SANAMES [1, 0] = "Tom";
SANAMES [1, 1] = "brown";
SANAMES [2, 0] = "SUE";
Sanames [2, 1] = "Thomas";
SANAMES [3, 0] = "jane";
SANAMES [3, 1] = "Jones";
SANAMES [4, 0] = "ADAM";
SANAMES [4, 1] = "johnson";
// Fill A2: B6 with an Array of Values (First and last names).
Osheet.get_Range ("A2", "B6"). Value2 = SANAMES;
// Fill C2: C6 with a relative formula (= a2 & "" & b2).
ORNG = Osheet.get_Range ("C2", "C6");
ORNG.FORMULA = "= a2 & /" / "& b2";
// Fill D2: D6 with a formula (= rand () * 100000) And Apply Format.
ORNG = OSHEET.GET_RANGE ("D2", "D6");
ORNG.FORMULA = "= Rand () * 100000";
ORNG.NUMBERFORMAT = "$ 0.00";
// Autofit Column A: D.
ORNG = Osheet.get_Range ("A1", "D1");
ORNG.ENTIRECOLUMN.AUTOFIT ();
// manipulate a variable number of columns for quarterly sales data.
DisplayquarterlySales (Osheet);
// Make Sure Excel Is Visible and Give The User Control
// of Microsoft Excel's Lifetime.
oxl.visible = true; oxl.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");
}
}
Private void DisplayquarterlySales (Excel._Worksheet OWS)
{
Excel._Workbook OWB;
Excel.series oseries;
Excel.Range OresizRange;
Excel._Chart Ochart;
String smsg;
Int inumqtrs;
// DETERMINE How Many Quarters to Display Data for.
For (inumqtrs = 4; inumqtrs> = 2; inumqtrs -)
{
SMSG = "Enter Sales Data for";
SMSG = String.concat (SMSG, Inumqtrs);
SMSG = string.concat (smsg, "quarter (s)?");
DialogResult Iret = MessageBox.show (SMSG, "Quarterly Sales?",
MessageboxButtons.yesno;
IF (IRet == DialogResult.yes)
Break;
}
SMSG = "Displaying Data for";
SMSG = String.concat (SMSG, Inumqtrs);
SMSG = String.concat (SMSG, "Quarter (s));
MessageBox.show (SMSG, "Quarterly Sales");
// Starting At E1, Fill Headers for the Number of Columns Selected.
OresizRange = OWS.GET_RANGE ("E1", "E1"). Get_resize (missing.value, inumqtrs);
OresizERANGE.FORMULA = "= /" q / "& column () - 4 & char (10) & /" sales / "
// change the orientation and wraptext property for the headers.
OresizERANGE.ORIENTATION = 38;
ract arentze.wraptext = true;
// Fill The Interior Color of the Headers.
OresizRange.interior.colorindex = 36;
// Fill the columns with a formaula and apply a number format.OrSizeRange = OWS.GET_RANGE ("E2", "E6"). Get_resize (missing.value, inumqtrs);
OresizERANGE.FORMULA = "= rand () * 100;
ractsizERANGE.NUMBERFORMAT = "$ 0.00";
// Apply Borders to the sales data and headers.
OrsizeRange = OWS.GET_RANGE ("E1", "E6"). Get_resize (missing.value, inumqtrs);
OresizERANGE.BORDERS.WEight = Excel.xlborderweight.xlt.xlt.
// Add A Totals Formula for the sales data and application a border.
ractsizERANGE = OWS.GET_RANGE ("E8", "E8"). Get_resize (Missing.Value, Inumqt);
OresizERANGE.FORMULA = "= SUM (E2: E6)";
OresizERANGE.BORDERS.GET_ITEM (Excel.xlbordersindex.xledgebottom) .LINESTYLE
= Excel.xllineStyle.xldouble;
OresizERANGE.BORDERS.GET_ITEM (Excel.xlbordersindex.xledgebottom) .weight
= Excel.xlborderweight.xlt;
// Add a chart for the selected data.
OWB = (Excel._Workbook) ip.Parent;
Ochart = (Excel._Chart) OWB.CHARTS.ADD (Missing.Value, Missing.Value,
Missing.Value, missing.value;
// Use the chartwizard to create a new chart from the successd data.
ractsizERANGE = OWS.GET_RANGE ("E2: E6", Missing.Value) .get_resize
Missing.Value, Inumqtrs);
Ochart.chartwizard (Oresize, Excel.xlcharttype.xl3dColumn, Missing.Value,
Excel.xlrowcol.xlcolumns, missing.value, missing.value, missing.value,
Missing.Value, missing.value, missing.value, missing.value;
Oseries = (Excel.series) Ochart.seriescollection (1);
Oseries.xvalues = OWS.GET_RANGE ("A2", "A6");
For (int IRet = 1; IRET <= Inumqtrs; IRET )
{
Oseries = (Excel.series) Ochart.seriesCollection (IRET);
String Seriesname; SeriesName = "= /" q ";
SeriesName = String.concat (SeriesName, IRET);
SeriesName = String.concat (SeriesName, "/" ");
Oseries.name = seriesName;
}
Ochart.Location (Excel.xlchartlocation.xllocationAsObject, OWS.NAME);
//Move the chart so as note to cover your data.
OrsizeRange = (Excel.Range) OWS.ROWS.GET_ITEM (10, Missing.Value);
OWS.SHAPES.ITEM ("Chart 1"). TOP = (float) (Double) ORESIZERANGE.TOP;
ractsizERANGE = (Excel.Range) OWS.COLUMNS.GET_ITEM (2, missing.value);
OWS.SHAPES.ITEM ("Chart 1"). Left = (float) (Double) ORESIZERANGE.LEFT;
}
Scroll to the top of the code window. Add the following code line to the end of the Using instruction list: use excel = microsoft.Office.Interop.Excel;
Using system.reflection;
Test the automated client
Press F5 to build and run the program. On the form, click Button1. The program will start Excel and populate data into a new worksheet. When prompted to enter the quarterly sales data, click Yes. A chart that links to quarter data will be added to the worksheet.
Refer to more information, visit the following Microsoft Developer Network (MSDN) Web site:
Microsoft Office Development with Visual Studio (using Visual Studio) http://msdn.microsoft.com/library/en-us/dnoffdev/html/vsofficedev.asp
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 (5.0) Keywords: kbpia kbautomation Kbhowto KB302084
