Working with the Worksheets, Charts, and Sheets Collections

The Worksheets, Charts, and Sheets collections are all very similar, so this section covers them together. They differ mainly in whether they contain worksheets (Worksheets) or chart sheets (Charts) or both (Sheets). In this section, as in the rest of the chapter, we use the word sheet to refer to either a chart sheet or a worksheet.

Iterating over the Open Sheets

These collections have a GetEnumerator method that allows them to be iterated over using the foreach keyword in C#, as shown in Listing 5-13.

Listing 5-13. A VSTO Customization That Iterates over the Worksheets, Charts, and Sheets Collections

private void ThisWorkbook_Startup(object sender, EventArgs e) { Excel.Application app = this.Application; this.Charts.Add(missing, missing, missing, missing); foreach (Excel.Worksheet sheet in this.Worksheets) { MessageBox.Show(String.Format( "Worksheet {0}", sheet.Name)); } foreach (Excel.Chart chart in this.Charts) { MessageBox.Show(String.Format( "Chart {0}", chart.Name)); } foreach (object sheet in this.Sheets) { Excel.Worksheet worksheet = sheet as Excel.Worksheet; if (worksheet != null) { MessageBox.Show(String.Format( "Worksheet {0}", worksheet.Name)); } Excel.Chart chart = sheet as Excel.Chart; if (chart != null) { MessageBox.Show(String.Format( "Chart {0}", chart.Name)); } } }

Accessing a Sheet in the Collection

To access a sheet in the Worksheets, Charts, and Sheets collections, you use a method called get_Item, which returns an object. You need to cast the returned object to a Worksheet or Chart. Objects returned from the Worksheets collection can always be cast to Worksheet. Objects returned from the Charts collection can always be cast to Chart. Objects returned from the Sheets collection should be tested using the is operator to determine whether the object returned is a Worksheet or a Chart. It can then be cast to the appropriate object.

The get_Item method takes an Index parameter of type object. You can pass a string representing the name of the worksheet or chart sheet or you can pass a 1-based index into the collection. You can check how many items are in a given collection by using the Count property.

Adding a Worksheet or Chart Sheet

To add a worksheet or chart sheet to a workbook, you use the Add method. The Add method on the Sheets and Worksheets collection takes four optional parameters of type object: Before, After, Count, and Type. The Charts collection Add method only takes the first three parameters.

The Before parameter can be set to a Worksheet or Chart representing the sheet before which the new sheet is to be added. The After parameter can be set to the Worksheet or Chart representing the sheet after which the new sheet is to be added. The Count parameter can be set to the number of new sheets you want to add. The Type parameter is set to XlSheetType.xlWorksheet to add a worksheet or XlSheetType.xlChart to add a chart sheet. Note that if you try to use xlChart as the Type parameter when using Worksheets.Add, Excel will throw an exception because Worksheets is a collection of only Worksheet objects. You can specify either Before or After, but not both parameters. If you omit the Before and After parameters, Excel adds the new sheet after all the existing sheets.

Listing 5-14 shows several different ways of using the Add method on the various collections.

Listing 5-14. A VSTO Customization That Uses the Add Method on the Charts, Sheets, and Worksheets Collections

private void ThisWorkbook_Startup(object sender, EventArgs e) { Excel.Chart chart1 = this.Charts.Add(missing, missing, missing, missing) as Excel.Chart; Excel.Chart chart2 = this.Sheets.Add(missing, missing, missing, Excel.XlSheetType.xlChart) as Excel.Chart; Excel.Worksheet sheet1 = this.Sheets.Add(chart1, missing, 3, missing) as Excel.Worksheet; Excel.Worksheet sheet2 = this.Worksheets.Add(missing, chart2, missing, missing) as Excel.Worksheet; }

Copying a Sheet

You can make a copy of a sheet by using the Copy method, which takes two optional parameters: Before and After. You can specify either Before or After, but not both parameters.

The Before parameter can be set to a Worksheet or Chart representing the sheet before which the sheet should be copied to. The After parameter can be set to a Worksheet or Chart representing the sheet after which the new sheet should be copied to. If you omit the Before and After parameters, Excel creates a new workbook and copies the sheet to the new workbook.

Moving a Sheet

The Move method moves the sheet to a different location in the workbook (that is, it moves it to a different tab location in the worksheet tabs) and has two optional parameters: Before and After. You can specify either Before or After, but not both parameters. If you omit both parameters, Excel creates a new workbook and moves the sheet to the new workbook.