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

Operation Workbooks Collection
The workbooks collection that can be obtained from the workbooks property of the Application object contains a collection of workbook objects that are currently open in the application. It also has methods for managing open workbooks, creating new workbooks, and opening existing workbook files.

Iterate open workbooks
A collection implements a special method called GetEnumerator that allows them to be iterated. You do not have to call the GetEnumerator method directly, because the foreach keyword in C # uses this method to traverse a set of workbooks. For an example of using foreach, see Listing 5-8.

Listing 5-8 VSTO customizations collected using the foreach Iteration workbook

Private void Sheet1_Startup (object  sender, System.EventArgs e) {  this. application.workbooks;   foreach inch workbooks)  {    MessageBox.Show (workbook). Name);}  }

Get workbook in workbooks
To get workbook in the Workbooks collection, you can use the get_item method that returns the Workbook object. The Get_item method has the index parameter of the Index object type. You can pass a single-index-based int that represents workbook in the collection that you want to access. (Almost all collections in the Office object model are based on 1.) ）

Alternatively, you can pass a string that represents the name of the workbook that you want to access. The name of the workbook is the name of the saved file (for example, "Book1.xls"). If the workbook has not been saved, it will be the temporary name that Excel creates for the new workbook, usually Book1, without the extension . Listing 5-9 shows an example of calling get_item using two indexes.

Listing 5-9 using get_item with int and string index to get the VSTO customization of the workbook

Private voidSheet1_Startup (ObjectSender,system.eventargs e) {excel.workbooks Workbooks= This.   Application.workbooks; if(Workbooks. Count>0) {//gets the first workbook in the collection (based on 1)Excel.Workbook WB = Workbooks.get_item (1);     MessageBox.Show (Wb.name); //get the same workbook by passing the name of the workbook. Excel.Workbook wb2 =Workbooks.get_item (wb.name);  MessageBox.Show (Wb2.name); }}

You can also use the Count property of the Workbooks collection to determine the number of open workbooks. You should check the Count property before accessing the workbook by index to ensure that your index is within the bounds of the collection.

Create a new workbook
To create a new workbook, you can use the Add method of the Workbooks collection. The Add method returns the newly created Workbook object. It takes an optional parameter as an object that can be set to a string that specifies the file name of an existing workbook as a template. Alternatively, you can pass members of the XlWBATemplate enumeration (Xlwbatchart or Xlwbatworksheet) to specify that Excel should create workbooks using either a single chart sheet or a single sheet. If you omit the argument by passing Type.Missing, Excel creates a new, blank workbook that contains the number of worksheets specified by the Application.sheetsinnewworkbook property. Listing 5-10 shows several ways to create a new workbook.

Listing 5-10 using workbook to create a new workbook for VSTO customizations

Private voidSheet1_Startup (ObjectSender,system.eventargs e) {excel.workbook workbooks= This.   Application.workbooks; //Create a new workbook using Mytemplate.xls as a templateExcel.Workbook Workbook1 =workbooks.   ADD (@ "C: \ Mytemplate.xls"); //Create a new workbook with a chart tableExcel.Workbook Workbook2 =workbooks.   ADD (Excel.XlWBATemplate.xlWBATChart); //Set the default number of new worksheets to create in//new blank workbook to ten This. Application.sheetsinnewworkbook =Ten; //Create a blank workbook with 10 worksheetsExcel.Workbook WORKBOOK3 =workbooks. ADD (missing);}

Open a workbook that already exists
To open an existing workbook, you can use the open method of the workbook collection, which returns an open Workbook object. Opens a required Parametera string that represents the file name of the workbook to open. It also has 14 optional parameters that you can pass Type.Missing if you do not want to use any of these parameters. Listing 5-11 shows the simplest possible way to call the Open method.

Checklist 5-11 VSTO Customization Method: Using the Workbooks.Open method to open a workbook

Private void ThisWorkbook_Startup (object  sender, EventArgs e) {  this. Application.Workbooks.Open (    @ "c:\myworkbook.xls", Missing, Missing, missing,    missing, missing, missing, missing, missing, missing, missing, missing, missing, missing,    missing);  MessageBox.Show (workbook. Name);}

Close all open workbooks
Excel provides a close method on the workbooks collection to close all open workbooks. Prompts the user to save any unsaved workbooks unless Application.DisplayAlerts is set to false. As with Application.Quit, there is no guarantee that all workbooks will be physically closed, because when prompted to save the workbook, the user can press the Cancel button while other event handlers loaded from other add-ins can handle the BeforeClose Event and set the Cancel parameter to True.

Manipulating Workbook objects
The Workbook object represents an open workbook in Excel. The workbook has a Name property that returns the names of the workbooks (for example, "Book1.xls") as a string. If the workbook has not been saved, this property returns the temporary name of the document, usually Book1. The name can be passed to get_item on the workbook collection to access the workbook through the name of the collection. If the workbook has been saved (for example, "C: \ My Documents \ Book1.xls"), workbook also has a FullName property to return the full file name of the workbook. For a new unsaved workbook, it returns the default name of Excel to the workbook, such as Book1.

Returns the properties of an active or selected object
The Workbook object has some properties that return the active objectsobjects representation of what is selected in the Excel workbook. The two properties are shown in table 5-4.

Table 5-4 returns workbook properties for the active object

Returns the properties of an important collection
The Workbook object has many properties that return the collection that you will use frequently. Table 5-5 shows some of these properties

Table 5-5 Workbook properties that return important collections

Accessing document properties
Workbook has a BuiltInDocumentProperties property, Returns an object that can be converted to the Microsoft.Office.Core.DocumentProperties collection that represents the built-in document properties associated with the workbook. These are properties that you click on the Summary tab when you choose Properties from the File menu, including attributes such as title, subject, author, and company. The name of the built-in document property associated with the workbook is shown in table 5-6.

Table 5-6 name of the built-in document property in Excel

Workbook also has a customdocumentproperties, It returns an object that can be converted to the Microsoft.Office.Core.DocumentProperties collection, which represents any custom document properties associated with the workbook. These are the custom properties that you see when you click the Customize tab when you choose Properties from the File menu. Custom properties can be created by code and used to store name and value pairs in the workbook. The DocumentProperties collection is discussed in more detail in the "Using Document Properties" section later in this chapter.

Save an Excel workbook
The Workbook object has many properties and methods for saving the workbook, detects whether the workbook has been saved, and gets the path and file name of the workbook.

The Saved property returns a bool value that tells you whether the most recent changes to the workbook have been saved. If closing the document causes Excel to prompt the user to save, the Saved property returns false. If the user creates a blank new workbook without modifying it, the Saved property returns true until the user or code changes the document. You can set the Saved property to True to prevent the workbook from being saved, but note that the user is not prompted to save any changes made to the document when the document is closed.

A more common use of the Save property is to try to keep the saved property in the same state as before the code runs. For example, your code might set or create some custom document properties, but you might not want users to be prompted to save if they did not make any changes to the document when the document was opened. Your code can get the value of the Saved property, make changes to the document properties, and then set the saved value to that value before the code changes the workbook. In this way, your code changes are saved only if the user makes other changes to the document that needs to be saved. Listing 5-12 shows this approach.

Listing 5-1 manipulating VSTO customizations of document properties without affecting saved properties

Private voidThisWorkbook_Startup (Objectsender, EventArgs e) {  BOOLoldsaved = This.  Saved; Try{office.documentproperties props= This. BuiltInDocumentProperties asoffice.documentproperties; props["Author"]. Value ="Mark Twain"; }  finally  {     This. Saved =oldsaved; }}

To save a workbook, you can use the Save method. If the workbook is already saved, Excel overwrites the last saved file. If the newly created workbook is not already saved, Excel tries to create a file name (for example, Book2.xls if the new workbook is named Book2) and saves it to the default file path set by Application.defaultfilepath.

If you want to specify a file name to save the workbook, you must use the SaveAs method. SaveAs takes the file name as a string parameter. It also requires some optional parameters that you can omit by passing type.missing.

If you want to save a copy of the workbook, use the SaveCopyAs method and pass it as a string parameter to the copy's file name. SaveCopyAs Create a backup copy of the workbook. It does not affect the file name or save location of the called Workbook.

You can also close the workbook by using the Close method to save the workbook. If all optional parameters are omitted, the user is prompted to save the workbook after the workbook is created or opened. If False is passed to the SaveChanges parameter, it closes the workbook without saving changes. If the SaveChanges parameter is set to True and the file name is passed as a string for the filename parameter, the workbook is saved to the specified file name.

Use several additional properties to access the file name and location of the workbook, as shown in table 5-7.

Table 5-7 Workbook properties that return file name and path information

Table 5-8 shows some of the other properties related to saving.

Table 5-8 Workbook properties related to saving an Excel workbook

Name the cell
Excel can associate a name (a string identifier) with any cell range. You can define the name of a range of cells by writing code or by using the Define Name dialog box that appears when you choose Insert > Name > Define from the Excel menu bar. You can also select the cell or range of cells to which you want to associate the name, and then type the name in the name box to the left of the formula bar, as shown in 5-2. When you type a name in the Name box, you need to press ENTER after you enter the name to set the name.

Figure 5-2 Naming a series of cells using a name box Mycells

The Names property returns a collection of names that can be used to access any range named in the workbook. The name collection also enables you to create a new named range. The Names collection is discussed in more detail in the section "Using name collections and Name Objects" later in this chapter.

When Excel embeds another application
Commandbars,container and Isinplace are properties that are used when you open a workbook in another application, such as Internet Explorer or Word. Isinplace is a property that returns a bool value that tells you whether the workbook has been opened in another application. The CommandBars property returns the Microsoft.Office.Core.CommandBars collection that is used when the document is in place. The Container property returns an object that can be used to access the object model that contains the application.

Create and activate Windows
The workbook class has a NewWindow method that you can use to create a new window on the workbook. Although you may want to create a new window in a way that will involve calling add on the Windows collection, it does not. The only way to create a new window is to use this method.

There is also an activation method that activates a workbook by setting the first window associated with the workbook as the active window. You can activate a window other than the first window associated with the workbook by using the Windows collection and the Window object. For more information about Windows and window objects, see the "Working with Window Objects" section later in this chapter.

Print workbooks
The PrintOut method prints the workbook. 8 Optional parameters are required, as shown in table 5-9.

Table 5-9 Optional parameters for the PrintOut method

Protect workbooks
Excel allows you to protect two things at the workbook level: the order of the worksheets in the workbook, and the size and location of the Windows associated with the workbook. The Protect method takes three optional parameters: password, structure, and Windows. The password is an optional parameter that you can pass to a string that is used for the workbook password. Structure is an optional parameter that can be set to true to protect the worksheet order so that users cannot rearrange the order of worksheets in the workbook.

Windows is an optional parameter that can be set to true to protect the Windows associated with the workbook from being moved or resized. For example, you can have two tile Windows showing workbooks, and locking them prevents users from moving them from a tiled location. (For more information about tiling windows, see the "Scheduling Windows" section later in this chapter.) ）

Although all of these parameters are optional, workbook protection does not really do anything unless you set the structure or Windows parameter to True. If you want to protect cells in a workbook from being edited, you must use the Worksheet.protect method.

Working with worksheets, charts, and table collections
worksheets, charts, and table collections are very similar, so this section describes them together. They differ mainly in whether they contain worksheets (worksheets) or charts (charts) or both (tables). In this section, as in the remainder of this chapter, we use the Word table to refer to a chart table or worksheet.

Iterate through open tables
These collections have a GetEnumerator method that can be iterated using the foreach keyword in C #, as shown in Listing 5-13.

Listing 5-13 a rewritten VSTO customization on a worksheet, chart, and table collection

Private voidThisWorkbook_Startup (Objectsender, EventArgs e) {Excel.Application app= This.  Application;  This.  Charts.add (missing, missing, missing, missing); foreach(Excel.Worksheet sheetinch  This. Worksheets) {MessageBox.Show (String.Format ("Worksheet {0}", Sheet.  Name)); }  foreach(Excel.Chart Chartinch  This. Charts) {MessageBox.Show (String.Format ("Chart {0}", chart.  Name)); }  foreach(ObjectSheetinch  This. Sheets) {Excel.Worksheet Worksheet= Sheet asExcel.Worksheet; if(Worksheet! =NULL) {MessageBox.Show (String.Format ("Worksheet {0}", worksheet.    Name)); } Excel.Chart Chart= Sheet asExcel.Chart; if(Chart! =NULL) {MessageBox.Show (String.Format ("Chart {0}", chart.    Name)); }  }}

accessing worksheets in a workbook collection
To access the worksheets in the Worksheets,charts and sheets collections, you can use a method named Get_item that returns an object. You need to convert the returned object to a worksheet or a chart. The objects returned from the worksheets collection can be converted to worksheet. Objects returned from the chart collection can always be converted to charts. The IS operator should be used to test the objects returned from the Sheets collection to determine whether the returned object is worksheet or chart. You can then convert it to the appropriate object.

The Get_item method uses the index parameter of the type object. You can pass a string that represents the name of a worksheet or chart table, or you can pass a 1-based index to the collection. You can use the Count property to examine how many items in a given collection.

Add a sheet or chart table
To add a worksheet or chart sheet to a workbook, use the Add method. The Add method on the sheet and sheet collections uses four optional parameters: before, after, count, and type. ")". The chart collection adds a method that requires only the first three parameters.

The before parameter can be set to a worksheet or chart that represents the worksheet to which you want to add a new sheet. The after parameter can be set to a worksheet or chart that represents the worksheet to which you want to add a new sheet. The count parameter can be set to the number of new worksheets to add. The type parameter is set to Xlsheettype.xlworksheet to add a sheet or Xlsheettype.xlchart to add a chart table. Note that if you try to use Xlchart as the type parameter when you use Worksheets.add, Excel throws an exception because worksheets is a collection of only worksheet objects. You can specify before or after, but you cannot specify two parameters at a time. If you omit the previous and subsequent arguments, Excel adds a new sheet after all existing worksheets.

Listing 5-14 shows several different ways to use the Add method in various collections.

Listing 5-14 VSTO customization using the Add method in charts, worksheets, and worksheet collections

Private voidThisWorkbook_Startup (Objectsender, EventArgs e) {Excel.Chart Chart1= This. Charts.add (missing, missing, missing, missing) asExcel.Chart; Excel.Chart Chart2= This. Sheets.add (missing, missing, missing, Excel.XlSheetType.xlChart) asExcel.Chart; Excel.Worksheet Sheet1= This. Sheets.add (Chart1, Missing,3, missing) asExcel.Worksheet; Excel.Worksheet Sheet2= This. Worksheets.add (Missing, Chart2, missing, missing) asExcel.Worksheet;}

Replication of tables
You can use the Copy method to make a copy of a worksheet that requires two optional parameters: front and back. You can specify before or after, but you cannot specify two parameters at a time.

The before parameter can be set to a worksheet or chart that represents the sheet to which the table should be copied. The after parameter can be set to a worksheet or a chart in a table, and then copied to a new sheet. If you omit the previous and subsequent arguments, Excel creates a new workbook and copies the worksheets to the new workbook.

Move Worksheets
The Move method moves the worksheet to a different location in the workbook (the workbook is moved to a different tab location on the Sheet tab) and has two optional parameters: front and back. You can specify before or after, but you cannot specify two parameters at a time. If you omit two parameters, Excel creates a new workbook and moves the worksheet to the new workbook.

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