Parser, Code Generator, and Data Calculation and Transformation Engine for Spreadsheet Calculations

• US 20120222000A1
• Filed: 05/04/2012
• Published: 08/30/2012
• Est. Priority Date: 08/16/2001
• Status: Active Grant

• Current Assignee: Michael H. Rubin, Michael R. Smialek
• Original Assignee: Michael H. Rubin, Michael R. Smialek
• Inventors: Smialek, Michael R., Rubin, Michael H.
• Granted Patent: US 8,656,348 B2
• Time in Patent Office: Days
• US Class Current: 717/106
• CPC Class Codes: G06F 16/00 Information retrieval; Database structures therefor; File system structures therefor
• CPC Class Codes: G06F 40/18 of spreadsheets form-filling G06F40/174
• CPC Class Codes: G06F 8/30 Creation or generation of source code

Abstract

A system including spreadsheet sheets, makes calculations and data transformations, which is available through a programming interface, and conforms to the grammar and syntax of a target software development language is presented. The system includes an Object Model with Data Structures representing entities involved in spreadsheets. The system includes a Parser and Code Generator that extracts data from a body of spreadsheet data, instantiates instances of Data Structures of the Object Model to represent the spreadsheet data, parses the data and formulas contained in the cells of the spreadsheets, iterates through the instantiated instances of the Data Structures, and generates source code that performs the calculations and data transformations embodied in the spreadsheet data. The system includes a Calculation Engine with software base classes that implement the common structural and data access features of spreadsheet data, and further implement the operations of common spreadsheet functions and operators.

Claims

1. A computer implemented method of executing computations representative of a spreadsheet, the method comprising:

receiving, by a processor, a request to load data representative of spreadsheet data;

instantiating, by the processor, in response to the received load request, at least one instance of a data structure representative of each of at least one cell from the spreadsheet data, the data structure having been generated from information extracted from the spreadsheet data and transformed into a representation of the spreadsheet data, each of the at least one instance of the data structure representative of each of the at least one cell from the spreadsheet data having a value associated therewith, the value comprising one of a literal value or a computed value;

receiving, by the processor, a request for a result representative of the result of a cell from the spreadsheet data;

locating, by the processor, in response to the result request, the instance of the data structure representative of the cell from the spreadsheet data and the value associated therewith; and

wherein, if the located value comprises a literal value, returning, by the processor, the located value in response to the result request; and

further wherein, if the located value comprises a computed value, computing, by the processor, any values on which the located value necessarily depends, if necessary, prior to computing the located value based thereon and returning, by the processor, the computed located value in response to the result request.

2. The computer implemented method of claim 1 further comprising:

instantiating, by the processor in response to the received load request, an instance of a workbook data structure representative of a workbook from the spreadsheet data, and at least one instance of a worksheet data structure representative of a worksheet from the spreadsheet data.

3. The computer implemented method of claim 2 wherein the instance of a workbook data structure represents global data behavior aspects of the workbook from the spreadsheet data and each of the at least one instance of a worksheet data structure represents global data behavior aspects of each worksheet from the spreadsheet data.

4. The computer implemented method of claim 1 wherein each instance of the data structure representative of a cell from the spreadsheet data represents data and behavior aspects of each cell that contains one of a literal value or formula.

5. The computer implemented method of claim 1 wherein the instances of the data structure representative of a cell from the spreadsheet that represent data and behavior aspects of each cell that contains a formula are instantiated at runtime.

6. The computer implemented method of claim 1 wherein the computed value is characterized as one of dirty or not dirty and further wherein, if the located value comprises a computed value and the computed value is not dirty, returning, by the processor, the located value without computing any values on which the located value necessarily depends.

7. The computer implemented method of claim 1 further comprising:

instantiating a value data structure corresponding to each instance of the data structure representative of a cell from the spreadsheet data, each value data structure operative to store the associated value.

8. The computer implemented method of claim 1 wherein each associated value comprises a value referenced by the data structure representative of the cell from the spreadsheet data.

9. The computer implemented method of claim 1 wherein each associated value comprises one of text, number, formula, Boolean, exception, date, or combinations thereof.

10. The computer implemented method of claim 1 further comprising:

instantiating a name data structure representative of a name from the spreadsheet data referring to at least one cell from the spreadsheet data.

11. The computer implemented method of claim 1 wherein the at least one cell from the spreadsheet data comprises a range of cells from the spreadsheet data.

12. The computer implemented method of claim 1 wherein the instantiating, receiving, locating, computing and returning are performed independent of a spreadsheet application.

13. The computer implemented method of claim 1 wherein the instantiating, receiving, locating, computing and returning are performed are optimized for the platform upon which they are performed.

14. The computer implemented method of claim 1 wherein the computing further comprises accessing a function library.

15. The computer implemented method of claim 14 wherein the function library only includes implementations of functions actually used in the spreadsheet data.

16. A system for execution of computations representative of a spreadsheet, the system comprising:

a processor operative to receive a request to load data representative of spreadsheet data;

the processor, in response to the received load request, being further operative to instantiate at least one instance of a data structure representative of each of at least one cell from the spreadsheet data, the data structure having been generated from information extracted from the spreadsheet data and transformed into a representation of the spreadsheet data, each of the at least one instance of the data structure representative of each of the at least one cell from the spreadsheet data having a value associated therewith, the value comprising one of a literal value or a computed value;

the processor being further operative to receive a request for a result representative of the result of a cell from the spreadsheet data;

the processor, in response to the result request, being further operative to locate the instance of the data structure representative of the cell from the spreadsheet data and the value associated therewith; and

wherein, if the located value comprises a literal value, the processor being further operative to return the located value in response to the result request; and

further wherein, if the located value comprises a computed value, the processor being further operative to compute any values on which the located value necessarily depends, if necessary, prior to computing the located value based thereon and returning, by the processor, the computed located value in response to the result request.

17. The system of claim 16 wherein the processor, in response to the received load request, is further operative to instantiate an instance of a workbook data structure representative of a workbook from the spreadsheet data, and at least one instance of a worksheet data structure representative of a worksheet from the spreadsheet data.

18. The system of claim 17 wherein the instance of a workbook data structure represents global data behavior aspects of the workbook from the spreadsheet data and each of the at least one instance of a worksheet data structure represents global data behavior aspects of each worksheet from the spreadsheet data.

19. The system of claim 16 wherein each instance of the data structure representative of a cell from the spreadsheet data represents data and behavior aspects of each cell that contains one of a literal value or formula.

20. The system method of claim 16 wherein the instances of the data structure representative of a cell from the spreadsheet that represent data and behavior aspects of each cell that contains a formula are instantiated at runtime.

21. The system of claim 16 wherein the computed value is characterized as one of dirty or not dirty and further wherein, if the located value comprises a computed value and the computed value is not dirty, returning, by the processor, the located value without computing any values on which the located value necessarily depends.

22. The system method of claim 16 wherein the processor is further operative to instantiate a value data structure corresponding to each instance of the data structure representative of a cell from the spreadsheet data, each value data structure operative to store the associated value.

23. The system of claim 16 wherein each associated value comprises a value referenced by the data structure representative of the cell from the spreadsheet data.

24. The system of claim 16 wherein each associated value comprises one of text, number, formula, Boolean, exception, date, or combinations thereof.

25. The system method of claim 16 wherein the processor is further operative to instantiate a name data structure representative of a name from the spreadsheet data referring to at least one cell from the spreadsheet data.

26. The system of claim 16 wherein the at least one cell from the spreadsheet data comprises a range of cells from the spreadsheet data.

27. The system of claim 16 wherein the processor is operative to perform the instantiation, reception, location, computation and return independent of a spreadsheet application.

28. The system of claim 16 wherein the processor is further operative to perform the computation by accessing a function library.

29. The system of claim 28 wherein the function library only includes implementations of functions actually used in the spreadsheet data.

30. A computer implemented system for executing computations representative of a spreadsheet, the system comprising:

means for receiving a request to load data representative of spreadsheet data;

means for instantiating in response to the received load request, at least one instance of a data structure representative of each of at least one cell from the spreadsheet data, the data structure having been generated from information extracted from the spreadsheet data and transformed into a representation of the spreadsheet data, each of the at least one instance of the data structure representative of each of the at least one cell from the spreadsheet data having a value associated therewith, the value comprising one of a literal value or a computed value;

means for receiving a request for a result representative of the result of a cell from the spreadsheet data;

means for locating in response to the result request, the instance of the data structure representative of the cell from the spreadsheet data and the value associated therewith; and

means for, wherein, if the located value comprises a literal value, returning the located value in response to the result request; and

further means for, wherein, if the located value comprises a computed value, computing any values on which the located value necessarily depends, if necessary, prior to computing the located value based thereon and returning, by the processor, the computed located value in response to the result request.