Apparatus and method for generating spreadsheet function source code for use in a source code application

A computer-implemented method includes designating selected spreadsheet cells within a spreadsheet document. Spreadsheet function source code characterizing the functionality associated with the selected spreadsheet cells is generated. The spreadsheet function source code is incorporated into a source code application.

BRIEF DESCRIPTION OF THE INVENTION

This invention relates generally to the field of computer data analysis and graphics. More particularly, this invention relates to creation of spreadsheet function source code for incorporation into a source code application.

BACKGROUND OF THE INVENTION

Spreadsheet software provides tools for information workers to model complex mathematical relationships in fields such as finance, science and engineering. An individual that is capable of modeling complex mathematical relationships in a spreadsheet may not be able to transform that logic into source code that may be used in a larger application. Similarly, a software engineer may have a difficult time understanding and programming information associated with a complex spreadsheet.

Software tools typically provide limited functionality in creating graphic representations of input and output data and limited ability for users to export the data and mathematical models represented by the formulas contained in the spreadsheet cells to create custom application programs. Currently, relationships between cells in a spreadsheet cannot be exported as a stand-alone piece of software that may be incorporated in other application programs and/or executed independently of the underlying spreadsheet application. Consequently, there is a need for tools to allow software developers to capture and export information contained in a spreadsheet in a component form that can be easily integrated into other application programs, such as applications supporting graphical representations of spreadsheet data.

SUMMARY OF THE INVENTION

The invention includes a computer-implemented method of designating selected spreadsheet cells within a spreadsheet document. Spreadsheet function source code characterizing the functionality associated with the selected spreadsheet cells is generated. The spreadsheet function source code is incorporated into a source code application.

The invention also includes a computer readable storage medium with executable instructions to receive selected spreadsheet cells. Spreadsheet function source code characterizing the functionality associated with the selected spreadsheet cells is then generated.

DETAILED DESCRIPTION OF THE INVENTION

The following terminology is used while disclosing embodiments of the invention:

An Application is a set of executable instructions to directly control the operation of a machine, such as a digital computer, to execute a specified task.

Data Values are data items that are associated with spreadsheet cells and graphical components. These values may represent a discrete value, ranges, strings, calculated values and the like. The data may be based on a separate application document, such as a sheet document.

A Programming Language is a set of semantic and syntactic rules to control the behavior of a machine, such as a digital computer. A programming language enables a programmer to precisely specify what data a computer will act upon, how these data will be stored/transmitted, and precisely what actions to take under various circumstances. C++, Java®, and Visual Basic® are examples of programming languages.

Source Code is a sequence of statements and/or declarations written in a human readable programming language to execute a task. Source code is typically generated in a form whereby it can be compiled in whole or part into a machine readable form.

A Spreadsheet Application is an application program for creating spreadsheet files. Microsoft Excel® is one example of a spreadsheet application. A spreadsheet is a rectangular table (or grid) of information, often including financial information.

FIG. 1illustrates a computer100configured in accordance with an embodiment the invention. The computer100has standard components including a central processing unit (CPU)110connecting input/output devices112via a bus114. The input/output devices112may include devices such as a keyboard, mouse, display, printer, and the like. An optional network interface circuit116is also connected to the bus114. The network interface circuit116facilitates communications with networked computers (not shown) and data sources. A memory120is also connected to the bus114. The memory120included executable instructions to implement operations of the invention.

In the example ofFIG. 1, the memory120includes a spreadsheet application module122to create, process, store and display spreadsheet files124and other spreadsheet information. The spreadsheet files124may include a plurality of spreadsheet cells containing numbers and/or formulae. The spreadsheet application module122may be a commercially available product, such as Microsoft Excel®.

The memory120also includes a code generator module126configured in accordance with an embodiment of the invention. The code generator module126includes executable instructions to generate spreadsheet function source code128. In particular, the code generator module126includes executable instructions to convert a set of selected spreadsheet cells within a spreadsheet file into spreadsheet function source code characterizing the functionality of the selected spreadsheet cells. Thus, the spreadsheet function source code should not be confused with spreadsheet functions (e.g., sum, product, and the like), although the spreadsheet function source code may express spreadsheet functions.

The source code generator module126also includes executable instructions to facilitate the incorporation of the spreadsheet function source code into an application program. Alternately, the spreadsheet function source code may be executed as an independent code base.

An application program module130may also be stored in memory120. The application program module130is modified to accept the spreadsheet function source code128from the code generator126.

For purposes of illustration, the components are shown on a single computer. However, modules may be located on different computers. The modules are designed to be distributed and integrated with multiple computers. It is the functions of the modules that are significant, not where they are performed or the specific manner in which they are performed.

FIG. 2illustrates a workflow associated with an embodiment of the invention. The user selects a spreadsheet document200. In one embodiment, the spreadsheet document is a Microsoft Excel® spreadsheet file. In another embodiment, the spreadsheet document is created with a different application program. The user then selects a set of spreadsheet cells202. Native tools associated with the spreadsheet application may be used to implement this operation. In an automated embodiment, all cells are selected or a specified set of cells are selected.

Once the desired spreadsheet cells are selected, code generator module126processes the selected spreadsheet cells to generate spreadsheet function source code204characterizing the data and formulae associated with the selected spreadsheet cells. The code generator126may use any number of techniques (e.g., Application Program Interfaces (APIs), function calls and the like) to collect the data and formulae associated with selected spreadsheet cells.

The resultant source code may be in any number of formats or programming languages (e.g. Java®. ActionScript®, and the like). The selection of source code format is typically dictated by the application into which the user desires to export the code.

The code generator126operates to insulate a user from having to sequentially program the operations associated with selected cells of a spreadsheet. This is advantageous because many individuals are comfortable programming in a spreadsheet context, but are incapable of converting this information into the highly structured format associated with any given instance of source code. Similarly, many software developers are not necessarily suited for modeling the complex relationships that may exist in a spreadsheet. The code generator126allows the utilization of complex spreadsheet relationships without the need to understand or program the underlying math behind the relationships. Thus, the work product of an expert in the spreadsheet domain may be easily ported into a larger software application.

The generated source code may then be stored in memory and subsequently executed. The generated source code may also be incorporated in an application program206. For example, in one embodiment the generated source code is incorporated in application program130as spreadsheet function source code128.

FIG. 3illustrates an exemplary computer screen shot associated with a spreadsheet application. In particular, the screen shot ofFIG. 3displays a representative Microsoft Excel® spreadsheet file, titled “Book1”. Cells A1and A2contain the numbers10and20, respectively. Cell A3is associated with the formula A1+A2. Thus, Cell A3displays the number30, the result of the application of the formula A1+A2to the numeric values of cells A1and A2.

In this example, the user selects the desired spreadsheet cells, such as cells A1through A3. The code generator126reads the selected values and formulae from the spreadsheet and generates source code representing the selected cells.

FIG. 4illustrates exemplary source code400generated by the code generator126. As shown inFIG. 4, the source code400models the values and mathematical relationships between the cells selected by the user, in this case the values and formulae associated with the spreadsheet inFIG. 3. For the value A1, the source code included dependency information (A1.dependents=[A3]) and value information (A1.value=10). Similar information is provided for value A2. Value A3also has dependency information (A3.dependents=[ ]) and function information (A3.calc=function ( ) {return A1. value+value;}). Thus, an aspect of the invention is to explicitly define and utilize cell dependencies.

Thus, the generated code represents the information associated with the selected cells, but is self-contained with no reliance on the spreadsheet. In one embodiment, the generated code has associated get and set properties or functions corresponding to selected cells in the spreadsheet. At execution time of the code, these properties or functions can be used to set the underlying cell values. As these underlying cell values are adjusted, any dependent cells are recalculated and appropriate events are generated indicating which cells have changed. For example, in ActionScript, the code “Spreadsheet.A1=15” could be used to set the value of cell A1in the spreadsheet engine to 15. This statement causes the spreadsheet engine to recalculate cell C1, which results in an A3value of 35 (15+20). The spreadsheet then generates an event indicating that C1has changed, after which the application queries the calculation engine for the new value of the cell.

The source code may now be incorporated into an application program. For example,FIG. 5illustrates an exemplary screen shot of an Adobe Flex Builder 2 application program wherein an application, titled “example.mxml,” is being developed. As shown inFIG. 5, the generated spreadsheet source code400illustrated inFIG. 4is incorporated into the example.mxml application. The code generator126may support copy techniques to facilitate this operation. Alternately, the code generator126may utilize prompts to specify a file name for the spreadsheet function source code (e.g., “SpreadsheetCode.as”). The application programmer may then call the spreadsheet function source code, for example, using the following code:

<mx:Script><![CDATA[public function startUp ( ) :void {include “SpreadsheetCode.as”}]]</mx:Script>
The spreadsheet function source code may now be compiled by another program or application.

Visual development environments typically include tools such as visual components, data binding, drawing tools and other development aids. Examples of visual development environments include Adobe Flex®, Adobe Flash® and Microsoft Expression Interactive Designer®. These development environments are often extensible allowing for third party product integration through add-ons. They may include features such as documented Application Program Interfaces (APIs) that can facilitate incorporation of add-ons, such as embodiments of the present invention in add-on form, so that the add-ons run directly in the visual development environment.

In one exemplary embodiment of a workflow of the present invention incorporating an add-on, a user creates a user interface in a visual environment through standard techniques. Data bindings are then used to link the graphical environment with the underlying spreadsheet. Binding mechanisms in programming languages allow properties to be bound together. When two properties are bound together, changing one property will cause the binding framework to automatically update the other property with the new value.

FIG. 5illustrates one example of binding source code500associated with an application. The binding source code500is also illustrated inFIG. 6in an enlarged format, which is easier to read. The binding source code500generates events to tie into an existing binding framework. As shown in the first three lines of code inFIG. 6, three graphical sliders are defined to correspond to the spreadsheet cells A1, A2and A3. The next three lines of code inFIG. 6bind these three graphical components to corresponding spreadsheet cell values.

FIG. 7illustrates a screen shot of a development application entitled “example.mxml”, which uses the Adobe Flex Builder 2 visual development environment. Sliders700A,700B and700C, respectively corresponding to spreadsheet cell values A1, A2and A3, are depicted. Binding block702facilitates the expression of bindings. Properties may be expressed using the properties block704. A user altering slider values associated with slider700A will see the resultant change of value at slider700C. Similarly, altering slider700B results in changes to slider700C. In addition, moving slider700C alters values associated with sliders700A and700B. The expressed bindings result in updates to the spreadsheet values. Thus, the techniques of the invention may be used to provide spreadsheet function source code in a larger application with associated graphical components. Manipulation of the graphical components may then result in updates to the spreadsheet.