Excel spreadsheet parsing to share cells, formulas, tables or entire spreadsheets across an enterprise with other users

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 add-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 add-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 add-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 client process to store a spreadsheet and its objects generated on a client computer in a master library on a server to which said client computer is coupled so as to enable sharing of said spreadsheet or one or more of its objects for viewing or insertion in one or more spreadsheets on any client computer coupled to said server, comprising the steps: A) parsing a spreadsheet to identify all objects that make up said spreadsheet;B) converting each object to a representation that can be transmitted over a data network to a server; andC) sending said representation to said master library on said server.

2. The process of claim 1 further comprising the step of automatically assigning a unique name to every object parsed from each spreadsheet.

3. The process of claim 1 wherein step B) comprises converting each object to an XML description thereof.

4. The process of claim 1 wherein step B) comprises converting each object to SOAP representation.

5. The process of claim 1 further comprising the step of automatically capturing metadata created about objects by said spreadsheet and including said metadata in said representation of said object.

6. The process of claim 1 further comprising the steps of: D) automatically capturing metadata created about objects by said spreadsheet;E) including said metadata in said representation of said object, and displaying user interface mechanisms by which users can enter additional metadata about objects on a spreadsheet;F) capturing any metadata entered by a user and including said user defined metadata in said description generated in step B.

7. One or more computer readable mediums having stored thereon computer instructions which, when executed by one or more computers control said computers to allow individual objects on a first spreadsheet existing on one of said computers to be imported into a second spreadsheet existing on one of said computers.

8. A client computer coupled to a server via any data path, said client computer programmed with an operating system, software to communicate over said data path, and a spreadsheet application and programmed to perform the following functions: provide a user interface mechanism added onto said spreadsheet application's native user interface which allows a user to issue a command to save a spreadsheet to a master library on said server computer;receive a user command to save a spreadsheet and respond thereto by: parsing individual objects of said spreadsheet and assigning each object a name;creating descriptions of each object and transmitting said descriptions to said server over any data path for decoding and recreation of the original object in a master library application running on said server.

9. A server computer coupled to a client computer by any data path, said server computer programmed with an operating system, software to communicate over said data path, said server computer: further programmed with an application program to implement a master library application which controls said server computer to perform the following functions: receive descriptions of objects parsed from a spreadsheet running on a client computer;decode said description of each object and recreate said object in an emulation of a spreadsheet implemented by said master library application;store said recreated object and all its metadata.

10. The server computer of claim 9 further programmed to create a link or mapping between each object stored in said emulation of a spreadsheet implemented by said master library such that all changes made to each object on a spreadsheet on a client computer can be imported into the corresponding object stored in said master library emulated spreadsheet.

11. A process carried out in an environment comprised of a client computer which is coupled via any data path to a server computer running a master library process, comprising: receiving a command to save a spreadsheet created on a client computer in a master library;parsing objects from said spreadsheet;converting said parsed object into descriptions in a language that can be transmitted over any data path to a master library process running on said server computer;sending said descriptions to said master library process;converting said descriptions of said objects into spreadsheet objects and storing said spreadsheet objects in a master library.

12. The process of claim 11 further comprising the steps carried out on said server computer running said master library process of: receiving a search command specifying the type of spreadsheet objects to search for in said master library and the type of search to perform and search criteria and identifying all objects in said master library of the sought after type satisfying the search criteria; andcreating descriptions of each object which met said search criteria and sending said descriptions to said client computer.

13. The process of claim 11 further comprising a sharing process comprising the steps of: receiving a command to share spreadsheet objects;responding to said command by determining if all said spreadsheet objects are owned by the current user of said client computer;if all said spreadsheet objects are owned by said current user, sending a share request to said server computer to share said spreadsheet objects;if not all said spreadsheet objects are owned by said current user, gathering all said spreadsheet objects which are owned by said current user and sending a share request to said server computer to share said gathered spreadsheet objects;responding to said share request by annotating a database record for each object to indicate said object is shared.

14. The process of claim 13 further comprising the steps of: receiving said request to download a spreadsheet object which has been indicated as shared and creating an XML description of said object at a detail level specified by said user as value only, value and formula only or all rollup details and sending said description to the client computer which made said download request;converting said description of said object into a spreadsheet object and storing said object in a spreadsheet being constructed by a user of said client computer at a location specified by said user.

15. The process of claim 14 further comprising the steps of receiving commands of said user using a using interface of said spreadsheet into which said object was stored to alter said object and altering said object as requested.

16. The process of claim 15 further comprising the steps of receiving a command to save the spreadsheet having the modified downloaded object therein and responding thereto by saving said spreadsheet and all its individual objects in said master library.

17. The process of claim 16 further comprising the steps of receiving a command to share user specified objects of a spreadsheet to a master library and responding by creating and sending descriptions of the objects to be shared to said master library process on said server computer where the descriptions are converted back into spreadsheet objects and stored in said master library.

18. The process of claim 16 wherein when said save command is received, all objects in said master library which also exist on said spreadsheet are synchronized to the current state of the corresponding object on said spreadsheet.

19. A process carried out on a client computer which is coupled via any data path to a server computer running a master library process, comprising: receiving a request from a user to open a master library;displaying one or more user interface mechanisms to allow a user to select the type of spreadsheet object to search for and the manner of search;displaying spreadsheet objects returned as a result of said search.

20. One or more computer readable mediums having stored thereon computer instructions which, when executed by one or more computers control said computers to allow individual objects on a first spreadsheet existing on one of said computers to be stored in a master library application running on a second computer and downloaded from said master library to a spreadsheet application running on another computer.

21. A spreadsheet object sharing process comprising the steps: A) upon receiving a command, parsing individual objects on a first spreadsheet existing a first computer which are to be stored in a master library application running on a second computer;B) converting said parsed objects to a description that can be transported to a second computer and sending said descriptions to said second computer;C) converting said descriptions to spreadsheet objects on said second computer and storing said descriptions in a master library as shared object\ts;D) receiving a request to download an object stored on said second computer and converting the requested object to a description that can be sent over a data path to the computer which requested download of said object and transmitting said description;E) at a computer which requested download of said object from said master library, converting the received description of the object into a spreadsheet object and storing said object in a spreadsheet.

22. The process of claim 21 further comprising executing on said first computer a plug-in program which adds user interface menus, commands and displays which are specific to said spreadsheet object sharing process to the normal user interface menus, commands and displays of said spreadsheet.

23. The process of claim 22 wherein said plug-in controls said first computer to add said user interface menus, commands and displays to an Excel spreadsheet.

24. The process of claim 21 wherein step B comprises converting each parsed object into an XML description.

25. A computer-readable medium having stored thereon computer-readable instructions which can control a computer to perform the following process: A) parsing a spreadsheet to identify all objects that make up said spreadsheet, and automatically assigning a name to each object;B) converting each object to a representation that can be transmitted over a data network to a server; andC) sending said representation to said master library on said server.

26. The computer-readable medium of claim 25 wherein said instructions control said computer to automatically name each parsed object and provide a user interface to allow a user to interact in the naming process, said name serving to enable mapping said object to its original location in the spreadsheet in which it was created and from which the object came.

27. A server process to receive information defining objects that make up a spreadsheet on a client computer and store said information in a master library, comprising the steps: receiving in a server coupled to one or more client computers by a network a description of each object that comprises a spreadsheet;parsing each received description of an object and converting each description into an object of a spreadsheet and storing said object in a master library on said server, and storing an automatically generated name for said object which provides a basis to map said object back to the original object on a spreadsheet of a client computer.

28. The process of claim 27 further comprising the steps: refreshing the objects stored in said master library with the latest information from a corresponding object of a spreadsheet on a client computer where the object in said master library being refreshed was originally created.

29. The process of claim 27 further comprising the steps: sending a request to refresh an object in a spreadsheet on a client computer which have been downloaded from said master library;receiving said request and refreshing an object corresponding to said object to be refreshed, said corresponding object being stored in said master library, said refreshing of said corresponding object being accomplished by obtaining the latest information from a corresponding object of a spreadsheet on a client computer where the corresponding object in said master library was originally createdcreating in said server computer a description of the refreshed object in said master library after it is refreshed and sending said description to said client computer; andconverting said description of said refreshed object received from said server to a spreadsheet object and replacing the object in said client computer spreadsheet for which the request to refresh was made with the refreshed object decoded from the description of the refreshed object received from the server computer.

30. The process of claim 29 further comprising the steps: creating a subscription for each cell or cell in a table which has been downloaded into a spreadsheet on a client computer and refreshing every cell imported from said master library using the process of claim 29 each time the user gives a refresh command such that each time the original creator of a spreadsheet object changes it, the changes are propagated throughout the organization through said subscriptions.