Chapter 2 Section 3 key points of the Programming Model Now, let's summarize our introduction to the workbook component. I will explain the main points of the (workbook) control programming model so that you can understand how to use this control, and how to implement different functions when you need to write scripts. This section is not a complete guide to programming models-it will be another book. Here we will introduce common attributes and Methods. Each attribute and method will be accompanied by a brief description. For more information about the programming model, see msowcvba. online help in the CHM file, which is located in the localization directory of the office directory (for English-speaking people, it is generally the Program Files \ Microsoft Office \ 1033 directory ). If you are familiar with the Excel programming model, you will find that it is very similar to the programming model of the workbook component.
Use enumeration in scripts COM and OLE Automation allow the component to define enumeration. Each enumeration is a specified set of constants, and the function is similar Programming Language Type Declaration in. An enumeration can be used as a property or method parameter, which enables environments such as VB and VC to display the statement completion drop-down list containing valid constants in an enumeration (
Translator's note : Statement completion refers to how developers are advised to complete a line in the development environment. Code So that developers do not need to remember all the members of the object ). Like many other ActiveX controls, owc contains a series of predefined enumerations. However, there is no type concept in a script environment such as a web page or an ASP page. Therefore, enumeration member names cannot be used in the existing scripting languages, because these languages cannot know what a specific constant (such as sshalignleft) corresponds. This means that your VBSCRIPT or ECMA script code will be messy because of the mysterious numbers that cannot be described by yourself (
Note: Refers Program ). To solve this problem as much as possible, all owc components have a top-level Attribute named constants, which can be used in the script language to access various enumeration members of the component. For example, if you want to use the sshalignleft constant in VBScript code to display the content of a unit to the left, you can write the code as follows:
| Myrange. halignment = spreadsheet1.constants. sshalignleft |
To use an enumerated constant, You can reference it as an attribute of the constants object, and the object returns the correct value of the enumerated member. Note that if you plan to use constant objects in multiple places of your function, it is very useful to set a variable equal to the constants object. This avoids unnecessary typing, it can also improve performance. For example, you can write the code as follows:
| Set c = spreadsheet1.constantsmyrange. halignment = C. sshalignrightmyrange. valignment = C. ssvalignbottom |
Constants objects are only valid in script languages without enumeration concepts. If you are writing code in VB, VBA, or C ++, use enumeration directly as usual, regardless of the constants object.
Input data to the component I have already talked about multiple data input components. I will describe these skills in the programming model in more detail now. Table 2-1 lists the attributes and methods related to data loading. All these attributes and methods are published through the workbook object interface.
Table 2-1.
Load data-related attributes and methods.
| Attribute or Method |
Description |
| Spreadsheet. datatype |
The string type attribute tells the workbook component which attribute should be used to load data when multiple attributes are set for loading data, the value you assign to this attribute is the name of the attribute you should use to load data-for example, set this attribute to a bit htmlurl, the component will load the data obtained from the URL in the htmlurl property. |
| Spreadsheet. htmldata |
String type attribute, which can be used to obtain or set the workbook content in HTML table format. This format also contains additional attributes and XML tags, which are used to retain the information required for recreating a spreadsheet model (such as a unit formula, however, they do not belong to the table format of html3.2. You can set this attribute to contain a string of an HTML table, or read the attribute value to obtain the complete content of the workbook when you want to save it. |
| Spreadsheet. htmlurl |
A string type attribute that contains a URL from which the workbook data can be loaded. This URL must return an HTML document containing the table. You can use this attribute to load workbooks stored in HTML format in Excel 2000. This attribute can also be an ASP page or CGI program that generates tables in real time from the database. |
| Spreadsheet. csvdata |
The string type property is similar to the htmldata property, but the format of the data accepted and returned is CSV. This attribute is useful when you need to load data from an old system that cannot publish HTML format. |
| Spreadsheet. csvurl |
The string type property is similar to the htmlurl property, but the data format it wants to obtain from the URL is in CSV format. Like the htmlurl attribute, this URL can be an ASP page or CGI program that generates tables in real time from the database. |
| Spreadsheet. loadtext |
You can load a separated text file into a workbook. Different from the CSV format, this text file can use any field separator. The range object can also use the loadtext Method and Its sibling method parsetext to load the text into a specified area of the workbook. The loadtext method can point to a file in your local file system or to a URL. |
Region The most commonly used programming interface of a workbook component is a range object. The range object is returned in many methods. When you need to modify, format, sort, and set automatic filtering for each unit in a region, the range object is used. Table 2-2 shows the properties and methods of the range object that you should understand when creating a workbook Component solution.
Table 2-2.
Main attributes and methods of the range object.
| Attribute or Method |
Introduction |
| Spreadsheet. Range |
This method accepts a region reference (such as A1: B2 or A: B) and returns a range object. Because a region can have only one unit, you can also reference a single unit (such as A1 ). This method can also accept two different unit references, and return a region containing the two units. |
| Range. Address |
This attribute returns the address of a region (for example, A1: B2 ). |
| Range. Cells |
When I first saw this function, I was very confused because it was defined as returning another range object. However, you can use this attribute as a set of multiple units, that is, you can use for each to access each unit in a loop. This attribute can also be accessed in the form of a two-dimensional array. For example, myrange. cells (). Value Returns the value of Row 1 and column 3 in the region. Workbook and worksheet objects also have the cells attribute, so you can use it to access the specified unit instead of the range method mentioned above. |
| Range. column, range. Row |
These attributes indicate the numbers of the first row and the first column in the region. This attribute is useful if you need to know which row and column are currently located when performing iteration in rows and columns of a region. |
| Range. columns, range. Rows |
Although the names are very similar to those of the first two attributes, these attributes return a set of rows or columns contained in the range object. Range. Columns. Count and range. Rows. Count tell you the number of rows and columns in the current region. |
| Range. htmldata |
This attribute is similar to the spreadsheet. htmldata attribute, but this attribute of the range object is read-only. You can use it to quickly obtain the representation of an HTML table with data in a given area. |
| Range. Value |
This attribute gets or sets the region value. Although the Help file says that when a region is composed of multiple units, range. Value Returns a two-dimensional variable array, but this is not actually implemented in the current version. However, range. value can accept a two-dimensional array variable to input data in the area. When you need to use a text value to set one or more unit values, you can use this attribute to obtain the unformatted value of a unit. (Note:A value without a format is a value that has not been formatted .) |
| Range. Formula |
This attribute reads and writes the formula string of a unit. This attribute can be used when you need to obtain or set a formula for one or more units in a region. Remember to use equal signs (=) at the beginning of the formula ). |
| Range. Text |
The range. Text attribute returns the formatted version of the range. value attribute. This attribute is useful when you need to display formatted values in a message box or another user interface element. This attribute value is the value that you pass when you add an autofilter object filter condition. (Note:That is to say, filtering is based on the surface value of a cell) |
Format After loading data into a workbook, you may need to control formatting by programming. Each unit has its own font, alignment, border, color, and numeric format attributes. All these features can be set using the attributes in Table 2-3.
Table 2-3.
The attribute used for formatting.
| Attribute |
Description |
| Range. numberformat |
String type attribute, the format of numbers in the control unit. You can reference existing format names to use many built-in formats (such as currency ). You can also create a custom format (for example, to display 1000 as 1,000 ). |
| Range. Font |
Returns a common font object that can be used by many components. Range. Font allows you to set multiple attributes of a font object, such as name, size, bold, italic, color, and underline. Note that you can use the IE color name in the font. Color Attribute if needed. |
| Range. halignment, range. valignment |
These two attributes control the horizontal and vertical text alignment of each unit in the area. Each attribute defines its own alignment enumerated values. |
| Range. Borders |
This property returns a borders object, which can be used to set the border features of each cell, such as the line width, style, and color of the border. |
Component-level appearance and Behavior Many Attributes and methods affect the entire workbook component. Table 2-4 lists the most interesting attributes and methods of custom solutions.
Table 2-4.
Affects the main attributes of the entire workbook.
| Property |
Description |
| Spreadsheet. allowpropertytoolbox |
Control whether to display the attribute Toolbox (Note:Refers to the "commands and options" window ). If this attribute is set to false, the icons on the toolbar of the property toolbox and the menu items on the right-click menu are disabled. You can use this attribute at any time to disable the Default Formatting user interface and provide your own formatting interface. |
| Spreadsheet. autofit |
Determines whether the control is in the automatic adaptation mode. See the autofit details in the previous chapter to learn how this feature works. |
| Spreadsheet. Dirty |
If the widget has any modifications and any unit is changed, this attribute is set to true to notify you of this change. The dirty attribute is usually used to determine whether to save the content. Note that this is a read/write attribute, so you can reset it to make the workbook "clean ". You commonly use the dirty property to determine whether you need to save the contents in some way. |
| Spreadsheet. displaycolheaders, spreadsheet. displayrowheaders |
Determines whether to display the row header and column header. The default values of these two attributes are true. Generally, you can set these two attributes to false when you need to use your own code to fully control the workbook interface. |
| Spreadsheet. displaygridlines |
Controls whether grid lines are displayed. This attribute is displayed by default. If you want to use a border in a custom solution where the unit separator is needed, this attribute is often disabled. |
| Spreadsheet. displaypropertytoolbox |
Determines whether to display the attribute toolbox. If the attribute is set to true, it is displayed. If the attribute is set to false, it is hidden. |
| Spreadsheet. displaytitlebar |
Determines whether to display the title bar. The title bar is displayed by default. You can use the titlebar attribute described below to modify the content and format on the title bar. |
| Spreadsheet. displaytoolbar |
Determines whether a toolbar is displayed. The default value is displayed. |
| Spreadsheet. enableautocalculate |
Controls how the workbook model is recalculated. If this attribute is set to false, the workbook model does not automatically recalculate. You must call the calculate method of the worksheet object to see the new result after the input is changed. This attribute is useful when you plan to modify a series of inputs and want to recalculate the model only after all modifications are completed. This property defaults to true-the model is automatically recalculated when the workbook is modified. |
| Spreadsheet. screenupdating |
By default, workbooks always display the latest data on the screen. However, if you plan to perform a large number of operations, you do not want the workbook to flash after each operation. You can set this attribute to fasle. Setting this attribute back to true triggers a thorough re-painting. |
| Spreadsheet. Selection |
Returns the selected object. You can use the typename function in VBA or VBScript to determine the object type. |
| Spreadsheet. titlebar |
You can access the title bar of a workbook to change the text and format of the title bar. |
| Spreadsheet. viewablerange |
Controls the actual visible part of the workbook. See the previous introduction to viewablerange and autofit to learn how this property works. |
Sorting and filtering Table 2-5 lists the attributes and methods that can be used to sort and filter data in the workbook component.
Table 2-5.
Attributes and methods with sorting and filtering functions.
| Attribute or Method |
Introduction |
| Range. Sort |
Sort the region based on the given column and sorting method. |
| Worksheet. autofilter |
This property returns an autofilter object, which can be used to set the details of the current filter. |
| Autofilter. Filters |
This attribute returns the filter set of the current automatic filter area. A filter object is applied to a column in the automatic filtering area. The subscript of the filter object corresponds to the subscript of the column in the corresponding area. |
| Autofilter. Apply |
This method applies a new automatic filter. After setting the filter condition, you must call this method to apply the filter. |
| Criteria. filterfunction |
This attribute controls whether the items that meet the filter criteria are included in the filter or excluded from the filter. If it is contained, the filter only contains items that meet the filtering condition set. If it is excluded, the filter contains all items that meet the filtering condition set. |
| Criteria. showall |
This attribute determines whether to display all data. When set to true, this attribute resets the filter to display all data. If it is set to false, no data is displayed without any filtering conditions. |
| Criteria. Add |
This method adds a new filter condition to the filter. |
| Range. autofilter |
This method enables the autofilter switch for a given area. Call this method first, and then use the worksheet. autofilter attribute (as described above) to access the filter and set the filter conditions. |
Protection If you want to protect a part of a workbook so that your users cannot modify the unit content or format, you need to use the attributes of the control protection function. Table 2-6 lists common protected attributes and provides a brief description of how to use each attribute. Note that the protection settings are applied to user interaction through the user interface and to operations executed in the code. Therefore, if you need to delete a row when a protection option is enabled, you must set the enabled attribute of the Protection object to false before performing the delete operation, set the enabled attribute back to true to return to the protected State.
Table 2-6.
Common attributes with protection functions.
| Attribute |
Description |
| Worksheet. Protection |
Returns a protection object that can be used to set various protection options to enable various global operations (such as inserting and deleting rows ). |
| Protection. Enabled |
Controls whether to enable protection in general. When you need to use the protection option or lock the cell, first set the option or lock the cell, and then set this attribute to true. When you need to perform operations in the code, you can set this attribute to false to temporarily disable protection. |
| Protection. allowinsertingcolumns, protection. allowinsertingrows, protection. allowdeletingcolumns, protection. allowdeletingrows |
You can insert or delete columns or rows in a workbook. For example, if allowinsertingrows is set to false, the workbook prohibits all commands used to insert a row, including the commands in the programming model. |
| Protection. allowsizingallcolumns, protection. allowsizingallrows |
You can or cannot adjust the size of a column or row. For example, if allowsizingallrows is set to false, the workbook does not allow you to adjust the row size or use code to do so. |
| Protection. allowsorting |
Allow or prohibit data sorting in workbooks. Setting allowsorting to false will prohibit users from sorting content in any region. |
| Protection. allowfiltering |
Allow or disable automatic filtering. Setting this attribute to false will disable automatic filtering. |
Withdrawal Table 2-7 lists the relevant attributes and methods used when you control the revocation mechanism of the workbook component.
Table 2-7.
Controls the attributes and methods related to the withdrawal mechanism.
| Attributes and Methods |
Description |
| Spreadsheet. beginundo |
One allows you to think of a series of operations as a method to undo operations. For example, if you call the beginundo method and then execute three different sorting actions or change many units, you can cancel all these operations. |
| Spreadsheet. endundo |
A method that marks the end of your logical undo unit. All operations performed between the beginundo method and the endundo method will be revoked by a single unit currently. |
| Spreadsheet. enableundo |
This attribute controls whether the Undo function is available. The default value is available. You may need to temporarily disable this feature to save memory or perform a large number of operations in the code. |
Useful events The top-level objects of workbooks publish a large number of events, which are more than any other controls in the owc library. Table 2-8 lists some key events that you may need to use when developing custom solutions around spreadsheet components. Almost all events in the workbook control pass a single parameter of the spreadsheeteventinfo type to the event handler. Spreadsheeteventinfo is a COM object. You can use it to obtain information about the status of the application when an event is triggered, including information about the selected status and the region affected, where is the mouse position, the key is pressed, and so on. This reflects how the event information in the Dom is processed. The most important reason for using an object as an event parameter is that it must support the event revocation function in Javascript. The parameters passed to the event in JavaScript are always passed values unless the parameters are object pointers. In other words, if the owc team designs the event as an additional returnvalue parameter, the script can undo the event by setting this parameter to true, in JavaScript, this will not work properly because of the parameter value passing method. However, if the script sets the returnvalue attribute of an object to true, the control that triggers the event will find it correctly. Therefore, if you need to cancel an event (most events starting with "before" can be undone), set the returnvalue attribute of the spreadsheeteventinfo object to false.
Table 2-8.
Useful events
| Event |
Description |
| Spreadsheet. Change |
This event is triggered as long as one or more units in the workbook are changed. You can use the range attribute of the spreadsheeteventinfo object to determine the region to be changed. |
| Startedit, endedit, canceledit |
Triggered when a unit is to be edited, is about to end editing, or is about to unedit. You can perform data verification in the endedit event handler and reject new input values by setting the returnvalue attribute of the spreadsheeteventinfo object to true. Use the editdata attribute of the spreadsheeteventinfo object to obtain the new value of the unit. For editing purposes, you can use another element in the startedit event to replace the displayed value. For example, you can use a TrueType font to display a special symbol instead of a text description. |
| Beforecommand, command |
Triggered before and after a command is executed, such as sorting, filtering, inserting or deleting rows or columns, displaying help, and cutting, copy or paste an action like this-when it is executed. In the msowcvba. CHM File or in your Object Browser, you can view the list of sheetcommandenum constants to learn all the commands that can be captured in these events. Similarly, to cancel the default behavior of an event, you can set the returnvalue attribute in the beforecommand event to false. For example, you may want to display your help page when you click the Help button on the workbook toolbar. |
Obtain version information Sometimes you need to check the version of the control in use to take advantage of the new features, or use the code to solve the problem in an earlier version. Most software programs release Sr versions between major versions. Therefore, you often need to check whether the version of your code is the version you expected. To help you do this, we have added the properties in Table 2-9 for each control in the owc library. You can use these attributes to determine the version of the control that your code is operating on and perform appropriate actions.
Table 2-9.
All Office Web Components have version information attributes.
| attributes |
description |
| majorversion |
an integer of the long type, indicating the main version number of the component. For Office 2000, this number is 9. |
| minorversion |
indicates the minor version number of a component. For Office2000, this number is 0. If any minor version is released before the next major version is released, this number will increase. Note that minorversion is a string value to handle version numbers such as ". We recommend that you compare the values with equal values instead of greater than or less. |
| buildnumber |
A compile value indicates the number of components compiled. The number of compilations increases with each compilation of the component DLL. This value of Office 2000 is not available when writing this book. It is also a numeric value of the character type. Therefore, it can handle the situation where a letter is added to the version number when a version is released. |
| version |
a numeric value of the entire version is returned. This attribute can be used when you need to display the version number, but when you need to determine whether a version is the version you need, use other attributes. |
