VSTO: Using C # to develop Excel, Word "16"

Working with sheet objects
The worksheet object represents a worksheet in an Excel workbook. Worksheet has a Name property, which returns the names of the worksheets (for example, "Sheet1").

Worksheet Management
The worksheet object has an index property that provides a 1-based tab location for worksheets in the Tabbed Worksheets tab that appears in the lower-left corner of the worksheet window. You can use the Move method to move a worksheet to a different tab location. The Move method has two optional parameters: A before parameter that you can pass to the worksheet before the worksheet is moved, and an after parameter that you can pass to the sheet after the worksheet that you want to move. If you omit two optional parameters, Excel creates a new workbook and moves the worksheet to the new workbook.

You can also use the Copy method to make a copy of the worksheet. Like the Move method, it requires two optional arguments: A before and after parameter that specifies where the copied worksheet should be relative to the other sheet. You can specify before or after, but you cannot specify two parameters at a time. If you omit two optional parameters, Excel creates a new workbook and copies the worksheets to the new workbook.

To activate a specific worksheet, use the Activate method. This method activates a worksheet by setting the first window associated with the worksheet as the active window. It also selects the tab that corresponds to the worksheet and displays the worksheet in the active window.

The equivalent of right-clicking on the sheet tab and selecting Delete from the pop-up menu is provided by the Delete method. When you use this method, Excel displays a warning dialog box. You can prevent this warning dialog box from appearing by using the DisplayAlerts property of the Application object, which is discussed in the "Controlling dialog boxes and alerts displayed in Excel" section earlier in this chapter.

You can hide a worksheet so that its tabs are not displayed at all by using the Visible property. The Visible property is of type xlsheetvisibility and can be set to Xlsheetvisible,xlsheethidden and xlSheetVeryHidden. The last value hides the worksheet so that it can only be displayed again by setting the Visible property to Xlsheetvisible. Set the Visible property to Xlsheethidden to hide the worksheet, but the user can still unhide the worksheet by going to the Format menu and selecting Sheet, and then clicking Hide.

Sometimes, you use the Visible property to hide a worksheet so that it can be used to store additional data that your application uses in a temporary worksheet that the user does not see. The cached data functionality of VSTO provides a better approach, as described in chapter 18th. It has the added benefit that you can manipulate hidden data in an Excel spreadsheet without having to start Excel. This enables you to pre-populate an Excel worksheet with custom data on the server.

Note that the workbook must contain at least one visible sheet. So when you use the Delete method and the Visible property, you must keep this restriction in mind. If your code tries to hide or delete the last visible form in the workbook, an exception is thrown.

Listing 5-23 illustrates the use of several of these properties and methods.

Listing 5-23 VSTO customizations for a collection of worksheets

Private voidThisWorkbook_Startup (Objectsender, EventArgs e) {Excel.Worksheet Sheeta= This. Worksheets.add (missing, missing, missing, missing) asExcel.Worksheet; Sheeta.name="Sheeta"; Excel.Worksheet Sheetb= This. Worksheets.add (missing, missing, missing, missing) asExcel.Worksheet; Sheetb.name="Sheetb"; Excel.Worksheet sheetc= This. Worksheets.add (missing, missing, missing, missing) asExcel.Worksheet; Sheetc.name="sheetc"; //Tab Indexes  stringmsg ="{0} is at tab index {1}";  MessageBox.Show (String.Format (msg, Sheeta.name, Sheeta.index));  MessageBox.Show (String.Format (msg, Sheetb.name, Sheetb.index));  MessageBox.Show (String.Format (msg, Sheetc.name, Sheetc.index));  Sheetc.move (Sheeta, missing); MessageBox.Show ("Moved sheetc in front of Sheeta"); //Tab IndexesMessageBox.Show (String.Format (msg, Sheeta.name, Sheeta.index));  MessageBox.Show (String.Format (msg, Sheetb.name, Sheetb.index));  MessageBox.Show (String.Format (msg, Sheetc.name, Sheetc.index));  Sheetb.copy (Sheeta, missing); Excel.Worksheet sheetd= This. Worksheets.get_item (Sheeta.index-1) asExcel.Worksheet; ((Excel._worksheet) Sheeta).  Activate (); MessageBox.Show (String.Format ("Copied Sheetb to create {0} at Tab index {1}", Sheetd.name, Sheetd.index));  Sheetd.delete (); Sheeta.visible=Excel.XlSheetVisibility.xlSheetHidden; MessageBox.Show ("Deleted sheetd and hid Sheeta.");}

Use name
As mentioned earlier, you can use Workbook.names to define a named range at the workbook level. You can also define a range of named ranges to a specific worksheet by using the names property associated with the worksheet object. The names property returns a Names collection that contains only the name of the scope to the job table. For more information about the names collection, see the "Working with name collections and Name Objects" section earlier in this chapter.

customizing properties with Worksheets
You can add custom properties that have names and values to the worksheet. Custom properties are a convenient way to associate other hidden information with worksheets that you do not want to put in cells. Unlike the document properties associated with the workbook, custom properties are not displayed anywhere in the Excel user interface. A custom property at the sheet level does not have a 256-character limit on the value of the document property. You can store larger chunks of data in a worksheet custom property.

The CustomProperties property returns a collection of custom properties associated with the worksheet. You can use the Add method of the CustomProperties collection to add a custom property, pass a string for the name of the custom property you want to create, and pass an object to the value that you want to associate with the custom property. To get a specific custom property, use the Customproperties.item method and pass the index of the property you want to get. Unfortunately, the Item method requires only a 1-based index, not the name of the custom attribute you added. Therefore, you must traverse the collection and examine the Name property of each returned CustomProperty object to determine whether the desired custom property has been found. Listing 5-24 shows an example of creating a custom property and then accessing it again.

Listing 5-24 accessing the VSTO customization of the custom DocumentProperty object

Private voidThisWorkbook_Startup (Objectsender, EventArgs e) {Excel.Worksheet sheet= This. Worksheets.add (missing, missing, missing, missing) asExcel.Worksheet; //ADD a custom propertyexcel.customproperties Props=sheet.  CustomProperties; Props. ADD ("MyProperty","Some Random Value"); Props. ADD ("Otherproperty",1); //Now , enumerate the collection to find MyProperty again.  foreach(Excel.customproperty propinchprops) {    if(Prop. Name = ="MyProperty") {MessageBox.Show (String.Format ("{0} property was set to {1}.", Prop. Name, Prop.      Value));  Break; }  }}

If you use VSTO to associate code with a workbook, it is often better to use cached data instead of custom properties. The cached data feature can put datasets and any XML serializable type into the data island of the document. The data island can also be accessed on the server without starting Excel. For more information about the caching data features of VSTO, see Chapter 18th.

Protect worksheets
The Protect method protects the worksheet so that the user cannot modify the worksheet. When you use the Protect method to protect a worksheet, all cells in the workbook are automatically locked. The protection method corresponds to the Protect Sheet dialog box shown in Figure 5-3. You can access this dialog box by selecting Tools > Protection > Protection pages.

Figure 5-3 Protect Sheet dialog box.

Some of the optional parameters passed to the Protect method control exactly what can be modified, as shown in table 5-14. Many of these options correspond to the list of checks shown in Figure 5-3.

Table 5-14 Optional parameters for the protection method

When a worksheet is protected, you have two methods that exclude certain ranges of cells from being locked. The first method is to add an exclusion object using the AllowEditRanges collection returned from Worksheet.protection.allowedit-ranges. The AllowEditRanges collection corresponds to the Allow user to edit the Scope dialog box, as shown in 5-4. You can access this dialog box by selecting Tools > Protection > Allow users to edit scopes.

Figure 5-4 Allow user to edit Scope dialog box

You must first use the AllowEditRanges collection to exclude, and then use the Protect method to protect the worksheet. After you protect the worksheet, you cannot make any changes to the AllowEditRanges collection until you unprotect the worksheet. To exclude this way, you can give the title and display it in the Allow users to edit Scopes dialog box. Scopes excluded from protection in this way will return true from their Range.allowedit property. Listing 5-25 shows a VSTO customization that uses AllowEditRanges to create two protection exclusions, and then uses the Protect method to protect the worksheet.

Listing 5-25 using alloweditranges will disallow adding to protected VSTO customizations

Private voidThisWorkbook_Startup (Objectsender, EventArgs e) {Excel.Worksheet sheet= This. Worksheets.add (missing, missing, missing, missing) asExcel.Worksheet; Excel.alloweditranges AllowEdits=sheet.    Protection.  AllowEditRanges; Allowedits.add ("Editable Cell", Sheet.get_range ("A1", missing), missing); Sheet. Protect (missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing,  Missing, missing, missing, missing); Excel.Range Protectedrange= Sheet.get_range ("A2", missing); MessageBox.Show (String.Format ("A2 ' s Locked is set to {0}", protectedrange.locked)); MessageBox.Show (String.Format ("A2 ' s allowedit is set to {0}", Protectedrange.allowedit)); Try{protectedrange.value2="should fail"; }  Catch(Exception ex) {MessageBox.Show (ex).  Message); }  Try{Allowedits.add ("This should fail", Sheet.get_range ("A2", missing), missing); }  Catch(Exception ex) {//You can ' t add to the AllowEditRanges collection//When the worksheet is protectedMessageBox.Show (ex.  Message); } excel.range AllowEditRange= Sheet.get_range ("A1", missing); MessageBox.Show (String.Format ("A1 ' s Locked is set to {0}", alloweditrange.locked)); MessageBox.Show (String.Format ("A1 ' s allowedit is set to {0}", Alloweditrange.allowedit)); Alloweditrange.value2="should succeed";}

When a worksheet is protected, the second way to exclude certain ranges of cells from being locked is to use the Range.locked property. Cells that are excluded in this manner do not appear in the Allow users to edit Scopes dialog box. Listing 5-26 shows the use of the Range.locked property to add exclusion to protection.

Listing 5-26 using range.locked to add a VSTO customization of an exclusion object

Private voidThisWorkbook_Startup (Objectsender, EventArgs e) {Excel.Worksheet sheet= This. Worksheets.add (missing, missing, missing, missing) asExcel.Worksheet; Excel.Range Range1= Sheet.get_range ("A2", missing); Range1. Locked=false; Sheet. Protect (missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing,  Missing, missing, missing, missing); MessageBox.Show (String.Format ("A2 ' s Locked is set to {0}", Range1.  Locked)); MessageBox.Show (String.Format ("A2 ' s allowedit is set to {0}", Range1.  AllowEdit)); Range1. Value2="should succeed";}

After the worksheet is protected, you can check the protection settings for the document through several properties, and further modify the protection options, as shown in table 5-15.

Table 5-15 allows you to examine and further modify the properties of document protection

Using OLEObjects
In addition to containing cells, worksheets can contain embedded objects from other programs, such as embedded Word documents, and ActiveX controls. To use these objects, you can use the OLEObjects method on the Worksheet object. The OLEObjects method accepts an optional Index object parameter, which you can pass the name of the OLEObject or the OLEObject based on the 1 index in the collection. The OLEObjects method can also be used as a way to access the OLEObjects collection, which can be confusing. If you pass a string that represents a name or a 1-based index as an int, it returns the specified OLEObject. If you pass it type.missing, it returns the OLEObjects collection.

Any time you add OLEObject to a worksheet, Excel also includes the object in the Shapes collection that is returned from the Style property in the sheet object. To get OLEObject unique properties, you can use the Shape.oleformat property.

You can write C # code that adds ActiveX controls to a worksheet and talk to them by converting Oleobject.object or Shape.OLEFormat.Object to the appropriate type. You must add a reference to the COM library that is associated with the ActiveX control that you want to use in your C # project. Doing so causes Visual Studio to generate an interop assembly and add it to your project. Or, if the primary interop assembly is registered for the COM library, Visual Studio automatically adds a reference to the pre-generated primary interop assembly. You can then convert Oleobject.object or Shape.OLEFormat.Object to the correct type that Visual Studio adds to the COM library object that corresponds to the ActiveX control.

VSTO enables you to add Windows Forms controls to a worksheet in a more powerful way, with. NET-centric handling of controls. Therefore, we will not use ActiveX controls more in this book. For more information about VSTO support for Windows Forms controls, see Chapter 14th, "Using Windows Forms in VSTO."

Working with shapes
The Shapes property returns a collection of shapes collections of Shape objects. The Shape object represents various objects that can be inserted into an Excel spreadsheet, including graphics, AutoShape, WordArt, embedded objects, or ActiveX controls or pictures.

The Shapes collection has a Count property that determines how many shapes are in the worksheet. It also has an item method that takes a 1-based index to get a specific shape in the collection. You can also enumerate the Shapes collection using foreach.

Several methods on the Shapes collection can add various objects that can be represented as "shapes." These methods include Addcallout,addconnector,addcurve,adddiagram,addlabel,addline,addoleobject,addpicture,addpolyline, Addshape,addtextbox and AddTextEffect.

The Shape object has properties and methods that place shape on the worksheet. It also has properties and methods that allow you to format and modify Shape objects. The properties on the Shape object return some of the object 3-20 shown.

Using ChartObjects
In this book, we use a phrase chart when referencing a chart from a worksheet in a workbook. Figure 5-5 shows the last page of the Chart Wizard that appears when you insert a new chart. Excel enables you to insert a chart into a new chart, which we call a chart sheet, and allows you to add the chart as an object to the worksheet. The object model calls the chart that is added as an object in the form.

Figure 5-5 Chart location steps for the Chart Wizard

What is more complicated is that the object in the chart Table object model is a chart, but ChartObject also has a property that returns the chart. ChartObject has its own set of properties that control the position of the chart on the worksheet. However, the chart object returned by the Chartobject.chart property can find properties and methods that actually manipulate the contents of the chart.

To use ChartObjects, you can use the ChartObjects method on the Worksheet object. The ChartObjects method accepts an optional index parameter for the type object, and you can pass the name of the ChartObject in the collection or the 1-based index of chartobject. The ChartObjects method can also be used as a way to get a ChartObjects collection, which can be confusing. If you pass a string that is represented as a name or a 1-based index, the specified chartobject is returned. If you pass it type.missing, it returns the ChartObjects collection.

To add chartobject to a worksheet, you can use the Chartobjects.add method, which takes "left", "top", "wide", and "high" as the double value of the point. Whenever you add chartobject to a worksheet, Excel also contains the object in the Shapes collection returned by the Shapes property in the sheet object.

Working with lists
Excel 2003 introduces the ability to create lists from a range of cells. Just select a range of cells, right-click the selection, and select Create List. The list has column headings and drop-down options that enable users to easily sort and apply filters to the data in the list. It has a total row that can be summed automatically and perform other operations on a column of data. It has an asterisk-marked insert row at the bottom of the list, allowing the user to add additional rows to the list. Figure 5-6 shows an example of a column representation in Excel.

Figure 5-6 the list in Excel

You can use the ListObjects property to access a list in a worksheet. The ListObjects property returns the ListObjects collection. The ListObjects collection has a Count property to determine how many lists are in the worksheet. It also has an item method that takes an index-based index or the name of a list object as a string to get a ListObject object from the collection. You can also use foreach to enumerate listobjects collections.

Table 5-16 shows some of the most commonly used properties of the ListObject object. You will discuss VSTO support for data in the 17th chapter, "Programming VSTO data."

Table 5-16 Key properties of ListObject

VSTO: Using C # to develop Excel, Word "16"