User:Foxall/20

Ka Wiktionary

Hour 20. Controlling Other Applications Using Automation[wax ka badal]

graphics/newterm.gif In [file:///C:/Documents%20and%20Settings/dani/Asztal/c%23in24h/ch17.htm#ch17 Hour 17], "Designing Objects Using Classes," you learned how to use classes to create objects. In that hour, I mentioned that objects could be exposed to outside applications�Excel, for example, exposes most of its functionality as a set of objects. The process of using objects from another application is called Automation. The externally accessible objects of an application compose its object model. Using Automation to manipulate a program's object model enables you to reuse components. For instance, you can use Automation with Excel to perform complex mathematical functions using the code that's been written and tested within Excel, rather than having to write and debug the complex code yourself.

Programs that expose objects are called servers, and the programs that consume those objects are called clients. Creating automation servers requires advanced skills, including a very thorough understanding of programming classes. Creating clients to use objects from other applications, on the other hand, is relatively simple. In this hour, you'll learn how to create a client application that uses objects of an external server application.

The highlights of this hour include the following:

  • [file:///C:/Documents%20and%20Settings/dani/Asztal/c%23in24h/ch20lev1sec1.htm#ch20lev1sec1 Creating a reference to an automation library]
  • [file:///C:/Documents%20and%20Settings/dani/Asztal/c%23in24h/ch20lev1sec2.htm#ch20lev1sec2 Creating an instance of an automation server]
  • [file:///C:/Documents%20and%20Settings/dani/Asztal/c%23in24h/ch20lev1sec3.htm#ch20lev1sec3 Manipulating the objects of an automation server]

To understand Automation, you're going to build a Microsoft Excel client�a program that automates Excel via Excel's object model.

graphics/bookpencil.gif This exercise is designed to work with Excel 2000 or Excel XP. If you don't have Excel installed on your computer, you won't be able to complete the exercise.

Create a new Windows Application named Automate Excel. Change the name of the default form to fclsMain, set its Text property to Automate Excel, and then set the entry point in Main() to reference fclsMain instead of Form1. Next, add a button to the form by double-clicking the Button item in the toolbox and set the button's properties as follows:

Property Value
Name btnAutomateExcel
Location 96,128
Size 104,23
Text Automate Excel
>

Creating a Reference to an Automation Library

graphics/newterm.gif To use the objects of a program that supports Automation (a server), you have to reference the program's type library. A program's type library (also called object library) is a file that contains a description of the program's object model. After you've referenced the type library of an automation server (also called a component), you can access the objects of the server as though they were internal C# objects.
graphics/newterm.gif To create a reference to a type library, first display the Add Reference dialog box by choosing Add Reference from the Project menu (do this now). A number of types of components support automation. Of course, .NET is the latest technology, but in the case of Excel, we're interested in the COM components. COM stands for Component Object Model, and it's been the technology for working with objects within windows for many years. Microsoft's .NET platform is designed to replace COM. This isn't going to happen overnight, however; literally thousands of objects are built on COM technology. In fact, all the Microsoft Office products up to and including Office XP are based on COM.

Click the COM tab now to display the available COM components (programs that have a type library) on your computer. Scroll the list and locate the Microsoft Excel X Object Library (where X is the version of Excel installed on your computer). Double-click the Excel item to add it to the list of selected components at the bottom of the Add Reference dialog box (see [file:///C:/Documents%20and%20Settings/dani/Asztal/c%23in24h/20.htm#ch20fig01 Figure 20.1]).

Figure 20.1. To use an object library, you need to reference it first.

graphics/20fig01.jpg

graphics/bookpencil.gif If you don't see an entry for Microsoft Excel, you probably don't have Excel installed on your computer; therefore, this code won't work.
graphics/newterm.gif Click OK now to add the reference to your project. C# doesn't work directly with COM components. Instead, it interacts through a wrapper, a set of code and objects that works as an intermediary between C# and a COM component. Chances are that your machine doesn't have an existing wrapper for Excel; if this is the case, C# automatically creates the wrapper and references the component.
>

Creating an Instance of an Automation Server

Referencing a type library allows C# to integrate the available objects of the type library with its own internal objects. After this is done, you can create object variables based on object types found in the type library. Excel has an object called Application, which acts as the primary object in the Excel object model. In fact, most Office programs have an Application object. How do you know what objects an automation server supports? The only sure way is to consult the documentation of the program in question or use the Object Browser discussed in [file:///C:/Documents%20and%20Settings/dani/Asztal/c%23in24h/ch03.htm#ch03 Hour 3], "Understanding Objects and Collections."

graphics/bookpencil.gif In this example, you'll be using about a half-dozen members of an Excel object. This doesn't even begin to scratch the surface of Excel's object model, and it isn't intended to. What you should learn from this example is the mechanics of working with an automation server. If you choose to automate a program in your own projects, you'll want to consult the program's developer documentation to learn about its object model; you're sure to be surprised at the functionality available to you.

Double-click the button to access its Click event, and then enter the following code, which creates a new Excel Application: 

Excel.Application objExcel = new Excel.Application();

Notice that C# included Excel in its IntelliSense drop-down list of available objects. It was able to do this because you referenced Excel's type library. Excel is the reference to the server and Application is an object supported by the server. This statement creates a new Application object based on the Excel object model. 

Manipulating the Server

After you have an instance of an object from an automation server, manipulating the server (creating objects, setting properties, calling methods, and so forth) is accomplished by manipulating the object. 

 Forcing Excel to Show Itself

When Excel is started using Automation, it's loaded but not shown. By remaining hidden, it allows the developer to use its functionality and then close Excel without the user ever knowing what happened. For instance, you could create an instance of an Excel object, perform a complicated formula to obtain a result, close Excel, and return the result to the user�all without the user ever seeing Excel. In this example, you want to see Excel so that you can see what your code is doing. Fortunately, showing Excel couldn't be easier. Add the following statement to make Excel visible: 

objExcel.Visible = true;

Creating an Excel Workbook and Worksheet

In Excel, a Workbook is the file in which you work and store your data; you can't manipulate data without a Workbook. When you first start Excel from the Start menu, an empty Workbook is created for you. However, when you start Excel via Automation, Excel doesn't create a Workbook; you have to do it yourself. To create a new Workbook, you use the Add method of the Workbooks collection. After the Workbook has been created, you need to set up a worksheet. Enter the following statements: 

//start a new workbook and a worksheet.
Excel.Workbook objBook =
 objExcel.Workbooks.Add(System.Reflection.Missing.Value);
Excel.Worksheet objSheet = (Excel.Worksheet)objBook.Worksheets.get_Item(1);
graphics/bookpencil.gif Notice how System.Reflection.Missing.Value is being passed into the Add() method. This is because the Add() method supports a default parameter and C# does not support default parameters. Using the System.Reflection.Missing.Value as the parameter in the Add() method enables the COM's late-binding service to use the default value for the indicated parameter value. 
Working with Data in an Excel Workbook

In this section, you're going to manipulate data in the worksheet. The following describes what you'll do:

  1. Add data to four cells in the worksheet.
  2. Select the four cells.
  3. Total the selected cells and place the sum into a fifth cell.
  4. Bold all five cells.

To manipulate cells in the worksheet, you manipulate the Range object, which is an object property of the Worksheet object. Entering data into a cell involves first selecting a cell and then passing data to it. Selecting a cell is accomplished by setting a range object by calling the get_Range () method of the Worksheet object; the get_Range () method is used to select one or more cells. The get_Range () method accepts a starting column and row and an ending column and row. If you want to select only a single cell, as we do here, you can substitute the ending column and row with System.Reflection.Missing.Value parameter. After a range is set, you pass data to the selected range by using the set_Value() method on the Range object. Sound confusing? Well, it is to some extent. Programs that support Automation are often vast and complex, and programming them is usually far from intuitive.

graphics/bookpencil.gif range.set_Value(Missing.Value,"75") is used for Excel 10 (Excel XP). Use range.Value = "75" for Excel 9 (Excel 2000).
graphics/bulb.gif If the program you want to automate has a macro builder (as most Microsoft products do), you can save yourself a lot of time and headache by creating macros of the tasks you want to automate. The "macros" are actually code, and in the case of Microsoft products, they're VBA code.

The following section of code uses the techniques just described to add data to four cells. Enter this code into your procedure: 

Excel.Range objRange;

objRange = objSheet.get_Range("A1", System.Reflection.Missing.Value);

// For EXCEL9 Use objRange.Value method in place of all of the
// objRange.set_Value() statements used in this example. i.e.
// objRange.Value = "75";

objRange.set_Value(System.Reflection.Missing.Value, 75 );

objRange = objSheet.get_Range("B1", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, 125 );

objRange = objSheet.get_Range("C1", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, 255 );

objRange = objSheet.get_Range("D1", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, 295 );

The next step is to have Excel total the four cells. You'll do this by using the get_Range() method to select the cell in which to place the total, and then use set_Value() method again to create the total by passing it a formula, rather than a literal value. Enter the following code into your procedure: 

objRange = objSheet.get_Range("E1", System.Reflection.Missing.Value);
objRange.set_Value(System.Reflection.Missing.Value, "=SUM(RC[-4]:RC[-1])" );

Next, you'll select all five cells and bold them. Enter the following statements to accomplish this: 

objRange = objSheet.get_Range("A1", "E1");
objRange.Font.Bold=true;

The last thing you need to do is destroy the object reference by setting the object variable to null. Excel will remain open even though you've destroyed the Automation instance (not all servers will do this). Add this last statement to your procedure: 

objExcel=null;

To help you ensure that everything is entered correctly, [file:///C:/Documents%20and%20Settings/dani/Asztal/c%23in24h/20.htm#ch20list01 Listing 20.1] shows the procedure in its entirety.

Listing 20.1 Code to Automate Excel[wax ka badal]
private void btnAutomateExcel_Click(object sender, System.EventArgs e)
{
 Excel.Application objExcel = new Excel.Application();
 objExcel.Visible = true;

 //start a new workbook and a worksheet.
 Excel.Workbook objBook =
 objExcel.Workbooks.Add(System.Reflection.Missing.Value);

 Excel.Worksheet objSheet =
 Excel.Worksheet)objBook.Worksheets.get_Item(1);

 Excel.Range objRange;

 objRange = objSheet.get_Range("A1", System.Reflection.Missing.Value);

 // For EXCEL9 Use objRange.Value method in place of all of the
 // objRange.set_Value() statements used in this example. i.e.
 // objRange.Value = "75";

 objRange.set_Value(System.Reflection.Missing.Value, 75 );
 objRange = objSheet.get_Range("B1", System.Reflection.Missing.Value);
 objRange.set_Value(System.Reflection.Missing.Value, 125 );

 objRange = objSheet.get_Range("C1", System.Reflection.Missing.Value);
 objRange.set_Value(System.Reflection.Missing.Value, 255 );

 objRange = objSheet.get_Range("D1", System.Reflection.Missing.Value);
 objRange.set_Value(System.Reflection.Missing.Value, 295 );

 objRange = objSheet.get_Range("E1", System.Reflection.Missing.Value);
 objRange.set_Value(System.Reflection.Missing.Value,
 "=SUM(RC[-4]:RC[-1])" );

 objRange = objSheet.get_Range("A1", "E1");
 objRange.Font.Bold=true;

 objExcel=null;
}

Testing Your Client Application

Now that your project is complete, press F5 to run it and then click the button to automate Excel. If you entered the code correctly, Excel will start, data will be placed into four cells, the total of the four cells will be placed into a fifth cell, and all cells will be made bold (see [file:///C:/Documents%20and%20Settings/dani/Asztal/c%23in24h/20.htm#ch20fig02 Figure 20.2]).

Figure 20.2. You can control almost every aspect of Excel using its object model.

graphics/20fig02.jpg

graphics/bookpencil.gif Automating applications, particularly Office products such as Excel and Word, requires a lot of system resources. If you intend to perform a lot of automation, you should use the fastest machine with the most RAM that you can afford.
>

Summary

In this hour, you learned how a program can make available an object model that client applications can use to manipulate the program. You learned that the first step in automating a program (server) is to reference the type library of the server. After the type library is referenced, the objects of the server are available as though they're internal C# objects. As you have seen, the mechanics of automating a program aren't that difficult�they build on the object-programming skills you've already learned in this book. The real challenge comes in learning the object model of a given server and in making the most productive use of the objects available.

>

Q&A

[file:///C:/Documents%20and%20Settings/dani/Asztal/c%23in24h/20.htm#qad1e60730 Q1:] What are some applications that support Automation?
A1: All the Microsoft Office products, as well as Microsoft Visio, support Automation. You can create a useful application by building a client that makes use of multiple automation servers. For instance, you could calculate data in Excel and then format and print the data in Word.
[file:///C:/Documents%20and%20Settings/dani/Asztal/c%23in24h/20.htm#qad1e60746 Q2:] Can you automate a component without creating a reference to a type library?
A2: Yes, but this gets considerably more complicated than when using a type library. First, you can't early bind to objects, because C# knows nothing about the objects. This means you have no IntelliSense drop-down list to help you navigate the object model; the chances for bugs in this situation are almost unbearably large. To use late binding in Visual C#, use the System.Type.InvokeMember method.

Workshop

The Workshop is designed to help you anticipate possible questions, review what you've learned, and get you thinking about how to put your knowledge into practice. The answers to the quiz are in [file:///C:/Documents%20and%20Settings/dani/Asztal/c%23in24h/app01.htm#app01 Appendix A],"Answers to Quizzes/Exercises." 

 Quiz
[file:///C:/Documents%20and%20Settings/dani/Asztal/c%23in24h/app01lev1sec20.htm#ch20ans01 1:] Before you can early bind objects in an automation server, you must do what?
[file:///C:/Documents%20and%20Settings/dani/Asztal/c%23in24h/app01lev1sec20.htm#ch20ans02 2:] What is the most likely cause of not seeing a type library listed in the Add References dialog box?
[file:///C:/Documents%20and%20Settings/dani/Asztal/c%23in24h/app01lev1sec20.htm#ch20ans03 3:] For C# to use a COM library, it must create a:
[file:///C:/Documents%20and%20Settings/dani/Asztal/c%23in24h/app01lev1sec20.htm#ch20ans04 4:] To manipulate a server via automation, you manipulate:
[file:///C:/Documents%20and%20Settings/dani/Asztal/c%23in24h/app01lev1sec20.htm#ch20ans05 5:] To learn about the object library of a component, you should: 
Exercises
  1. Modify the Excel example to save the Workbook. Hint: Consider the SaveAs() method of the Workbooks collection.
  2. If you have Word installed, add the Word type library to a new project, create an object variable that holds a reference to Word's Application object, create a new document, and send some text to the document.
Waxaa laga keenay "https://so.wiktionary.org/w/index.php?title=User:Foxall/20&oldid=5612"