Translator's description: Welcome to my blog: http://daidaoke.donews.net/daidaoke/
The mistake in the translation is not to point out.
My email: tangtaike@163.com
For reprint, please notify us in advance.
Chapter II spreadsheet components
This chapter will discuss the function and programming mode of the Microsoft Office spreadsheet components. Because this book focuses on building practical solutions, I will show more interesting materials for individual component characteristics and illustrate. At the second part of this book, you will see many usage.
This chapter will show you what you can do, what you can't do, recommend you to use your own code to add a function of adding functions to the component, and show you the key to the use of spreadsheet component programming mode, this is your learning electronics The starting point of the table component.
Basic function of spreadsheet components
Before we know the details of the spreadsheet components, let's take a look at its basic functions, which is supported and saved in the data.
Recombine engine
The bail engine is the core of the spreadsheet control - it makes the component no longer just a grid control. Because spreadsheet components are developed by developers developing the Microsoft Excel development revisor, it almost supports all functions in Excel2000, including functions in most data analysis packages (ATP).
Note:
If you are interested in the above, please note that the following functions are not supported by the spreadsheet components: ASC, Call, Datedif, Findb, Frequency, Getpivotdata, Growth, Info, ISPMT, JIS, LEFTB, LINB, LINEST, LOGEST, MDeterm , MIDB, Minverse, Mmult, Phonetic, Register.id, Replaceb, Rightb, Searchb, Sql.Request, Transpose, Trend, and Yen. Also, all functions ending with "B" will operate in byte levels, rather than like a character level as in the double-byte character set system.
Please refer to the part of this chapter on the part of the attribute binding and plug-in function to learn how to use VBA and VBScript to implement these functions. Also, there are two forms in Excel in the Excel, one using an array as a parameter, another use vector (one-dimensional queue) as a parameter. The spreadsheet control supports vector parameters, but does not support array parameters.
Any spreadsheet component including formulas will naturally require support for the support of the random engine - for example, a solution that allows users to view product balance balance tables, changing assumptions, and view resume results, will need An engine. The recombination function is the backbone function of spreadsheet analysis, which is the core function of the spreadsheet product. The bail engine can also use some advanced features that will be discussed later in this chapter, such as attribute binding and plug-in functions.
When using spreadsheet components (ie, memory object form), the retraction engine is used as a user interface. The recalculation engine can perform complex calculations that are easy to express in any spreadsheet model. It is also possible to rehabilitate on the server and send the display result to the web browser or send it to the email information. Many calculations work, if you use scripts or C, it will be very cumbersome, but it will be easy in the spreadsheet model.
For example, banks or lending agencies may develop a spreadsheet model for assessing specific types of loan risks. Because lending insurance operators are often not programmers, let their loan assessment functions running on the server may be difficult - just like the programmers who do not understand the complex calculation formulas used by the borrowing insurance industry to develop. However, if the rabbed engine of the spreadsheet component can load the electronic table model released by the insurance owner, change the input value, and output the new risk assessment result.
The spreadsheet component supports a worksheet that maximum 655, 36 lines, 702 columns (from the A column to zz columns), but each component instance only supports a worksheet. This is the same as the number of rows supported by Excel2000, but almost 3 times the number of columns supported by Excel2000. (Excel only supports 256 columns) Note:
If you want to load all 655, 36 rows and 702 columns, you will wait longer. Because the spreadsheet control is loaded from the data from the HTML format, the load operation is of course slower than the loading binary data (such as Excel loading XLS file). Although it is generally quite fast when loading hundreds of records, the spreadsheet components cannot quickly load the number of large models.
The spreadsheet control simultaneously supports absolute unit references in the formula ($ A $ 1) and relative cell references (A1), just like Excel, when you move, insert, or delete rows or columns, it automatically adjusts these references. The use of absolute and relative references is quite useful when copying the units containing the formula from a region to another. For example, in any way, which is included in this formula, you should use an absolute unit reference when the reference must remain unchanged. And if the reference must be relatively relative to the current rows and columns, relatively reference is required. In other words, after the copy, $ A $ 1 will also be $ A $ 1, and A1 will turn to the current ranks of copies.
Comment
The spreadsheet component does not support the old R
1C
1 Reference style. It does not support English formulas, English formulas are trying to create formulas based on named areas, however it is just a perfect goal, actually rarely works like it.
Unfortunately, this version of Office does not support naming areas in the version of Office. When Excel releases an electronic table model with interactive power (in other words, release a page containing the spreadsheet control), it automatically converts the named area reference to an absolute area reference. This conversion occurs when you copy sticky units from Excel to the spreadsheet control. If you want to use a named area in the encoding of the spreadsheet component, consider using the Dictionary object (scripting.dictionary), because this object can be easily accessed by name. (Dictionary object is located in the Microsoft Scripting Runtime library)
For example, if you need to define a named area for a set of data in the spreadsheet, then use this area in the script to complete a sort, you can use the following code:
SET DICT = CreateObject ("scripting.dictionary") Dict.Add "MyName", "A1: F20" set rng = spreadsheet1.range (DICT ("MyName"))
Dictionary object is an associated array. You can add the key / value to it, and then pass the key to efficiently re-values. With this object, you can easily track the naming area. When you pass a reference to the spreadsheet, you can use the method in the last row code above to re-get a truly reference corresponding to a given name.
Electronic Form Component User Interface
The spreadsheet component user interface is located on the rabbing engine. Of course, it is similar to the user interface of Excel, but in order to support and interact with the existing spreadsheet model, it makes special design. Many of Excel enables users to easily create a new spreadsheet, and there is no existence in the spreadsheet control; (however, it is enough for functions that create new spreadsheets for your solution). Also, you can copy and paste from Excel to the spreadsheet components - this means you can do most of the creation work in Excel. Table 2-1 shows an example of a basic spreadsheet component. Crazy spreadsheet reference
We can do one of the spreaders of spreadsheets, Andrew Milton, show me some other Excel support and the type references that the spreadsheet components are not supported. But most of these reference methods have never seen, even don't know that they can do this in Excel. Try the steps below:
1. Enter 1, 2, 3, 4, 5 in each unit of A1: A5.
2, then, the value 2 is input in unit B1.
3, finally, in the unit C1, the formula is input: = SUM (A1: Choose (B1, A1, A2, A3, A4, A5)). The result is 3.
4, now, the value of the unit B1 is now 3. The formula in the unit C1 will be recalculated, and the result is programmed 6.
Function Choose's behavior is similar to the Select Case statement in VBA, which can be used to dynamically define the end of the area at the end of the area. You can also use Indirect functions to implement dynamically defined areas, but the spreadsheet components do not support this dynamic area definition.
The intersection between the regions is another interesting creation. In the final example, if you are using the spreadsheet in unit D1, "= A1: A
5 a
1: B1 "will be obtained 1. Such references make Excel to calculate the intersection of the two areas, the result is a reference to the A1. The spreadsheet component allows you to enter such formula, but convert the formula to" = A1 ", and Excel will keep your originally entered formula.
Figure 2-1 A foundation spreadsheet component
The spreadsheet component user interface contains many features that are considered to be a granular spreadsheet. I am more willing to let you discover most of the features when you use the control, although the following list is not complete, but you can understand the user interface characteristics of the user interface support for the spreadsheet control. high.
Change line, column size
Transparent selection (Translator: When the user selects a cell, it will not hide the format)
Auxiliary selection when entering a formula
Multi-level revocation
Insert and remove ranks
Cut, copy and paste
Based on the formatting of the unit, including setting font, background, alignment, etc.
A full set of digital formats, including support for euro format
Automatic filtering
Automatic summation
Sort the area
Look up
Unit border
Hide or display toolbars, lines, columns, and table lines
Merge Cells
Optional title bar
Manual rejuvenation and automatic rejuvenation
Protection units can prohibit insertion and delete rows, columns.
Visual area and automatic adjustment
Support in Microsoft IE, scaling and maximizing display in percentage
Built-in online help
Frozen panel
Full keyboard support
Can control the existence of the scroll bar
How to choose the current unit after the carriage return
You can run in a computer environment in all countries around the world, support multi-language, support from right to left
Because the spreadsheet component itself is not an application, it is just a control, so it provides most of the formatting functionality through a non-mode tool window named attribute toolbox. This part of the user interface is actually shared by each component, whenever, developer or user needs to change the format of the elements in the control. And if the developer needs to provide a custom runtime user interface, or for the runtime, the user changes the elements in the control, or by changing an attribute in the programming mode to disable the property tank at runtime. (This chapter will tell this process later) is very interesting that the attribute kit is completely written using dynamic HTML (DHTML). The spreadsheet component implements the property tank through an instance of the host Internet Explorer control, and interacts with an instance of the IE control when interacting with an instance of the IE control while interacting with an instance of the IE control. The property tank is just a script that performs a programming model that relying on the OWC component, so you can complete any functionality of the property toolbox in your code.
The spreadsheet control, and other Office web components support Office 2000 language settings, so users can set the user interface language in the spreadsheet control and the same as in the Office application, without setting the underlying. System area. If these language settings have been changed, the control automatically adjusts their user interface, and displays according to the selected language. This will also affect the amount of amounts, events and numbers. The spreadsheet component supports entering a Unicode character in the unit, which is also supported from right to left in an area environment that needs to be left from right to left.
Appendix: English
Chapter 2
The Spreadsheet Component
This chapter will delve into the functionality and the programming model of the Microsoft Office Spreadsheet component. Since this book focuses on building real solutions, I'll present a few interesting uses of each component feature along with its description. In Part II of the book You'll See Many of these Ideas Actually Implement.
This chapter will give you an understanding of what the Spreadsheet component can and can not do, suggest clever ways you can use your own code to add functionality to the component, and show the key elements of the programming model that will get you started.
The Basics of The Spreadsheet Component
BEFORE We get Too Detailed, Let's Examine The Basic Features of The Spreadsheet Component and Describe The Various Ways It Can Load and save data.
Recalculation Engine
The recalculation engine lies at the heart of the Spreadsheet component-making this component more than just a typical grid control. Designed by the same developers who built the recalculation engine for Microsoft Excel, the Spreadsheet component supports nearly all the functions in Excel 2000, including Most of those in the analysis toolpak (ATP) .note
For those who are curious, the following functions are not supported by the Spreadsheet component: ASC, CALL, DATEDIF, FINDB, FREQUENCY, GETPIVOTDATA, GROWTH, INFO, ISPMT, JIS, LEFTB, LENB, LINEST, LOGEST, MDETERM, MIDB, MINVERSE , MMULT, PHONETIC, REGISTER.ID, REPLACEB, RIGHTB, SEARCHB, SQL.REQUEST, TRANSPOSE, TREND, and YEN. All the functions ending in the letter "B" operate at the byte level instead of the character level in a double- Byte Character Set (DBCS) System.
Refer to the sections on property binding and function add-ins later in this chapter to see how to use the VBA or VBScript equivalents of these functions. Also, the INDEX and LOOKUP functions each have two forms in Excel, one using arrays and one using Vectors (Single-Dimension Ranges). The Spreadsheet Control Supports The Vector Forms But Not The Array Forms.
Any use of the Spreadsheet component that involves formulas will naturally require the recalculation engine-for example, a solution allowing users to view a product break-even model, change assumptions, and see the recalculated results. Recalculation is the backbone of spreadsheet "what if "analysis and the core feature of any spreadsheet product. The recalculation engine can also take advantage of some of the advanced features discussed later in the chapter, such as property binding and function add-ins.
When the Spreadsheet component is used without a user interface (that is, as an in-memory object), the recalculation engine becomes its primary service. The recalculation engine can perform any complex calculation that's easily expressed in a spreadsheet model. It can also recalculate an existing model on the server and then send the output to a web browser or into an e-mail message. Many calculations can be cumbersome to construct in script or C code but can be expressed rather easily in a spreadsheet model.For example, a bank or lending institution might develop a spreadsheet model to assess the risk of a particular type of loan. Since loan underwriters typically are not programmers, developing a loan assessment function to run on the server probably would be difficult-just as it would be for the PROGRAMER WHOESN 'THEEVER, USING SPREEET COMPONENT'S RECAL CAN, The Program Mer Can Load The Underwr Iter's Published Spreadsheet Model, Change The Inputs, And Grab The New Risk Assessment as The Output.
The Spreadsheet component supports a worksheet with a maximum of 65,536 rows by 702 columns (A through ZZ) but supports only one worksheet per instance of the component. This is the same number of rows that Excel 2000 offers but nearly three times the number of columns Excel supports Only 256 Column.)
Warning
If you try to load all 65,536 rows and 702 columns, you'll be waiting quite a while. Since the Spreadsheet control loads its content from HTML, the load operation is naturally slower than that of loading binary data, such as when Excel loads an XLS file. Although you can generally load files with hundreds of rows fairly quickly, large models will not load quickly in the Spreadsheet component.The Spreadsheet control supports both absolute ($ A $ 1) and relative (A1) cell references in formulas and, just like Excel, it automatically adjusts these references when you move, insert, or delete rows or columns. Using both absolute and relative references can be especially useful when copying cells containing formulas from one part of a range to another. For example, you would use an absolute cell reference if that reference must remain the same regardless of which row and column contains the formula. A reference that must refer to the current row and column, however, needs to be relative. In oth ER Words, $ 1 WILL REMAIN $ 1 $ 1 after the copy operation, while a1 will be converted to the current row and color.
Note
The Spreadsheet component does not support the old R1C1 reference style. It also does not support English language formulas, which attempt to let you build formulas based on named ranges. While this was a wonderful goal, it seldom works as expected.
Unfortunately, the Spreadsheet component does not support named ranges in this version of Office. When Excel publishes a spreadsheet model with interactivity (in other words, publishes a page containing the Spreadsheet control), it automatically converts named range references to absolute range references. This also occurs when you copy and paste cells from Excel to the Spreadsheet control. If you want to use a named range in the code around the Spreadsheet component, consider using the Dictionary object (Scripting.Dictionary) as an easy way to store and retrieve the actual references for a given name. (The Dictionary object is implemented in the Microsoft Scripting Runtime library.) for example, if you want to define a named range that refers to a list of data in the spreadsheet and want to use that range in Script to Perform A Sort, You Can Write Code Like this:
SET DICT = CreateObject ("scripting.dictionary") Dict.Add "MyName", "A1: F20" set rng = spreadsheet1.range (DICT ("MyName"))
The Dictionary object is an associative array. You add key / value pairs to it and, given the key, you can efficiently retrieve any value. You can easily keep track of named ranges with this object, and any time you need to pass a reference .
Spreadsheet Component User Interface
On top of the recalculation engine sits the Spreadsheet component user interface. The user interface is, of course, similar to that of Excel but is specifically designed to favor activities you'd need to perform when interacting with an existing spreadsheet model. Many of the features found in Excel that make authoring a new spreadsheet easy do not yet exist in the Spreadsheet control; however, there are enough so that you can create new spreadsheets for your solutions as needed Plus, you can copy and paste from Excel to the Spreadsheet. Component-meaning you can do nurse of Your Authoring in Excel. Figure 2-1 shows an esample of a basic spreadsheet component.spreadsheet Reference Madness
While reviewing this chapter, one of our capable Spreadsheet component developers, Andrew Milton, showed me many other types of range references supported by Excel but not supported by the Spreadsheet component. However, I had never seen most of these references and did not even KNOW Theee Possible in Excel. Try this:
Type 1, 2, 3, 4, 5 INTO the Cells A1: A5. Next, Type The Value 2 in Cell B1. Finally, Enter the Formula = SUM (A1: Choose (B1, A1, A2, A3, A4, A5 )) INTO CELL C1. You Should Get 3 as The Result. Now Change Cell B1 To 3. The Formula Will Recalculate To 6.
The CHOOSE function acts like a Select Case statement in VBA and can be used in the middle of a range reference to define the range's end point dynamically. Defining a range dynamically also works with the INDIRECT function, but the Spreadsheet component simply does not support Such Dynamic Range Definition.
. Range intersection is another interesting construct On the same spreadsheet you used for the last example, type the formula = A1: A5 A1:. B1 into cell D1 You should get 1. This type of reference makes Excel perform an intersection of the two ranges , resolving to a reference of A1. The Spreadsheet component lets you enter a formula like this, but it resolves the formula to = A1, while Excel leaves the formula as you originally entered it.Figure 2-1. A basic Spreadsheet component.
The Spreadsheet component user interface contains a great number of spreadsheet features that people now take for granted. Rather than list every last one here, I'll leave you to discover most of them as you play with the control. Although the following list is not Exhaustive, IT Should Give You An IDEA of The Level of User Interface Features Supported by The Spreadsheet Control:
Column and row resizing
See-THRough SELECTION
SEMISELECT WHEN Entering Formulas
Multilevel Undo
INSERT AND REMOVE COLMNS AND ROWS
CUT, COPY, AND PASTE
PER-Cell Formatting, Including Font, Background, Alignment, And So ON ON
Full Set of Number Formats, Including EURO Support
AutoFilter
AutoSum
Sort Ranges
Find
Cell Borders
Hide or show Toolbar, Row and Column Headers, And Gridlines
Merge Cells
Optional Title Bar
Manual or Automatic Recalculation Switch
Protected Cells and Ability To Disable Insertion and deleion of rows and columns
Viewable Range And Autofit
Percent Sizing and Maximum Size in Microsoft Internet Explorer
Built-in Online Help
Frozen Panes
Full Keyboard Support
Control over The Existence of Scroll Bars
Control over Current Cell SelectionAve Return
Worldwide, Multilanguage, And Right-to-Left Support
Since the Spreadsheet component is just a control and not an application unto itself, most of the formatting capabilities are exposed through a modeless tool window called the Property Toolbox. This part of the user interface is actually shared among all the components and used whenever the developer or user wants to change the formatting of an element in the control. A developer that wants to supply a custom runtime user interface or disable runtime changes altogether can disable the Property Toolbox at runtime by changing a property in the programming model. (I'll describe this process later in the chapter.) Interestingly enough, the Property Toolbox was written entirely in Dynamic HTML (DHTML). The Office Web Components host an instance of the Internet Explorer control to render the Property Toolbox and execute its code when you interact with The Elements on The Web Page. The Property Toolbox Merely Executes Script Against The Component's Programming Model So That Anything The Proper Ty Toolbox Does, you can do in code too.
The Spreadsheet control, along with the other Office Web Components, supports the Office 2000 language settings that enable users to set the user interface language used in the Office applications without having to reset the underlying system regional settings. If these language settings have been changed from their defaults, the controls will automatically adjust their user interfaces to show the selected language. This also affects currency, date, and number formatting. The Spreadsheet component supports Unicode characters in cells, as well as right-to-left layout for those locales that Require IT.
