Translator's description: Welcome everyone to visit me
BLOG:
Http://daidaoke.donews.net/daidaoke/
My email: tangtaike@163.com
For reprint, please notify us in advance.
Chapter III Section 3 Programming Model Points
Now summary our introduction to the spreadsheet components, I will tell the various points of the (spreadsheet) control programming model so you can learn how to use this control, and how you need to write a script to achieve different features. This section is not a full guide for programming models - that will be another book. Here, you will introduce common properties and methods, each attribute and method comment with a short description. If you need more information for programming model, you can refer to online help in the msowcvba.chm file, which is located in the localization directory of the Office directory (for people in English language, generally Program files / Microsoft Office / Office / 1033 table of Contents).
If you are very familiar with Excel's programming model, you will find that it is very similar to the programming model of the spreadsheet component.
Use enumeration in the script
COM and OLE automation allow component definition enumeration, each enumeration is a specified set of constants, and the type declaration in similar programming languages. A parameter that enumerates acts or method can be used, which enables the environment such as VB and VC to display a statement containing a valid constant in an enumeration completion drop-down list (
Translator Note: Statement completion of the function refers to how the development environment will suggest how developers complete a row of code, so that developers do not need to remember all members of the object). OWC, like many other ActiveX controls, includes a series of predefined enumerations.
However, in a script environment such as a web page or an ASP page, there is no type of concept. Therefore, enumeration member names cannot be used in existing scripting languages because these languages cannot know what a particular constant (such as sshalignleft) corresponds. This means that your VBScript or ECMA script code will be confusing because of the mysterious numbers that cannot be described.
Translator Note: It is difficult to read a large number of digital constants in the program.
To try to solve this problem, all OWC components have a top-level property called Constants, which can use this attribute to access various enumeration members of the component in the scripting language. For example, if you need to use SSHALIGNLEFT constants in the VBScript code to make the content of a unit, you can write code like this:
MyRange.halignment = spreadsheet1.constants.sshalignLeft
In order to use an enumeration, you can reference it as an attribute of the Constants object, and the object will return the correct value of the enumerated member.
Please note that if you plan to use a constant object in your function, set a variable equal to the ConStants object, which avoids unnecessary typing, and can improve performance. For example, you can write code like this:
Set c = spreadsheet1.constants
MyRange.halignment = C.sshalignRight
MyRange.valignment = C.ssValignBottom
The Constants object is only meaningful in scripting languages without enumeration concepts. If you are writing code in VB, VBA or C , please use the enumeration directly as usual, and you don't have to pay attention to the Constants object.
Enter the data into components
I have already described multiple skills that enter the data into the component. I will now describe these techniques in the programming model. Table 2-1 lists the properties and methods related to loading data, all of these attributes and methods are published through the interface of the spreadsheet object.
table 2-1.
Load data related properties and methods.
Attribute or method
description
Spreadsheet.DataType string type properties, telling the spreadsheet components When multiple properties of loading data are set, which property should be loaded, you assign the value of this property is the one you should use to load data. The name of the property, for example, set this property to HTMLURL, which loads the components to load data obtained from the URL in the property HTMLURL.
Spreadsheet.htmldata
The property of string types can be used to get or set the electronic table content of the HTML table format. This format also contains additional properties and XML tags, which are used to retain information (eg, a unit formula) required to redevelop the spreadsheet model, but they do not belong to the table format of HTML3.2. You can set this property to a string containing an HTML table, or you get the full content of the spreadsheet by reading this property value when you need to save.
Spreadsheet.htmlurl
The property of the string type contains a URL that can be loaded from this URL. This URL must return an HTML document that contains the table. Use Excel 2000 to save to an HTML format using this property to be loaded, this property can also be an ASP page or CGI program that generates a table from the database.
Spreadsheet.csvdata
The properties of string types, and attribute HTMLData, but it accepts and returns the format of the data is CSV. This attribute is useful when loading data from the old system that cannot publish the HTML format.
Spreadsheet.csvurl
The properties of string types, and attribute HTMLURL, but it wants to be csv format from the data format obtained from the URL. Like the HTMLURL properties, this URL can be an ASP page or CGI program that generates a table in real time from the database.
Spreadsheet.LoadText
A method of loading a separate text file into a spreadsheet. Unlike the CSV format, this text file can use any field separator. The Range object can also use the loadText method and its brother approach Parstext to load text into a specified area of the spreadsheet. The LoadText method can point to a file in your local file system or point to a URL.
applied area
The most commonly used programming interface in the spreadsheet component is the RANGE object. Many methods returned to the RANGE object that is used to modify, format, sort, and set automatic filtering, format, sort, and set automatic filtering, all of them need to be modified, formatted, sorted, and setup automatic filtering. Table 2-2 shows the properties and methods of the RANGE object that should be understood when you establish a solution solution.
Table 2-2.
The main attributes and methods of the Range object.
Attribute or method
Introduction
Spreadsheet.Range
This method accepts a zone reference (eg, A1: B2 or A: B), returns a RANGE object. Because a region can have only one unit, you can also pass a single unit reference (eg, A1). The method can also accept two different unit references, and returns a region containing the two units.
Range.address
This attribute returns an 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 collection of multiple units, that is, you can use the for Each loop to access each unit in it. This attribute can also be accessed in the form of a two-dimensional array. For example, MyRange.cells (1, 3) .Value will return the value of the row 1 and column 3 in the area. The spreadsheets and worksheet objects also have a Cells property, so you can use it instead of the previously mentioned RANGE method to access the specified unit.
Range.column, Range.Row
These properties point out the first line and the first column in the area. When you own iteration in the rows and columns of a region, this property is useful if you need to know which row is currently located. Range.columns, Range.Rows
Although it is very similar to the first two attributes on the name, this pair attribute returns a collection of rows or columns included in the RANGE object. Range.columns.count and Range.Rows.count can tell you the number of banks and columns in the current region.
Range.htmldata
This property is similar to the spreadsheet.htmldata attribute, but the property of the Range object is read-only. Use it to quickly get the expression of the HTML table of data in a given area.
Range.Value
This attribute obtains or sets the value of the area. Although the help file says Range.Value returns a two-dimensional variable array when it is composed of multiple units in the area, but this is not implemented in the current version. However, Range.Value can accept a two-dimensional array variable to set the data input area when you need to use the text value to set one or more unit values, or when you need to obtain a format of a unit, you can use it. This property is. (
Translator Note: There is no formatted value that does not have formatted values. )
Range.formula
This attribute reads and writes a formula string of a unit. This property can be used when you need to obtain or set one or more units in the region, remember to use the equal sign (=) in the beginning of the formula.
Range.text
Range.Text Properties Returns the formatted version of the Range.Value property. This property is useful when you need to display a formatted value in a message box or another user interface element, which is the value you are incorporated when you add the filter condition of the Autofilter object. (
Translator Note: That is, filtering is filtered according to the surface value of a cell)
format
After loading the data into the spreadsheet, you may need to control formatted by programming. Each unit has its own font, alignment, border, color, and digital format properties, all of which can be set using the properties in Table 2-3.
Table 2-3.
Used to format the properties.
Attributes
description
Range.NumberFormat
The properties of the string type, the format of the digital in the control unit. You can reference the existing format names to use numerous built-in formats (such as Currency). You can also create a custom format (for example, #, ###, making 1000 as 1,000).
Range.font
Returns a common Font object that many components can use. Range.Font allows multiple properties of the Font object, such as Name, Size, Bold, Italic, Color, and underline. Note that if needed, you can use the color name of the IE in the font.color property.
Range.halignment, Range.Valignment
These two attribute control areas are aligned in the horizontal levels of each unit and the vertical direction. Each property defines an enumeration value of each alignment that can be used.
Range.Borders
This property returns a Borders object, which can set each of the features of each unit border, such as the lines, styles and colors of the frame line.
Component level look and behavior
Many properties and methods will affect the entire spreadsheet component. Table 2-4 lists the most interesting properties and methods in customized solutions.
Table 2-4.
The main attributes affecting the entire spreadsheet.
Property
Description
Spreadsheet.AllowPropertyToolbox
Controls whether to display the property tank (
Translator Note: Refers to the Command and Options window). If this property is set to false, the property toolbox on the toolbar and the menu item on the right-click menu will be disabled. You can use this property to use this property from the default formatting user interface and provide your own formatting interface.
Spreadsheet.autofit determines if the control is in the Auto Adaptation mode. Check out how this feature works about the details about Autofit in the previous chapter.
Spreadsheet.dirty
When there is any modification in the control, any unit is changed, this property is set to True to inform you this change. Usually use the dirty attribute to determine if saving is required. Note that this is a READ / WRITE attribute, so it can make the spreadsheet "clean" by resetting it. You Commonly Use the dirty property to determine WHether You Need to save the contents in some way.
Spreadsheet.displayColheaders, Spreadsheet.displayRowheaders, SPLAYROWHEADERS
Controls whether a line and list are displayed. The default value of these two attributes is TRUE. Typically, you can set these two properties to false when you need to use your own code to fully control the spreadsheet interface.
Spreadsheet.displayGridLines
Controls if the grid line is displayed. The default is to display, if in the custom solution, you want to use the border in a set of unit separation lines, often close this property.
Spreadsheet.displayPropertyToolbox
Controls if the property toolbox is displayed. The property is displayed to true, and it is hidden when it is set to FALSE.
Spreadsheet.displayTitlebar
Controls whether the title bar is displayed. The default is to display the title bar. You can use the titlebar attributes described below to modify the contents and formats on the title bar.
Spreadsheet.displaytoolbar
Controls if the toolbar is displayed. The default is displayed.
Spreadsheet.enableautocalculate
How to control the spreadsheet model. If this property is set to false, the spreadsheet model does not automatically pay; the Calculate method of the worksheet object must be called to see the new results after changing the input. This property is useful when planning to modify a series of inputs and hope that only the model is only raised after all modifications are completed. This attribute defaults to the true - model automatically rejuvenation when the spreadsheet is modified.
Spreadsheet.screenupdating
By default, the display of the spreadsheet on the screen always reflects the latest data, but if a large number of operations are planned, it is not desire to flash because of the update after each operation. This property can be set to FASLE. Return this property back to TRUE to trigger a thorough redraw action.
Spreadsheet.seeet.seeet
Returns the currently selected object. You can use the type of TypenAme functions in VBA or VBScript to determine the type of this object.
Spreadsheet.titlebar
Enable you to access the title bar of the spreadsheet, you can change the text and format of the title bar.
Spreadsheet.ViewableRange
Control the actual visible part of the spreadsheet. Please see the introduction to ViewableRange and Autofit in front to understand how this property works.
Sort and filter
Table 2-5 lists the properties and methods that can be used when sorting and filtering data in spreadsheet components.
Table 2-5.
It has the properties and methods of sorting and filtering.
Attribute or method
Introduction
Range.sort
Sort the region based on a given column and sorting method.
Worksheet.autofilter
This property returns an AutoFilter object that can be used to set the details of the current filter.
AutoFilter.Filters
This property returns a collection of filter currents that are automatically filtered. A filter object is applied to a column in the automatic filter area, the subscript of the filter object, and the subscript in the corresponding area.
AutoFilter.Apply
This method applies a new automatic filtering. After setting the filter condition, you must call this method to actually apply the filter.
Criteria.FILTERFunction This property controls the item that matches the filter condition is included in the filter or is excluded from the filter. If it is included, the filter contains only items that meet the filter criteria, and if it is excluded, the filter contains all items other than the items that match the filter criteria.
Criteria.showll
This attribute determines whether all data is displayed. When set to TRUE, the property resets the filter to display all the data. When set to FALSE, if there is no filtering condition, no data will be displayed.
Criteria.add
This method adds a new filtration condition to the filter.
Range.autofilter
The method opens the AutoFilter switch for a given area. This method first calls and uses the Worksheet.autofilter property (described above) to access the filter and set the filter condition.
Protect
If you need some part of the spreadsheet, your user cannot modify the unit content or change the cell format, you need to use the properties of the control protection function. Table 2-6 lists the commonly used properties with protective functions and gives a short description of each attribute.
Note that the protection settings are applied to the user interaction through the user interface, and is also applied to the operations executed in the code. So if you need to delete a row in the case where a protection option is enabled, you must set the enabled property of the Protection object to false before performing the delete operation, and set the enabled attribute back to True to return to protected. status.
Table 2-6.
Commonly used properties with protection functions.
Attributes
description
Worksheet.Protection
Returns a protection object, you can use it to set various protection options to enable a variety of global operations (for example: insert and delete rows).
Protection.enabled
Control is generally enabled. Set the option or lock the cell when you need to use the protection option or lock cell, and set this property to true. When you need to perform actions in your code, you can set this property to false to temporarily prohibit the protection function.
Protection.AllowinsertingColumns, Protection.AllowinsertingRows, Protection.AllowDeletingColumns, Protection.AllowDeletingRows
Allow or prohibit insertion, delete columns or rows in the spreadsheet. For example, if AllowInsertingRows is set to false, the spreadsheet disables all commands to insert a row, including the commands in the programming model.
Protection.AllowsizingAllColumns, Protection.AllowsizingAllRows
Allow or disable the size of the column or row. For example, if AllowsizingAllRows is set to false, the spreadsheet will not allow the user to adjust the size of the row, nor will you do this through the code.
Protection.Allowsorting
Allow or prohibit sorting data in the spreadsheet. Setting AllowSorting to false disables users from sorting content in any area.
Protection.allowfiltering
Allow or prohibit the use of automatic screening. Setting this property to false will prohibit the user from enable automatic filtering.
Withdraw
Table 2-7 lists the relevant properties and methods that will be used when you control the revocation mechanism of the spreadsheet component.
Table 2-7.
Control the attributes and methods related to the revocation mechanism.
Properties and methods
description
Spreadsheet.Beginundo
One allows you to view a range of operations as a way of undo operation. For example, if you call the Beginundo method, then perform three different sorting actions, or change a lot of units, you can undo all of these operations.
Spreadsheet.endundo A method that tagged your logic undo unit. All operations performed between the Beginundo method and the calling endundo method will be revoked by the current a single unit.
Spreadsheet.enableundo
This property controls whether the undo function is available. The default is available. You may need to temporarily disable this feature to save memory or perform a lot of operation in your code.
Useful event
The top object of the spreadsheet publishes a large number of events, which is much more than any other controls in the OWC library. Table 2-8 lists some key events when developing custom solutions around the spreadsheet components, you can use them.
Events in almost all spreadsheet controls pass a single parameter for the event handler to SpreadsheetEventInfo. SpreadsheetEventInfo is a COM object that you can use to get the various information of the application status when the event is triggered, including what is selected, that area has been affected, where is the location of the mouse, and that button is pressed, wait, etc. . This reflects how to process event information in the DOM.
Using an object as the most important reason for event parameters, it is because it is necessary to support the functionality of undo incident in JavaScript. The parameters passed to the event in JavaScript are always values, unless the parameters are the pointers of the object. In other words, if the OWC team designs an event as an additional ReturnValue parameter, the script can withdraw the event by setting this parameter to True, then in JavaScript, this is not working properly because the value of the parameters is not working properly in JavaScript. However, if the script sets an object's ReturnValue property to True, the control that triggers the event will find it correctly. Therefore, if you need to undo an event (most of the event starting with "before" can be revoked), set the ReturnValue property of the SpreadsheetEventInfo object to false.
Table 2-8.
Useful event
event
description
Spreadsheet.change
This event will be triggered as long as one or more units in the spreadsheet are changed. You can use the RANGE property of the SpreadsheetEventInfo object to determine the changed area.
Startedit, Endedit, Canceledit
When a unit will be edited, it is correct to end editing, or trigger when you want to undo. You can perform a data check in the EndEdit event handler, and refuse the new value by setting the ReturnValue property of the SpreadsheetEventInfo object to True. The new value of the unit can be obtained using the EditData property of the SpreadsheetEventInfo object. In order to edit, another element can be used in the Startedit event instead of the displayed value. For example, a TrueType font is used to display a special symbol instead of using a description of the text.
BeforeCommand, Command
Trigger before and after executing a command is required -, for example, sorting; filtering; insert or delete rows or columns; display help; and cut, copy or paste such action - is executed. In the msowcvba.chm file or view the SheetComMandenum constant in your object browser, you can know all commands that can be captured in these events. Similarly, if you need to undo a default behavior of an event, you can set the returnValue property in the BeforeCommand event to false. For example, you may want to display your own help page when the user clicks on the help button on the Spreadsheet toolbar.
Get version information
Sometimes you need to view the version of the control you are using so that you can use the advantages of new features, or use the changed code to solve an earlier version. Most of the software programs will publish SR versions between major versions, so you often need to check if your code is interacting. Is the version you expect. To help you complete these work, we add the properties in Table 2-9 for each of the controls in the OWC library. You can use these properties to determine the version of the controls that your code is operating and perform the appropriate action.
Table 2-9.
All Office Web components have version information properties.
Attributes
description
Majorversion
A long-type integer value indicating the main version of the component. This figure is 9 for Office 2000 versions.
MinorVersion
A character type value indicates the second version number of the component. For Office2000, this number is 0. If you release any small version before the next host version is released, this number will increase. Note that in order to handle the case of "A" version number, MinorVersion is a string value. Preferably, the value is equal to the comparison operation, not a comparison operation greater than or less.
BuildNumber
A character type value indicates the number of compilation of the component. The number of compilation will increase with each compilation of the component DLL. This value of Office 2000 is not available when writing this book. This is also a value of a character, so it can handle a letter from the version number in the release of a secondary version.
Version
A character type value returns the entire version value. This property can be used when you need to display the version number, but use other properties when you need to determine if a version is the version you need.
Appendix (English):
Key Elements of The Programming Model
To conclude our introduction to the Spreadsheet component, I'll cover the key elements of the control's programming model so that you'll know how to get the control working and where to go when you want to write script for different functionality. This section is not a full reference to the programming model-that would be a book unto itself. Instead, the properties and methods you'll commonly use are presented here, along with a brief description of each. For more information on any part of the programming model Refer to the online help in the msowcvba.chm file, Which you can find in the local folder under the office folder (General Program Files / Microsoft Office / Office / 1033 for english-speaking people).
If you are at all familiar with the excel programming model
COM and Ole Automation Enable Components to Define
enumerations, each of which is a named set of constant values that acts like a type declaration in a programming language. A property or method argument can be typed as an enumeration, which causes environments such as Visual Basic and Microsoft Visual C to display the statement COMpletion Drop-Down List Containing The Valid Constants for That Enumeration. The Office Web Components, Like Many Other ActiveX Controls, Have a Predefined Set of Enumements.
However, in scripting environments such as a web page or an ASP page, no notion of types exists. Therefore, no ability to use enumeration member names in script languages exists since these languages have no way of knowing what a particular constant, such as ssHAlignLeft , Evaluates to. This Means your VBScript or Ecma Script Code Can Get Littered with Magical Numbers That Are Not Self-Describing.
To alleviate this problem, all the Office Web Components have a top-level property called Constants that can be used in scripting languages to access their various enumeration members. For example, if you want to use ssHAlignLeft in VBScript code to align a cell's contents to The Left, You Can Write Code Like this:
MyRange.halignment = spreadsheet1.constants.sshalignLeft
Working with Ranges
The most commonly used programming interface in the Spreadsheet component is that of the Range object. Range objects are returned from many methods and are used whenever you want to modify the contents, formatting, sort order, or filter settings of a range of cells. Table 2-2 Shows The Properties and Methods of The Range Object That You Should Know About When Building Solutions with The Spreadsheet Component.table 2-2.
PRINCIPAL
Range
Object Properties and Methods.
Property Or Method
Description
Spreadsheet.Range
This Method Returns A Range Object Given A Range Reference (Such AS A1: B2 or A: B). Because a Range Can Be Just One Cell, You Can Also Pass A Single Cell Reference (Such as A1). The Range Method Can Also Take Two Different Cell References and Return A Bounding Range.
Range.address
This Property Returns The Address of The Range (for Example, A1: B2).
Range.cells
I was highly confused by this property when I first saw it because it's typed to return another Range object. However, you can use this property as a collection of cells, meaning that you can access the individual cells using a For Each loop. The property can also be accessed as a two-dimensional array. for example, MyRange.Cells (1,3) .Value will return the value from row 1, column 3 in the range. There is also a Cells property for the Spreadsheet and Worksheet objects SO You CAN Use it instead of the Range Method (Described Earlier) To Access Specific Cells.
Range.column, Range.Row
................................... ..
Range.columns, Range.Rows
Although strikingly similar in name to the previous two properties, this duo returns a collection of columns or rows contained in the Range object. Range.Columns.Count and Range.Rows.Count tell you the number of columns and rows in the current range. Range.htmldata
.
Range.Value
This property gets or sets a variant value for the range. Although the help file states that Range.Value returns a two-dimensional array of variants if the range constitutes more than one cell, it actually is not implemented in this version. However, Range.Value can accept a two-dimensional array of variants for putting data into the range. Use this property when you want to set the cell or cells to a literal value or when you want to get a cell's current unformatted value.
Range.formula
This Property is buy to read or write the formula string for a cell. Use it for a cell or cells in a ing, and repeember to use the equals sign (=) at the beginning Formula.
Range.text
The Range.Text property returns the formatted version of the Range.Value property. It is useful when you need to present the formatted value in a message box or another user interface element, and it's the value you pass when adding AutoFilter criteria.
Formatting
Once you have loaded data into the spreadsheet, you might want to apply formatting programmatically. Each cell can have its own font, alignment, border, color, and number formatting, and all these aspects are set using the properties shown in Table 2-3 .
Table 2-3.
Formatting Properties.property
Description
Range.NumberFormat
A string-based property that controls the formatting used for a cell's numeric value. A number of built-in formats that you can use by name exist (such as Currency). You can also construct your own format definitions (for example, #, ###, Which Makes 1000 APPEAR AS 1,000).
Range.font
A property that returns the common Font object used by many of the components. Range.Font lets you set various properties of the Font object such as Name, Size, Bold, Italic, Color, and Underline. Note that you can use the Internet Explorer Color Names with the font.color property if you want.
Range.halignment, Range.Valignment
TWO Properties That Control The Horizontal and Vertical Text Alignment With The Range's Cells. Enumements That Contain The Possible Alignment Values Are Defined for Each of these Properties.
Range.Borders
A Property Returning The Borders Object That Lets You Set The Various Aspects of Each Cell Border, Such As Line Weight, Line Style, and Line Color.
Component-Level Appearance and Behavior
A Number of Properties and Methods Affect The Entire Spreadsheet Component. Table 2-4 Shows The Most Interesting Ons for Custom Solutions.
Table 2-4.
Principal Properties That Affect The Spreadsheet as a..
Property
Description
Spreadsheet.AllowPropertyToolbox
Controls whether the Property Toolbox can be shown. If AllowPropertyToolbox is set to False, the Property Toolbox toolbar icon and context menu command are disabled. You use this property any time you want to disable the default formatting user interface and supply your own.
Spreadsheet.autofit
DETERMINES WHETHER THE Control IS in Autofit Mode. See The Section Earlier on Autofit for more details on how this feature works.spreadsheet.dirty
Tells you if anything has changed in the control. If any cell has been modified, this property returns True. You commonly use the Dirty property to determine whether you need to save the contents in some way. Note that this is a read / write property SO you can also reset it to make the spreadsheet "clean" again.
Spreadsheet.displayColheaders, Spreadsheet.DisplayRowheaders
Control whether the column and row headers are displayed. These two properties are True by default. Normally, you set them to False if you want to have total control over the spreadsheet surface from your code.
Spreadsheet.displayGridLines
Controls WHETHER GRIDLINES ARE DISPLAYED. By Default Thee, And It's Common To Turn Therm Off For Custom Solutions What Use Borders in Specific Places Where You Want Cell Separator Lines.
Spreadsheet.displayPropertyToolbox
Controls WHether The Property Toolbox Is Displayed. Set this to True To Display It or false to hide it.
Spreadsheet.displayTitlebar
.. ......................
Spreadsheet.displaytoolbar
......................
Spreadsheet.enableautocalculate
. Controls how the spreadsheet model recalculates If this property is set to False, the spreadsheet model will not automatically recalculate;. You must call the Calculate method of the Worksheet object to see new results for changed inputs This property can be useful if you plan to make many changes to a set of inputs and want to recalculate the model only when you're done with all the changes. By default, this property is True-models will automatically recalculate when changed.Spreadsheet.ScreenUpdating
By default, the screen display of the spreadsheet always reflects the most current data, but you can set this property to False if you plan to perform a number of operations and do not want the spreadsheet to flicker after each one. Setting this property back To True Causes a full.
Spreadsheet.seeet.seeet
Returns the currently selected object. You can use the TypeName Function in VBA or vbscript to determine what type ip.
Spreadsheet.titlebar
Gives You Access To The Spreadsheet Control's Title Bar, Which You Chan Change The Text And Formatting of.
Spreadsheet.ViewableRange
.
Sorting and filtering
Table 2-5 Lists The Properties and Methods you will use.
Table 2-5.
Properties and methods for sorting and filtering.
Property Or Method
Description
Range.sort
This Method Sorts The Range Given a Column and Sort Direction.
Worksheet.autofilter
.
AutoFilter.Filters
This property returns the Filters collection for the current AutoFilter range. One Filter object applies to each column in the AutoFilter range, and the index of the Filter object matches the column index in the range.AutoFilter.Apply
., You mustage the filter.
Criteria.filterFunction
This property controls whether the criteria is included in the filter or excluded from it. Include filters include exactly the items in the criteria set, while exclude filters exclude the items in the criteria set but include everything else.
Criteria.showll
This Property DETERMINES WHENER All Data Will Be Shown. When Set To True, The Property Resets A Filter To Show All Data. When Set To False, Assuming There No Filter Criteria, IT Shows NO Data.
Criteria.add
This Method is used to add new criteria to a filter.
Range.autofilter
THIS Method Is Used to Turn Autofilter ON for a Given Range. Call this method first, and then used the worksheet.autofilter Property (Described Above) To access the filters and set up the criteria.
PROTECTION
If you want to protect part of the spreadsheet so that your users can not modify cell contents or change cell formatting, you need to work with the properties that control protection. Table 2-6 lists the common protection properties and gives a brief description of how each IS used.
Note that the protection settings apply to user interactions through the user interface and to operations performed in code. If you want to delete rows while a protection option is enabled, you must set the Enabled property of the Protection object to False before performing the operation, And the set it back to true to return to the protected state.table 2-6.
Common Protection Properties.
Property
Description
Worksheet.Protection
ROWS INSERTING OR DELETING ROWS.
Protection.enabled
Controls whether protection in general is enabled. To use the protection options or lock cells, first set the option or lock the cells and then set this property to True. You can set this property to False to temporarily disable protection while you perform operations in code .
Protection.AllowinsertingColumns, Protection.AllowinsertingRows, Protection.AllowDeletingColumns, Protection.AllowDeletingRows
Enable or disable the ability to insert or delete columns or rows in the spreadsheet. For example, if AllowInsertingRows is set to False, the spreadsheet will disable all commands that can be used to insert a row, including those in the programming model.
Protection.AllowsizingAllColumns, Protection.AllowsizingAllRows
Enable or disable the ability to resize columns or rows. For example, if AllowSizingAllRows is set to False, the spreadsheet will not allow the user to resize the rows, nor will it let you do so through code.
Protection.Allowsorting
................... ..
Protection.allowfiltering
Allows or prohibits the use of the autofilter feature. Set this property to false to prohibit users from enabling the autofilter feature.undo
Table 2-7 Lists the Relevant Properties and methods you will use when controling the undo manchanism of the spreadsheet component.
Table 2-7.
Properties and Methods for Controlling Undo.
Property Or Method
Description
Spreadsheet.Beginundo
......................... ..
Spreadsheet.endundo
A Method That Marks The End Of Your Logical Und. All Operations Performed Between The Beginundo Call and The endundo call will be undone as a single unit.
Spreadsheet.enableundo
A Property That Controls WHETHER THE UNEO Feature Is Available. By Default, IT IS. You Might Want To Temporarily Disable this Feature To Save Memory or Perform A Number of Operations in Code.
USEful Events
A number of events are exposed from the top-level Spreadsheet object, more than from any other control in the OWC library. Table 2-8 lists several of the key events you likely will want to use when developing custom solutions around the Spreadsheet component.
Nearly all the events in the Spreadsheet control pass a single parameter of type SpreadsheetEventInfo to the event handler. SpreadsheetEventInfo is a COM object that you can use to retrieve all kinds of information about the state of the application when the event was fired, including what was SELECTED, WHAT RANGE WAS Affected, WHERE The Mouse WAS, WHAT WERE PRESSED, AND SO ON. THIS MIRRORS The Treatment of Event Information in The Dom.
The biggest reason to use an object as the parameter is to support cancelable events in JavaScript. Parameters passed to an event in JavaScript are always passed by value unless they are object pointers. In other words, if the OWC team had designed the events with a ReturnValueParameter That The Script set to True To Cancel The Event, IT WOULDN '安 j j p i i' '' h p
property to True, the control raising the event will see it. So if you want to cancel an event (most of the events whose names begin with "Before" can be canceled), set the ReturnValue property of the SpreadsheetEventInfo object to False.
Table 2-8.
Useful events.
Event
Description
Spreadsheet.change
Fires Any Time a Change Is Made to a Cell or Cells in The Spreadsheet. Use The Range Property of The SpreadshetementInfo Object To Determine The Range Affected.
Startedit, Endedit, Canceledit
Raised whenever a cell is about to be edited, was just edited, or just had its edit canceled. You can perform data validation in the EndEdit event and set the ReturnValue property of the SpreadsheetEventInfo object to True to deny the new value. Use the EditData property of the SpreadsheetEventInfo object to get the new value for the cell. Use the StartEdit event to replace a displayed value with another element for editing purposes, such as using a TrueType font for displaying a special symbol instead of a text description.
BeforeCommand, Command
Raised just before and after a command-an action such as sorting; filtering; inserting or deleting rows or columns; showing help; and cutting, copying, or pasting-is processed See the list of SheetCommandEnum constants in the Msowcvba.chm file or. in your object browser for all the possible commands you can catch using these events. Again, set the ReturnValue property to False in the BeforeCommand event if you want to cancel the default behavior for an event. for example, you might want to show your own Help Page When The User Click The Help Button On The Spreadsheet Toolbar.RetrieVing Version Information
Sometimes you need to find out the version of the control you're working with so that you can either take advantage of new features or use workaround code to solve problems in an older version. Most software programs have service releases between their major version releases, So you offten ued to verify what the version your code is talking to is indeed the version you expect.
To help you do so, we added the properties listed in Table 2-9 to every control in the Office Web Components library. You can use them to determine the version of the control you're coding against and take the appropriate action.
Table 2-9.
Version Information Properties for All Office Web Components.
Property
Description
Majorversion
A long integer value That INDICES The MAJOR VERSION NUMBER of The Component. For the office 2000 release, this number is 9.
MinorVersion
A string-based value that indicates the minor version number of the component. For the Office 2000 release, this number is 0 and will be incremented if any minor releases occur before the next major version release. Note that MinorVersion is a string value in case An "a" Release Occurs. It's Best to Perform An Equality Comparison On This Value Rather Than Greater-Than Or Less-Than Comparison.BuildNumber
A string-based value that indicates the build number of the component. The build number is incremented with every build of the component DLL, and the value for the Office 2000 release was not yet available at the time of this writing. Again, this IS A STRING VALUE, SO IT CAN HANDLE CASES IN WHICH A Letter Is Added to a Version Number In The Event of a Minor Release.
Version
A string-based value That Returns the entire version name. Use the version of the................. ..
