Manipulating the Server | Sams Teach Yourself Visual C# 2005 in 24 Hours, Complete Starter Kit

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. In the following sections, you'll manipulate the new Excel object by setting properties and calling methods, and in so doing you will be manipulating Excel itself.

Forcing Excel to Show Itself

When Excel is started using automation, it's loaded but not shown. Remaining hidden enables the developer to use Excel's functionality and then close it without the user knowing what happened. For example, 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 userall without the user 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 any easier. Add the following statement to make Excel visible:

objExcel.Visible = true;

Creating an Excel Workbook

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. When you start Excel via automation, however, 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. Enter the following statements to create a new workbook and reference the default worksheet (explained in the next section):

//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);

Working with Data in an Excel Workbook

Workbooks contain a single worksheet by default. 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 ActiveCell object, which is an object property of the Application object. Entering data into a cell involves first selecting a cell and then passing data to it. Selecting a cell is accomplished by calling the Select method of the Range object; the Range object is used to select one or more cells. The Select 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 omit the ending column and row. After the range is set, you pass data to the FormulaR1C1 property of the ActiveCell object (which references the cell specified by the Range object). Setting the FormulaR1C1 property has the effect of sending data to the cell. 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.

Did you Know?

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 headaches 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. VBA code is similar to Visual Basic 6 code. Although this code won't port directly to Visual C# 2005, it's rather easy to migrate in most cases, and the macro builder does all or most of the work of determining objects and members for you.

The following section of code uses the techniques just described to add data to four cells. Follow these steps now to automate sending the data to Excel:

1.	Enter this code into your procedure: Excel.Range objRange; objRange = objSheet.get_Range("A1", System.Reflection.Missing.Value); objRange.Value2 = 75; objRange = objSheet.get_Range("B1", System.Reflection.Missing.Value); objRange.Value2 = 125; objRange = objSheet.get_Range("C1", System.Reflection.Missing.Value); objRange.Value2 = 255; objRange = objSheet.get_Range("D1", System.Reflection.Missing.Value); objRange.Value2 = 295; The next step is to have Excel total the four cells. You'll do this by using the `Range` object to select the cells, activating a new cell in which to place the total, and then using `FormulaR1C1` again to create the total by passing it a formula rather than a literal value.
2.	Enter this 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])" );
3.	Next, 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 Nothing. Excel remains open even though you've destroyed the automation instance (not all servers do this).

4.	Add this last statement to your procedure to release the reference to Excel: objExcel=null;

To help ensure that everything is entered correctly, Listing 22.1 shows the procedure in its entirety.

Listing 22.1. Code to Automate Excel

private void btnAutomateExcel_Click(object sender, 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);             // Use a Range object to select cells and set data.             Excel.Range objRange;             objRange = objSheet.get_Range("A1", System.Reflection.Missing.Value);             objRange.Value2 = 75;             objRange = objSheet.get_Range("B1", System.Reflection.Missing.Value);             objRange.Value2 = 125;             objRange = objSheet.get_Range("C1", System.Reflection.Missing.Value);             objRange.Value2 = 255;             objRange = objSheet.get_Range("D1", System.Reflection.Missing.Value);             objRange.Value2 = 295;             // Use a Range object to select cells and sum them.             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 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 Figure 22.2).

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