Excel spreadsheet parsing to share cells, formulas, tables, etc.

Abstract

Discloses a method and apparatus and user interface for parsing individual objects of a spreadsheet created on a client computer and sending the entire workbook, its binary and the objects for storage on a server computer in a master library. The master library can be opened, searched and objects downloaded. The downloaded object can have their metadata displayed, and the level of detail about each object that is downloaded can be controlled by the user. The user can designate objects for sharing in the master library with other spreadsheet users. Downloaded objects can be modified with the native tools of the spreadsheet application, typically Excel, and re-stored in the master library under a different name and with the person who changed the object listed in the metadata as the owner. Searches can controlled by user interface tools to designate object type to search, and search type can be by owner or functional area of the company. Users can enter their own metadata. Automatic naming of objects decouples objects from their original locations in the workbooks upon which they were created.

Description

BRIEF DESCRIPTION OF THE DRAWINGS

FIG. 1 is a high level diagram showing the concepts of client computers running Excel plug-ins to parse spreadsheets and upload cells, formulas and tables to an interchange server and to download primitives stored in said server to spreadsheets being composed in the client computers.

FIG. 2 is a pseudo flowchart illustrating the steps of a typical process for creating a new spreadsheet using primitives downloaded from the master library and editing said primitives to suit the needs of the author and publishing the primitives of the completed spreadsheet back to the master library.

FIG. 3 is a screenshot illustrating the first step in a process of using an embodiment to create a new spreadsheet using the primitives master library.

FIG. 4 is screenshot of the Excel spreadsheet showing how the menu structure has been modified to add the BDNA functionality added by the Excel plug in to enable downloading primitives from the master library and uploading and sharing spreadsheets composed by the user.

FIG. 5 is a screenshot of a typical display which the Excel plug-in causes to be displayed when the Open Master Library Option is selected.

FIG. 6 is a screenshot of a screen which is displayed when the user chooses to search by owner for primitives in the master library.

FIG. 7 is screenshot of a screen which appears in the preferred embodiment when the user chooses to browse tables in the master library by functional area.

FIG. 8 is a more detailed view of the table 32 of FIG. 7 which illustrates the user interface of the preferred embodiment which can be used to sort or filter the primitives in the master library, preview a particular primitive in the respository, select a primitive for return to the spreadsheet under construction or drill down into a primitive to see more details about the primitive.

FIG. 9 is a screenshot of the type of screen which is displayed in the preferred embodiment after the user has selected a particular table in FIG. 8 and selected the preview button.

FIG. 10 is a screenshot of the options display which the Excel plug-in causes the client computer to display when the user chooses to return a primitive from the master library to the spreadsheet under construction.

FIG. 11 is a screenshot of the client computer's spreadsheet application after a primitive from the master library has been selected and returned to the spreadsheet under construction.

FIG. 12 is a screenshot of the display on the client computer rendered by the combination of the Excel plugin and the Excel spreadsheet application which illustrates how a user can drill down to inspect the formula behind a particular cell in the table primitive imported from the master library.

FIG. 13 is a screenshot of the display that results when Formula Inputs tab 84 is selected in FIG. 12.

FIG. 14 is a screen shot of the display that results when the user chooses to add custom cells to a primitive that has been downloaded from the master library.

FIG. 15 is a screen shot of the user interface mechanism by which the user can give commands to open the BDNA Master Library, refresh the workbook, save the workbook to the master library master library or share the workbook.

FIG. 16 is a diagram of a table element in a spreadsheet that is to be parsed showing how the system automatically uses elements of the table to generate a name for the primitive to be used in storing the primitive in the master library.

FIGS. 17A and 17B are a flowchart of the process of opening the master library and searching it.

FIGS. 18A and 18B are a flowchart of the process of viewing object details of objects stored in the master library.

FIG. 19 is a flowchart of he process to preview a table stored in the master library.

FIGS. 20A and 20B are a flowchart of the process to retrieve objects from the master library.

FIGS. 21A and 21 b are a flowchart of the process to save workbooks and the individual objects therein in the master library.

FIG. 22 is a flowchart of the process of validating a user.

FIG. 23 is a flowchart of the process to define a table.

FIG. 24 is a flowchart of the process to share a workbook.

FIG. 25 is a flowchart of the process to refresh a workbook.

FIG. 26 is a software architecture diagram of the Excel plug in referred to in the diagram as a COM Excel Add-In because it is designed to use the COM interface to add functionality to Excel application programs.

FIG. 27 is a screen shot of a user interface screen.

Claims

1. A process for presenting a user interface on a client computer coupled to a server computer which maintains a master library of spreadsheet objects, comprising the steps: A) using a computer to implement a user interface mechanism that a user can invoke to save a spreadsheet to a master library; andB) using a computer to implement a user interface mechanism that a user can invoke to designate a spreadsheet stored in said master library for sharing with other users thereby enabling sharing of each individual object in said spreadsheet;C) using a computer to implement one or more user interface mechanisms to open said master library, examine its contents and import an object stored therein into a spreadsheet being executed on said computer.

2. The process of claim 1 further comprising the steps: D) using a computer to implement a user interface mechanism that can be invoked to refresh a spreadsheet.

3. The process of claim 1 wherein step C further comprises the steps: using a computer to implement user interface mechanisms to allow a user to do searches of objects stored in said master library.

4. The process of claim 1 wherein step C further comprises the steps: using a computer to implement user interface mechanisms to allow a user to do searches of objects stored in said master library and to specify whether the search is for cells, tables or templates and to specify whether the search is by a text search or an advanced search or is done by browsing.

5. The process of claim 1 wherein step C further comprises the steps: using a computer to implement user interface mechanisms to allow a user to do searches of objects stored in said master library and to specify whether the search is for cells, tables or templates and to specify whether the search is by a text search or an advanced search or is done by browsing, and further allows the user to specify whether the search is by functional area of the company or by owner.

6. The process of claim 1 wherein step C further comprises the steps: displaying the type and method of searching in a first area of a computer display, displaying in a second area of a computer display a list of folders organized by letter of the alphabet if owner is chosen as the method of organizing the objects in said master library, and displaying under a selected alphabetic folder folders named for all the owners having a last name starting with the selected letter, and displaying in a third area of said computer display a table of all the objects of the specified type which were created by a particular owner whose folder has been selected and displaying the name of each object and at least some of the object's metadata.

7. The process of claim 1 wherein step C further comprises the steps: displaying the type and method of searching in a first area of a computer display, displaying in a second area of a computer display a list of folders organized by functional area of a company in which an object was created in a spreadsheet if functional area is chosen as the method of organizing the objects in said master library, and displaying under a selected functional area folder folders named for all the subareas within a particular functional area, and displaying in a third area of said computer display a table of all the objects of the specified type which were created by an owner working in a selected subarea of a selected functional area whose folder has been selected and displaying the name of each object created in the selected subarea and the selected functional area and at least some of the object's metadata.

8. A process for presenting a user interface on a client computer coupled to a server computer which maintains a master library of spreadsheet objects, comprising the steps: A) using a computer to implement one or more user interface mechanisms to open a master library which stores objects parsed from a spreadsheet which has been shared by its owner:B) using a computer to implement one or more user interface mechanisms to search the objects stored in said master library;C) using a computer to implement one or more user interface mechanisms to analyze what the objects found in said search do; andD) using a computer to implement one or more user interface mechanisms to download into a spreadsheet any object in said master library.

9. The process of claim 8 further comprising the steps of using a computer to provide a user interface mechanism to allow a user to store objects downloaded from said master library after they have been changed on said client computer and recognize the fact that the object has been changed from the state it was in when it was downloaded and prompt the user to give the object a new name and save the object as a new object or to save the object as a revised version of the original object with a name indicative of the object's revised status and overwrite the old object.

10. The process of claim 8 further comprising the steps of using a computer to provide a user interface mechanism to invoke a refresh command which will cause all objects imported from the master library and owned and created by another user to be updated in the spreadsheet into which they were imported to include the latest values, constants, formulas and cell references of the original object created by said other user.

11. A computer-readable medium storing computer-readable instructions which, when executed by a computer, cause to occur a spreadsheet object sharing process carried out on a client computer which is coupled via any data path to a server computer running a master library process, comprising: controlling said client computer to implement a user interface mechanism that can be invoked by a user to provide a request from said user to open a master library which stores entire spreadsheets, workbooks and individual cells, tables and templates from said workbooks;controlling said client computer to implement one or more user interface mechanisms which can be used to select the type of spreadsheet object to search for in a master library with which said client computer is in contact and to specify the manner of search and send a search request to said server computer;controlling said client computer to display a user interface mechanism display results of said search request, and display spreadsheet objects returned as a result of said search.

12. The computer-readable medium of claim 11 wherein said computer-readable instructions further cause said client computer to implement user interface mechanisms that provide a way for a user to select an object from said master library and request that said object be downloaded into a spreadsheet on said client computer.

13. The computer-readable medium of claim 11 wherein said computer-readable instructions further cause said client computer to implement user interface mechanisms that provide a way for a user to select to store a spreadsheet in said master library which launches a process to identify the objects in said spreadsheet and store them in said master library such that they can be individually downloaded.

14. The computer-readable medium of claim 11 wherein said computer-readable instructions further cause said client computer to implement user interface mechanisms that add additional functionality to the user interface mechanisms of a spreadsheet and provide a way for a user to select an object from said master library and request that said object be downloaded into a spreadsheet on said client computer so as to enable the user to compose a spreadsheet which includes object created by the user using the spreadsheet's native user interface as well as objects created by other users and stored in said master library and downloaded therefrom using said interface mechanisms that add additional functionality to the user interface mechanisms of said spreadsheet.

15. The computer-readable medium of claim 11 wherein said computer-readable instructions further cause said client computer to implement user interface mechanisms that add additional functionality to the user interface mechanisms of an Excel spreadsheet and provide a way for a user to choose to open the master library, refresh objects in the spreadsheet which have been imported from said master library, save the spreadsheet and its individual objects in said master library and share the spreadsheet and the objects therein.

16. The computer-readable medium of claim 11 wherein said computer-readable instruction further cause said client computer to implement user interface mechanisms that allow a user to do searches of objects stored in said master library and to specify whether the search is for cells, tables or templates and to specify whether the search is by a text search or an advanced search or is done by browsing.

17. The computer-readable medium of claim 11 wherein said computer-readable instruction further cause said client computer to implement user interface mechanisms that augment the user interface mechanisms of the host spreadsheet application to allow a user to do searches of objects stored in said master library and to specify whether the search is for cells, tables or templates and to specify whether the search is by a text search or an advanced search or is done by browsing, and further allows the user to specify whether the search is by functional area of the company or by owner.

18. The computer-readable medium of claim 11 wherein said computer-readable instruction further cause said client computer to implement user interface mechanisms that augment the user interface mechanisms of the host spreadsheet application and controls said client computer to display the type and method of searching in a first area of a computer display, display in a second area of a computer display a list of folders of spreadsheet objects stored in master library organized by letter of the alphabet if owner is chosen as the method of organizing the objects in said master library, and displaying under a selected alphabetic folder folders named for all the owners having a last name starting with the selected letter, and displaying in a third area of said computer display a table of all the objects of the specified type which were created by a particular owner whose folder has been selected and displaying the name of each object and at least some of the object's metadata.

19. The computer-readable medium of claim 11 wherein said computer-readable instruction further cause said client computer to implement user interface mechanisms that augment the user interface mechanisms of the host spreadsheet application to control the client computer to display the type and method of searching in a first area of a computer display, and display in a second area of a computer display a list of folders of spreadsheet objects stored in master library organized by functional area of a company in which an object was created in a spreadsheet if the user has chosen functional area as the method of organizing the objects in said master library, and displaying under a selected functional area folders named for all the subareas within a particular functional area, and display in a third area of said computer display a table of all the objects of the specified type which were created by an owner working in a selected subarea of a selected functional area whose folder has been selected and displaying the name of each object created in the selected subarea and the selected functional area and at least some of the object's metadata.

20. The computer-readable medium of claim 11 wherein said computer-readable instruction further cause said client computer to implement user interface mechanisms that augment the user interface mechanisms of the host spreadsheet application to control the client computer to implement a user interface mechanism which allows a user to choose to view all details about an object in said master library, and, if the user chooses to view all details, to display metadata about an object.

21. The computer-readable medium of claim 11 wherein said computer-readable instruction further cause said client computer to implement user interface mechanisms that augment the user interface mechanisms of the host spreadsheet application to control the client computer to implement a user interface mechanism which allows a user to choose to view all details about an object in said master library, and, if the user chooses to view all details, to display metadata about an object including displaying one or more user interface mechanisms which a user can use to add user defined metadata.

22. The computer-readable medium of claim 11 wherein said computer-readable instruction further cause said client computer to implement user interface mechanisms that augment the user interface mechanisms of the host spreadsheet application to control the client computer to implement a user interface mechanism which allows a user to choose to view all details about an object in said master library, and, if the user chooses to view all details, to display metadata about an object including displaying one or more user interface mechanisms which a user can use to add user defined metadata including a user defined name for said object, one or more user defined searchable attributes and display a command choice which a user can invoke to share the object to which said metadata pertains and display a command choice which a user can invoke to choose the object to download into a workbook or spreadsheet.

23. The computer-readable medium of claim 11 wherein said computer-readable instruction further cause said client computer to implement user interface mechanisms that augment the user interface mechanisms of the host spreadsheet application to control the client computer to implement a user interface mechanism which allows a user to choose to view all details about an object in said master library, and, if the user chooses to view all details, to display metadata about an object including displaying one or more user interface mechanisms which a user can use to add user defined metadata including a user defined name for said object, one or more user defined searchable attributes and display a command choice which a user can invoke to share the object to which said metadata pertains and display a command choice which a user can invoke to choose the object to download into a workbook or spreadsheet, said computer-readable instructions further causing said client computer to implement user interface mechanisms that can be invoked to drill down further into metadata pertaining to said object to view containing columns, containing rows, containing cells and change history of the object.

24. The computer-readable medium of claim 11 wherein said computer-readable instruction further cause said client computer to implement user interface mechanisms that augment the user interface mechanisms of the host spreadsheet application to control the client computer to implement a user interface mechanism which allows a user to choose to view all details about an object in said master library, and, if the user chooses to view all details, to display metadata about an object including displaying one or more user interface mechanisms which a user can use to add user defined metadata including a user defined name for said object, one or more user defined searchable attributes and display a command choice which a user can invoke to share the object to which said metadata pertains and display a command choice which a user can invoke to choose the object to download into a workbook or spreadsheet, and when a user chooses to download an object, displaying a user interface mechanism to allow a user to choose where in a spreadsheet to insert the selected object, and displaying a user interface mechanism to allow a user to choose whether to return the value(s) only of the cell or cells in the object, the value(s) and formula(s) programmed into the cell(s) of the object, or all the roll-up details.

25. The computer-readable medium of claim 24 wherein said instructions control said client computer to store formula inputs on a separate workbook in the same spreadsheet as the spreadsheet into which a downloaded object from the master library was stored when the user chooses to download an object with the formulas or all the roll-up details, and wherein said instructions control said client computer to display the formula and cells which are combined to make up the total in a selected cell of a downloaded cell or table to be displayed in an area reserved for that purpose when a cell of a downloaded object is displayed, and control said computer to display a tab which when selected by said user causes the workbook upon which said formula inputs were stored to be displayed.

26. The computer-readable medium of claim 25 wherein said instructions control said client computer, when said tab is selected by said user symbolizing the separate workbook upon which said formula inputs for a selected cell are stored, to display a table of said formula inputs organized as a column of the automatically assigned cell names of all the cells combined by a formula to make up the value of said selected cell and a second column of the values of those cells in the original workbooks where said cells were originally created whose automatically assigned names are displayed on said separate workbook.