System and methods for building spreadsheet applications5623591
Abstract
An electronic spreadsheet system includes a notebook interface having a plurality of notebook pages, each of which may contain a spread of information cells, or other desired page type (e.g., Graphs page). The system includes a spreadsheet application development module having a user interface (UI) builder. The UI builder provides "live" links between system objects. In particular, link commands are provided as statements a developer may attach to a control to indicate what should happen when an end-user activates or changes the control. Link commands usually specify three things: an event, an action, and an object. The event indicates what occurrence should trigger the link command; the action indicates what should happen. The object is what should be acted upon. One control can have many link commands. In operation, a link command can send a value to a cell in the notebook, run a macro, close a dialog box, change the zoom factor in the notebook, or the like.
Claims
What is claimed is:
1. In an electronic spreadsheet system having a memory and a storage device for storing data in a plurality of cell objects, each of said cell objects having a set of properties including a value property, each of the set of properties have associated values, a method for defining selected user interaction with a given cell object, the method comprising:
in response to first user input, generating a user interface object of a predefined type distinct from cell objects, said user interface object having a set of properties including a value property;
in response to second user input, linking said value property of the given cell object with said value property of said user interface ojbect;
displaying said user interface object with a value of said value property corresponding to the value of said value property of the given cell object; and
in response to end-user input that effects a change in the value of said value property of said user interface object, propagating the change to the given cell object so that the given cell object has its value of said value property set to said changed value of said value property of said user interface object.
2. The method of claim 1, wherein said predefined type of user interface object is one of a plurality of predefined types of user interface object, said plurality of types including an edit field, a scroll bar, and a screen button.
3. The method of claim 1, wherein said set of cell object properties includes a particular display-attribute property, the method further comprising:
in response to third user input, generating an additional user interface object of a predefined additional type distinct from cell objects, said additional user interface object having a set of properties including said display-attribute property;
linking said display-attribute property of an additional given cell object to that of said additional user interface object; and
in response to end-user input that specifies a change in value of the display-attribute property of said additional user interface object, propagating said change to the display-attribute property of said additional given cell object.
4. The method of claim 3, wherein:
said additional user interface object is a color control; and
said display-attribute property of an object includes the color of the object.
5. The method of claim 1, wherein:
said objects have inlets and outlets; and
inlets for an object include actions which are performed by the object.
6. The method of claim 1, wherein:
said objects have inlets and outlets; and
outlets for an object include events which occur at the object.
7. In a digital computer, a system for linking objects in an electronic spreadsheet together, the system comprising:
means for storing a plurality of objects, including at least one cell object and at least one user interface object distinct from cell objects;
means for storing for each object in the system a set of properties, inlets, and outlets, each cell object having a plurality of properties;
means for storing links between objects
means for generating user interface objects in response to user input, said user interface objects including at least first and second types having respective first and second ones of said plurality of properties that characterize each cell object;
said first and second types of user interface object being operable to set values for the respective first and second properties of cell objects to which said first and second types of user interface object are linked;
means, responsive to user input, for specifying a link between (a) a particular user interface object generated by said means for generating user interface objects, said particular user interface object having one of said first and second types, and (b) a particular cell object, said particular user interface object, so linked to said particular cell object expressing a value of the property corresponding to the value of the property for the particular cell object;
means for receiving input for changing the value of said particular user interface object's property; and
means for communicating said change in said particular user interface object's property to said particular cell object so that said particular cell object expresses a value of said particular cell object's property corresponding to the value of said particular user interface object's property, so changed.
8. The system of claim 7, further comprising:
means for receiving input for changing the value of said particular cell object's property; and
means for communicating said change in said particular cell object's property to said particular user interface object so that said particular user interface object expresses a value of its property corresponding to the value of said particular cell object's property, so changed.
9. The system of claim 7, wherein said properties of an object include display attributes of the object.
10. The system of claim 7, wherein inlets for an object include actions which are performed by the object.
11. The system of claim 7, wherein outlets for an object include events which occur at the object.
12. The system of claim 7, wherein said particular user interface object is a selected one of a scroll bar, a radio button, and a check box.
13. In an electronic spreadsheet comprising a plurality of information cells, each information cell having a plurality of properties including a value property and a display-attribute property, which properties have associated values that are subject to being modified, a method for linking user interface controls to information cells, said user interface controls for receiving end-user input for said electronic spreadsheet, the method comprising:
receiving first user input for generating a user interface control of a selected one of a plurality of different types for receiving end-user input, the types of user interface control including first and second types, each characterized by a particular property, which is the value property for the first type and the display-attribute property for the second type;
in response to receiving said first user input, generating a user interface control of a desired one of the first and second types, as designated by the user input;
displaying the user interface control, so generated;
receiving second user input for linking the user interface control, so generated, to at least one desired information cell;
in response to receiving said second user input, linking the user interface control to a given information cell;
the user interface control, so linked to the given information cell, expressing a value of the interface control's particular property corresponding to the value of the given information cell's particular property, and further being operable to modify the value of the given information cell's particular property;
receiving end-user input at the user interface control specifying a change in the value of the particular property expressed by the user interface control to a new value;
determining whether the user interface control has been linked to any information cells; and
updating all information cells determined to be linked to the user interface control, based on the end-user input received, so that the information cells, so updated, have their values of the particular property set to said new value.
14. The method of claim 13, wherein said types of user interface control include an edit field, a scroll bar, and a screen button.
15. The method of claim 13, wherein said end-user input comprises a selected one of keyboard input and pointing device input.
16. The method of claim 13, wherein said step of receiving second user input for linking the user interface control includes:
receiving user input for invoking a link command, said link command specifying an action to be performed by a particular user interface control in response to occurrence of a particular event.
17. The method of claim 16, wherein said particular event comprises an event of a type signifying that a value has changed.
18. The method of claim 16, wherein:
the electronic spreadsheet includes a spreadsheet macro; and
said link command is executed from the spreadsheet macro.
Description
COPYRIGHT NOTICE
A portion of the disclosure of this patent document contains material which is subject to copyright protection. The copyright owner has no objection to the facsimile reproduction by anyone of the patent document or the patent disclosure as it appears in the Patent and Trademark Office patent file or records, but otherwise reserves all copyright rights whatsoever.
BACKGROUND OF THE INVENTION
The present invention relates generally to the field of information processing by digital computers and, more particularly, to the processing and presentation of information by program applications, particularly electronic spreadsheets.
Before computers, numerical analyses, particularly financial ones, were usually prepared on an accountant's columnar pad or spreadsheet, with pencil and calculator in hand. By organizing data into columns and rows, spreadsheets afford the rapid assimilation of information by a reader. The task of preparing a spreadsheet on paper, however, is not quite so fast. Instead, the process tends to be very slow, as each entry must be tediously calculated and entered into the spreadsheet. Since all calculations are the responsibility of the preparer, manually prepared spreadsheets are also prone to errors. Hence, preparation of spreadsheets by hand is slow, tedious, and unreliable.
With the advent of microcomputers, a solution was forthcoming in the form of "electronic spreadsheets." Better known simply as "spreadsheets," these software programs provide a computerized replacement for the traditional financial modeling tools: the accountant's columnar pad, pencil, and calculator. In some regards, spreadsheet programs are to those tools what wordprocessors are to typewriters. Spreadsheets offer dramatic improvements in ease of creating, editing, and using financial models.
A typical spreadsheet program configures the memory of a computer to resemble the column/row or grid format of an accountant's columnar pad, thus providing a visible calculator for a user. Because this "pad" exists dynamically in the computer's memory, however, it differs from paper pads in several important ways. Locations in the electronic spreadsheet, for example, must be communicated to the computer in a format which it can understand. A common scheme for accomplishing this is to assign a number to each row in a spreadsheet, and a letter to each column. To reference a location at column A and row 1 (i.e., the upper-lefthand corner), for example, the user types in "A1". In this manner, the spreadsheet defines an addressable storage location or "cell" at each intersection of a row with a column.
Data entry into an electronic spreadsheet occurs in much the same manner that information would be entered on an accountant's pad. After a screen cursor is positioned at a desired location, the user can enter alphanumeric information. Besides holding text and numeric information, however, spreadsheet cells can store special instructions or "formulas" specifying calculations to be performed on the numbers stored in spreadsheet cells. In this fashion, cell references can serve as variables in an equation, thereby allowing precise mathematical relationships to be defined between cells. The structure and operation of a spreadsheet program, including advanced functions such as functions and macros, are documented in the technical, trade, and patent literature. For an overview, see e.g., Cobb, S., Using Quattro Pro 2, Borland-Osborne/McGraw-Hill, 1990; and LeBlond, G. and Cobb, D., Using 1-2-3, Que Corp., 1985. The disclosures of each of the foregoing references are hereby incorporated by reference.
Electronic spreadsheets offer many advantages over their paper counterparts. For one, electronic spreadsheets are much larger (i.e., hold more information) than their paper counterparts; electronic spreadsheets having thousands or even millions of cells are not uncommon. Spreadsheet programs also allow users to perform "what if" scenarios. After a set of mathematical relationships has been entered into a worksheet, the spread of information can be recalculated using different sets of assumptions, with the results of each recalculation appearing almost instantaneously. Performing this operation manually, with paper and pencil, would require recalculating every relationship in the model with each change made.
While electronic spreadsheets offer significant productivity gains in the task of complex data modeling, none has been as intuitive to use as ordinary paper and pencil--objects already familiar to the user. Instead, the user must master many complex and arbitrary operations. To find the proper command for a task at hand, for example, the user must hunt through a complex menuing system, with the desired choice often buried under several menus. Even simple tasks can pose a significant challenge to the user. To change the punctuation format of a number in one prior art spreadsheet, for example, the user must traverse several nodes of a menu tree, carefully selecting among cryptic menu choices along the way. A mistake at any one of the nodes can lead to harsh consequences, including the loss of valuable data.
Finding this approach to be unworkable, many users memorize frequently-needed commands instead. To accomplish the foregoing task, for example, the user would memorize the command: /Worksheet Global Default Other International. As one can only memorize just so many arbitrary commands, however, the user typically masters only a very small subset of available commands and features. And without constant practice, these commands tend to be quickly forgotten. Moreover, many useful and needed commands are sufficiently hidden in layers of menus that they are never discovered by the user. All told, the non-intuitive interfaces of prior art spreadsheets have led to steep learning curves for users. Even after mastering a particular spreadsheet interface, the user typically only knows a fraction of available commands and features, most of which are easily forgotten.
Even with advances in computer and software technology, electronic spreadsheets have not necessarily become easier to use. Instead, technological advances have been largely employed to build more complex functions and modeling features into spreadsheets, often with more complicated menu trees; or worse yet, a staggering array of icons which leave the user even more bewildered. Thus, while prior art spreadsheets have continued to increase in functionality, they have also greatly increased in complexity for the user.
Three dimensionality is one such example. Three-dimensional spreadsheets allow the user to create a worksheet having cells arranged in a 3-D grid. In this manner, the user can manipulate multi-dimensional ranges, i.e., solid blocks of cells. This feature has distinct advantages. For example, the user can build a worksheet consisting of multiple two-dimensional spreads, define 3-D ranges that span these spreads, and copy a range of rows and columns into each of many 2-D spreads at once. This feature eases difficult choirs, such as consolidation of multiple spreads.
Despite its advantages, three-dimensionality, as presently implemented, is an advanced feature beyond the grasp of many spreadsheet users. This is not a necessary result of a three-dimensional model per se but, instead, has resulted from poor implementations of the model in prior art spreadsheet programs. One popular implementation of the model in the prior art, for example, requires the user to manipulate each additional spread of a three-dimensional spreadsheet as a separate window in a graphical windowing environment. This approach is far from intuitive, however. In particular, this approach requires the user to master actions which have no counterpart in everyday activities. While three-dimensional spreadsheets provide additional functionality, they serve to illustrate how non-intuitive implementations of new technology greatly add to the complexity of the user interface.
Another disadvantage of prior art spreadsheet systems is the lack of tools for building application programs which operate in a spreadsheet environment. More particularly, it is desirable to create spreadsheets which include user interface components (e.g., dialog boxes, screen buttons, and the like) which are customized for a particular spreadsheet task at hand, thus providing a custom application with the look and feel of the underlying spreadsheet system. Moreover, conventional systems lack the ability to connect or link spreadsheet cells to user interface objects, so that a cell may be updated dynamically with a change to a user interface object.
SUMMARY OF THE INVENTION
Application software, such as electronic spreadsheets, have found wide application in the processing and presentation of information. Despite their computational power, however, these programs require users to master complex interfaces, adopting abstract representation models which are beyond the grasp of ordinary users. As a result, much of the computational power of these applications goes un-utilized by end users.
The present invention, in contrast, provides system and methods having a highly intuitive interface for users. More particularly, the present invention provides interface objects which are familiar to the user. In an exemplary embodiment of the present invention, an electronic spreadsheet system includes a notebook interface having a plurality of notebook pages, each of which contains a spread of information cells, or other desired page type (e.g., Graphs page).
Methods are provided for rapidly accessing and processing information on the different pages, including, for example, displaying a plurality of page identifiers for selecting individual pages. Additional methods are provided for editing cells and blocks of cells. In this fashion, the spreadsheet notebook of the present invention provides a convenient means for organizing and presenting information. Moreover, the spreadsheet notebook of the present invention readily accommodates complex data (e.g., consolidation across multiple spreads); yet, at the same time provides the user with a highly intuitive interface, one which employs interface objects which are familiar to the user.
The present invention also includes system and methods for conveniently inspecting and setting the properties of objects. A method for accessing an object's property, in accordance with the present invention, includes receiving a request from the user for inspection of an object; accessing properties for the object; and displaying the properties to the user. From the displayed properties, the user may alter the characteristics of the object, as desired.
The present invention also includes a spreadsheet application development module having a user interface (UI) builder for building custom applications operative in an electronic spreadsheet. An application is a combination of components which work together to simplify an end user's task. When building an application using the system of the present invention, the user (in a role as developer) can create custom components, including dialog boxes, toolbars, menus, and the like. After creating these components, the user can assemble them into an integrated application notebook which comprises all of the programming logic and user interface components for the spreadsheet application.
The UI builder provides "live" links between system objects (e.g., a dynamic link between a spreadsheet cell and a scroll bar). In particular, link commands are provided which a developer may attach to a control to indicate what should happen when an end-user activates or changes the control. Link commands usually specify three things: an event, an action, and an object. The event indicates what occurrence should trigger the link command; the action indicates what should happen. The object is what should be acted upon. One control can have many link commands. In operation, a link command can send a value to a cell in the notebook, run a macro, close a dialog box, change the zoom factor in the notebook, or the like.
BRIEF DESCRIPTION OF THE DRAWINGS
FIG. 1A is a block diagram of a computer system in which the present invention may be embodied.
FIG. 1B is a block diagram of a software system of the present invention, which includes operating system, application software, and user interface components.
FIG. 1C is bitmap screen shots illustrating the basic architecture and functionality of a graphical user interface in which the present invention may be embodied.
FIG. 2A is a screen bitmap illustrating a spreadsheet notebook of the present invention.
FIG. 2B is a bitmap of a tool bar component of the spreadsheet of the present invention.
FIG. 2C is a screen bitmap illustrating a notebook window from the spreadsheet notebook of the present invention.
FIGS. 2D-E are bitmaps illustrating page identifiers for rapidly accessing and manipulating individual pages of the notebook of the present invention.
FIGS. 3A-C illustrate navigation (i.e., access of desired information) in the spreadsheet notebook of the present invention.
FIGS. 4A-E are screen bitmaps illustrating the selection of information cells in the spreadsheet notebook of the present invention.
FIG. 4F is a screen bitmap illustrating the operation of grouping one or more desired pages in the spreadsheet notebook of the present invention.
FIG. 4G is a screen bitmap illustrating drag-and-drop editing in the spreadsheet notebook of the present invention.
FIGS. 4H-J are a series of screen bitmaps illustrating the model copy method of the present invention.
FIG. 4K is a set of bitmaps illustrating the operation of moving and copying a notebook page.
FIG. 4L-M are screen bitmaps illustrating the presentation of information in a notebook of the present invention.
FIG. 5A is a screen bitmap illustrating exemplary objects of the spreadsheet notebook of the present invention.
FIGS. 5B-E are bitmaps illustrating property inspectors of the present invention, for the objects of FIG. 5A.
FIGS. 6A-K are bitmaps illustrating different states (and accompanying panes) for the property inspector of FIG. 5E, each state depending on a particular property of the object being inspected.
FIG. 7A is a screen bitmap illustrating a graph window of the present invention, with different graph objects available for property inspection.
FIGS. 7B-H are bitmaps illustrating exemplary property inspectors of the present invention for the graph objects of FIG. 7A.
FIG. 8A is a block diagram illustrating the structure and operation of a spreadsheet notebook of the present invention.
FIGS. 8B-C illustrate the correspondence between a page table data structure and pages which are displayed on the screen to the user.
FIGS. 8D-F illustrate the referencing of information in the spreadsheet notebook of the present invention.
FIG. 9A is a flowchart illustrating a method of the present invention for interpreting information references.
FIG. 9B is a flowchart illustrating a method of the present invention for drag and drop editing.
FIG. 9C is a flowchart illustrating a method of the present invention for model copying.
FIG. 10A is a block diagram illustrating a system class hierarchy of the present invention, which is employed for property inspection.
FIG. 10B is a block diagram illustrating the correspondence between a parent object and a child object, and their respective property lists.
FIG. 10C is a flowchart illustrating a method for inspecting and setting properties of objects, in accordance with the present invention.
FIG. 10D is a flowchart illustrating a set property method of the present invention (substep from the method of FIG. 10C).
FIG. 11A is a bitmap screenshot illustrating a spreadsheet application, including its major components.
FIGS. 11B-D are bitmap screenshots illustrating organization of the spreadsheet application within a notebook, with each major module occupying a single notebook page.
FIG. 11E is a bitmap screenshot illustrating a sample dialog box created with the dialog builder of the system of the present invention.
FIG. 11F is a bitmap screenshot illustrating initial layout of a dialog box (empty dialog box) within a dialog window.
FIG. 11G illustrates two views of a dialog box: an editable (developer) form and a runtime (user) form.
FIG. 11H is a bitmap screenshot illustrating a toolbar of the system of the present invention for use in the dialog window.
FIG. 11I illustrates arguments of the DODIALOG command.
FIG. 11J is a bitmap screenshot illustrating a completed dialog box (in test mode).
FIG. 11K is a bitmap screenshot illustrating entry of a macro command in a notebook for displaying a dialog box.
FIGS. 12A-C are screen bitmaps illustrating an Object Link dialog box of the present invention, for creating links between objects.
FIG. 13 is a bitmap screenshot illustrating a macro in a notebook that displays the dialog box of FIG. 11E (and uses the settings within the dialog box to calculate a loan payment).
FIGS. 14A-H are bitmap screenshots illustrating additional uses for the Object Link dialog box, for creating more complex links.
FIG. 15 is a bitmap screenshot illustrating the dialog window after placement of a file control and an edit field.
FIGS. 16A-B are block diagrams illustrating the relationship of an object (e.g., button object) and inlets, outlets, and properties.
FIG. 16C is a block diagram illustrating connection of properties of two objects, using link commands of the present invention.
DETAILED DESCRIPTION OF A PREFERRED EMBODIMENT
System Hardware
As shown in FIG. 1A, the present invention may be embodied on a computer system such as the system 100, which comprises a central processor 101, a main memory 102, an input/output controller 103, a keyboard 104, a pointing device 105 (e.g., mouse, track ball, pen device, or the like), a display device 106, and a mass storage 107 (e.g., hard disk). Additional input/output devices, such as a printing device 108, may be included in the system 100 as desired. As illustrated, the various components of the system 100 communicate through a system bus 110 or similar architecture. In a preferred embodiment, the computer system 100 includes an IBM-compatible personal computer, which is available from several vendors (including IBM of Armonk, N.Y.).
Illustrated in FIG. 1B, a computer software system 150 is provided for directing the operation of the computer system 100. Software system 150, which is stored in system memory 102 and on disk memory 107, includes a kernel or operating system 151 and a shell or interface 153. One or more application programs, such as spreadsheet module 152, may be "loaded" (i.e., transferred from storage 107 into memory 102) for execution by the system 100. The system 100 receives user commands and data through user interface 153; these inputs may then be acted upon by the system 100 in accordance with instructions from operating module 151 and/or application module 152. The interface 153, which is preferably a graphical user interface (GUI), also serves to display results, whereupon the user may supply additional inputs or terminate the session. In a preferred embodiment, operating system 151 is MS-DOS, and interface 153 is Microsoft.RTM. Windows; both are available from Microsoft Corporation of Redmond, Wash. Spreadsheet module 152, on the other hand, includes Quattro.RTM. Pro for Windows, available from Borland International of Scotts Valley, Calif.
Interface: User-familiar Objects
A. Introduction
The following description will focus on the presently preferred embodiments of the present invention, which are embodied in spreadsheet applications operative in the Microsoft Windows environment. The present invention, however, is not limited to any particular application or any particular environment. Instead, those skilled in the art will find that the system and methods of the present invention may be advantageously applied to a variety of system and application software, including database management systems, wordprocessors, and the like. Moreover, the present invention may be embodied on a variety of different platforms, including Macintosh, UNIX, NextStep, and the like. Therefore, the description of the exemplary embodiments which follows is for purposes of illustration and not limitation.
Referring now to FIG. 1C, the system 100 includes a windowing interface or workspace 160. Window 160 is a rectangular, graphical user interface (GUI) for display on screen 106; additional windowing elements may be displayed in various sizes and formats (e.g., tiled or cascaded), as desired. At the top of window 160 is a menu bar 170 with a plurality of user-command choices, each of which may invoke additional submenus and software tools for use with application objects. Window 160 includes a client area 180 for displaying and manipulating screen objects, such as graphic object 181 and text object 182. In essence, the client area is a workspace or viewport for the user to interact with data objects which reside within the computer system 100.
Windowing interface 160 includes a screen cursor or pointer 185 for selecting and otherwise invoking screen objects of interest. In response to user movement signals from the pointing device 105, the cursor 185 floats (i.e., freely moves) across the screen 106 to a desired screen location. During or after cursor movement, the user may generate user-event signals (e.g., mouse button "clicks" and "drags") for selecting and manipulating objects, as is known in the art. For example, Window 160 may be closed, resized, or scrolled by "clicking" (selecting) screen components 172, 174/5, and 177/8, respectively.
In a preferred embodiment, screen cursor 185 is controlled with a mouse device. Single-button, double-button, or triple-button mouse devices are available from a variety of vendors, including Apple Computer of Cupertino, Calif., Microsoft Corporation of Redmond, Wash, and Logitech Corporation of Fremont, Calif., respectively. More preferably, screen cursor control device 105 is a two-button mouse device, including both right and left "mouse buttons." Programming techniques and operations for mouse devices are well documented in the programming and hardware literature; see e.g., Microsoft Mouse Programmer's Reference, Microsoft Press, 1989, the disclosure of which is hereby incorporated by reference.
B. Spreadsheet Notebooks
In contrast to conventional applications, even those operative in a windowing environment, the present invention includes user-familiar objects, i.e., paradigms of real-world objects which the user already knows how to use. In an exemplary embodiment, system 100 includes a spreadsheet notebook interface, with such user-familiar objects as pages and tabs. In this manner, complexities of the system are hidden under ordinary, everyday object metaphors. By drawing upon skills which the user has already mastered, the present invention provides a highly intuitive interface--one in which advanced features (e.g., three-dimensionality) are easily learned.
1. General interface
Shown in FIG. 2A, spreadsheet module 152 includes a spreadsheet notebook interface of the present invention will now be described. The spreadsheet notebook or workbook of the present invention includes a notebook workspace 200 for receiving, processing, and presenting information, including alphanumeric as well as graphic information. Notebook workspace 200 includes a menu bar 210, a tool bar 220, a current cell indicator 230, an input line 231, a status line 240, and a notebook window 250. The menu bar 210 displays and invokes, in response to user inputs, a main level of user commands. Menu 210 also invokes additional pulldown menus, as is known in windowing applications. Input line 231 accepts user commands and information for the entry and editing of cell contents, which may include data, formulas, macros, and the like. Indicator 230 displays an address for the current cursor (i.e., active cell) position. At the status line 240, system 100 displays information about the current state of the workbook; for example, a "READY" indicator means that the system is ready for the user to select another task to be performed.
The tool bar 220, shown in further detail in FIG. 2B, comprises a row or palette of tools which provide a quick way for the user to choose commonly-used menu commands or properties. In an exemplary embodiment, tool bar 220 includes cut, copy, and paste buttons 221, a power button tool 222, a graph tool 223, alignment buttons 224, a style list 225, font buttons 226, insert/delete buttons 227, a fit button 228, and action buttons 229. Buttons 221 cut, copy and paste data and objects to and from Windows clipboard. The same actions are also available as corresponding commands in the Edit menu (available from menu bar 210). Tool 220 creates "powerbuttons" which allow a user to run spreadsheet macros; in a specific embodiment, powerbuttons appear as floating objects in a layer above spreadsheet cells. In a similar fashion, the graph tool 223 creates floating graphs that appear above spreadsheet cells.
Other tools are available for formatting cells. Alignment buttons 224 place cell entries flush left, centered, or flush right, as desired. The style list 225 specifies the style for the active block and may be selected from a plurality of pre-defined styles (e.g., normal, currency, fixed, percent, and the like). The font buttons 226 effect font changes, including toggling bold and italic fonts, as well as increasing and decreasing font (point) size. The insert and delete buttons 227 permit the user to insert or delete blocks, rows, columns, and pages (described in further detail hereinbelow), as desired. The fit button 228 allows the user to quickly tailor a column's width to match its widest cell entry. Action buttons 229 provide automated spreadsheet operations, including sorting and summing operations. For example, a Sort button, when invoked, performs a sort on data in a currently active block. A sum button, on the other hand, totals the values in any block of cells by generating an @SUM function, just outside a selected range of blocks.
2. Notebook and pages
Notebook 250, shown in further detail in FIG. 2C, provides an interface for entering and displaying information of interest. The notebook 250 includes a plurality of spreadsheet pages, such as page 251 (Page A). Notebook 250 also includes a "Graphs page" for readily accessing all the graphs created for the notebook. Each page may include conventional windowing features and operations, such as moving, resizing, and deleting. In a preferred embodiment, the notebook 250 includes 256 spreadsheet pages and one Graphs page, all of which are saved as a single disk file on the mass storage 107. In accordance with the present invention, workspace 200 may display one or more notebooks, each sized and positioned (e.g., tiled, overlapping, and the like) according to user-specified constraints. When a single notebook is used, notebook 250 will typically occupy a majority of workspace 200.
Each spreadsheet page of a notebook includes a 2-D spread. For example, spreadsheet page 251 includes a grid in row and column format, such as row 252 (row 3) and column 255 (col. F). At each row/column intersection, a box or cell (e.g., cell C4) is provided for entering, processing, and displaying information in a conventional manner. Each cell is addressable, with a selector 253 being provided for indicating a currently active one (i.e., the cell that is currently selected).
As shown in FIGS. 2C-E, individual notebook pages are identified by page identifiers 260, preferably located along one edge of the notebook 250. In a preferred embodiment, each page identifier is in the form of a tab member (e.g., members 261a, 262a, 263a) situated along a bottom edge of the notebook. Each tab member may include representative indicia, such as textual or graphic labels, including user-selected titles representing the contents of a corresponding page. In FIG. 2D, the tab members 260 are set to their respective default names. For example, the first three tab members (members 261a, 262a, 263a) are respectively set to A, B, and C. In a preferred embodiment, however, tab members are typically given descriptive names provided by the user. As shown in FIG. 2E, for example, the first three tab members have now been set to "Contents" (tab member 261b), "Summary" (tab member 262b), and "Jan" (tab member 263b). In a similar manner, the remaining tabs are set to subsequent months of the year. In this manner, the user associates the page identifiers with familiar tabs from an ordinary paper notebook. Thus, the user already knows how to select a page or spread of interest: simply select the tab corresponding to the page (as one would do when selecting a page from a paper notebook).
In addition to aiding in the selection of an appropriate page of information, the user-customizable page identifiers aid in the entry of spreadsheet formulas. For example, when entering a formula referring to cells on another page, the user may simply use the descriptive page name in the formula itself (as described hereinbelow), thus making it easier for the user to understand the relationship of the cell(s) or information being referenced.
3. Navigation in a notebook
Referring now to FIGS. 3A-C, movement (i.e., location of desired information cells) within a spreadsheet notebook of the present invention is illustrated. To move to different pages in the notebook, the user simply selects the corresponding tab from tabs 260. To move to Page B, for example, the user selects (e.g., with keyboard 104 or pointing device 105) tab 262a; similarly, Page C is reached by selecting tab 263a. Continuing the example, the user may return to Page A by selecting tab 261a. Thus instead of finding information by scrolling different parts of a large spreadsheet, or by invoking multiple windows of a conventional three-dimensional spreadsheet, the present invention allows the user to simply and conveniently "flip through" several pages of the notebook to rapidly locate information of interest.
Notebook 250 also includes supplemental tools for navigation between pages, including a tab scroller 271 and a fast-forward button 272. The tab scroller (shown in two different states: 271a and 271b) permits access to identifiers for pages which are not currently visible on the screen device 106. If a desired identifier or tab is not currently in view, the user simply activates the tab scroller 271 to reveal additional tabs. The fast-forward button 272, on the other hand, provides immediate access to the last pages of the notebook, including the Graphs page. As shown in FIGS. 3A and B, after invoking the fast-forward button 272a, the page identifiers for the last pages (e.g., tabs 261c, 262c, 263c, 265) are accessible. To switch back to the previously active spreadsheet page, the user may select or click the fast-forward button 272c again. For navigation within a spreadsheet page, horizontal and vertical scrollbars 278, 259 are provided; general features and operations of basic scroller or sliders are described in Windows user and programming literature, including Microsoft's Windows Software Development Kit.
4. Selections and aggregate operations within a notebook
The selection of desired information cells in the notebook of the present invention is now described. For selecting a plurality of information cells, both 2-D blocks (e.g., block 254 of FIG. 2C) and 3-D blocks of cells may be defined. A 2-D block is a rectangular group of one or more cells and is identified by block coordinates, such as the cell addresses of its upper-left and bottom-right corners. Similarly, a 3-D block represents a solid block (e.g., cube) of cells.
A 2-D block is specified by selecting, with mouse 105 or keyboard 104, opposing corners. In FIG. 2C, for example, the block 254 is defined by corner cells C5 and F14. Additional selection examples are illustrated in FIGS. 4A-E. For example, column B (col. 411) is selected by clicking column heading 410; similarly, row 3 (row 421) is chosen by clicking row heading 420. Selection may be additive (i.e., additional selections are appended to the current ones), as shown by selection of a row 420 and a column 410 in FIG. 4C. To facilitate the selection of all cell members (e.g., block 431), a select-all button 430 is also provided. In addition to these "contiguous" blocks, non-contiguous block selection (e.g., selection of blocks 441, 442) is provided by use of a status key (e.g., CTRL--, ALT--, or SHIFT--) plus a mouse event (e.g., click and drag operations).
Selection of 3-D cell blocks, i.e., cell ranges spanning more than one page, occurs in a similar fashion. To extend the block 254 (of FIG. 2C) into a 3-D block, the user specifies an additional or third dimension by selecting an appropriate page identifier. If the user selects the D tab while the block 254 is selected (e.g., with a SHIFT or other status key), the 2-D block is extended into a 3-D block spanning from Pages A to D. Additional 3-D operations may be performed by utilizing a method of the present invention for grouping pages, which will now be described.
Pages may be selected or grouped together, thereby providing a means for changing multiple pages simultaneously. In much the same manner as cells from a spread are grouped into 2-D blocks, a range of pages are grouped by specifying beginning and ending members. As shown in FIG. 4F, a range from Page A to Page K may be achieved by selecting tabs A (261) and K (267) from identifiers 260, for example, while depressing a key (e.g., status key). A grouping indicator 268 is displayed for indicating members of a group; groupings may also be annotated with user-specified labels. Once grouped, a page of the group may have its operations (e.g., selection, data entry, and the like) percolate to the other members of the group, as desired. A non-contiguous selection of pages may also be selected (even across different pages); for example, a selection of Pages A and D, but not B and C, may be achieved by selecting tabs A and D while depressing a second key (e.g., CTRL-- key). Furthermore, groups may overlap (i.e., a page can be in more than one group), as desired. By selectively activating a group mode (e.g., by toggling group button 273), groupings may be temporarily turned off, in which case events are not percolated to other members of the group.
With group mode active, an activity in a page which is a member of a group can also affect similarly situated cells of the other pages of the group. For example, information entered in a cell on one page of the group can also propagate to the same (relative) cell in other pages of the group; data entry may be "drilled" (propagated) to other group pages by concluding data entry, for example, with a "Ctrl-Enter" keystroke (instead of a "Enter" command). For instance, an entry into cell C4 of Page A may also conveniently and automatically appear in cell C4 of pages B, C, D, E, F, G, H, I, J, and K, for an A-K grouping. Similarly, block or aggregate operations may propagate across member pages. For example, a block operation (e.g., Block Fill) for cells A1 to C4 in Page A in an A-D grouping would also operate (in this case, fill information) in the same cells (i.e., from A1 to C4) for Page B to Page D.
Employing the user-specified page identifiers of the present invention, a simple nomenclature is available for specifying these solid blocks of information. In a preferred embodiment, a solid block is specified by:
<<First Page>> . . . <<Last Page>>:<<First Cell>> . . . <<Last Cell>>
For example, a solid block may be defined as A . . . D:A1 . . . C4, in which case the block spans from cells A1 to C4, and spans across Pages A-D. By permitting alias names (i.e., user-supplied alternative labels), the present invention allows the block to be specified as 1989 Sales . . . 1992 Sales:A1 . . . C4; or even 1989 Sales . . . 1992 Sales:First Quarter . . . Third Quarter. Additionally, the present invention readily accommodates notebook information as well, for example, [TAX]1989 Sales . . . 1992 Sales:First Quarter . . . Third Quarter, thus permitting information to be linked across various notebooks. Wildcard characters (e.g., * and ?) may also be employed for cell, page, and notebook identifiers, as desired. Thus, the spreadsheet notebook of the present invention provides a 3-D interface which readily accommodates real-world information in a format the user understands (instead of forcing the user to adapt his or her information to fit an arbitrary spreadsheet model).
5. Advanced Editing
Whether 2-D or 3-D in nature, blocks of cells may be easily copied and cut (i.e., moved) using drag-and-drop editing techniques of the present invention. As shown in FIG. 4G for a 2-D block, for example, a method for copying a block of cells includes (1) selecting a source block by dragging a range of cells (e.g., mouse button-down events coupled with mouse movement across the range; close selection with a button-up event), (2) dragging the block (e.g., click within block, followed by repeated mouse button-down events), and (3) dropping the block (e.g., mouse button-up event at desired target location). In a similar fashion, 3-D blocks may be dragged and dropped.
In typical cut and copy operations, relative and absolute cell addressing is employed, as is known in the art (see e.g., Using 1-2-3). According to the present invention, however, a "model copy" technique for copying blocks is also provided. Model copying, illustrated in FIGS. 4H-J, is useful when the user is copying a block that contains absolute references to cells within the copied block. In FIG. 4H, a small spread model 496 is shown which contains a formula to figure the monthly payment for a 30-year loan at different interest rates; a reference to the loan amount was entered as absolute so that when the formula is copied, it continues to refer to cell B1. The user may want to calculate (at the same time) monthly payments for different loan amounts and, thus, might copy the model, with the loan amount changed (shown in FIG. 4I as spread 497). However, in this approach the absolute reference still refers to row 1; to correct this, the user would typically manually edit each formula to refer to B6 instead of B1.
With model copying of the present invention enabled (e.g., by default or through a user dialog), however, the problem is solved. In particular, absolute references adjust to the new location of the referenced cell, as shown by spread 498 in FIG. 4J; however, absolute references remain absolute to make future copies absolute. For instance, should the user make more copies of the formula, the reference to cell B6 is still absolute. In this manner, model copying of the present invention saves the user time-consuming and error-prone editing of formulas.
As shown in FIG. 4K, notebook pages may be copied or moved using the drag-and-drop editing techniques of the present invention (a dialog box is also provided as an alternative). As shown, for example, the "Salads" page is moved by selecting its tab identifier 264a from the identifiers 260; in a preferred method, the identifier is selected by dragging it downward (mouse button-down plus downward movement) with the mouse cursor. Next, the identifier is dragged to a desired new position (mouse button-down plus left and right movement). Once positioned at a desired location (as indicated by in-transit tab identifier 264b), the page is then "dropped" (mouse button-up) into position. Similarly, a "copy" operation may be effected by coupling the above operation with a status key (e.g., CTRL--); in a preferred method of copying, only the page information (not its tab identifier) is copied to a target location.
Additional editing operations may be easily accomplish using the page identifiers of the present invention. For example, an additional page may be inserted by selecting a desired tab and entering "INS" (keyboard or mouse). Similarly, a notebook page may be deleted by coupling the selection of a corresponding page tab to a delete command.
6. Advantages
In contrast to prior art spreadsheet implementations, use of the spreadsheet notebook of the present invention is easily ascertained by the user. The notebook interface of the present invention provides a convenient means for organizing many spreadsheets together into one file. This permits the user to load (into memory 102) all related information with a single command, without having to remember a variety of different file names. Moreover, the notebook interface 250 encourages a user to spread data out among multiple pages, thus better organizing one's data. In FIG. 4L, for example, spreadsheet page 300 illustrates the conventional method for grouping unrelated information onto a single spreadsheet. As shown in column B, the incorporation of unrelated information into a single spread leads to unnecessary whitespace padding of information. In FIG. 4M, in contrast, related information is grouped on separate pages 351, 352 (e.g., with their own columns, Col. B.sub.1 and Col. B.sub.2) of notebook 350. Not only does this eliminate grouping of disparate information, with its disadvantages (e.g., padding data entries), but it also encourages better organization of information.
Inspecting and Setting the Properties of Objects
A. Disadvantages of Prior Techniques
Standard windowing interfaces depend heavily on a clunky system of pull-down menus. While pull-down menus are an improvement over command-line (e.g., MS-DOS) interfaces, they are non-metaphoric or non-analogous to ordinary objects, i.e., ones familiar to the user. Thus, prior art windowing GUIs are no more intuitive or useful to the user than other menuing systems.
Perhaps the biggest weakness of pull-down menus is the requirement that the user must know beforehand which menu contains the item or function of interest. If the user does not know which pull-down menu, submenu, or dialog box contains the item he or she is seeking, the user will spend an inordinate amount of time checking the contents of each in an effort to hunt down the needed item. And often the search is in vain. Moreover, since functions for a given object (e.g., text object) are often scattered over several disparate menus, the user is discouraged from interacting and experimenting with the object.
One approach to overcoming this problem has been the implementation of "smart icons." This interface technique employs screen buttons painted with icons which are supposed to tell the user what the buttons do. This approach, however, often makes the interface problem even worse because the meaning of the icons are not easily grasped. Thus, the approach is often no more helpful than hiding the desired function deep inside a menuing system. Worse, some button images are so small or so numerous that it is hard to see the icons well enough to discern what they mean.
B. Property Inspectors
1. Introduction
Overcoming this problem, the present invention provides "property inspectors" for inspecting and setting the properties of objects. Property inspectors of the present invention examine an object of interest to the user and tell the user what can be done to it. In this manner, the present invention requires the system and its interface components (e.g., menus or dialog boxes), not the user, to know what functions are being sought or may be of immediate interest to the user. Moreover, property inspectors of the present invention require the program to present functions to the user when and where he or she requests them, rather than making the user hunt for them when they are needed. Thus, the user need only know which data item or object he or she wants to inspect or manipulate.
In the spreadsheet notebook, for example, there are many kinds of objects which one can use. Examples include cells, blocks of cells, pages, notebooks, graphs and their elements (including bars or axes), dialog boxes, and even the application program itself. Each of these objects has properties, which are characteristics of that particular object. For example, blocks of cells have a font property that can be set to bold, so that the text of entries in the block appear in boldface type. A property of a workbook page, on the other hand, is the name that appears on its tab. Each notebook has its own palette property for controlling available colors. Application properties, for instance, include system defaults, such as a default storage directory or file extension. Thus in any system, the user has a variety of objects available, each of which has its own set of properties.
Property inspection of the present invention provides the user with immediate access to an object's properties. If the object of interest is a spreadsheet cell, for example, the property inspector of the present invention produces a menu that includes font, color, border, size, and other display properties, along with formula properties which may be modified. If, on the other hand, the object is a graph, the property inspector will display a menu to change its color, shape, borders, and other features of a spreadsheet graph. Moreover, inspection menus are state or context-sensitive, i.e., constructed on-the-fly to reflect the current state of the object. If an object's properties change so that what a user can do to it also changes, the property inspector of the present invention will create a new and appropriate menu reflecting those changes.
A variety of user events, including keyboard and mouse events, may be employed to invoke property inspection and setting. In a preferred method of the present invention, however, an object is inspected by selecting the object with a screen cursor, i.e., clicking (generating a mouse event) on the object. Since, according to the present invention, property inspection may be available across different modes of operation (i.e., generally available at all times), the generated mouse event or signal is preferably one which does not launch or otherwise invoke routine object actions. Instead, the mouse signal should be one which the user will easily associate with inspection of an object. In a two or three mouse button embodiment of the present invention, therefore, the generated mouse signal is most preferably from the lesser-used or right mouse button (e.g., Windows' WM.sub.-- RBUTTONDOWN). In this manner, the user associates object actions with left button signals and object inspection with right button signals.
2. Exemplary Embodiments
Referring now to FIGS. 5A-E, the inspecting and setting of object properties, in accordance with the present invention, is illustrated. In FIG. 5A, a notebook application window 500 is shown. Window 500 includes a variety of objects which may be inspected by the property inspector of the present invention. The top-most object is the application itself: application object 510. By selecting the application object (e.g., by right mouse clicking on the application title bar), various properties for the application may be inspected and set. As shown in FIG. 5B, inspection of the application object 510 invokes application inspector 515, which lists the various properties of the application object; the user may now inspect and set the properties of the object, as desired.
Referring back to FIG. 5A, the next level which may be inspected is the notebook object 520. User inspection of this object, e.g., by right mouse clicking on the notebook title bar, invokes the active notebook property inspector 525 of FIG. 5C. In a manner similar to the application property inspector, the notebook property inspector 525 includes all the properties and corresponding options which may be set for the notebook 520.
Notebook object 520 includes a plurality of page objects, which may themselves be inspected. To inspect the page object 550, for example, the user brings the page tab into view (if it is not already displayed), then right clicks the tab page. This action invokes the active page property inspector 535, as shown in FIG. 5D. With access to page properties, the user can control the page name, overall page protection, line color, colors in cells that meet certain conditions, default label alignment, whether zeroes are displayed, default column width, row and column borders, gridline display, and the like. To assign a more descriptive name to a page, for example, its tab is inspected and the option "NAME" is selected from the page property inspector (or, as shown, it is selected by default). After the user enters a new name, the new name appears on the page tab. Other page properties, most of which affect the appearance of data or screen elements, may be set in a similar manner.
Each page object may also contain other objects, including cell, blocks of cells, graph objects, and the like, each of which may be inspected. By right clicking the active block 530, the active block property inspector 545 is opened as shown in FIG. 5E. With block properties, the user can change alignment of entries, numeric format of values, block protection, line drawing, shading, font, text color, types of data allowed in a cell, row height, column width, and whether columns or rows are revealed or hidden; inspection of a single cell occurs in a similar manner (i.e., active block is one cell). In this manner, the user may select block properties which affect the appearance of cell entries or of rows or columns; additionally, properties which affect the way data can be entered into blocks, such as protection and data entry input, may be made.
Upon invocation of property inspection, a dialog box or property inspector is displayed, preferably at or near the object of interest. A different property inspector appears depending on the type of object which is inspected. At this point, the user may then select the property that he or she wishes to change from a list of object properties. The contents of the right side of the inspector (i.e., a "pane" within the inspector) change to correspond to the property one chooses, as shown. Then, the user chooses settings for the current property. If desired, the user can then choose and set other properties for the current object. In a preferred embodiment, the property inspector includes an example box, which shows the display result of the property the user has just set, before they are applied to the actual object.
Referring now to FIGS. 6A-K, the construction and operation of a property inspector dialog box, in accordance with the present invention, is described. For purposes of illustration, the following description will present active block property inspector 600; other inspector dialogs may be implemented in a similar fashion. Shown in FIG. 6A, the active block property inspector 600 includes an object property list 601 and property settings pane 602. For an active block, for example, relevant properties include Numeric Format, Font, Shading, Alignment, Line Drawing, Protection, Text Color, Data Entry Input, Row Height, Column Width, and Reveal/Hide. Property settings pane 602 include the valid options or settings which a property may take. For the property of numeric format, for example, valid settings include fixed, scientific, currency, and the like. Property setting pane 602 may further include subproperties. For example, the currency setting 602a shown may also include a decimal place setting 602b. In this manner, property inspector dialog 600 presents a notebook dialog: property list 601 serves as notebook tabs (e.g., dialog tab 601a) and property settings panes 602 serves as different pages of the notebook dialog. Thus, the user may access numerous dialog options with the efficiency of a notebook.
Also shown, property inspector dialog 600 also includes an example window 605 and dialog controls 603. As new properties are selected and set, their net effect is shown. The example element in example window 605, for example, shows the effect of selecting a numeric format of currency with two decimal places. Thus, the user may experiment with changes before they are applied to the data object. After desired property changes are entered, control components 603 are invoked to either accept ("OK button") or reject ("CANCEL button") the new property settings; if desired, help may be requested.
As shown in FIGS. 6B-K, other properties of a cell block are accessed by selecting the desired property from the property list 601. Thus the active blocks property inspector 600 changes to inspector 620 for font properties, inspector 625 for shading properties, inspector 630 for alignment properties, inspector 635 for line drawing properties, inspector 640 for cell protection properties, inspector 645 for text color properties, inspector 650 for data entry input properties, inspector 655 for row height properties, inspector 660 for column width properties, and inspector 665 for reveal/hide properties. In each instance, a new pane (i.e., one having the attributes of interest) is displayed in the inspector window.
Referring now to FIGS. 7A-H, the inspection (and setting) of properties for graphs is illustrated. Graph window 700 includes a plurality of graph objects, each of which may be customized through use of a corresponding property inspector of the present invention. To display a corresponding property inspector, the user invokes the inspector (e.g., right-clicks) for the part (object) of the graph he or she wishes to change. A right-click on the graph window object 710, for example, will invoke the graph window inspector 715; at this point, the user may inspect and set various properties of the graph window object. In a similar manner, other objects of the graph window may be inspected. For example, inspection of graph background 720 invokes inspector 725, Y-axis object 730 invokes inspector 735, X-axis 740 invokes inspector 745, area fill object 750 invokes inspector 755, bar series object 760 invokes inspector 765, and bar series object 770 invokes inspector 775.
Internal Oderations
A. Introduction
Internal operations of the system 100 will now be described in detail. In general, operation is driven by methods of the present invention, which are invoked by Windows' message dispatcher in response to system or user events. The general mechanism for dispatching messages in an event-based system, such as Windows, is known in the art; see, e.g., Petzold, C., Programming Windows, Second Edition, Microsoft Press, 1990. Additional information can be found in Microsoft's Window Software Development Kit, including: 1) Guide to Programming, 2) Reference, Vols. 1 and 2, and 3) Tools, all available from Microsoft Corp. of Redmond, Wash. of particular interest to the present invention are class hierarchies and methods of the present invention, which are implemented in the C++ programming language; see e.g., Ellis, M. and Stroustrup, B., The Annotated C++ Reference Manual, Addison-Wesley, 1990. Additional information about object-oriented programming and C++ in particular can be found in Borland's C++ 3.0: 1) User's Guide, 2) Programmer's Guide, and 3) Library Reference, all available from Borland International of Scotts Valley, Calif. The disclosures of each of the foregoing are hereby incorporated by reference.
B. Notebooks
Referring now to FIG. 8A, the internal structure and operation of spreadsheet notebooks of the present invention will be described. The spreadsheet notebook system of the present invention includes a system hierarchy 800, at the top level, having a notebook table 805 with entries or slots for accessing a plurality of notebooks. For example, the first slot addresses or points to notebook 810. In this manner, the system may track multiple notebooks.
Notebook structure 810, in turn, includes or accesses various data members for a particular notebook. For example, a "Name" field, stores the name assigned for the notebook. This name is displayed in the titlebar for the notebook and is used as the name for the corresponding disk file; the notebook name is also used to reference the notebook in formulas (e.g., contained in other notebooks). Notebook 810 also includes other data members, such as block names and fonts. Block names are text strings or labels which the user has defined for particular blocks of interest. Fonts, on the other hand, include font information (e.g., type and size) for the notebook. Other desired properties, specific to a notebook, may be included as desired. Additional description of the construction of a notebook structure (class) is set forth in commonly-owned U.S. Pat. No. 5,416,895.
Of particular interest in the notebook structure 810 is the Pagetable field, which includes a pointer to a page table for the notebook. Pagetable 815 includes a plurality of slots or entries for accessing individual page structures of the notebook. Each page structure, in turn, contains or accesses information of interest to an individual page. As shown, for example, page 820 (pointed to by the first slot of the pagetable) includes or accesses: a sheet table (or Graphs page), a pagename, floating objects (e.g., graphs and buttons), page properties, pane, and the like.
The Sheettable field of the page 820 points to a sheet table 825. It, in turn, includes a plurality of the slots for accessing different column strips. As shown, for example, the first entry in the sheet table 825 accesses a column strip of cells 830. In turn, column strip 830 addresses individual information cells, each of which may have one of a variety of information types, including empty, integer, number (real), formula, label, and the like. In a preferred embodiment, column strip 830 may address up to 8,000 cells; those skilled in the art, however, will appreciate that column strip 830 may be set to any desired range (depending on the limits of one's target hardware).
Referring now to FIGS. 8B-C, the function of the page table of the present invention is illustrated. In FIG. 8B, two images are presented: a view image and a data image. The view image illustrates what the user sees on the screen 106; at startup, for example, only a single Page A (page850) is active. As shown by the corresponding data image supporting this view image, pagetable 855 includes only a single reference, page A. In turn, page A references sheettable.sub.A, which supports the information cells required (e.g., they are currently needed for display) for the page. Thus, at startup, only a single page entry exists in the pagetable 855, even though the screen displays multiple page tabs.
Upon selection of Page F (e.g., by clicking tab F 860), the data image changes, however. As shown in FIG. 8C, remaining Pages B-F are initialized into the page table 865. In this example, Page F must now exist as a view object. Thus, Page F references supporting data structures (e.g., sheettable.sub.F). The intervening Pages (B-E), on the other hand, are initialized, but only to the extent that they can serve as place holders in the pagetable 865. In this manner, the underlying page table for a notebook instantiates underlying page data structures only as needed (e.g., for viewing or referencing by other pages), but, at the same time, provides on-demand access to pages.
A particular advantage of this design is the ease in which information may be referenced and presented. Even complex data models, such as those spanning multiple dimensions, may be represented in a clear fashion. Moreover, a syntax is provided by the present invention for intuitively referencing information.
Referring now to FIGS. 8D-F, the referencing of information in a spreadsheet notebook of the present invention is now described. Shown in FIG. 8D, a three-dimensional reference (i.e., identifier for a solid block of information cells) includes a notebook, starting page, ending page, starting cell and ending cell. As shown, the notebook (which is the same as the file name) is identified preferably by delimiters, such as brackets ([]). This is followed by page name(s), which may in fact be grouped. As shown, a range of pages has been defined from '89 Income to '92 Income; these are alias names which may correspond to pages A-D, for example.
Next, one or more cells of interest are identified. For example, a range from January to (. . . ) December is shown, which serve as aliases for blocks A1 to A12, for example. The block or cell information is separated from group or page information, for example, by a colon (:) identifier.
The hierarchy of this nomenclature is shown in FIG. 8E. Specifically, a notebook references pages, which may be members of one or more user-defined groups. In turn, a page references cells, which may alternatively be referenced by alias identifiers. As shown in FIG. 8F, page and cell identifiers may be grouped to form even more succinct references. For example, Pages '89 Income to '92 Income may be renamed as Four-Year Income. Similarly, the cell range from January to December may be renamed Entire Year. In this manner, information ranges in the spreadsheet notebook of the present invention are easily named and easily visualized by the user.
Depending on the context of the system, certain identifiers may be assumed and, thus, eliminated from a reference. Unless otherwise specified, for example, the notebook identifier is assumed to be the currently active notebook. Similarly, the page identifier may be assumed to be the currently active page, unless the user specifies otherwise. Thus, a valid reference need only include as much information (identifiers) as is necessary to access the desired information.
Referring now to FIG. 9A, a method for interpreting or parsing an information reference, in accordance with the present invention, is shown. Upon receiving a reference (e.g., text string) to information in a spreadsheet notebook, the method proceeds as follows. In step 901, a loop is established to tokenize (i.e., parse) the string into individual identifiers, which may then be processed. Each token is examined as follows. In step 902, the token is checked to determine whether it is a notebook identifier. In a preferred method, a notebook identifier is delimited, for example, by bracket symbols; alternatively, a notebook identifier may be realized simply by the position of the identifier relative to other tokens in the string, or by its relationship to disk (notebook) files on mass storage 107. If the notebook is successfully identified, the notebook identifier is returned at step 903, and the method loops back to step 901 for any additional tokens.
If it is not a notebook (no at step 902), however, then in step 904 the token is examined to determine whether it is a group name. Group names may be determined by accessing group names listed for the notebook (e.g., by accessing a group name string pointer from notebook structure 810), and/or by the token's context (e.g., preceding a ":" delimiter). If a group name is identified, it is returned at step 905, and the method loops for any remaining tokens. Otherwise (no at step 904), the token is examined to determine whether it is a page. In a manner similar to that for checking group names, a page may be identified by examining a notebook's page table, with corresponding page name accessed (dereferenced). The page is returned at step 907, with the method looping back to step 901 for remaining tokens.
If a page is not found (no at step 906), however, then at step 908 the token is examined to determine whether it defines a range. A range may include a named block of cells (e.g., "entire year") or, simply, two cell addresses separated by an appropriate identifier (e.g., A1 . . . B1). If a range is found, then it is returned in step 909, with the method looping for any remaining tokens. Otherwise (no at step 908), the identifier is examined to determine whether it is a cell. A token may be identified as a cell if it is of the appropriate column/row format (e.g., A1). If a cell is found, it is returned at step 911, with the method looping for any remaining tokens.
As shown (conceptually) at step 912, if any ambiguities remain, they may be resolved according to an order of precedence; for example, notebook>groupname>page and the like. At the conclusion of the method, a reference, if it is in proper form, will be successfully identified and may now be processed according to three-dimensional techniques.
Referring now to FIGS. 9B-C, other methods of the present invention are illustrated. In FIG. 9B, for example, a drag-and-drop edit method 920 of the present invention is shown. Its steps are as follows. In step 901, a block of cells is defined (e.g., by dragging a mouse cursor from one corner of the block to another). After a block has been selected, it is next grabbed with the mouse cursor (i.e., position the mouse cursor within the block and depress a mouse button). In steps 923 and 924, a loop is established to "drag" the block to a new location. In particular, while the mouse button is depressed, any movement of the mouse will cause the block to follow (animated on screen, e.g., by XOR technique). At step 925, the block is "dropped" into place by releasing the mouse button (button up signal). Dropping the block causes information from the source location to be copied into the target location. By coupling the method with a status key (e.g., SHIFT-- or CTRL--), the technique supports either "cut" (erase original) or "copy" (leave original) modes of operation. Thus, for example, if a status key is not active as step 926, then in step 927 the original (at the source location) is deleted. Otherwise (yes at step 926), the original remains at the source location as well.
Referring now to FIG. 9C, a model copy method 940 of the present invention is illustrated. In step 941, a block is defined or selected (e.g., dragging a selection). In step 942, model copy is enabled or disabled (as desired); alternatively, model copy may be enabled by default. In step 943 if model copy has been enabled, then in step 945 absolute address references are copied as if they were relative address references, as previously described (with reference to FIGS. 4H-J). However, the address labels will remain absolute, so that they will be treated as absolute for future copying operations. Otherwise (no at step 943), absolute addresses are treated conventionally (i.e., referencing absolute addresses) in step 944. As shown in step 946, relative addresses are not affected, i.e., they continue to be treated relatively. In step 947, the copy operation is performed, employing the addresses as just determined, after which the method concludes.
C. Property Inspection
Referring now to FIGS. 10A-C, construction and operation of property inspection in accordance with the principles of the present invention will be described. As shown in FIG. 10A, user interface (UI) objects are constructed from a UI object class hierarchy 1000. As shown, class hierarchy 1000 includes as its base class an object class 1001. From object class 1001, a responder class 1002 is derived. As the child of class 1001, responder class 1002 inherits all properties of its parent and adds event handling capability. Both object class 1001 and responder class 1002 are abstract classes, i.e., objects are not instantiated from them directly. From responder class 1002, two child classes are derived: view class 1003 and window class 1004. From view class 1003, all screen objects (e.g., text, graphs, scrollbars, and the like) are created. From window class 1004, container objects are instantiated; in particular, window class 1004 provides container objects which hold the various view objects.
Associated with each class (and thus objects derived from each class) is a ClassInfo structure. ClassInfo, which is itself implemented as a separate class, contains information about a corresponding class and, thus, objects of that class. For example, it contains information about object type, name, number of properties, and the like. Of particular relevance to property inspection are two data members: a pointer to the parent and a property record pointer which points to an array of property records for an object of interest.
Referring now to FIG. 10B, the relationship between parent and child (and hence the importance of the pointer to the parent) is illustrated. In the system of the present invention, an object-oriented mechanism is employed whereby new objects may be created from existing objects (classes). As shown, for example, a child object 1030 may be derived from a parent object 1020. Also shown, each object has its own property record array or list. For example, the parent object 1020 has a property list 1025 describing its properties. Child object 1030, in turn, also has its own property list 1035; child object 1030 still inherits from parent object 1020, however. By means of the parent pointer, the child object 1030 also has direct access to its parent 1020 and, thus, the property list 1025 of the parent. In this manner, when an object is inspected, the system of the present invention may view back across the inheritance hierarchy to fetch all (ancestor) properties for the object, as needed.
The property record, on the other hand, describes an individual property for the object. The property record, which is implemented as a separate class, includes a name ID for the property and a pointer to the property object itself (which may be shared). For example, property record objects may be instantiated from the following exemplary C++ class definition:
__________________________________________________________________________
class .sub.-- EXPORT.sub.-- PropRecord
public:
Property * pProp;
// pointer to SHARED (!) property object
WORD nameID;
// property name string ID
// (PROPSTR.HPP, PROPSTR.CPP)
WORD flags;
// optional information about the property
// ( HIDDEN, USE MENU, LINK ONLY, etc.)
inline Property * getProp ( );
inline LPSTR getName ( );
inline PROPTYPE getType ( );
inline WORD getNamesCnt ( );
inline WORD getNameID ( );
inline WORD getFlags ( );
};
__________________________________________________________________________
The property object, in turn, includes a type ID for identifying the property and also includes a pointer to a dialog for the property. An property object may be instantiated from the following exemplary C++ class:
__________________________________________________________________________
class .sub.-- EXPORT.sub.--
Property
public:
static DWORD dwPropErr;
static char far conversionBuffer [MAXPROPSTR];
PROPTYPE typeID;
WORD namesCnt;
LPSTR pDialog;
Property ( WORD id );
virtual BOOL stringToValue ( LPSTR, PROPTYPE pt = 0 );
virtual BOOL valueToString ( LPSTR, PROPTYPE pt = 0 );
/*
Convert the passed binary value to string using the conversionBuffer
*/
LPSTR convertToString ( LPSTR pS, PROPTYPE pt );
/*
Convert the string in conversionBuffer to binary value pointed by pS
*/
BOOL convertToBinary ( LPSTR pS, PROPTYPE pt );
};
__________________________________________________________________________
Exemplary property types may include:
______________________________________
Exemplary property types may include:
prop.sub.-- Undefined = 0,
prop.sub.-- Text,
prop.sub.-- Bool,
prop.sub.-- Window,
prop.sub.-- Color,
prop.sub.-- Bitmap,
prop.sub.-- List,
prop.sub.-- Word,
prop.sub.-- Int,
prop.sub.-- Key,
prop.sub.-- Double,
. . .
______________________________________
The pointer to the property dialog (LPSTR pDialog), on the other hand, indicates the appropriate dialog to be displayed to the user for the current property; the dialog displays the appropriate information (e.g., pane) for the property which the user may then use for inspecting and setting attributes of that property.
Referring now to FIG. 10C, a method 1050 for property inspection, in accordance with the present invention, is illustrated; additional disclosure is provided for the inspection of an exemplary object: a spreadsheet cell. In step 1051, the user indicates that he or she desires property inspection for an object. In a preferred method of the invention, the user will right-mouse click on the object of interest (as set forth hereinabove). To inspect a spreadsheet cell, for example, the user would position the mouse cursor over the cell and click the right mouse button. In this instance, the notebook which contains that cell will receive (i.e., will be the recipient of) a right-mouse button down message. The event will be processed by routing the message to a RightMouseDown method.
In step 1052, the object which the user clicked on is identified by the RightMouseDown method. Specifically, the method invokes a RouteMouseEvent method which returns the current view object (i.e., the object which the user has clicked on). This is accomplished as follows. Every view object contains a StartInspect method which returns an actual object for inspection. The object which appears on the screen to the user (i.e., view object) is not necessarily the object which will be inspected. For example, it would be computationally too expensive to have each cell of a spreadsheet be its own object. Instead, the present invention embraces the notion of surrogate or imaginary objects which may be inspected (using the properties of the screen or view object selected). In most instances, the StartInspect method will return an object for inspection which is the same as the view object. In those instances where it is not feasible to inspect an object which the user perceives on a screen, however, the method returns a surrogate object whose properties assume the attributes of the screen object.
An additional example illustrates this point. When inspecting a block of cells, for example, StartInspect returns a current block object which is a surrogate object, i.e., it does not have a visible expression on the screen. Instead, it is a substitute object which assumes the characteristics necessary to allow inspection of the screen object of interest. In this manner, it functions as a surrogate for the object which the user observes on the screen. If desired, objects may also be blocked from inspection at this step (e.g., by setting flags); in which case, the method terminates. At the conclusion of step 1052, the object of interest, either actual or surrogate, is ready for inspection.
Next, the user interface for inspection is built by a DoUI method of the present invention. The method proceeds as follows. The first property record for the object is accessed in step 1053. Preferably, DoUI is a virtually defined method, with each object (class) designed to include method steps for accessing the property record according to that object's own particularities. The remaining property records for the object are also located (e.g., by traversing a list of records).
At step 1054, the dialog panes for each property are loaded (e.g., into memory 102, if not already present) for use by a compound dialog box. As previously described, the dialog associated with each property is accessible from the property record (by a pointer to the dialog). At this point, an empty property inspection window is displayed. Into the property inspection window, a corresponding pane for each property is loaded (substep 1054b), thus creating a display hierarchy. Using a getProperty method, each property is initially set to the attribute(s) contained in the retrieved property record; the setProperty method operates essentially the reverse of a setProperty method, which is described in detail hereinbelow. An associated screen button or label is provided for rapidly accessing a desired pane in the inspector window, in much the same fashion as one accesses pages in a notebook with tabs. In this manner, a property inspector is built from a dynamic list of properties (as opposed to a static list of properties which is determined beforehand), each of which may be rapidly accessed by the user.
While the property list loaded into an inspector window for each object is dynamically constructed, the panes supporting each property may be pre-built. For example, the pane supporting a color palette, since it is complex in design, will be built in advance.
However, the method of the present invention may dynamically build individual panes as well. Candidates for dynamic pane building include simple properties, especially those which may have only a Boolean or yes/no value. Referring back to FIG. 7B, for example, inspector 715 includes a snap-to-grid property. Instead of loading a preconstructed pane for this property, the method of the present invention dynamically constructs an appropriate pane (in this case, a simple check box), as shown in substep 1054a. An automatic list, on the other hand, is typically a simple group box (i.e., one having a plurality of radio buttons), which is preferably constructed dynamically, as shown by the inspector window 650 of FIG. 6H. In either case, the method may build appropriate inspector dialog components on-the-fly by simply examining a particular property and discerning its possible attributes. In a similar manner, "pick" lists of properties may be constructed and displayed, as shown in substep 1054c. A property pick list serves as an index to other property lists or other functions, as desired. By dynamically building inspectors for simpler properties, the method conserves system resources.
Construction of the inspector window is completed by inserting dialog controls (e.g., "OK", "CANCEL", and "HELP") for operating the dialog. In addition, an example window is displayed for indicating various changes to the properties of an object. This is accomplished by sending a message to the inspected object setting forth the current properties in the dialog; the inspected object returns an example or sample object which may then be displayed in the window. After changing a property, the dialog tab or button (e.g., tab 601a of FIG. 6A) corresponding to that property is updated (e.g., different font or screen color) for indicating that a change has been entered.
After constructing the property inspector dialog or window, at step 1055 the method enters a user event loop where the user inspects and sets various properties of the object by accessing the property (through the screen button or tab) and setting a new attribute. At the conclusion of the user event (e.g., by selecting "OK" or "CANCEL"), the user event is terminated.
At step 1056, the property list for the object being inspected is updated for any changes which occurred to the properties during the user event (of step 1055). This is accomplished by calling a setProperty method, which conceptually loops through each pane which has changed and updates the property list, accordingly. By way of illustration, the setProperty method may be defined as:
__________________________________________________________________________
virtual BOOL setProperty( LPSTR lpPropStr, LPSTR lpValueStr,
PROPTYPE pt = 0 );
__________________________________________________________________________
The method receives the name of a property, either passed as a text string (lpPropStr) or as a handle (pt), and a value for that property, typically passed as a text string (lpValueStr).
Referring now to FIG. 10D, the general steps of a setProperty method 1070 are illustrated. In step 1071, the property text string is translated; alternatively, the property is referenced directly by passing a handle (i.e., integer or word value). At steps 1072-1073, the property is updated for the property value passed (e.g., by a CASE statement construct). If the property is not acted upon, it may be passed up the object's inheritance chain for action by an ancestor object, at step 1074. In this fashion, the values collected from the various property panes are used by the method to set the properties within the object.
While the foregoing outlines the general steps or framework for setProperty, the method is in fact defined as a virtual method. Thus, each class of objects may assume responsibility for setting its own properties. For an Annotate object (i.e., screen objects found in Graph windows), for example, an exemplary setProperty method may be constructed as follows:
__________________________________________________________________________
BOOL Annotate::setProperty ( LPSTR lpPropStr, LPSTR lpValueStr, PROPTYPE
pt )
WORD w;
if ((w = info.propIndex (lpPropStr, pt)) < annoEndProps) {
if (info.ppProps [w].getType( ) > prop.sub.-- Compound)
switch (w) {
case annoFrame:
getFrame (GlobalFrameProp.x,
GlobalFrameProp.y,
GlobalFrameProp.w,
GlobalFrameProp.h);
break;
}
if ((info-ppProps [w].getProp( ))-->StringToValue (lpValueStr,
pt)) {
BOOL f = TRUE;
switch (w) {
case annoFrame:
setFrame (GlobalFrameProp.x,
GlobalFrameProp.y,
GlobalFrameProp.w,
GlobalFrameProp.h);
break;
case annoHidden:
if (GlobalYesNoProp.index)
hide (TRUE);
else
show (TRUE);
break;
case annoShow:
if (GlobalYesNoProp.index)
show (TRUE);
else
hide (TRUE);
break;
case annoId:
if (pCW && pCW-->infoPtr( )-->objType ==
ot.sub.-- Dialog
f = ((DialView
*)pCW-->getContentView( ))-->setIdx (this, GlobalWordProp.w);
else
idx = GlobalWordProp.w;
break;
case annoAttach:
flags.nochild = !GlobalYesNoProp.index;
break;
case annoPosition:
*(WORD *)&flags = (*(WORD *)&flags
& .about.(vfPOSITION .vertline. vfLEFTREL .vertline.
vfTOPREL .vertline. vfRIGHTREL .vertline. vfBOTTOMREL
.vertline. vfTOPTYPE .vertline. vfRIGHTTYPE))
.vertline.
GlobalPositionProp.flags;
flags.bottomType = flags.topType;
flags.leftType = flags.rightType;
break;
}
if (f)
callConnection (lpPropStr, lpValueStr, pt);
return f;
}
else
return FALSE;
}
else
return Tracker::setProperty (lpPropStr, lpValueStr, pt);
}
__________________________________________________________________________
As shown, the override method processes properties specific for Annotate (e.g., change frame, ID, position, and the like). In the event that a property is not identified, the property is passed up the inheritance chain for appropriate processing (by a parent's setProperty method). In this manner, an individual object (e.g., an Annotate object) in the system is designed to know how to get (getProperty) and set (setProperty) its own properties.
After the update of step 1056, the method 1050 concludes. At this point, the system is ready for another inspection. Alternatively, the user may undertake other activities, as desired.
Building Spreadsheet Applications
A. Basics
The system of the present invention includes a spreadsheet application development module for building custom applications operative in an electronic spreadsheet. An application is a combination of components which work together to simplify an end user's task. When building an application using the system of the present invention, the user (in a role as developer) can create custom components, including dialog boxes, toolbars, menus, and the like. After creating these components, the user can assemble them into an integrated application notebook which comprises all of the programming logic and user interface components for the spreadsheet application.
For purposes of the following description, it is necessary to differentiate between a developer user ("developer") and an end-user ("user"). A developer, as used herein, refers to the person who is building a spreadsheet application. A user, on the other hand, refers to the person who runs a completed application, that is, one who is interested in using the application to perform tasks.
B. Sample application: Budgeteer
The spreadsheet application development module of the present invention is perhaps best described by introducing an example of a spreadsheet application. FIG. 11A illustrates a sample application 1100, called the Budgeteer. From examining the application, one should note differences between the application's user interface (UI) and that of the system's UI. For instance, the application UI 1100 displays the title "Budgeteer" in the title bar 1101 of the interface. Also, the menu bar 1103 includes menu commands which are entirely new; similarly, a custom toolbar 1105 resides below the menu bar 1103. Finally, the spreadsheet application includes a custom dialog box 1107, as shown on screen.
The Budgeteer application menu bar provides commands to perform global operations like saving budget files, printing, and displaying statistics. For example, with the Budget menu commands one can create a new budget; open an existing budget file; save current data to the budget file; write current data to a different budget file; and creates a new expense column; and exit the application and restores standard system settings. A Print menu is included to print the contents of the active budget file. One can also use Print.linevert split.Current to print the records currently displaying in the record window, or All to print the contents of all expense databases in the active budget file. A Statistics command displays statistics on the records displaying in the record window, including the total expense cost, average expense cost, greatest cost, and smallest cost.
The toolbar of the Budgeteer application operates as follows. When the Budgeteer is active, the standard system toolbar is replaced by the Budgeteer toolbar 1105, which lets users display the precise view of the data they need. It includes the following commands:
(1) View specifies the database to display in the record window. One can use Month, Day, and Year to search for and display specific records.
(2) Month lets the user display records entered during a specific month. For example, if set to June it will display any records entered for June. If Month is set to All, all months may be viewed.
(3) Day lets one display records entered on a specific day of the week. For example, if it is set to Tuesday it will display any records entered on a Tuesday. If Day is set to All, all days may be viewed.
(4) Year lets the user display records entered during a specific year. For example, if Year is set to 1994 any records entered during 1994 will be displayed. If Year is set to All, all years may be viewed.
(5) Add adds a new record to the expense database being viewed. A new record appears only if it meets the search conditions specified by the Toolbar.
(6) Graph creates a graph using the records in the record window. Each expense is plotted as a separate series.
Spreadsheet application construction
A. Design components
The general process of building a spreadsheet application, such as the Budgeteer application, will now be described. In a preferred embodiment, a spreadsheet application is constructed within a notebook. In particular, its major modules are organized by notebook page. For instance, the Budgeteer application can be organized into the following pages.
______________________________________
Exemplary Pages used in the application
Page(s) Purpose
______________________________________
Startup This page (shown in FIG. 11B) contains
the macro that changes the system UI and
runs the Budgeteer. It also contains
the macro that closes down the
Budgeteer.
Menus This page (shown in FIG. 11C) contains
the menu block that defines the
Budgeteer menu bar.
Help This page contains help messages
displayed by the Budgeteer, and
developer notes.
Task.sub.-- Macros
These pages contains macros specific to
a Budgeteer function. For example,
ChangeDB.sub.-- Macros (shown in FIG. 11D)
contains macros to change expense
databases; Query.sub.-- Macros contains the
macros to search expense databases.
Expense.sub.-- View
This page is the record window that the
user sees. It is in the budget file.
Stats This page is the window that the user
sees when he or she chooses Statistics.
It is in the budget file.
Input This page contains the form that users
manipulate to add records. It is in the
budget file.
Daily . . . Annually
These pages contain the expense
databases when the Budgeteer is running.
Each page contains one database (stored
in the budget file).
Graphs This page contains the dialog boxes used
by the Budgeteer.
______________________________________
The developer can use macros to assemble these components into an application. One can also use macros to automate system operations, change the system's appearance, and display dialog boxes. Link commands (described below) are also provided to assign actions or connections to controls in a dialog box or a toolbar. A link command can get or change an object's property settings, or run macros.
Specific-purpose user interface components which are provided include menus, dialog boxes, and toolbars. First, menus provide custom lists of operations the user can choose from the menu bar. Next, dialog boxes prompt users for information and let the user make choices. Finally, toolbars give users shortcuts to common application tasks and perform operations that affect the active data; they also display information about the active window or object. The general operation and construction of each will next be described.
Menu commands can initiate actions, run macros, display dialog boxes, or display other menus. To create a menu, the developer describes the menu with a block of labels. The following table lists macro commands provided by a preferred embodiment for modifying menus, menu commands, and the menu bar:
______________________________________
Defining a custom menu
Macro command Description
______________________________________
{DELETEMENU} Removes a menu from the
current menu bar (including
submenus like Tools.vertline.Macro).
{DELETEMENUITEM} Removes menu commands from
the specified menu.
{ADDMENU} Adds names to the current menu
bar.
{ADDMENUITEM} Adds menu commands to the
specified menu.
{SETMENUBAR} Replaces the active menu bar
with the menu described by the
block.
{SETOBJECTPROPERTY}
Changes existing menu
commands.
______________________________________
For example, FIG. 11C shows some of the labels that define the Budgeteer's menu bar. The Budgeteer application uses the command {SETMENUBAR} at startup (see FIG. 11B) to replace the standard menu bar with the items described in this block. As seen in column C (1120) of FIG. 11C, each menu command has one or more link commands in its block. These link commands determine what action occurs when the menu command is chosen. Command names that appear in column B appear as items on the menus defined in column A.
Spreadsheet applications of the present invention employ dialog boxes for displaying information and receiving user input. A dialog box is a box of options that a developer creates to prompt the application user for information. As shown in FIG. 11E, a dialog box can contain various controls, such as check boxes and edit fields. Upon selection of a Tools.linevert split.UI Builder command by the developer, the system displays an empty dialog box 1130 within a dialog window, as shown in FIG. 11F. By default, new dialog boxes contain an OK button and a Cancel button, to provide a consistent way for the user to close the dialog box. Also, a default name is automatically assigned. When one is creating a dialog box in a dialog window, it looks slightly different from how it will appear to the user in a completed application. FIG. 11G shows a dialog box 1150a as the user sees it and as the developer creates it (dialog 1150b).
The dialog window is the dialog box in an editable form; it contains all the Windows user interface elements for a window: a window border, a menu-control box, and Minimize and Maximize buttons. In the dialog window, the name of the dialog box (e.g., LOANPMT.WB1:LoanData) appears in the title bar. The developer uses this name in a macro to display the dialog box or change its property settings. This name is different from the title of the dialog box ("Enter Loan Information"), which is what the user sees when the application runs. In contrast, toolbars do not have titles, but they do have names (which, like dialog box names, appear in the title bar).
The system of the present invention includes a dialog window toolbar 1160, shown in FIG. 11H, which contains tools that are specifically designed to help the developer build dialog boxes and toolbars. It includes tools which let the developer quickly create, copy, move and test controls. Controls are elements the developer puts in a dialog box to gather information from the user or to perform a desired action.
The Cut, Copy, and Paste buttons 1161 are shortcuts for Edit.linevert split.Cut, Edit.linevert split.Copy, and Edit.linevert split.Paste, respectively. The developer can copy a control to the Windows Clipboard to duplicate it or to move it to another dialog box or toolbar. The Test button 1162 tests the operation of a dialog box (or toolbar). In Test mode, the dialog box controls behave exactly as they will when the application runs. The Selection tool 1163 lets the developer select a control in the dialog window to move it, resize it, change its properties, and so forth. The developer can select several controls at a time to move or size them together. The remaining tools on the toolbar let the developer add controls to a dialog box; each functions as shown in FIG. 11H.
The general steps for creating a custom dialog box (or toolbar) may be summarized as follows. First, the developer chooses a Tools.linevert split.UI Builder command; a dialog window appears. Next, a name for the dialog box is entered. Later, the developer may use this name in a macro to make the dialog box appear. As the third step, the developer enters a title for the dialog box; the title is what the user will see. Any desired controls are added to the dialog box. The developer customizes each control, as necessary. The dialog box can also be resized, if desired. A command to invoke the dialog is created by inserting in the notebook a {DODIALOG} command (described below); the command calls the dialog box and passes initial defaults to it. The developer adds a link command to each control, as desired. A link command is a command which indicates what should happen when a user manipulates that control in a specific manner. By selecting the Test button, the developer may test the operation of each control during dialog box construction. Finally, the dialog box is saved by saving its notebook.
The system of the present invention offers a variety of different controls. Each control is best suited for presenting (or receiving) a certain type of information. A control displays a setting and provide a way for users to change that setting. The following is a summary description of the exemplary controls provided by the system of the present invention.
(1) Push Button. A push button usually performs a specific action when the user clicks it. The developer determines what that action is.
(2) Check Box. Check boxes present a Yes/No choice to the user. The user checks the check box to accept the choice. Check boxes act as toggles; when checked again, they "uncheck"--the choice is no longer accepted.
(3) Radio Button. Radio buttons (also called option buttons) are usually grouped to give the user a mutually-exclusive list. When a user clicks a radio button, its diamond darkens to indicate that it is chosen.
(4) Label. A label clarifies for the user what a specific control does.
(5) Edit Field. An edit field is where a user types specific information.
(6) Spin Control. A spin control lets the user click an arrow to increase or decrease the value it displays. (The user can also type a value instead.)
(7) Rectangle. A rectangle embellishes a dialog box or groups two or more controls together.
(8) Group Box. A group box usually contains other controls such as radio buttons or check boxes. It looks like a rectangle with a title at the top. For a general discussion of constructing controls operative in a window environment, see above-referenced Programming Windows.
After a custom dialog box has been created, it can be displayed by using the macro command {DODIALOG}. Macros are the primary method employed within the system for tying all of the application components together. In addition to the {DODIALOG} macro command, which displays dialog boxes, macros are commonly used in an application to automate tasks, duplicate keyboard and mouse actions, change the active menus, affect the screen display, select, reposition, and resize objects, and prompt users for input. FIG. 11I illustrates a typical macro for displaying a dialog box (e.g., the dialog shown in FIG. 11E). In the macro, information the user enters the dialog box is passed to the Budgeteer application. The macro will next be described in greater detail.
B. {DODIALOG} command
The process of displaying the dialog box via a macro command and storing the user's choices in cells in the notebook will now be described. The macro will send initial values to each control in the dialog box. If the user changes a setting in the dialog box, that change will be sent back to a cell in the notebook when the user closes the dialog box.
For the Budgeteer sample application, the developer can set up an area that will send default settings to the dialog box in cells A2, A3, and A4, respectively, by entering the following: Amount:, Term:, and Due Date:. The initial defaults are entered as follows. In cells B2, B3, and B4, the values of 20000, 4, and "15th of month are entered respectively. This will set the default loan amount at 20,000, the default term at 4 years, and the default payment date to the 15th of the month.
Next, a cell is set to display the amount of the monthly payment. The formula in this cell will use values the user enters in the dialog box. In cell A6, Payment: is entered; in cell B6, @PMT(B2,0.125/12,B3*12) is entered. The result displayed in cell B6 is 531.60 (the cell's format will be set to Currency and recalculate, if necessary). This payment amount is based on the loan amount in cell B2 and the term in cell B.
The {DODIALOG} command is employed in a macro for displaying the dialog box and sending the user's choices back to the notebook. The {DODIALOG} command contains several arguments, as shown in FIG. 11I. The first argument is the name of the notebook that contains the dialog box desired together with the name of the dialog box. The name of the notebook is optional if the dialog box is stored in the active notebook. The second argument specifies a cell that will store a value indicating how the user closed the dialog box. If the dialog box was canceled, 0 is stored in the cell; if the user closed the dialog box by pressing Enter or OK, 1 is stored. The third argument specifies a block that (1) contains initial values for the dialog box controls and (2) receives final values from the dialog box controls when the user closes the dialog box. Each cell in the block (starting at the upper left cell and proceeding row-wise to the lower right cell) sets the initial value of one control. If the user cancels the dialog box, the values in this block remain unchanged.
To display the LOANDB dialog box 1170 of FIG. 11J, for instance, the user enters the following in cell A8:
{DODIALOG "LOANDB",A1,B2 . . . B4}
The notebook after adding this DODIALOG command is shown in FIG. 11K. The command displays the LOANDB dialog box and passes the initial values in cells B2 through B4 to the dialog box controls (20000 for the loan amount, 4 for the term, and 15th for the monthly due date). It also sends a value of 0 to cell A1 if the user cancels the dialog box; otherwise, it sends a value of 1.
C. Linking commands to controls
Link commands of the present invention will now be described in detail. To make a dialog box dynamic (i.e., so that the user's changes are immediately reflected in the notebook), the system of the present invention allows link commands to be added to each control. Link commands are statements which are attach to each control to indicate what should happen when the user changes the control.
The {DODIALOG} command brings changed values back to the notebook when the user closes the dialog box. A link command, on the other hand, is a dynamic link--one which does not require the dialog box to be closed in order to see the calculated loan payment. Link commands usually specify at least three things: an event, an action, and an object. Hence, link commands may be thought of as statements which specify "when A happens, do B to C." The event indicates what occurrence should trigger the link command; the action indicates what should happen; and the object is what should be acted upon. In other words, when the user changes a setting, the system sends the new value back to a specific cell in the notebook. Thus, link commands are statements a developer attaches to a control to indicate what should happen when the user changes or selects the control.
For the LOANDB dialog box example, for instance, a link command may be added to the edit field to make it write changes back to cell B2 immediately. This is added as follows. In the LOANDB dialog box, the Loan Amount edit field is selected. Then, the developer invokes a Dialog.linevert split.Links command. An Object Link dialog box 1201 appears, as shown in FIG. 12A. The developer then selects Add 1202 to start the link definition. Next, the developer specifies what "event" will trigger the command (i.e., a "when" statement); a link command runs in response to some event that occurs in a dialog box (or toolbar). In a preferred embodiment, an event pick list button is provided for selecting among a plurality of events; it is available upon selection of Init button 1203. In an exemplary embodiment, the following events are detected.
______________________________________
Event Will trigger the link command when . . .
______________________________________
Init The dialog box is about to be displayed.
One can trap this to set initial
settings.
Init Complete
Available only when adding a link
command to the dialog box itself; this
event occurs immediately after all
controls have run all link commands that
respond to Init.
OKExit The user closes the dialog box. The
link command runs before the dialog box
closes.
CancelExit
The user cancels the dialog box. The
link command runs before the dialog box
closes.
Clicked The user clicks the control.
Right.sub.-- bdown
The user right-clicks the control.
Left.sub.-- bdown
The user points to the control while
holding down the left mouse button.
Releasing the button generates a Clicked
event.
Doubleclick
The user double-clicks the control.
Valuechanged
The user changes the value of the
control. This event occurs anytime the
value is changed (by the user, by a link
command, or by a macro command).
key:keystroke
The user presses the key keystroke.
When one chooses key from the event pick
list, it flashes to indicate that a key
needs to be pressed to generate the
event. The key displays after key: in
the pick list button when it is set.
For example, key:Ctrl+F5 indicates that
the link command is triggered by
Ctrl+F5.
Activate The user has chosen the control for
manipulation. For example, clicking an
edit field generates this event. One can
use Valuechanged to trap the final
result.
Deactivate
The user has chosen another control,
leaving this control inactive.
Enter The user has pressed Enter (edit fields
only).
Lineup The user increases the scroll bar's
value by clicking a scroll arrow
(available only on scroll bar controls).
Linedown The user decreases the scroll bar's
value by clicking a scroll arrow
(available only on scroll bar controls).
Pageup The user increases the scroll bar's
value by clicking it between a scroll
arrow and the scroll box (available only
on scroll bar controls).
Pagedown The user decreases the scroll bar's
value by clicking it between a scroll
arrow and the scroll box (available only
on scroll bar controls).
Thumb The user clicks or drags the scroll
bar's scroll box (scroll bar controls
only).
Editdynamic
The user is inserting or deleting
characters in the combo box's edit field
(combo box controls only).
Trigger This event cannot be generated by any
user action; it is used to set link
commands that can be run only by other
link commands. One can use the link
command TRIGGER to generate this event.
Alarm The time of day specified in the timer's
Alarm Time property has been reached
(time controls only).
Timer The amount of time indicated in the
timer's Timer Interval property has
elapsed (time controls only).
______________________________________
The action that should occur in response to the event is specified next (i.e., a "what" statement). The user may toggle the second button 1204 from RECEIVE (shown) to SEND; send causes the change to be sent back to the notebook. The third button 1205 specifies Value--what to send. The user toggles the fourth or TO/FROM button 1206 to select TO (i.e., send the value "to" a target). The Object pick list button 1207 is where the object to be acted upon is specified. By choosing <POINT>, the developer may point to an intended target in the spreadsheet. For the instant example, cell B2 is selected; this is where the developer desires the value to be sent. The last button 1208 is set to Value to indicate what is to happen to the cell--set its value. The completed Link dialog box appears as shown in FIG. 12B. The user closes the dialog by choosing OK.
A similar process may be followed to add link commands to other controls. To add a link to the spin control in the LOANDB dialog box, the developer selects the spin control, chooses Dialog.linevert split.Links, then Add. Next, the developer sets the first button (the event) to Valuechanged. Then he or she sets the second button (the action) to SEND, leaving the third button set to Value. Then, the fourth button is set (the object) to POINT for selecting cell B. Next, the fifth button is set to Value. Finally, the user clicks OK to return to the LOANDB dialog box. For the group box in the LOANDB dialog box, the first button is set (the event) to Valuechanged and the second button (the action) to SEND. The third button is set to Value, and the fourth button (the object) to POINT for selecting cell B4. The fifth button is set to Value. Finally, OK is selected to complete the dialog. A summary of the functions of the Object Links dialog box is illustrated in FIG. 12C.
Control properties
Each control stores one setting; the user manipulates the control (by clicking a scroll arrow, typing text, and so on) to change that setting. There are several ways to set a control's value: (1) through initial settings one will pass using a {DODIALOG} command; (2) by a user changing a setting from within the dialog box; (3) through the {SETOBJECTPROPERTY} command; and (4) through link commands from any dialog control. For example, the following macro command uses the Value property to enter "Spanish" into an edit field:
{SETOBJECTPROPERTY "Dialogl:EditField3.Value","Spanish"}
The macro command {DODIALOG} displays a specified dialog box. As shown in FIG. 13, the macro that displays the dialog box of FIG. 11E uses the settings within the dialog box to calculate a loan payment. Cell D6 contains the {DODIALOG} command. The {DODIALOG} command in this figure is:
{DODIALOG "LoanData", Result, B1 . . . B5}
This command displays the LOANDATA dialog box, places the result of how the user closed the dialog box in the cell named Result, and passes the values in cells B1 through B5 to the controls in the dialog box, in order.
If a control's Process Value property is set to No, it will not receive an initial value from the block specified in the {DODIALOG} command. For example, if a user's dialog box has six controls, but he or she has set the Process Value property of the third control to No, the setting block should be only five cells long. The values in the first two cells will be passed to the first two controls. However, the value in the third cell will go to the fourth control, and the values in the fourth and fifth cells will go to the fifth and sixth controls, respectively. Controls that do not have a Process Value property will receive no value from a {DODIALOG} command.
By default, the order in which the controls are created is the order in which a developer passes each control's value (through the {DODIALOG} command). In other words, the first cell in the specified block sends its value to the first control a developer created no matter where it is positioned within the dialog box. For example, the last argument in the command {DODIALOG "LoanData", Result, B1 . . . B5} specifies that cells B1 through B5 contain initial values for the controls in the dialog box. Cell B1 contains the initial value for the first control a developer created, cell B2 contains the value for the second one, and so on. The order may be changed to match the order of the values in the setting block, if desired.
Dialog controls are provided with a variety of properties that can be used to temporarily hide or disable them. One can use these properties in conjunction with link commands to hide unneeded controls or reveal special settings.
______________________________________
Properties to hide or disable dialog controls
Property Purpose
______________________________________
Grayed Specifies whether the control can be
used. When set to Yes, the control
appears dimmer to indicate it is
unavailable.
Disabled Specifies whether the control can be
used, like Grayed, but does not dim the
control when it is set to Yes.
Depend On Specifies the areas of the system in
which the control is enabled (discussed
next).
Enabled The opposite of Disabled (setting this
property to No disables the control).
It is used by link commands and does not
appear in a control's Inspector.
Hidden Setting this property to Yes hides the
control when the dialog box displays.
(The control still appears in the dialog
window while the developer is editing.)
Show The opposite of Hidden (setting this
property to No hides the control). It
is used by link commands and does not
appear in the control Inspector.
______________________________________
Some dialog controls have an Attach Child property that makes controls dragged on top of them become attached. The control on top is then a child of the control beneath it, which is the parent. Grouping controls provides many advantages to the developer. Moving the parent control moves any attached elements. Copying the parent control copies any attached elements. Hiding the parent control hides any attached elements. Disabling the parent control disables any attached elements. Resizing the parent control resizes any attached elements proportionally.
Child controls cover the parent control only; controls partially covering one another will not attach. Here is an example of grouping controls together: First, a developer chooses Tools.linevert split.UI Builder to display a new dialog window. Next, he or she uses the Rectangle tool to create a rectangle that is big enough to contain the OK button. Next he or she moves the OK button into the rectangle. The OK button is now attached to the rectangle. Then the developer moves the rectangle; the OK button stays with the rectangle. Next he or she right-clicks the rectangle, chooses Disabled, and sets it to Yes. Then he or she chooses the Test button to test the dialog box's operation. Next, he or she clicks the OK button; nothing happens, since the rectangle (its parent control) is disabled. Finally, the developer clicks the Cancel button to exit Test mode and return to the dialog window.
To prevent an object from becoming a parent control, its Attach Child property is set to No. This does not affect controls already attached. All controls in a dialog window are child controls of the dialog box (or Toolbar), which is the parent control. Therefore one can disable all controls in a dialog box by right-clicking the dialog box and setting Disabled to Yes.
Attached controls can be nested (for example, moving a parent control on top of another control to attach them). The attached controls still stay associated with the original parent control, not that control's new parent. Moving a parent control moves any child controls with it. (Moving child controls does not affect the parent.) One can use a child control's Position Adjust property to specify how it moves (or resizes) when the parent control containing it is resized.
By default, a child control moves when a handle on the left or top side of its parent control is dragged. If a developer right-clicks a child control, then chooses Position Adjust, and checks Depend on parent, each edge of the child control is locked into place so that when the parent control is resized, the child control resizes proportionally.
When a handle of the parent control is dragged while Position Adjust is enabled, an edge of the child control is pushed or pulled. Dragging a corner handle affects two sides of the child control. For example, dragging the lower right handle would affect the bottom and right edges of the child control. If the edge of a child control is pushed toward or pulled away from an edge of the child control that's lo |
