Using a spreadsheet engine as a server-side calculation model

• US 8,082,489 B2
• Filed: 04/20/2006
• Issued: 12/20/2011
• Est. Priority Date: 04/20/2006
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Jiang, Xin (Edward)
• Primary Examiner(s): Ries, Laurie
• Assistant Examiner(s): Mills, Frank D
• Application Number:
  US11/408,826
• Publication Number:
  US 20070250764A1
• Time in Patent Office: 2,070 Days
• Field of Search: 715212-220
• US Class Current: 715/212
• CPC Class Codes: G06F 40/143 Markup, e.g. Standard Generalized Markup Language [SGML] or Document Type Definition [DTD]
• CPC Class Codes: G06F 40/18 of spreadsheets form-filling G06F40/174
• CPC Class Codes: G06F 40/205 Parsing

Abstract

This disclosure is directed to novel solutions for processing data in a manner similar to that employed by traditional spreadsheet applications (including, merely by way of example, evaluating expressions, producing reports and the like) without the need for a spreadsheet application. In an aspect, a spreadsheet calculation engine might be emulated in a Java environment, allowing for enhanced data input/output flexibility while still maintaining the ease with which traditional spreadsheet applications allow users to design a calculation model. In another aspect, the spreadsheet engine might be provided on a web server, database server and/or application server, allowing for data (including, for instance, reports comprising data processed and/or produced by the spreadsheet calculation engine) to be published easily (and, in some cases dynamically) on the web.

Claims

1. A computer system, comprising:

a processor; and

a computer readable storage medium having stored thereon software executable by the processor, the software comprising;

a spreadsheet parser configured to parse an electronic spreadsheet file to identify a calculation model implemented by the electronic spreadsheet file, identify formulas within the electronic spreadsheet file including identifying which input and output cells of the formulas correspond to or are used to hold input and output values for the formulas, and determine relationships between the input cells, output cells, and the formulas, wherein the spreadsheet parser is further configured to determine which of the input cells and output cells hold primary and intermediate values and based on the determination consolidating the formulas, and wherein the calculation model specifies how input values are to be processed and how the formulas are processed;

an input interface configured to receive, from a business application, a set of data in a structured format, wherein the structured format comprises information specifying how the set of data relates to the calculation model;

a spreadsheet emulator comprising a calculation engine configured to process the data according to the calculation model; and

an output interface configured to output a result from the calculation engine, wherein the result comprises one or more data elements that represent values obtained by processing the data and the formulas according to the calculation model.

2. A computer system as recited by claim 1, wherein the structured format comprises eXtensible Markup Language (“

XML”

) formatting information.

3. A computer system as recited by claim 1, further comprising:

a publisher module configured to publish the result from the calculation engine in HyperText Markup Language (“

HTML”

) format for distribution via a web server.

4. A computer system as recited by claim 1, wherein the output interface is configured to output the result as an eXtensible Markup Language (“

XML”

) file.

5. A computer system as recited by claim 1, wherein the output interface is configured to output the result as an eXtensible Stylesheet Language-Formatting Objects (“

XSL-FO”

) file.

6. A computer system as recited by claim 5, further comprising:

a publisher module comprising an XSL-FO transformation engine configured to transform the XSL-FO file into a file of a specified format.

7. A computer system as recited by claim 6, wherein the specified format is a format selected from the group consisting of rich text format (“

RTF”

) MICROSOFT WORD document (“

.doc”

) format, MICROSOFT EXCEL spreadsheet (“

.xls”

) format, Hypertext Markup Language (“

HTML”

) format, and portable document format (“

PDF”

).

8. A computer system as recited by claim 1, wherein the spreadsheet emulator is implemented in a JAVA environment.

9. A method, comprising:

receiving, at a computer system, an electronic spreadsheet file comprising a calculation model specifying how input values to the spreadsheet are to be processed;

parsing the electronic spreadsheet file with the computer system to identify the calculation model;

identifying formulas within the electronic spreadsheet file including identifying which input and output cells of the formulas correspond to or are used to hold input and output values for the formulas, and determine relationships between the input cells, output cells, and the formulas;

determining which of the input cells and output cells hold primary and intermediate values and based on the determination consolidating the formulas;

implementing the calculation model in a spreadsheet emulator on the computer system, the spreadsheet emulator comprising a calculation engine;

receiving, from a business application, a set of data in a structured format, the set of data comprising a set of one or more input values and how the formulas are processed for the calculation model, and the structured format comprising information specifying how the set of data relates to the calculation model;

processing the set of data with the calculation engine on the computer system to produce a result comprising a set of one or more data elements; and

outputting at least a portion of the set of one or more data elements and the formulas in a portable format.

10. A method as recited by claim 9, further comprising:

receiving with the set of data at least one expression configured to use at least one of the one or more input values as input; and

updating the calculation model with the at least one expression.

11. A method as recited by claim 10, wherein updating the calculation model comprises replacing at least one existing expression in the calculation model with the at least one expression.

12. A method as recited by claim 9, wherein the portable format is an eXtensible Markup Language (“

XML”

) format.

13. A method as recited by claim 9, further comprising:

publishing at least a portion of the set of one or more data elements in one or more HyperText Markup Language (“

HTML”

) documents for distribution via a web server.

14. A method as recited by claim 9, wherein:

receiving a set of input data comprises receiving a first set of input values and receiving a second set of input values;

processing the set of data comprises processing the first set of input values to produce a first result comprising a first set of one or more data elements and processing the second set of input values to produce a second result comprising a second set of one or more data elements; and

outputting at least a portion of the set of data elements comprises outputting at least a portion of the first set of data elements and outputting at least a portion of the second set of data elements.

15. A method as recited by claim 9, wherein the spreadsheet emulator is implemented in a Java environment.

16. A method as recited by claim 9, wherein parsing the spreadsheet to identify the calculation model comprises:

identifying, in the electronic spreadsheet file, one or more input cells, each of the one or more input cells being configured to hold an input value;

identifying, in the electronic spreadsheet file, one or more output cells, each of the one or more output cells being configured to hold an output value; and

for each of the output cells, identifying a corresponding formula that takes as input at least one of the one or more input cells and produces the output value.

17. A method as recited by claim 16, wherein implementing the calculation model in the spreadsheet emulator comprises:

for each of the identified input cells, defining an input variable to hold an input data element;

for each of the identified output cells, defining an output variable to hold an output data element;

for each of the identified output cells, defining an expression that applies the formula corresponding to the identified output cell to produce a result that is assigned to the defined output variable for the identified output cell.

18. A method as recited by claim 17, wherein processing the set of data comprises:

for each of the input variables;

(i) identifying a member of the set of input values corresponding to the input variable; and

(ii) setting the value of the identified member as the value of the input variable; and

evaluating each of the defined expressions to produce values for each of the defined output variables.

19. A computer program embodied on a computer readable storage medium, the computer program comprising a set of instructions executable by one or more computers, the set of instructions comprising:

instructions to receive an electronic spreadsheet file comprising a calculation model specifying how input values to the spreadsheet are to be processed;

instructions to parse the electronic spreadsheet file with a computer to identify the calculation model;

instructions to identify formulas within the electronic spreadsheet file including identifying which input and output cells of the formulas correspond to or are used to hold input and output values for the formulas, and determine relationships between the input cells, output cells, and the formulas;

instructions to determine which of the input cells and output cells hold primary and intermediate values and based on the determination consolidating the formulas;

instructions to implement the calculation model in a spreadsheet emulator comprising a calculation engine;

instructions to receive, from a business application, a set of data in a structured format, the set of data comprising a set of one or more input values and how the formulas are processed for the calculation model, and the structured format comprising information specifying how the set of data relates to the calculation model;

instructions to process the set of data with the calculation engine to produce a result comprising a set of one or more data elements; and

instructions to output at least a portion of the set of one or more data elements and the formulas in a portable format.