Subclass 502	Subclass 503	Subclass 504
Spreadsheet

Client side, web-based spreadsheet

6988241

Abstract

A method and system that that allows a designer to create "spreadsheet" web pages, which can then be viewed and used by the designer and/or by other users. The described embodiments of the present invention allow people to collaborate and to share spreadsheets over the web. The described embodiment allows a user of the spreadsheet to email the spreadsheet to others and to embed the spreadsheet into web pages owned by the designer or by third parties. A described embodiments of the web-based spreadsheet allowed the designer to specify both web data and real-time data in the cells of the "spreadsheet." The web data includes a URL of an image that is to be placed in a cell. The web data includes a link to a web page in a cell. The real-time data includes stock quotes and currency conversion information in the cells of the spreadsheet. Such data reflects a current (or specified) day's stock quote or a current (or specified) currency conversion value.

Claims

What is claimed is:

1. A method of displaying a web-based spreadsheet comprising:

sending a request to a server to retrieve the web-based spreadsheet;

receiving at least part of a web page in response to the request;

executing one or more user-defined macros, comprising one or more instructions of a programming language, in the received web page to display the web-based spreadsheet, the web-based spreadsheet comprising cells, the one or more user-defined macros comprising a first user-defined macro that is expressly referenced in a first formulas of a first cell;

executing information in the received web page to allow information to be entered into a second cell of the web-based spreadsheet; and

executing information in the received web page to allow information to be entered into a second cell of the web-based spreadsheet; and

executing information in the received web page to update a third cell dependent on the second cell.

2. The method of claim 1, wherein said sending the request includes sending a request from a client to the server.

3. The method of claim 1, wherein the information in the received web page and the programming language are Javascript.

4. The method of claim 1, wherein said second cell of the web-based spreadsheet comprises a second formula expressly referencing a second user-defined macro of the one or more user-defined macros, to retrieve live data that is updated periodically as the spreadsheet is being displayed, the second user-defined macro comprising one or more instructions of the programming language, and

wherein said executing information in the received web page to allow information to be entered into at least one second cell comprises invoking the second user-defined macro.

5. The method of claim 4, wherein the live data is stock quotation information.

6. The method of claim 5, wherein the stock quotation information is obtained from the server.

7. The method of claim 5, wherein the stock quotation information is obtained from a third party.

8. The method of claim 5, wherein the stock quotation information also includes historical information of a stock.

9. The method of claim 4, wherein the live data is currency conversion information.

10. The method of claim 9, wherein the currency conversion information is obtained from the server.

11. The method of claim 9, wherein the currency conversion information is obtained from a third party.

12. The method of claim 1, wherein at least one other cell of the web-based spreadsheet contains a link to another web page.

13. The method of claim 1, wherein at least one other cell of the web-based spreadsheet contains an image obtained from an address on the World Wide Web.

14. The method of claim 13, further comprising: allowing the user to expand the size of the other cell to view more of the image.

15. The method of claim 1, wherein at least one other cell of the web-based spreadsheet contains an image obtained from a data processing device connected to the user via a network.

16. The method of claim 2, where the client is connected to the server via World Wide Web.

17. A method of allowing a user to design a web-based spreadsheet, comprising:

executing information in the received web page to allow the user to create a user-defined macro comprising Javascript instructions, the user-defined macro having a macro name;

executing information in the received web page to allow the user to enter information into the cells of the web-base spreadsheet, the user entering a formula expressly referencing the macro name in at least one cell;

sending a number or rows and columns of the web based spreadsheet, the user-defined macro, and the information entered by the user to a server for storage on the server; and

sending a request to the server to retrieve the web-based spreadsheet.

18. The method of claim 17, further comprising e-mailing a link to the spreadsheet, including:

allowing the user to click on an "e-mail this page" button; and

allowing the user to enter a recipient's address.

19. The method of claim 17, further comprising embedding the spreadsheet in a web page to create an embedded HTML web page, comprising:

displaying an HTML code including a spreadsheet ID of the spreadsheet; and

allowing the user to copy the HTML code and paste it into the web page's HTML.

20. The method of claim 19, wherein the embedded HTML web page comprises "JavaScript include" tags.

21. The method of claim 17, wherein the spreadsheet is displayed on a client system.

22. The method of claim 21, wherein JavaScript Dynamic HTML is downloaded to the client system, the JavaScript including functionality, format and content of the spreadsheet web page.

23. The method of claim 17, wherein the information in the received web page and the user-defined macro are Javascript.

24. The method of claim 17, wherein at least one cell of the web-based spreadsheet contains live data that is updated periodically as the spreadsheet is being displayed.

25. The method of claim 24, wherein the live data is stock quotation information.

26. The method of claim 25, wherein the stock quotation information is obtained from the server.

27. The method of claim 25, wherein the stock quotation information is obtained from a third party.

28. The method of claim 27, wherein the stock quotation information also includes historical information for a stock.

29. The method of claim 28, wherein the live data is currency conversion information.

30. The method of claim 29, wherein the currency conversion information is obtained from the server.

31. The method of claim 29, wherein the currency conversion information is obtained from a third party.

32. The method of claim 17, wherein at least one other cell of the web-based spreadsheet contains a link to a web page.

33. The method of claim 17, wherein at least one other cell of the web-based spreadsheet contains an image obtained from an address on the World Wide Web.

34. The method of claim 33, further comprising: allowing the user to expand the size of the other cell to view more of the image.

35. The method of claim 17, wherein at lest one other cell of the web-based spreadsheet contains an image obtained from a data processing device connected to the user via a network.

36. The method of claim 21, where the client is connected to the server vial the World Wide Web.

37. A computer-implemented method performed by a server data processing system, comprising:

receiving a request, from a client system, for a spreadsheet web page;

reviewing parameters received with the request for the spreadsheet web page, said parameters include a spreadsheet mode and a data ID; and

sending the requested spreadsheet web page to the client system, based on the parameters received, wherein said spreadsheet web page contains embedded data specific to the requested spreadsheet web page and capable of causing display of a spreadsheet, said spreadsheet web page comprising one or more user-defined macros comprising Javascript, the spreadsheet web page comprising cells, at least one cell of said cells comprising a formula expressly referencing at least one user-defined macro.

38. The method of claim 37, wherein before sending the spreadsheet web page, JavaScript data is embedded into the spreadsheet web page.

39. The method of claim 37, wherein the data ID identifies a file name.

40. The method of claim 37, wherein the server includes data for more than one spreadsheet web page.

41. A method of displaying a web-based spreadsheet comprising:

sending a request to a server to retrieve the web-based spreadsheet;

receiving at least part of a web page in response to the request;

executing one or more user-defined macros in the received web page to display the web-based spreadsheet, said one or more user-defined macros comprising at least one of an initialization macro which is invoked when the spreadsheet web page is loaded and a first macro which is expressly referenced in a formula of at least one cell of the web-based spreadsheet;

executing a second macro of said one or more user-defined macros in the received web page to allow information to be entered into a second cell of the web-based spreadsheet; and

executing a third macro of said one or more user-defined macros in the received web page to update a third cell dependent on the second cell.

42. The method of claim 41, wherein said sending the request includes sending a request from a client to the server.

43. The method of claim 41, wherein the one or more user-defined macros in the received web page are Javascript.

44. The method of claim 41, wherein the second cell of the web-based spreadsheet contains live data that is updated periodically as the spreadsheet is being displayed.

45. The method of claim 44, wherein the live data is stock quotation information.

46. The method of claim 45, wherein the stock quotation information is obtained from the server.

47. The method of claim 45, wherein the stock quotation information is obtained from a third party.

48. The method of claim 45, wherein the stock quotation information also includes historical information of a stock.

49. The method of claim 44, wherein the live data is currency conversion information.

50. The method of claim 49, wherein the currency conversion information is obtained from the server.

51. The method of claim 49, wherein the currency conversion information is obtained from a third party.

52. The method of claim 41, wherein at least one other cell of the web-based spreadsheet contains a link to another web page.

53. The method of claim 41, wherein at least one other cell of the web-based spreadsheet contains an image obtained from an address on the World Wide Web.

54. The method of claim 53, further comprising: allowing the user to expand the size of the other cell to view more of the image.

55. The method of claim 41, wherein at least one other cell of the web-based spreadsheet contains an image obtained from a data processing device connected via a network.

56. The method of claim 42, where the client is connected to the server via World Wide Web.

57. An article of manufacture readable by a computer, tangibly embodying instruction executable by the computer to perform a method of displaying a web-based spreadsheet, said method comprising:

sending a request to a server to retrieve the web-based spreadsheet;

receiving at least part of a web page in response to the request;

executing one or more user-defined macros, comprising one or more instructions of a programming language, in the received web page to display the web-based spreadsheet, the web-based spreadsheet comprising cells, the one or more user-defined macros comprising a first user-defined macro that is expressly referenced in a first formula of a first cell;

executing information in the received web page to allow information to be entered into a second cell of the web-based spreadsheet; and

executing information in the received web page to update a third cell dependent on the second cell.

58. The article of manufacture of claim 57 wherein the one or more user-defined macros comprise an initialization macro which is invoked when the web-based spreadsheet is loaded, the initialization macro comprising user specified Javascript instructions,

wherein said executing said one or more user-defined macros also executes the initialization macro in the received web page.

59. The article of manufacture of claim 58 wherein the web-based spreadsheet comprises a user-defined global variable, accessible to the initialization macro and the one or more user-defined macros.

60. The article of manufacture of claim 57 wherein the programming language is Javascript.

61. The article of manufacture of claim 57 wherein the second cell of the web-based spreadsheet comprises a second formula expressly referencing a second user-defined macro of said one or more user-defined macros, to retrieve live data that is update periodically as the spreadsheet is being displayed, the second user-defined macro being Javascript, and

wherein said executing information in the received webpage to allow information to be entered into the second cell comprises invoking the second user-defined macro.

62. The article of manufacture of claim 61, wherein the live data is stock quotation information.

63. The article of manufacture of claim 61, wherein the live data is currency conversion information.

64. The article of manufacture of claim 61 wherein the third cell of the web-based spreadsheet comprises a third formula expressly referencing a third user-defined macro and said one or more user-defined macros, the third user-defined macro being Javascript, and

wherein said executing information in the received web page to update the third cell invokes the third user-defined macro.

Description

BACKGROUND OF THE INVENTION

The present invention relates generally to dynamic hypertext markup language (DHTML) web pages and, more specifically, to a method for creating and viewing a spreadsheet web page.

Traditional spreadsheets and ledgers are large sheets of paper with columns and rows that accountants use to detail sets of business transactions such as expenses and revenues. A spreadsheet lays out a comprehensive set of numerical values and calculations on a single sheet of paper.

Conventional electronic spreadsheets are implemented as a standalone program executed on a data processor. A desktop electronic spreadsheet is a spreadsheet that executes on a standalone desktop computer and organizes information into columns and rows within an electronic ledger. Like its physical counterpart, a desktop electronic spreadsheet can add and view data to help explore business issues. An advantage of the electronic version of a spreadsheet is that data can be added by "formula" instead of by hand. Thus, when a number is changed in an electronic spreadsheet cell, the effect on the entire scenario can be seen immediately.

Ever since early electronic spreadsheets, such as VisiCalc, desktop electronic spreadsheets have been used to organize and tabulate data. A currently popular spreadsheet is Microsoft's Excel 2000 spreadsheet, which allows the user to enter data into "cells" of the spreadsheet and to tabulate and organize those cells. While Excel 2000 allows the user to enter, for example, a link to the World Wide Web ("the web") into a cell, Excel and most desktop spreadsheet applications are not designed with the web in mind. What is needed is a spreadsheet that allows users to make full use of the capabilities of the web and to include data only available via the web. This invention allows the spreadsheet to be displayed or edited in a web browser, without installing a specific application program.

SUMMARY OF THE INVENTION

A described embodiment of the present invention allows a designer to create "spreadsheet" web pages, which can then be viewed and used by the designer and/or by others. Many people want to use spreadsheets, but only a small percentage actually want to create them. It will be understood that, in the paragraphs that follow, the term "designer" is generally used to refer to the person who created and designed a web-based spreadsheet, while the term "user" generally refers to a person who views or uses the web-based spreadsheet. Some people can be both designer and user. The described embodiments of the present invention allow people to collaborate and to share spreadsheets over the web. The described embodiments allow a user of the spreadsheet to email the spreadsheet to others and to embed the spreadsheet into web pages owned by the user or by third parties.

An embodiment of the web-based spreadsheet allows the user to specify both web data and real-time data in the cells of the spreadsheet. A described embodiment of the present invention allows the spreadsheet designer to enter a URL of an image stored on a network, such as the Internet. The image is then fetched and placed in a cell. The described embodiment also allows the spreadsheet designer to enter a URL of a web page into a cell. Furthermore, the described embodiment allows the spreadsheet designer to specify real-time data, such as stock quotes or currency conversion information, in the cells of the spreadsheet. Such a cell will reflect a current (or specified) day's stock quote or a current (or specified) currency conversion value.

Moreover, the described embodiment allows the user to specify "macros" that enlarge the functionality of the web-based spreadsheet.

Advantages of the invention will be set forth in part in the description which follows and in part will be apparent from the description or may be learned by practice of the invention. The objects and advantages of the invention will be realized and attained by means of the elements and combinations particularly pointed out in the appended claims and equivalents.

BRIEF DESCRIPTION OF THE DRAWINGS

The accompanying drawings, which are incorporated in and constitute a part of this specification, show several embodiments of the invention and, together with the description, serve to explain the principles of the invention.

FIG. 1 is a block diagram of a network environment in which the present invention can be implemented.

FIG. 2 shows an empty web-based spreadsheet in accordance with a described embodiment of the present invention.

FIG. 3 is a flow chart showing a method performed by a server of FIG. 1.

FIG. 4 is a flow chart showing a method performed on a client of FIG. 1 to obtain spreadsheet data from the server of FIG. 1.

FIG. 5 is a flow chart showing a method performed on the client of FIG. 1 to display a web-based spreadsheet.

FIG. 6 shows the web-based spreadsheet of FIG. 2 showing a drop down menu of web data and real-time data that can be inserted in the web-based spreadsheet.

FIG. 7(a) shows an example of adding a web-based image to the web-based spreadsheet.

FIG. 7(b) shows the result of adding a web-based image to the web-based spreadsheet.

FIG. 7(c) shows the result of right clicking on a cell containing the web-based image.

FIG. 7(d) shows an example in which the vertical size of the cell containing the web-based image is increased.

FIG. 8(a) shows an example of adding a real-time stock quote to the web-based spreadsheet.

FIG. 8(b) shows an intermediate step while the real-time stock quote data is being loaded.

FIG. 8(c) shows the result of adding real-time stock quote data to the web-based spreadsheet.

FIG. 9(a) shows an example of adding a hypertext link to the web-based spreadsheet.

FIG. 9(b) shows the result of adding the hypertext link to the web-based spreadsheet.

FIG. 10(a) shows an example of adding a real-time currency value to the web-based spreadsheet.

FIG. 10(b) shows the result of adding the real-time currency value to the web-based spreadsheet.

FIG. 11 shows an example of a cell that contains the sum of two other cells.

FIGS. 12(a)-12(e) show examples of drop down menus used in a described embodiment of a web-based spreadsheet in accordance with the present invention.

FIG. 13(a) shows an example window enabling a user to email a spreadsheet.

FIG. 13(b) shows an example email used to send a link to a spreadsheet.

FIGS. 14(a)-14(d) show an example spreadsheet created and viewed in accordance with the present invention and including macros.

FIG. 15 shows a process of allowing a web page user to embed a spreadsheet in any web page.

FIG. 16(a) is a flow chart showing a log in process.

FIG. 16(b) is a flow chart showing a process of saving data into "My Files."

FIG. 16(c) shows a "My Files" page containing saved files.

FIG. 17(a) shows a format of a member file.

FIG. 17(b) shows a format of a data file.

FIG. 18 shows an example of a web-based spreadsheet containing real-time data.

DETAILED DESCRIPTION OF EMBODIMENTS

Reference will now be made in detail to several embodiments of the present invention, examples of which are shown in the accompanying drawings. Wherever practicable, the same reference numbers will be used throughout the drawings to refer to the same or like parts.

1. System

A described embodiment of the present invention allows a spreadsheet designer to include web data and real-time data in a web-based spreadsheet. The data used in the web-based spreadsheet is preferably stored on a server data processing system and is sent to a user's browser in response to a user request. Thus, a web-based spreadsheet in accordance with the present invention can be viewed from any computer or device having browser capability. The user does not have to be tied to a spreadsheet stored on a desktop. The described embodiment of the present invention is written entirely in Dynamic HTML and thus does not require any special downloads, plug-ins or Java. After loading, the spreadsheet will execute equally fast with a 28.8 KBPS connection or a T3 line. In the described embodiment, a user will always have the most current version of the program—just as he always has the most recent version of any web page he chooses to view. Because it is web-based, the described embodiment will not cause any installation problems and cannot create DLL conflicts. The files describing the web-based spreadsheet are stored on secure servers, so a user never needs to worry about backing up data, and can access his files from any web-connected computer.

FIG. 1 is a block diagram of a network environment in which the present invention can be implemented. The figure includes a client device 102, such as a computer, data processing system network appliance, television-based browsing device, wireless telephone, or other wireless device. The figure also shows a server data processing system 104. In the described embodiment, a web-based spreadsheet is created and displayed from within a user's browser 110 on client device 102 using functionality carried in a spreadsheet page 112 received from the server data processing machine 104. The contents of Data Array 114 is also included within the spreadsheet web page 112.

Server data processing system 104 includes server software 120, which implements functionality of the server, and a spreadsheet datafile 122 that contains information needed to create spreadsheet page 112. In some implementations, spreadsheets of multiple users are stored in a central repository on server 104. In such embodiments, server 104 also includes a member file 126 listing all the designers who have created and stored web-based spreadsheets and listing all users who have registered to gain access to the web-based spreadsheets. Certain other embodiments do not include a central repository and do not require or use member files.

The member file 126 includes information for all members who have an account with the system. Initially, when a user logs into the system, he is asked whether he has an account. If the user is a member, he is allowed to access the system. In the alternative, if the user is not a member, he is prompted to enter his identifying information, which might include a password and user ID.

FIG. 1 includes a source of real-time data 108, which is coupled to network 106. Dotted line 107 indicates a path of real-time data from source 108, to server 104, to client 102.

A Member file includes information for all members who have an account with the system. Initially, when a user logs into the system, he is asked whether he is a member who has an account with the system. If the user is a member, he is allowed to access the system. In the alternative, if the user is not a member, he is offered to enter his identifying information, which might include a password and email address. The Spreadsheet/Calculator data file contains information related to spreadsheets and calculators. Such information may include a data ID, which refers to a file name, and information for each individual cell of a spreadsheet/calculator. In particular, the cell information related to each individual cell includes, but is not limited to, the information related to cell dependency, formatting, content, and editability. The server software manages the files and communicates with the browser. The server software handles opening, saving, and incorporating live data (such as stock quotes) into the files. In addition, the server software returns the HTML page and calculator page in response to a request received from the client system, via a browser. The client system includes browser software and a Data Array. The user, via the browser, sends a request to the server for an HTML page. The server software reviews the parameters received with the request. If the parameters include a "spreadsheet" mode, a spreadsheet HTML page is returned to the client. In the alternative, if the parameters include a "calculator" mode, the server returns the calculator HTML page. If the parameters include an "embed" mode, the server returns a fully formed calculator web page. The Data Array is an array of cell descriptions whose values are loadable from JavaScript in the HTML page. The Data Array contains information related to each individual cell, which includes, but is not limited, to cell dependency, formatting, content, and editability. When the designer/creator saves a file, client turns the definition of each cell into a string. The concatenated cell strings define the entire spreadsheet. Client sends the concatenated string to the server. The server takes the string and writes it into the Spreadsheet/Calculator Data File. Thus, a client-format Data Array is translated into Spreadsheet/Calculator Data File in the server file format.

The browser is software effecting the requesting and displaying of HTML web pages. The browser software can be standalone or integrated within other software products. It should be understood that each of clients and web servers in the described embodiment preferably includes a processor and a memory. The memory includes instructions capable of being executed by the processor to perform the functions described below. The server can also include a computer readable medium for storing the instructions. The server system communicates with the client system via any appropriate communication mechanism, including but not limited to, a network, an intranet, the Internet, wireless communications, telecommunications, cable modems, and satellite communications.

FIG. 17(a) shows a block diagram showing a Member file 1712 and an example member record 1700. The Member file 1712 keeps member information in the form of member records 1700. The member record 1700 identifies a particular member. In one embodiment of the present invention, the example member record may have the following fields: a member ID, a password, an email address, and a data ID, which refers to a file name. The example record 800 features a member ID equal to ANNA, 0124 as a password, ANNA@HOTMAIL.COM as an email address, and "Make the Ultimate Holiday Dinner" as a file name. When a user initially signs up with the system, all user information is stored in the member file 1712.

Referring to FIG. 1, server 104 creates spreadsheet page 112 in response to a request from browser 110. The spreadsheet page 112 is sent across a network 106 and stored in the user's browser 110. Thus, no special software, aside from standard browser 110, is required to execute the functionality of the web-based spreadsheet. The Spreadsheet data file 122 contains information related to spreadsheets. Such information may include a data ID, which refers to the name of a spreadsheet, and information for each individual cell of a spreadsheet. In particular, the cell information related to each individual cell includes, but is not limited to, the information related to cell dependency, formatting, content, and editability. The server software 120 manages the files and communicates with the browser. The server software 120 also handles opening, saving, and incorporating live data (such as stock quotes and currency conversion information) into the files. In addition, the server software 120 returns the spreadsheet page 112 in response to a request received from the client system's browser 110.

In the described embodiment, the functionality of the spreadsheet is implemented using JavaScript included in spreadsheet web page 112. The use of Javascript ensures that the spreadsheet will be usable in any browser 110 that is capable of executing Javascript. Use of Javascript also ensures that the spreadsheet web page 112 will pass though any firewall and also avoids the problems inherent in upgrading to a newer version of the spreadsheet functionality, since the functionality is re-loaded with each spreadsheet web page 112.

FIG. 17(b) is a block diagram showing a Spreadsheet data file 1716 and an example record 1750. The Spreadsheet data file 1716 keeps spreadsheet data. The same data file is used to generate spreadsheet page 112. In particular, data file 1716 contains a user name or ID, a data ID (for example, a file name) and information about each cell in the spreadsheet. The information for every cell may include, for example, value, formula, formatting, editability, and borders. Each data array file 114 and spreadsheet data file 122 includes an editability flag for each cell, which is assigned a "FALSE" or "TRUE" logic value. This value indicates whether a certain cell is editable or not. If the cell has a "TRUE" value, i.e., if it is locked, a user cannot edit that cell when viewing the spreadsheet. Alternatively, if the cell has a "FALSE" value, i.e., if it is unlocked, that cell can be edited when viewing the spreadsheet. FIG. 17(b) features the example record 1750 having the following fields: User Name=ANNA, Data ID="Make the Ultimate Holiday Dinner," and information about each cell. In particular, cell A3 which includes the text "HOW MANY TOTAL GUESTS ARE THE USER HAVING?" is described by the following parameters: {ENTRY:'HOW MANY TOTAL GUESTS ARE THE USER HAVING?',LOCKED:'FALSE',VIEWSIZE:'9PT',FORECOLOR:'NAVY',VIEWFAMILY:'VERDA NA',_WRAPTEXT:'TRUE',_TEXTALAIGN:'LEFT',M_ROW:3,M_COL:1,I_NR:'HOW MANY TOTAL GUESTS ARE THE USER HAVING?'}. According to these parameters, cell A3 is the intersection of a third row and a first column and is referenced with a Column letter Row number notation A3. The text in cell A3 is aligned to the left, the text color of the cell is navy, and the font size of the cell is 9pt. The datafile 122 also includes real-time data, web data, and macros.

FIG. 2 shows an embodiment of a web-based spreadsheet 200. In this embodiment, web-based spreadsheet 200 includes a tool bar 202, various icons 204, and an icon 206 to allow the user to email the spreadsheet 200. The spreadsheet 200 contains columns and rows in which all of the spreadsheet calculations are performed. Each row has a number and each column has a letter. A cell is the intersection of a row and a column and is referenced with a Column letter, Row number notation, such as A1 or C3. The example spreadsheet 200 has at least 27 rows (numbered 1 through 27) and 8 columns (labeled A through H). More rows can be viewed with the scroll bar. The designer can reset the number of rows and columns in the spreadsheet with the "Set Size" command discussed below. In addition, the designer can add or delete columns with the "Insert/Delete Rows or Columns" commands. A cell can contain labels, numbers, test (strings), dates/times or formulas. (Other embodiments may have additional types of data in their cells.) A string is a text entry, such as "Sales targets." A number is an integer or decimal value. Numbers can be formatted, for example, as currency, as integers (whole numbers), as decimal numbers, or as percentages. Users can define arbitrary formulas.

Formulas are often used to express mathematical relationships between cells. For example, if the formula =B2+B3 is entered into cell B4, then the number displayed in cell B4 will always be the sum of those two cells. If the values in either B2 or B3 change, B4 will be automatically updated to reflect the change. In the described embodiment, formulas are always preceded with an "=" sign. Other formula formats may be used. In addition, cells that contain formulas can, themselves, be used in formulas. If the formula =B4*1.08 is placed in cell C4, then both B4 and C4 will change accordingly when values in B2 and B3 are changed.

Formulas can be written with simple arithmetic operators, such as "+", "-", "*", and "/," and they can also use functions. Functions let the designer perform more sophisticated calculations. For example, the SUM() function lets the designer add the contents of all the cells within the parentheses. For example, =SUM(B3,B4) is the same as B3+B4. =SUM(A6:K6) adds the values in all the cells between A6 and K6, inclusive. The notation A6:K6 is called a range and is shorthand for expressing all the cells between the two (e.g. A6, B6, C6, D6, E6, F6, G6, H6, I6, J6, K6). A more complete list of all the available functions is in the Function Reference section below.

A current cell area 208 contains the name of a currently selected cell in the spreadsheet (such as cell A1 220). Area 210 is preferably a drop down function menu, although any appropriate user interface can be used. The drop down function menu 210 allows the designer to select web data and real-time data to be placed in a cell of the spreadsheet as shown in FIG. 6. The designer can change information for any specific cell in the spreadsheet. To do so, the designer has to click on that cell to highlight it. The command line 222 is the area of the spreadsheet 200 where a designer enters and edits cell values and formulas. To change information for a specific cell, a designer clicks on that cell to highlight it. Then, the designer types the value, text or formula for that cell into the command line field 222. If the cell already has an entry, that entry will appear in the command line. If the designer makes a mistake while typing, he can discard his entry by clicking the "X" button at the end of the command line. To accept his entry, the designer clicks the check mark, or hits the Enter key.

The designer can set or reset the number of rows and columns in his spreadsheet with the Set Size command. The designer can add or delete columns with the Insert/Delete Rows or Columns commands. The format bar 204 contains clickable icons and drop down menus used to format numbers and text within cells. These controls will be comfortable to most users of standard word processors and spreadsheets. They include font family and size controls, font style (bold, italic, underline), text alignment, text color, and cell background color (among others).

All spreadsheets can be accessed in two ways. In Spreadsheet Mode, the designer works with a full-featured, web-based spreadsheet. The designer can create formulas, add and delete columns and format cells. Certain designers may also allow other users to use the spreadsheet in full spreadsheet mode. A spreadsheet can also be used in Calculator Mode. When the user views a spreadsheet as a Calculator, he is working with the spreadsheet as a fill-in-the-blanks, text application. The user is not allowed to view any formulas or spreadsheet controls. He merely enters data, and sees what the spreadsheet calculates.

FIG. 3 is a flow chart showing a method performed by server 104 of FIG. 1. When this method is performed, a user or other entity (such as a computer program) has requested a particular web-based spreadsheet from server 104. This request may, for example, take the form of an http request sent to the server:

http://server.com/mycalc&dataID=1231231&mode=spreadsheet

When the server receives 302 this request, it looks at the parameters of the request (here, dataID and mode) 304. The dataID parameter identifies 306 a file containing the data for the web-based spreadsheet. If the mode indicates a spreadsheet mode 308, the data needed to create a web-based spreadsheet on the client device is embedded in spreadsheet web page 112 and returned 310 to client 102. If the mode indicates a calculator mode 312, the user wishes to view the spreadsheet in a "fill in the blank" mode and does not wish to modify the format or layout of the spreadsheet. In this case, calculator data is added 318 to web page 112, which is returned to the client 102. If the mode is not spreadsheet and is not calculator, it may be "embedded mode." In this case, the spreadsheet is returned 314 in a form that can be embedded in a web page. Both calculators and embedded mode are discussed in the copending application "Client Side, Web-Based Calculator" of Guttman et al.

The spreadsheet page 112 contains script tags, spreadsheet data, and an HTML user interface. For example, in the case of a spreadsheet page 112, the script tag states href="/css/spreadsheet_ie4_eda7fc0b.cssx"/. Cells in spreadsheet page 112 have different parameters than cells in a calculator page. Specifically, cells in a spreadsheet are unlocked and the editability flag has a "FALSE" value, whereas most cells in a calculator are locked and the editability flag has a "TRUE" value. If the designer requested data in the Embed mode, a loaded fully formed HTML element is returned, in element 316. This HTML element does not include HTML script tags, unlike the spreadsheet pages returned in steps 310 and 314. The embedded HTML page dynamically returns the HTML page. This is accomplished using "JavaScript include" tags. The "JavaScript include" tags return dynamically created JavaScript reflecting the current change in the spreadsheet or spreadsheet file. The process ends in 318 once the HTML page is returned.

FIG. 4 is a flow chart showing the steps performed by a browser 110 in accordance with the present invention. Once the browser 110 sends a request for an HTML page, as was discussed with reference to FIG. 3, the server 104 reviews the parameters received with the request and returns the HTML page accordingly. Once the browser parses the page in element 426, it might send additional requests to the server 104 for data, which were referred to in the HTML page. In element 428, loading progress bar reports are generated and displayed informing the user about the status of the progress of program execution. In particular, the progress bar may indicate the following: "Loading a Spreadsheet" or "Loading a Calculator." In element 430, the spreadsheet is built (as shown in FIG. 5). In element 432, the designer has the option of saving the file he is currently working with if the data was modified since the last time the file was saved. The process ends in element 434. Building the spreadsheet 430 is discussed below.

FIG. 5 is a flow chart illustrating the process of building a spreadsheet in accordance with a described embodiment of the present invention. Initially, a client system 102 receives the number of rows and columns to build a particular spreadsheet, in element 502. As shown in connection with FIG. 2, an example spreadsheet has columns and rows. A two-dimensional array of cell elements is built. In element 504, each cell is initialized and dynamic HTML is evaluated for each cell. This step includes obtaining information about a particular cell from a Data Array file 114. This information includes, but is not limited to, cell dependency, formatting, content, and editability. Thus, as shown in FIG. 17(b), cell A3 which includes the text "HOW MANY TOTAL GUESTS ARE YOU HAVING?" is described by the following parameters, as shown in Table 1: {ENTRY:'HOW MANY TOTAL GUESTS ARE YOU HAVING?',LOCKED:'FALSE',VIEWSIZE:'9PT',FORECOLOR:'NAVY',VIEWFAMILY:'VERDA NA',_WRAPTEXT:'TRUE',_TEXTALIGN:'LEFT',M_ROW:3,M_COL:1,I_NR:'HOW MANY TOTAL GUESTS ARE YOU HAVING?'}. According to these parameters, cell A3 is the intersection of a third row and a first column and is referenced with a Column letter, Row number notation A3. Cell A3 is an editable cell because the editability flag is set to "FALSE" (the spreadsheet was so-designed by its designer), the text in the cell is aligned to the left text, the color of the cell is navy, and the font size of the cell is 9pt.

Once dynamic HTML is specified for each cell, the browser executes Javascript in the page to build a dependency tree in step 505. The dependency tree is part of the Data Array 114. Any spreadsheet has at least two types of cells: the ones that depend on a particular cell and the ones on which a particular cell depends. Some cells in the Data Array 114 contain the field "i_rt" and others contain the field "i_tb". The fields are lists of other cells that a given cell depends on (i_rt) and a list of cells that depend on this cell (i_tb). "I_tb" stands for "initialize the referred to by cell" and "i_rt" stands for "initialize refers to cell. If, for example, cell B13 is a dependent cell, the following is the list in Data Array 114 of other cells upon which cell B13 depends: I_RT:'[E[7][2],E[3][2],E[5][2]]',LOCKED:'FALSE',VIEWSIZE:'9PT',_WIDTHCLUE:'80',_TEXTALIGN:'RIGHT',M_ROW:13,M_COL:2,I_NR:'12.5'}. Accordingly, cell B13, which is the intersection of row 13 and column 2, depends on cells B7 ([7][2]), B3 ([3][2]) and B5 ([5][2]).

Once all cells are built, they become visible, in element 506. This is done with a combination of HTML that is generated on the fly using JavaScript and by JavaScript itself. In element 508, calculations are performed for each cell, when needed.

Once all calculations are made, the determination is made whether a particular cell is editable (whether it is locked) (not shown). If a cell is non-editable, that cell is locked and the user is not allowed to enter data into that cell in spreadsheet mode. For example, according to 114 description, cells A13-A23 are locked and the user cannot change their content. In the alternative, if the cell is editable (unlocked) (for example, cells B3 and B5, as described in the Data Array 114), an input box is built around that cell. The user can type in the value in that cell because it is unlocked in spreadsheet mode. Thus, the user can type in "12" in cell B3 and "2" in cell B5.

2. User Interface

The following section discusses the user interface for an example web-based spreadsheet. The functionality described herein is implemented using Javascript. When necessary, reference is made to FIG. 2.

FIG. 6 shows the web-based spreadsheet of FIG. 2 with a drop down menu 602 of web data and real-time data. In the described embodiment, the image URL and the Web link are web data and the Stock Quote and Currency Quote are real-time data.

Image URL: Inserts the image function (which places a reference picture in the cell)
Stock Quote: Automatically inserts the Quote() function, which provides access to 20 min delayed stock quotes.
Web link: Inserts the link function (text which opens up a new browser window with a specified URL, when clicked)
Currency Quote: Automatically inserts the Currency() function, which provides access to 20 min delayed currency quotes.

These functions, described in Table 2, give the designer the ability to draw live, real-time information from the Web and incorporate it into his spreadsheets. For the standard set of functions, see Table 4.

TABLE 2

quote( symbol, data )	Returns the 20-min delayed stock price
	for a given security. Both symbol and
	data should be enclosed in quotes (e.g.
	"HBRN").
	symbol = stock symbol
	data = optional data type requested.
	Choices are, "Price", "date", "time",
	"change", "open", "high", "low",
	"volume", "name". If data is omitted,
	quote( ) returns the price.
image( url )	Displays a web-hosted image within a
	spreadsheet cell. url is the URL where the
	image is located, and should be enclosed
	within quotes.
link( url, text )	Creates a link to url from the words text
	displayed within a cell. If text is omitted,
	the url name is displayed.
	Note that any text preceded by "http://"
	will automatically be turned into a link by
	the application.
currency( from, to, date,	Converts from one currency to another.
option )	from = currency units to convert from.
	Should be enclosed in quotes. See the
	currency code table
	to = currency units to convert to. Should
	be enclosed in quotes. See the currency
	code table
	date = (optional) date for conversion
	information. If omitted uses today's
	information.
	option = (optional) conversion code,
	"bid", "ask", "min_bid", "min_ask",
	"max_bid", "max_ask". If omitted, "bid"
	is assumed.

FIG. 7(a) shows an example of adding a web-based image to the web-based spreadsheet. The designer selects "Image URL" from the drop down menu 602, which places "=image("put image url here")" in the function area 222. The designer then enters a URL (e.g., "http://abccorp.com/image1.gif"). At this point, the functionality of the spreadsheet page 112 (e.g., JavaScript in the page) sends an HTTP request to network 106, which routes the request to the web server referenced by the domain name (e.g., "abccorp.com"). The web server returns an image (e.g., "image.1.gif"), which is displayed inside the cell associated with the Image function.

FIG. 7(b) shows the result of adding a web-based image to the web-based spreadsheet. In the described embodiment, if the image is larger than the cell, only a part of the image 702 that will fit in the cell will be displayed.

FIG. 7(c) shows the result of right clicking on a cell containing the web-based image (or of right clicking on any cell). Right clicking causes a menu to be displayed, which includes the following functions: Cut, Copy, Paste, Paste Values, Clear, Insert Row, Insert Column, Delete Row, Delete Column, Row Height, and Column Width.

FIG. 7(d) shows an example in which the vertical size of the cell containing the web-based image is increased to 120 pixels. As can be seen in the example, increasing the size of the row causes more of the image "image1.gif" to be displayed

FIG. 8(a) shows an example of adding a real-time stock quote to the web-based spreadsheet. The designer selects "Stock Quote" from the drop down menu 602, which places "=quote("put stock symbol here")" in the function area 222. In the example, the user then enters a stock symbol (e.g., "SUNW"). At this point, the functionality of the spreadsheet page 112 (e.g., JavaScript in the page) sends an http get request to server 104 via network 106. The web server 104 returns a stock quote for the symbol entered (e.g., "SUNW"). The returned stock quote is displayed inside the cell associated with the Quote function.

FIG. 8(b) shows an intermediate step while the real-time stock quote data is being loaded. After the user has entered a stock symbol, but before a stock quote has been received, the functionality of page 112 displays a legend "loading."

FIG. 8(c) shows the result of adding real-time stock quote data to the web-based spreadsheet. After a stock quote (e.g., "$115.25") has been received, it is displayed in the cell 902 associated with the Quote function.

In the described embodiment, the server 104 obtains real-time data such as stock quotes or currency conversion information in any appropriate manner. For example, server 104 may contract with a third party, which supplies stock and/or currency information periodically. As another example, server 104 may obtain data from a third party's web page (screen scraping) periodically. The described embodiment currently obtains its stock quotes from a Yahoo service that returns textual stock information when an http get request is sent to the Yahoo server, although any appropriate source could be used.

If, for example, stock information is provided or obtained every fifteen minutes, the information supplied to the designer's web-based spreadsheet is no more than fifteen minutes old when it is received. The data is called real-time data because its value depends on the time it was requested and sent. In addition, certain embodiments allow the user to periodically request new real-time information and update the cell relying on that real-time information as discussed below in connection with macros. If, for example, new data is requested every fifteen minutes, the data in the spreadsheet is never older than fifteen minutes old.

It will be understood by persons of skill in the art that, whenever real-time data is updated, all cells that depend on the updated cell also are recalculated and redisplayed if needed. Thus, if a cell depends on a cell containing real-time data, that cell would also be updated every time the real-time data cell is changed. The described embodiment keeps track of which cells depend from other cells and, when new data is received for a cell, all cells depending from that cell are also checked to see if they need updating.

FIG. 9(a) shows an example of adding a hypertext link to the web-based spreadsheet. The designer selects "Web Link" from the drop down menu 602, which places "=link("put URL here", "put optional shortcut name here")" in the function area 222. In the example, the designer then enters a URL (e.g., "http://abccorp.com") and a shortcut (e.g., "Link to ABC Corp"). At this point, the functionality of the spreadsheet page 112 (e.g., JavaScript in the page) inserts a link in the cell associated with the Web Link function. The link contains the shortcut specified by the designer. When the shortcut is clicked, the functionality of the spreadsheet page 112 (e.g., Javascript in the page) sends an http request to network 106, which passes the request to the appropriate server. Web server 104 returns the data (such as a web page) associated with the address/URL. The returned data is displayed inside a new browser window (not shown).

FIG. 9(b) shows the result of adding the hypertext link to the web-based spreadsheet. It will be understood that other parameters can be used for web data and real-time data. It will also be understood that other web functions and real-time functions can be used in conjunction with the present invention. For example, real-time data might include interest rate information, financial data, or engineering data corporate sales, customer complaints, HR expenses, benefits, trading exchange stats, and manufacturing monitoring data.

FIG. 10(a) shows an example of adding a real-time currency value to the web-based spreadsheet. The designer selects "Currency Quote" from the drop down menu 602, which places "=currency("put from currency here", "put to currency here", 09/11/2000", "bid")" in the function area 222. Currency conversion is performed from the FROM currency to the TO currency using the conversion rate of the date specified (default is today's date). "Bid" is an example of the conversion option, which is described in more detail in FIG. 5. In the example, the designer then enters a TO currency symbol (e.g., "usd" for U.S. dollars) and a FROM currency ("gbp" for Great Britain pound). The designer enters that day's date and "bid." At this point, the functionality of the spreadsheet page 112 (e.g., JavaScript in the page) sends an HTTP request to server 104 via network 106. The web server 104 returns a currency conversion quote for the values entered. The returned currency conversion quote (e.g., "$1.41") is displayed inside the cell associated with the Quote function.

FIG. 10(b) shows the result of adding real-time currency conversion quote data to the web-based spreadsheet. After a currency conversion quote (e.g., "$1.41") has been received, it is displayed in the cell 1002 associated with the Quote function.

Table 3 lists currency codes for use with the currency(from, to, date, option) function.

	TABLE 3

	Currency	Code

	U.S. Dollar	USD
	Afghanistan Afghani	AFA
	Albanian Lek	ALL
	Algerian Dinar	DZD
	Andorran Franc	ADF
	Andorran Peseta	ADP
	Angolan New Kwanza	AON
	Argentine Peso	ARS
	Aruban Florin	AWG
	Australian Dollar	AUD
	Austrian Schilling	ATS
	Bahamanian Dollar	BSD
	Bahraini Dinar	BHD
	Bangladeshi Taka	BDT
	Barbados Dollar	BBD
	Belgian Franc	BEF
	Belize Dollar	BZD
	Bermudian Dollar	BMD
	Bhutan Ngultrum	BTN
	Bolivian Boliviano	BOB
	Botswana Pula	BWP
	Brazilian Real	BRL
	British Pound	GBP
	Brunei Dollar	BND
	Bulgarian Lev	BGL
	Burundi Franc	BIF
	CFA Franc BCEAO	XOF
	CFA Franc BEAC	XAF
	Cambodian Riel	KHR
	Canadian Dollar	CAD
	Cape Verde Escudo	CVE
	Cayman Islands Dollar	KYD
	Chilean Peso	CLP
	Chinese Yuan Renminbi	CNY
	Colombian Peso	COP
	Comoros Franc	KMF
	Costa Rican Colon	CRC
	Croatian Kuna	HRK
	Cuban Peso	CUP
	Cyprus Pound	CYP
	Czech Koruna	CSK
	Danish Krone	DKK
	Djibouti Franc	DJF
	Dominican R. Peso	DOP
	Dutch Guilder	NLG
	ECU	XEU
	Ecuador Sucre	ECS
	Egyptian Pound	EGP
	El Salvador Colon	SVC
	Estonian Kroon	EEK
	Ethiopian Birr	ETB
	Euro	EUR
	Falkland Islands Pound	FKP
	Fiji Dollar	FJD
	Finnish Markka	FIM
	French Franc	FRF
	Gambian Dalasi	GMD
	German Mark	DEM
	Ghanaian Cedi	GHC
	Gibraltar Pound	GIP
	Gold (oz.)	XAU
	Greek Drachma	GRD
	Guatemalan Quetzal	GTQ
	Guinea Franc	GNF
	Guyanese Dollar	GYD
	Haitian Gourde	HTG
	Honduran Lempira	HNL
	Hong Kong Dollar	HKD
	Hungarian Forint	HUF
	Iceland Krona	ISK
	Indian Rupee	INR
	Indonesian Rupiah	IDR
	Iranian Rial	IRR
	Iraqi Dinar	IQD
	Irish Punt	IEP
	Israeli New Shekel	ILS
	Italian Lira	ITL
	Jamaican Dollar	JMD
	Japanese Yen	JPY
	Jordanian Dinar	JOD
	Kazakhstan Tenge	KZT
	Kenyan Shilling	KES
	Kuwaiti Dinar	KWD
	Lao Kip	LAK
	Latvian Lats	LVL
	Lebanese Pound	LBP
	Lesotho Loti	LSL
	Liberian Dollar	LRD
	Libyan Dinar	LYD
	Lithuanian Litas	LTL
	Luxembourg Franc	LUF
	Macau Pataca	MOP
	Malagasy Franc	MGF
	Malawi Kwacha	MWK
	Malaysian Ringgit	MYR
	Maldive Rufiyaa	MVR
	Maltese Lira	MTL
	Mauritanian Ouguiya	MRO
	Mauritius Rupee	MUR
	Mexican Peso	MXP
	Mongolian Tugrik	MNT
	Moroccan Dirham	MAD
	Mozambique Metical	MZM
	Myanmar Kyat	MMK
	NL Antillian Guilder	ANG
	Namibia Dollar	NAD
	Nepalese Rupee	NPR
	New Zealand Dollar	NZD
	Nicaraguan Cordoba Oro	NIO
	Nigerian Naira	NGN
	North Korean Won	KPW
	Norwegian Kroner	NOK
	Omani Rial	OMR
	Pakistan Rupee	PKR
	Palladium (oz.)	XPD
	Panamanian Balboa	PAB
	Papua New Guinea Kina	PGK
	Paraguay Guarani	PYG
	Peruvian Nuevo Sol	PEN
	Philippine Peso	PHP
	Platinum (oz.)	XPT
	Polish Zloty	PLZ
	Portuguese Escudo	PTE
	Qatari Rial	QAR
	Romanian Leu	ROL
	Russian Rouble	RUB
	Samoan Tala	WST
	Sao Tome/Principe Dobra	STD
	Saudi Riyal	SAR
	Seychelles Rupee	SCR
	Sierra Leone Leone	SLL
	Silver (oz.)	XAG
	Singapore Dollar	SGD
	Slovak Koruna	SKK
	Slovenian Tolar	SIT
	Solomon Islands Dollar	SBD
	Somali Shilling	SOS
	South African Rand	ZAR
	South-Korean Won	KRW
	Spanish Peseta	ESP
	Sri Lanka Rupee	LKR
	St. Helena Pound	SHP
	Sudanese Dinar	SDD
	Sudanese Pound	SDP
	Suriname Guilder	SRG
	Swaziland Lilangeni	SZL
	Swedish Krona	SEK
	Swiss Franc	CHF
	Syrian Pound	SYP
	Taiwan Dollar	TWD
	Tanzanian Shilling	TZS
	Thai Baht	THB
	Tonga Pa'anga	TOP
	Trinidad/Tobago Dollar	TTD
	Tunisian Dinar	TND
	Turkish Lira	TRL
	Uganda Shilling	UGS
	Ukraine Hryvnia	UAH
	Uruguayan Peso	UYP
	U.S. Dollar	USD
	Utd. Arab Emir. Dirham	AED
	Vanuatu Vatu	VUV
	Venezuelan Bolivar	VEB
	Vietnamese Dong	VND
	Yugoslav Dinar	YUN
	Zambian Kwacha	ZMK
	Zimbabwe Dollar	ZWD

FIG. 11 shows an example of a cell A3 that contains the sum of two other cells (A1 and A2).

Moving and Selecting Cells

To navigate within a worksheet, the designer clicks the cursor on the cell that the designer is interested in editing. The chosen cell will be highlighted with a different color background. The designer can also select multiple cells by clicking and dragging the cursor across several cells. This is useful when the designer wants to apply a format to several cells at once. When the designer has selected multiple cells, the "target" cell is highlighted with a darker color than other cells in the selection. Note that while the designer can simultaneously format all the cells in a selection, the designer can only edit the value (or formula) of the target cell.

The designer can select an entire row or column by clicking on the row or column header within the worksheet (e.g. "A" or "5"). The designer can also select the entire worksheet by clicking on the single cell at the intersection of the row and column headers (to the left of "A" and above "1"). This is useful for applying global formatting, such as changing the font, to the entire sheet.

The arrow keys can also be used to navigate through the spreadsheet. The Tab key moves the selection one cell to the right; Shift-Tab moves it one cell to the left. Enter moves the active cell down.

Entering Data

To enter data into a spreadsheet, the designer highlights the cell of interest and begins typing. The typed data will replace any information currently contained within the cell. The designer can edit a cell's data by highlighting the cell of interest and moving the cursor to the command line. The designer locates the cursor wherever the designer wants to insert or replace data and begins typing.

Types of Cell Data

Several kinds of data that can be displayed in a spreadsheet cell:

Labels: Labels are simply strings of text that appear within a cell. They are typically used to name rows or columns of numbers, and for descriptive text introducing or explaining a spreadsheet. Text can also be used within formulas, but they must be enclosed in quotes (i.e. "text"). The "&" operator lets the designer create a long text string by "adding" together a number of smaller components.
Numbers: The designer can input numbers with or without a decimal point, and these numbers can be expressed as currency, as integers, as decimal numbers or as percentages (note, when using percentages 0.01=1%, 0.30=30%, etc).

Dates and Times: also supports the input and display of date and time data. Use a "/" to separate the parts of a date; for example, type 10/6/1996. When entering a time, use the colon, ":" to separate hours and minutes. To enter a time based on the 12-hour clock, type a space, then an "A" or "P" to indicate AM or PM.

Formulas: Formulas are mathematical expressions that let the user perform a calculation within a spreadsheet cell. Formulas are always preceded with the "=" symbol. These formulas can be simple numerical expressions like, "=34*67/12." However, the real power of spreadsheet lies in using cell references as parts of formulas. Each cell has an address that is expressed as the intersections of its column and row. For example, the cell in column 5, row 3, is E3. Instead of using absolute numbers in formulas, the designer can use a cell reference to employ the number within a given cell as part of a formula. The formula, "=B3*C4" multiplies the values within the cells B3 and C4, and displays the result in the cell containing the formula.
Absolute and Relative References

One of the most useful things about using cell references within formulas is that the designer can easily duplicate those formulas in other cells using the copy and paste commands. For example, if the designer is creating a spreadsheet that describes his monthly expenses, and the designer has summed all expenses for January into cell B8, the designer can copy and past that formula into cell C8 for February, D8 for March, etc. To accomplish this without making a mistake, however, the designer needs to understand absolute and relative cell references.

Relative references are written in the notation introduced above, column letter, row number. When the designer copies and pastes a relative reference, it is automatically adjusted, based on the number of cells between where it is copied from, and where it is being copied to. In the example above, say the formula in cell B8 is, "=sum(B5:B7)," which simply adds all the numbers in cells B5 through B7. If the designer copies this formula from B8 and pastes it into C8, it will automatically be adjusted from "=sum(B5:B7)" to read, "=sum(C5:C7)." If it was pasted into D8, the formula in that cell would read, "=sum(D5:D7)," and so on.

Note that either the number or letter in a cell reference, or both can be relative or absolute. In other words, the designer can write, A2, $A2, A$2, or $A$2. The described embodiment checks to see which letters or numbers the $-sign applies to, when the designer is doing a copy/paste, and adjusts formulas accordingly.

Functions

The described embodiment contains a wide variety of built-in functions that can perform sophisticated analysis. Currently, the functions are broken down into the following categories:

Math: Such as SUM(), which adds the contents of listed cells.
Financial: Such as PMT(), which calculates the payment on a loan.
Logical: Such as IF(), which lets the designer display one of two outcomes, based on whether an expression is true or false.
Lookup functions: Such as HLOOKUP(), which lets the designer look up a value in a row full of data.
Date and Time: Such as DAY(), which extracts the day of the month from a date string.
Text: Like CONCATENATE(), which creates a text string by putting several other strings together.
Web: Like QUOTE(), which provides a 20 min delayed quote for a given stock.
Functions are used in formulas, just like numbers or cell values (e.g. =A3+B7+SUM(C12,C14,H17)). For a complete listing of functions, see Table 4.
Because the program is dynamically delivered, the user always gets the newest version with the latest functionality.

TABLE 4

Math Functions
abs( number )	Returns the absolute value of the
	number
acos( number )	ArcCosine of a value (angle returned in
	radians). If the designer wants the angle
	in degrees, combine acos( ) with the
	degrees( ) function. For example,
	degrees(acos(0.5)) = 60.
asin( number )	ArcSine of a value (angle returned in
	radians). If the designer wants the angle
	in degrees, combine asin( ) with the
	degrees( ) function. For example,
	degrees(asin(0.5)) = 30.
atan( number )	ArcTangent of a value (angle returned in
	radians). If the designer wants the angle
	in degrees, combine atan( ) with the
	degrees( ) function. For example,
	degrees(atan(1.0)) = 45.
atan2( x-coord, y-coord )	ArcTangent of the angle between the x-
	axis and a line between the origin (0,0)
	and a point with the coordinates (x-coord,
	y-coord). The angle is returned in
	radians. If the user wants the angle in
	degrees, combine atan2( ) with the
	degrees( ) function. For example,
	degrees(atan2(3,3)) = 45.
average( number1,	Calculate the average of all numbers.
number2 . . . )
ceiling( number, round )	Rounds a number up to the nearest
	multiple of round. For example, ceiling
	(2.25, 0.5) = 2.50
cos( angle )	Cosine of an angle (in radians). If his
	angle is in degrees, combine cos( ) with
	the radians( ) function. For example,
	cos(radians(30)).
degrees( radians )	Converts an angle in radians to degrees.
e( )	Returns the constant, e.
exp( exponent )	Raises the constant e to the specified
	exponent
floor( number, round )	Rounds value down to the nearest
	multiple of round. For example, floor
	(2.25, 0.5) = 2.00.
int( number )	Returns the integer (or non-fractional)
	part of number. Note, int( ) does not
	round up or down. For example,
	int(3.765) = 3.
ln( number )	Takes the natural logarithm (base e) of
	number.
log10( number )	Takes the logarithm (base 10) of number.
max( number1, number2 . . . )	Returns the maximum value of all
	numbers.
median( number1,	Returns the median (or middle) value of a
number2 . . . )	set of numbers.
min( number1, number2 . . . )	Returns the minimum value of all
	numbers.
mod( number, divisor )	Returns the remainder of number/divisor.
	For example, mod(14,4) = 2.
pi( )	Returns the constant, pi.
power( number, exponent )	Raises number to the specified exponent.
	For example, power(3,2) = 9.
product( number1,	Multiplies all the number arguments
number2 . . . )	together and returns the product. For
	example, product(3,2,5) = 30.
radians( degrees )	Converts an angle in degrees to radians.
rand( number1, number2 )	Returns a random number. Both
	arguments are optional. If number1 is
	included, rand(number1) returns a
	random number between 0 and number1.
	If number2 is included, then it returns a
	number between number1 and number2
round( number, places )	Rounds value up or down to the specified
	number of decimal place. For example,
	round(3.14159, 2) = 3.14.
sin( angle )	Sine of an angle (in radians). If his angle
	is in degrees, combine sin( ) with the
	radians( ) function. For example,
	sin(radians(30)).
sqrt( number )	Takes the square root of a number
sum( range )	Adds the values of all the listed cells and
	ranges. Multiple cells addresses or ranges
	can be used.
tan( angle )	Tangent of an angle (in radians). If the
	designer angle is in degrees, combine
	tan( ) with the radians( ) function. For
	example, tan(radians(30)).
trunc( number )	Truncates a number to an integer. For
	example, trunc(12.893) = 12.
Financial Functions
fv( r, nper, pay )	The future value of a series of payments,
	at a specific interest rate, where:
	r = the interest rate
	nper = the number of periods of
	compounding
	pay = the payment amount
	Note: Make sure the designer is using
	consistent values for r and np. If
	payments are made monthly, the interest
	rate must be monthly (annual rate/12), as
	well. For monthly payments, nper =
	number of years × 12.
ipmt( r, per, np, pv )	Returns the interest portion of a
	payment for a specified period, where:
	r = the interest rate
	per = the number of the period of interest
	nper = the total number of compounding
	periods.
	pv = the value of the series of payments,
	today.
	Note: Make sure the designer is using
	consistent values for r and np. If
	payments are made monthly, the
	interest rate must be monthly (annual
	rate/12), as well. For monthly
	payments, nper = number of years × 12.
nper( r, pay, pv )	Generates the number of periods needed
	to pay off loan, pv, based on constant
	periodic payments and a constant
	interest rate.
	r = the interest rate
	pay = period payment amount (note: this
	is normally a negative number).
	pv = the value of the series of payments,
	today.
	Note: Make sure the designer is using a
	consistent value for r. If payments are
	made monthly, the interest rate must be
	monthly (annual rate/12).
npv( r, pay1, pay2 . . . )	Returns the net present value of a series
	of payments, where:
	r = the interest rate per period.
	pay1 . . . payn = payment amounts (note:
	payments must be equally spaced in time)
	Note: Make sure the designer is using a
	consistent value for r. If payments are
	made monthly, the interest rate must be
	monthly (annual rate/12).
pmt( r, nper, pv )	Returns the payment amount for a loan of
	amount pv, where:
	r = the interest rate
	nper = the total number of compounding
	periods
	pv = the loan amount
ppmt( r, per, nper, pv )	Returns the principle portion of a
	payment for a specified period, where:
	r = the interest rate
	per = the number of the period of interest
	nper = the total number of compounding
	periods.
	pv = the value of the series of payments,
	today.
	Note: Make sure the designer is using
	consistent values for r and np. If
	payments are made monthly, the interest
	rate must be monthly (annual rate/12), as
	well. For monthly payments, nper =
	number of years × 12.
pv( r, nper, pay )	Returns the present value of a series of
	payments, where:
	r = the interest rate
	nper = the total number of compounding
	periods.
	pay = the amount paid each period (note:
	this is normally a negative number)
	Note: Make sure the designer is using
	consistent values for r and nper. If
	payments are made monthly, the interest
	rate must be monthly (annual rate/12), as
	well. For monthly payments, nper =
	number of years × 12.
Logical and
Information Functions
iserror( cell )	Returns true (the value 1) if the cell
	contains an error value. Otherwise, it
	returns 0 (false).
isblank( cell )	Returns true (the value 1) if the cell is
	blank. Otherwise, it returns 0 (false).
isstring( cell )	Returns true (the value 1) if the cell
	contains a string value. Otherwise, it
	returns 0 (false).
isvalue( cell )	Returns true (the value 1) if the cell
	contains a numeric value. Otherwise, it
	returns 0 (false).
and( arg1, arg2 . . . )	Returns true (the value 1) if all arguments
	are true.
or( arg1, arg2 . . . )	Returns true (the value 1) if any or all of
	the arguments are true
xor( arg1, arg2 . . . )	Returns true (the value 1) if only one of
	the arguments is true.
not( logical )	Returns false (the value 0) if true; true
	(the value 1) if false
if( test, trueval, falseval )	Returns trueval if test returns true,
	falseval if test returns false
false( )	Returns false (the value 0).
true( )	Returns true (the value 1).
Lookup Functions
select( index, val1, val2 . . . )	Selects value number index, from a set of
	values, or from a range.
	For example, select( 3, apple, baker,
	charlie, david) = charlie
hlookup( val, range, row )	Does a lookup for a value within a
	horizontal range of cells, and returns a
	cell value from the column in which the
	looked-up value was found.
	val = number or text value to be looked-
	up.
	range = range of cells containing both the
	row of values to do look-up in, as well as
	the row of values containing return
	values.
	row = number of the row within "range"
	containing return values.
vlookup( val, range, col )	Does a lookup for a value within a
	vertical range of cells, and returns a cell
	value from the row in which the looked-
	up value was found.
	val = number or text value to be looked-
	up.
	range = range of cells containing both the
	row of values to do look-up in, as well as
	the column of values containing return
	values.
	col = number of the column within
	"range" containing return values.
Date and Time Functions
date( year, month, day )	Returns the serial number (the internal
	date and time representation) for a
	specified date, where.
	year = a four-digit number from 1900 to
	9999.
	month = a number from 1 (January) to
	12 (December).
	day = a number from 1 to 31.
datevalue( datestring )	Returns the serial number (the internal
	date and time representation) for a date
	string in the day/month/year format.
day( serialnumber )	Returns the day of the month
	corresponding to the supplied serial
	number.
hour( serialnumber )	Returns the hour corresponding to the
	supplied serial number. Hours are
	returned in military format ranging from
	0 to 23. where 0 = 12 AM and 23 =
	11 PM.
minute( serialnumber )	Returns the minute corresponding to the
	supplied serial number. Minutes range
	from 0 to 59.
month( serialnumber )	Returns the month of the year
	corresponding to the supplied serial
	number. Months range from 1 (January)
	to 12 (December).
now( )	Returns the serial number (the internal
	date and time representation) for the date
	and time, combined.
	Use today( ) if the designer only wants the
	date value returned.
second( serialnumber )	Returns the second corresponding to the
	supplied serial number. Seconds range
	from 0 to 59.
time( hour, minute, second )	Returns the serial number (the internal
	date and time representation) for a
	specified date, where:
	hour = a two-digit number ranging from
	0 (12 AM) to 23 (11 PM).
	minute = the minute of the hour ranging
	from 0 to 59.
	second = a number ranging from 0 to 59.
timevalue( timestring )	Returns the serial number (the internal
	date and time representation) for a
	specified time, where timestring is in the
	format, hour:minute:second.
	hour = a two-digit number ranging from
	0 (12 AM) to 23 (11 PM).
	minute = the minute of the hour ranging
	from 0 to 59.
	second = a number ranging from 0 to 59.
today( )	Returns the serial number (the internal
	date and time representation) for the date.
	Use now( ) if the designer wants both the
	date and time returned.
weekday( serialnumber/	Returns the day of the week ranging from
datestring )	1 (Sunday) to 7 (Saturday). The argument
	can be supplied either as an internal date-
	time serial number or as a date string in
	the format month/day/year.
year( serialnumber/datestring )	Returns the 4-digit year. The argument
	can be supplied either as an internal date-
	time serial number or as a date string in
	the format month/day/year.
Text Functions
concatenate( string1,	Joins all the strings supplied as
string2 . . . )	arguments. The designer can also use the
	"&" operator to do concatenation.
User Interface Functions
menu( key1, value1, key2,	Builds a popup menu within a cell.
value2 . . . )	key = text to be displayed in the popup
	menu.
	value = value that is seen by other cells,
	and used in calculations when "key" is
	chosen.
Spreadsheet
Linking Functions
scell( row, col,	Returns the last saved value of a cell
"filename@membername" )	contained within another spreadsheet. If
or	the member is logged in, any spreadsheet
scell( "file-	within their "My Files" directory may be
name@membername!cell" )	referenced. Otherwise, any public file
	may be referenced.
	row, col = row and column number of
	referenced cell.
	cell = cell reference in "a1" format. Note:
	entire fn@mn!cell string must be
	enclosed in quotes.
	filename = file name from "My Files"
	membername = member name.
	Note that "filename@membername" can
	be replaced by the fileID retrieved using
	sfind( ).
svlookup( val, file-	val = number or text value to be looked-
name@membername!range",	up.
col )	filename = file name from "My Files"
	membername = member name. For
	gallery files use the "gallery member
	name.
	range = range of cells containing both the
	column of values to do look-up in, as
	well as the column of values containing
	return values. Note: entire fn@mn!range
	string must be enclosed in quotes.
	col = number of the column within
	"range" containing return values.
	Note that "filename@membername" can
	be replaced by the fileID retrieved using
	sfind( ).
shlookup( val, file-	val = number or text value to be looked-
name@membername!range",	up.
row )	filename = file name from "My Files"
	membername = member name. For
	gallery files use the "gallery member
	name."
	range = range of cells containing both the
	row of values to do look-up in, as well as
	the row of values containing return
	values. Note. entire fn@mn!range string
	must be enclosed in quotes.
	row = number of the row within "range"
	containing return values.
	Note that "filename@membername" can
	be replaced by the fileID retrieved using
	sfind( )
slist( membername )	slist( ) returns a vector of filenames and
	fileIDs for use with the menu( )
	function.
	For example, = menu(slist( "smarty")))
	would return a list of the public files for
	member "smarty". If the member is
	logged in, slist( ) will return a list of all
	"My Files" spreadsheets. Otherwise, it
	will build a list of public files for a
	referenced member.
sfind( "filename",	sfind( ) returns the fileID of a file given
"membername" ) or	its name (from "My Files") and
sfind	membername.
( "filename@membername" )	Use sfind( ) when the designer has to
	make multiple references to cells in
	another spreadsheet. Put
	"=sfind("fn@mn")" in a spreadsheet
	cell, and reference that cell in scell( )
	and svlookup( )function in other cells.
	Using the fileID instead of the
	filename@membername combination
	in scell( ) and svlookup( ) functions can
	make linked cell references much
	faster.

Cell Ranges

Cell ranges are shorthand notation for describing a group of contiguous cells. The cell range expression, "D3:D7," is equivalent to "D3, D4, D5, D6, D7." Thus, the expression, SUM(D3:D7), is the same as D3+D4+D5+D6+D7. Many of the functions that take multiple arguments can use range values as well as single cell references.

Error Messages

When the described embodiment detects and error—either in a formula the designer has input, or within the spreadsheet itself, it will normally report an "[error]" (or similar) message in the appropriate cell. A couple of the common mistakes that generate error messages are unbalanced parentheses, and referring to a cell that has an invalid value. For example, the formula, "=IF(A3>0,0, SUM(A2:C2)," will generate an error because it is missing a closing parenthesis. Similarly, the expression, "=A2+B2," will display an error if either A2 or B2 contains an invalid value (such as a text label), instead of a number.

3. EDITING THE SPREADSHEET

Setting the Spreadsheet Size

FIGS. 12(a)-12(e) show examples of drop down menus used in a described embodiment of a web-based spreadsheet in accordance with the present invention. FIG. 12(a) is the drop down file menu.

New: Opens a new browser window with a blank spreadsheet.

Open: Brings up a list of the members files and lets them select an open a file.

Close: Closes the current spreadsheet window. If the current document hasn't been saved, the member is prompted to save it.

Save: Saves the current document.

Save As: Saves the current document with a new, designer-prompted name.

Export to Excel: Exports the currently active spreadsheet into Excel format and asks the designer to save on to their computer.

Import Excel file: Prompts designer to select an Excel file to import as a new spreadsheet.

Set Size: Brings up a dialog letting the designer change the number of rows and columns in the spreadsheet. If the data will be cropped (resulting in data loss) a warning is displayed.

Print: Opens a static view of the spreadsheet in a new window as a preview of how the spreadsheet will be printed, and then prints the active spreadsheet.

The following paragraphs describe some of the commands on this menu. When the designer creates a new spreadsheet, it is automatically created in the default dimensions. After initializing a spreadsheet, there are several ways that the designer can change the size of the spreadsheet. The Set Size command, found in the FILE menu lets the designer immediately adjust the number of rows and columns in his spreadsheet. Increasing the number of rows or columns will add cells onto the edge of the spreadsheet. Decreasing the number of rows or columns will crop cells from the edge of the sheet. The designer should be careful when removing rows or columns not to delete any needed data.

FIG. 12(c) describes the drop down Tools menu. The TOOLS menu has a variety of operations that affect the display, contents and ordering of spreadsheet cells. The tools menu includes;

Insert Row/Column: Adds rows or columns, shifting highlighted rows or columns, down or to the right.

Delete Row/Column: Deletes rows or columns containing the currently highlighted cell or range of cells.

Autofit Row/Column: Automatically resizes rows or columns to accommodate the size of the content in highlighted cells.

Sort Ascending/Descending: Sorts a cell range by row, based on the leftmost column of cells.

Hide Grid: Suppresses the display of the light blue grid around spreadsheet cells.

Calculator Preview: Lets the designer simulate the look of a spreadsheet in Calculator mode, and adjust which cells can be edited when viewed as a calculator.

Summary & Instructions: Brings up a dialog box that lets designers type in a file description and instructions on how to use the spreadsheet. These instructions are accessible when the document is opened in Calculator Mode.

The following paragraphs describe some of the commands on this menu. The Insert Column, Insert Row, Delete Column and Delete Row commands, found under the TOOLS menu, perform the corresponding operations. To delete a column or row, the designer clicks on the row number or column letter (e.g. "3" or "D"), to highlight that row or column. Or, the designer selects cells within the row(s) and column(s) that he wants to delete and then selects the Delete Column or Delete Row command from the menu. Alternatively, the designer can simply select a cell or cells within the row or column the designer wants to delete before issuing the command. The designer can also right click on the selected cells to choose Insert/Delete Row or Column from the Tools menu that appears for the selected cells. The designer can delete multiple columns or rows in the same operation by highlighting all the rows or columns the designer wants to delete, at once. If the designer wants to delete multiple rows or columns simultaneously, they must be contiguous. To insert columns or rows, the designer selects the column or row immediately after the location where the designer wants the new column or row to be added. The new column or row will be added before the highlighted cells. The designer can insert multiple columns or rows by highlighting multiple columns or rows before the designer executes Insert Column/Row. New rows or columns will increase the total size of the spreadsheet.

Resizing Rows and Columns

The designer can modify the width of a row or column by clicking in the row or column header next to the dividing line between the columns or rows the designer wants to change, and then dragging to the right, left, up or down. The resizing mechanism is sensitive to exactly where the designer clicks. If the designer has clicked in an appropriate location, a gray outline will appear around the column or row that the designer is about to resize.

The designer can resize multiple columns or rows simultaneously by highlights all the rows or columns the designer wants to resize, clicking on the edge of one of these rows or columns, and then dragging the border accordingly. All the rows or columns will be sized to the width of the one the designer set.

Clearing and Deleting Data

FIG. 12(b) is the drop down Edit menu. The Edit menu includes the commands:

Cut, Copy, Paste: When used in combination, these commands let the designer duplicate a cell or cell range elsewhere on the spreadsheet, or to move that cell or range to a new location within the sheet.

Clear: Erases cell contents

Fill Down/Right: Takes the highlighted range and fills blank cells with either a number or series of numbers determined by the topmost or leftmost cells in the range.
Using, Cut, Copy and Paste

Cut, Copy and Paste are found on the EDIT menu, and work the same way in as they do in most software applications. To cut or copy a group of cells, first select the "source" cells by clicking on the first cell of interest and dragging to highlight the cell range. Choose Cut or Copy from the EDIT menu, or click on the corresponding icon, on the Formula Bar. The cell range will be enclosed with a black border.

The designer selects the destination cell or cell range in the same manner, and then chooses Paste, or clicks on the Paste icon. If the designer initially chooses Copy, a duplicate of the source cells will appear in the destination location. As in most software applications, the keyboard shortcuts for Cut, Copy and Paste (Ctrl-X, Ctrl-C, Ctrl-V) and the right-click menu options for these actions are available for selected source cells. The formula in these cells will be adjusted based on whether their cell references were relative or absolute. If the designer initially chooses Cut, the selected cells will be moved to the new location. The references in formulas within this range will continue to point to the original cells.

Filling and Sorting

The Fill Down and Fill Right commands on the EDIT menu let the designer fill a section of cells with a set of numbers or a series of numbers. To use the Fill Down command, the designer selects a range of cells, making sure that the topmost cell in the range contains the value the designer wants to use for the fill. Choose Fill Down to populate the range with that value. The Fill Right command works the same as Fill Down except it propagates its values along rows instead of columns.

The Sort Ascending and Sort Descending commands on the TOOLS menu let the designer reorder a rectangular selection by the values in the first column of the range. The Sort commands will only reorder the selection row-wise.

Formatting Numbers, Dates and Time with the Format Menu

FIG. 12(d) is the drop down Format menu. The FORMAT menu contains formatting commands for numbers and dates. These commands include:

General: Removes any pre-determined format for highlighted cells. Most commonly used for cells containing text.

Custom: Brings up the Custom Format dialog, which gives access to a flexible system for setting the display of numbers and dates within cells.

8,901, 6% etc: Pre-built formats, which can instantly applied to a range of selected cells.

Wrap Text: Lets the designer wrap text within the right and left cell borders. The default behavior is to let text overrun the cell borders.

The described embodiment contains a flexible and powerful engine for formatting numbers, dates and times. Numbers can be expressed as currency (preceded by $), decimal values, integers, percentages, or dates. To format a number of range of numbers, the designer selects the cell or range of cells the designer wishes to format. He then clicks on the FORMAT menu and drags down to highlight the appropriate format style (FIG. 6a). The selected format will be applied to those cells.

Using the Custom Format Dialog

The FORMAT menu contains a variety of commonly used number, time and date formats. There will be occasions, however, when the designer will need a format that is unavailable on the FORMAT menu. The Custom . . . format control dialog, accessed from the FORMAT menu, lets the designer specify exactly how the designer would like a number, time or date to appear. The dialog lets the designer specify four sections of formatting: for positive numbers, for negative numbers, for zero and for errors.

Numbers: The dialog uses a set of special codes that tell how to show numbers. The symbol "#" is used as a placeholder for "optional" digits within a format (see examples below). The symbol "0" is used to indicate mandatory digits within a number format. When a "0" is specified within a format string, it means that the number will be padded with 0's if there are no digits in the appropriate place in the number, natively. For example, the number "0.3" formatted with the code, "0.00," will display as, "0.30." Table 5 has examples of number formats.

	TABLE 5

	To display	Use the code

	1526.37 as 1,526.4	#,###.#
	15.2 as 15.200	##,000 (or #,###.000)
	.327 as 0.33	0.00
	56.3 as $56.30	$##.00
	-56.3 as ($56.30)	($##.00) in the negative
		number field

Note, that if the designer use symbols other than "#" or "0", those symbols are inserted as-is in the format. This is a useful way to include symbols for non-US currency. A quantity of 35 British pounds (£35), for example, could be expressed with the code, "£##".

Date: recognizes date strings by the combination of the letters, "m", "d", "y", with the delimiter. "/". The designer can use the "mm" or "dd" to insure that day or month numbers are always two digits (e.g. "2" is always "02"). Years are always expressed as either 2-digits or 4 (e.g. 95 or 1995). Table 6 has date formats.

	TABLE 6

	To display	Use the code

	April 5, 1989 as 4/5/89	m/d/yy
	April 5, 1989 as 5/4/1989	d/m/yyyy
	April 5, 1989 as 050/4/1989	dd/mm/yyyy

Time: recognizes times by the combination of letters, "h", "m", "s", with the delimiter. ":". The designer can use the "hh" or "mm" to insure that hours or minute numbers are always to digits (e.g. "6" is always "06"). Use the tag, "AP" or "ap" to indicate "AM/PM" formatting. Otherwise military (24 hr) formatting is assumed. Table 7 has time formats.

	TABLE 7

	To display	Use the code

	9:25 PM as 9:25 PM	h:m AP or h:mm AP
	8:05 PM as 20:05 PM	h:mm
	9.25 PM as 09:25 PM	hh:m AP

Using the Type, Color and Border Controls

Fonts can be selected, styled, sized, colored and aligned using the controls on the Format Bar. Like all formatting controls, type, color and border controls can be applied to a single cell or to a range of cells. These controls work identically to similar controls in standard desktop applications. Note that the designer can only apply this formatting to the entire text with a cell. The designer cannot select a piece of the text and apply type controls to only that piece.

The Color menu, accessed from the Format Bar, allows the designer to set the text color and background colors for a cell. The designer clicks on the color menu icon to pop-out the color selector, then chooses a swatch to change either text or cell color.

The Border menu, shown in FIG. 12(e), is also accessed from the Format Bar, and lets the designer control the outline style on the sides of spreadsheet cells. The designer can apply a variety of line styles to cell edges. To use the Border menu, the designer clicks on the menu icon to pop-out the available border styles. The designer can use the icon with no lines to erase all existing borders from a cell or range of cells. Some of these controls work differently depending on whether the designer has selected a single cell or a range of cells. This lets the designer apply a thin outline around the outside of a rectangular selection, instead of to every cell within a selection.

Adjusting Text Wrapping

By default, any text typed within a spreadsheet cell will flow across the cell border into adjacent cells—if it is too large to fit into its own cell (and if there is no text or number in the adjacent cell). The designer can change this behavior by turning text wrapping on. With text wrapping on, text within a cell will wrap at the cell edge to create multiple lines within the same cell. The designer can then adjust the row width so these multiple lines display correctly. To turn on text wrapping, the designer selects the Wrap Text command from the FORMAT menu.

Copying Cell Formats with the Format Stamp

The Format Stamp is used to apply a set of cell formats to a cell range. With the Format Stamp, the designer can adjust the font type, style, color, alignment and border for a single cell and then apply all those formats to other cells in a single operation. To use the Format Stamp, the designer selects the cell or cell range that has the formatting the designer wants to copy. The designer clicks the Format Stamp icon on the Format Bar, then highlights the cell or range to which the designer wants to apply the formatting.

The designer can also use the Format Stamp on column or row headers by choosing a column letter or row number, clicking the Format Stamp, and then clicking on the column or row the designer wants the format applied to. If the designer wants to copy a cell's formatting multiple times, double-click the Format Stamp icon (it will stay in the depressed state), then applies the formatting to all the relevant selections. When the designer is finished applying the formatting, click the Format Stamp icon again to turn it off.

FIG. 13(a) shows an example window enabling a user to email a spreadsheet. This window is displayed when the user clicks on "Email this page" 206 (see FIG. 2). The window allows the user to specify a TO email address and a message. When the user presses the SEND button in the window, the window of FIG. 13(b) is displayed.

FIG. 13(b) shows an example email used to send a link to a spreadsheet. In this example, John Smith has sent a web-based spreadsheet to Sue Jones. The email contains a link (http://www.blox.com/open?id=002e3b8c4bb8) to server 104 with an ID of the spreadsheet. When Sue Jones opens the email and clicks on it, a browser will open and display the spreadsheet.

4. MACROS

The described embodiments let designers enhance the program themselves using macros. Macros allow the designer to extend the built-in capabilities of the application by wiring his own JavaScript code. Certain embodiments will also feature a Macros Gallery where the designer can share the designer's own macro and reuse the code of others.

Custom Functions Using Macros

The described embodiment contains a rich library of built-in functions. As a spreadsheet author, the designer combines these functions into cell formulas to make his spreadsheets work. For most people, most of the time, the built-in function set is all that is needed.

Sometimes, however, the designer will find himself wishing that the system had a function that it doesn't. Maybe the designer needs to do arithmetic on dates, or find the volume of sphere, or do a search and replace operation on a bit of text. Or perhaps the designer needs to do a calculation specific to his company or business. In some of these cases, the designer might suffice with creating a complex cell formula. But even if the designer could, it would probably be difficult to reuse this formula in other cells or other spreadsheets.

Custom functions are the answer. Using JavaScript, the designer can invent a new spreadsheet function that does exactly what the designer wants. Here are the hallmarks of a custom function:

It is invoked as part of the cell formula

It accepts zero or more arguments, which can be literal values (like numbers) or cell references.

It returns a single value.

It does not internally set the value of other cells (see setcell() below).

It may internally call other built-in or custom functions.

In other words, a custom function is just like a built-in function: it returns a value based on passed-in arguments and internal logic.

Traditional Macros

A macro can:

get and set the value of multiple spreadsheet cells.

get and set the value of global variables.

use timers to conduct periodic processing.

call other macros or built-on functions.

This may sound simple, but the capabilities here are very powerful. By rewriting cell contents, the designer can change entire parts of his spreadsheet based on designer input or other factors. In fact, macros can effectively treat the spreadsheet grid as in an output device and dynamically present information in ways quite beyond the capabilities of a traditional spreadsheet.

Invoking a Macro

In the described embodiment, there are three ways to invoke a macro:

Enter it into a spreadsheet formula

Automatically invoke it at load time using the special init() macro.

Call it from another macro.
From a Spreadsheet Formula

The designer can enter a macro call into a cell formula just as the designer would be built-in function. Simply type its name following by opening and closing parenthesis (and any arguments). Keep in mind that all cell formulas (as opposed to literal values) must begin with an equal sign ("="). Here is a simple example of a cell formula using a macro:

=makeLowerCase(B3)

In keeping with the case-insensitive nature of formulas, capitalization is not important when referencing a macro in a formula.

A macro will execute whenever the cell is recalculated. This is most useful for custom functions that are designed to return a single value to the formula. However, with careful design, it is possible to invoke more far-reaching macros from a cell. See the Advanced topics section for more information.

The Init()function

If the designer creates a macro called "init()", it will be automatically executed when his spreadsheet loads from server 104 and before any cells start to recalculate. This provides an easy way for the designer to initialize the state of his application by setting up globals, updating cell contents, and starting timers.

From Another Macro

Naturally, the designer can call one macro from another. To do so, simply reference a macro name as the designer would any other function in JavaScript.

The Macro Editor

FIGS. 14(a)-14(d) show an example spreadsheet created and viewed in accordance with the present invention and including macros. In this example, the cell E7 has an associated macro "theNote," which has 5 inputs, as shown in function area 222.

FIG. 14(b) shows two examples of macro editor. The macro editor is used to allow the designer to create and modify his macros. To get there, choose Macros . . . from the EDIT menu in FIG. 12(b).

The macro editor is a very simple text editor with these main parts:

The text area. This is where the designer types in this macro code and perform basic editing operations like insert, delete, copy, cut, paste, and undo. When the designer first opens the macro editor, the text area contains an empty init() function to get the designer started.

The function jump menu. This menu contains the names of all the macro functions currently defined in the text area. To jump to a function, just select its name in the menu.

Clicking OK in the macro editor will save any edits you've made, execute his init() function (if any), and recalculate any cells in the spreadsheet that have macro calls in their formulas. In this sense, clicking OK in the macro editor is a lot like re-opening the spreadsheet.

Writing Macros

This section discusses the details of writing macro code. A basic discussion of JavaScript syntax and using JavaScript in web browsers is found in: JavaScript: The Definitive Guide (O'Reilly Press)
JavaScript vs. Macros

Macro code is, by and large, standard JavaScript code. It uses the same syntax, control structures, data types, built-in functions and objects, and so on.

This is not too surprising when the user considers that the described embodiment itself is written in JavaScript, and that the browser conveniently supplies a built-in JavaScript interpreter.

What is Included and What is Not

All the features of core JavaScript are available to a macro. In addition to the standard data types (scalars, arrays, objects), operators (like +, *, &&, ==,etc.) and statements (like if/else, switch, for, for/in, while, and return) the designer also has access to the core built-in objects like Math and Date

Note that the core JavaScript language is defined separately from any context (like a web browser) in which it is used. This is an important distinction, because the entire DOM (document object model) that the designer may be accessing when scripting is not available in a browser. The DOM—including the window and document objects and their many sub-objects—are not available in macros. If they were, macro code could arbitrarily rewrite or delete portions of this page, which in this case is itself.

That is important enough to restate: if the designer macro code tries to access the window or document objects or other parts of the DOM, it will generate an error. These objects do not exist in the macro's scope, so do not try to use them.

There are a few parts of the DOM that are so generally useful that we've left them available. Here is a list of these objects and methods:

alert()

confirm()

prompt()

setTimeout(), clear Timeout()

setInterval(), clearInterval()

navigator

screen

Note that although these objects and methods are normally properties of the window object, in the macro code they can only be accessed by calling them directly. So calling "screen" will work, while "window.screen" will cause an error.

The Structure of a Macro

A macro looks just like a regular JavaScript function. It begins with the "function" keyword, has an optional arguments list, and then has a body of one or more lines of JavaScript. Here is a simple example:


	// returns an all-lowercase version of a string
	function makeLowerCase(aString) {
	return aString.toLowerCase( );
	}

A set of macros is just a series of these JavaScript function definitions.

Cell References as Arguments

Assume that we use the above macro in a cell formula:

=makeLowerCase( B7 )

If a cell reference is passed to the macro, the value of the "aString" argument in the macro code is what the designer might expect: the argument contains the value of cell B7. In other words, as a macro author the designer doesn't have to worry about whether a macro argument is a cell reference or a literal value. The spreadsheet engine takes care of translating cell references into cell values for you.

This statement also holds true for passing cell ranges (like A3:C5) into a macro, although the designer may have to do a little extra work to access all the values in the range.

Local and Global Variables

Local Variables

Inside a macro, local variables are declared in the standard fashion:

var schmoo:
var my Var, i, counter=0;

It is important that the designer uses the "var" statement to declare all variables. If the designer does not, he'll accidentally create a global variable that could, in some circumstances, interfere with the normal operation of the spreadsheet. Thus, declare all variables with "var". (The only exception is a named argument, which is implicitly declared as local by JavaScript.)

Global Variables and the "g" Object

In standard JavaScript, the designer creates a global variable by declaring it outside the scope of any function. Although this is possible in macro code, it is strongly discouraged Declaring global variables in this manner could, in some circumstances, result in conflicts with the spreadsheet application itself.

Instead, macros have access to a special global object named "g". Initially this object is empty. Macros can, at any time, read and write properties in this object. This allows the designer to create global values that can be shared across all macros in a spreadsheet.

Often, a designer will find it easiest to initialize globals in the special init( ) macro. This ensures that his globals are ready before any other macro code starts executing. Here is an example init( ) macro that sets several globals:


	function init( ) {
	// set up globals
	g.inputCell = "B3";
	g.startPrompt = "Please enter a value to start.";
	g.lettersUsed = new Object( );
	// other code follows . . .
	}

Note that the designer can also treat spreadsheet cells as "global variables" using the special getcell( ) and setcell( ) functions.

Calling Other Macros

To call one macro from another, the designer should call it directly as the designer would any other JavaScript function. Here is an example of a macro that relies on another:


	// returns true if two strings are the same
	// (the compariso