Using a spreadsheet engine as a server-side calculation model

• US 20070250764A1
• Filed: 04/20/2006
• Published: 10/25/2007
• 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)
• Granted Patent: US 8,082,489 B2
• Time in Patent Office: Days
• 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 spreadsheet parser configured to parse an electronic spreadsheet file to identify a calculation model implemented by the electronic spreadsheet file, wherein the calculation model specifies how input values are to be processed;

an input interface configured to receive 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 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 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 a computer to identify the calculation model;

implementing the calculation model in a spreadsheet emulator comprising a calculation engine;

receiving a set of data comprising a set of one or more input values for the calculation model;

processing the set of data with the calculation engine 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 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 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 implement the calculation model in a spreadsheet emulator comprising a calculation engine;

instructions to receive a set of data comprising a set of one or more input values for 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 in a portable format.

20. A method comprising:

receiving, at a computer, an electronic spreadsheet file comprising

a) a set of data including one or more example data elements, each of the data elements being represented by an example cell identifier; and

b) one or more formulas configured to use at least one of the example data elements as input;

parsing, with the computer, the electronic spreadsheet file to identify one or more relationships between the one or more formulas and one or more example cell identifiers representing the example data elements used as input by the one or more formulas;

generating a calculation model configured to replicate the one or more formulas with respect to the one or more cell identifiers, wherein the calculation model is generated independent of a spreadsheet application; and

implementing the calculation model in a spreadsheet emulator comprising a calculation engine.

21. A method as recited by claim 20, further comprising:

receiving, at the spreadsheet emulator, a set of data elements, each of the data elements being identified by a cell identifier; and

identifying, based on the cell identifiers, that one or more of the data elements is input data for the one or more formulas;

processing the input data with the one or more formulas to produce one or more output values.

22. A method as recited by claim 21, further comprising:

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

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

24. A method as recited by claim 20, wherein the spreadsheet emulator is implemented on a web server.

25. A system, comprising:

a processor; and

a computer readable medium comprising a set of instructions executable by the processor, the set of instructions comprising;

a) instructions to receive an electronic spreadsheet file comprising;

i) a set of data including one or more example data elements, each of the data elements being represented by an example cell identifier; and

ii) one or more formulas configured to use at least one of the example data elements as input;

b) instructions to parse the electronic spreadsheet file to identify one or more relationships between the one or more formulas and one or more example cell identifiers representing the example data elements used as input by the one or more formulas;

c) instructions to generate a calculation model configured to replicate the one or more formulas with respect to the one or more cell identifiers, wherein the calculation model is generated independent of a spreadsheet application; and

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

e) instructions to receive a set of data comprising a set of input values for the calculation model;

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

g) output at least a portion of the set of one or more data elements in a portable format.