System and method for dynamic data access in a spreadsheet with external parameters

Abstract

In one aspect, the present invention relates to utilizing a spreadsheet by defining a parameter external to the spreadsheet and associating the parameter with the spreadsheet to define a parameterized workbook. In one embodiment, this utilization further comprises storing a location of the spreadsheet and storing the name of the parameter in the same storage module as the location. In another embodiment, it includes defining a result external to the spreadsheet, the result referencing one or more cells within the spreadsheet. In another embodiment, this utilization further comprises receiving a value for the parameter and generating the result based at least in part on the value and the spreadsheet. In another embodiment, it further comprises associating a type with the parameter. The type can define a range of values or attributes associated with the parameter.

1 . A method for utilizing a spreadsheet, the method comprising:
 defining a parameter associated with the spreadsheet; and   generating a data query based at least in part on the parameter.   
     
     
         2 . The method of  claim 1 , further comprising:
 generating an output based at least in part on the results of the data query.   
     
     
         3 . A method for utilizing a spreadsheet, the method comprising:
 defining a template within the spreadsheet; and   generating an output based at least in part on the template.   
     
     
         4 . The method of  claim 3 , further comprising:
 generating a data query based at least in part on a parameter associated with the spreadsheet.   
     
     
         5 . The method of  claim 3  wherein one or more cells within the template contain formulas. 
     
     
         6 . The method of  claim 5  wherein the formulas are written in spreadsheet formula language. 
     
     
         7 . The method of  claim 3  wherein generating an output further comprises:
 replicating one or more cells within the template. 
 
     
     
         8 . The method of  claim 7  wherein replicating one or more cells further comprises:
 preserving relative cell references. 
 
     
     
         9 . The method of  claim 7  wherein replicating one or more cells further comprises:
 replicating formatting of the template cells. 
 
     
     
         10 . The method of  claim 7  wherein replicating a cell further comprises:
 associating values from a data query with the one or more replicated cells by using column names in formulas within the one or more replicated cells; and 
 performing calculations using the associated values. 
 
     
     
         11 . The method of  claim 10 , further comprising:
 performing special processing on the output when the data query returns no associated values.   
     
     
         12 . The method of  claim 10 , further comprising:
 automatically sorting the output based at least in part on the associated values of the one or more cells in the output.   
     
     
         13 . The method of  claim 3 , further comprising:
 associating a formula language name with the output.   
     
     
         14 . The method of  claim 3 , further comprising:
 automatically updating the output when a change is detected.   
     
     
         15 . The method of  claim 14 , wherein the change comprises a change to:
 i) template cell formulas   ii) template cell formatting   iii) template cell values, or   iv) data query parameters.   
     
     
         16 . A method for utilizing a spreadsheet, the method comprising:
 defining an output range within the spreadsheet;   rendering the output range; and   allowing a user to modify the rendered output range.   
     
     
         17 . The method of  claim 16 , further comprising:
 rendering the output range using HTML.   
     
     
         18 . The method of  claim 16 , wherein allowing a user to modify the rendered output range further comprises:
 allowing the user to sort columns within the output range using a user input.   
     
     
         19 . The method of  claim 16 , wherein allowing a user to modify the rendered output range further comprises:
 allowing a user to interactively expand and collapse a hierarchy using a user input.   
     
     
         20 . A system for utilizing a spreadsheet, the system comprising:
 means for defining a parameter associated with the spreadsheet; and   means for generating a data query based at least in part on the parameter.   
     
     
         21 . A system for facilitating the functional use of a spreadsheet embodying a computational model created using a spreadsheet application program for non-programmer users that when opened with said spreadsheet application program visually presents a non-programmer spreadsheet interface with at least one two-dimensional grid of cells each of which can contain data values or formulas that can refer to and be calculated dependent on other cells with relative referencing, which cells can access external data sources when so opened and which model can be modified by direct user modification of cell contents when so opened, the system comprising:
 a computer programmed to provide a design-time program environment comprising a non-programmer interface adapted to:
 define a parameter external to the spreadsheet when the spreadsheet is run as a programmatic function in a run-time program environment wherein a run-time caller of a parameterized workbook including the spreadsheet can not modify cell values except through the parameter; and 
 associate the parameter with the spreadsheet to define the parameterized workbook wherein the parameter applies to the spreadsheet as a whole, thereby allowing any formula in the spreadsheet to reference the parameter when the spreadsheet is run at run time and allowing a formula that refers to an external data source to query that source based on a parameter value received at run time. 
   
     
     
         22 . A system for functionally utilizing a spreadsheet embodying a computational model created using a spreadsheet application program for non-programmer users that when opened with said spreadsheet application program visually presents a non-programmer spreadsheet interface with at least one two-dimensional grid of cells each of which can contain data values or formulas that can refer to and be calculated dependent on other cells with relative referencing, which cells can access external data sources when so opened, and which model can be modified by direct user modification of cell contents when so opened, the system comprising:
 a computer programmed to provide a run-time program environment,
 wherein said spreadsheet can be called as part of a parameterized workbook to be executed as an automated programmatic function where the only allowed input to the function from the caller is through a parameter previously associated with the spreadsheet to define the parameterized workbook, and 
 wherein said function receives a value for the parameter at run time, computes cell values in the spreadsheet that are dependent, directly or indirectly, on the parameter, including a query to an external data source, which query is based on said received parameter value, and renders an output to the run-time caller based on the computed cell values. 
   
     
     
         23 . Computer-readable media for facilitating the functional use of a spreadsheet embodying a computational model created using a spreadsheet application program for non-programmer users that when opened with said spreadsheet application program visually presents a non-programmer spreadsheet interface with at least one two-dimensional grid of cells each of which can contain data values or formulas that can refer to and be calculated dependent on other cells with relative referencing, which cells can access external data sources when so opened, and which model can be modified by direct user modification of cell contents when so opened, said media containing instructions to program a computer program to:
 provide a design-time program environment comprising said non-programmer spreadsheet interface and a non-programmer interface adapted to:
 define a parameter external to the spreadsheet when the spreadsheet is run as a programmatic function in a run-time program environment wherein a run-time caller of a parameterized workbook including the spreadsheet can not modify cell values except through the parameter; and 
 associate the parameter with the spreadsheet to define the parameterized workbook wherein the parameter applies to the spreadsheet as a whole, thereby allowing any formula in the spreadsheet to reference the parameter when the spreadsheet is run at run time. 
   
     
     
         24 . Computer-readable media for functionally utilizing a spreadsheet embodying a computational model created using a spreadsheet application program for non-programmer users that when opened with said spreadsheet application program visually presents a non-programmer spreadsheet interface with at least one two-dimensional grid of cells each of which can contain data values or formulas that can refer to and be calculated dependent on other cells with relative referencing, which cells can access external data sources when so opened, and which model can be modified by direct user modification of cell contents when so opened, said media containing instructions to program a computer program to:
 provide a run-time program environment wherein said spreadsheet can be called to be executed as an automated programmatic function where the only allowed input to the function from the caller is through a parameter previously associated with the spreadsheet to define a parameterized workbook, and   wherein said function receives a value for the parameter at run time, computes cell values in the spreadsheet that are dependent, directly or indirectly, on the parameter, and renders an output to the run-time caller based on the computed cell values.   
     
     
         25 . A system for utilizing a spreadsheet embodying a computational model created using a spreadsheet application program for non-programmer users that when opened with said spreadsheet application program visually presents a non-programmer spreadsheet interface with at least one two-dimensional grid of cells each of which can contain data values or formulas that can refer to and be calculated dependent on other cells with relative referencing, which cells can access external data sources when so opened, and which model can be modified by direct user modification of cell contents when so opened, the system comprising:
 a computer programmed to provide a design-time program environment comprising said non-programmer spreadsheet interface and a non-programmer interface adapted to:
 define a parameter external to the spreadsheet when the spreadsheet is run as a programmatic function in a run-time program environment wherein a run-time caller of a parameterized workbook including the spreadsheet can not modify cell values except through the parameter; and 
 associate the parameter with the spreadsheet to define the parameterized workbook wherein the parameter applies to the spreadsheet as a whole, thereby allowing any formula in the spreadsheet to reference the parameter when the spreadsheet is run at run time. 
   
     
     
         26 . The system of  claim 25  further comprising:
 a computer programmed to provide a run-time program environment wherein the parameterized workbook can be called and the spreadsheet run as a programmatic function where the only input to the function from the caller is through the parameter and wherein said function receives a value for the parameter at run time, computes cell values in the spreadsheet that are dependent, directly or indirectly, on the parameter, and renders an output to the run-time caller based on the computed cell values. 
 
     
     
         27 . A method for utilizing parametrically a spreadsheet embodying a computational model created using a spreadsheet application program for non-programmer users that when opened with said spreadsheet application program visually presents at least one two-dimensional grid of cells each of which can contain data values or formulas that can refer to and be calculated dependent on other cells with relative referencing, which cells can access external data sources when so opened and which model can be modified by direct user modification of cell contents when so opened, the method comprising:
 defining a parameter external to the spreadsheet when the spreadsheet is run parametrically in a run-time program environment wherein a run-time caller of the spreadsheet can not modify cell values except through the parameters;   associating the parameter with the spreadsheet at design time of the spreadsheet in a design-time non-programmer program environment allowing said model creation and modification by said direct user modification of cell contents to define a parameterized workbook, wherein the parameter applies to the spreadsheet as a whole, thereby allowing any formula in the spreadsheet to reference the parameter when the spreadsheet is run parametrically;   receiving a value for the parameter at run time;   computing cell values in the spreadsheet that are dependent, directly or indirectly, on the parameter; and   in this rendering an output to the run-time caller based on the computed cell values.   
     
     
         28 . A method for utilizing a spreadsheet embodying a computational model created using a spreadsheet application program for non-programmer users that when opened with said spreadsheet application program visually presents a non-programmer spreadsheet user interface of at least one two-dimensional grid of cells each of which can contain data values or formulas that can refer to and be calculated dependent on other cells with relative referencing, which cells can access external data sources when so opened and which model can be modified by direct user modification of cell contents when so opened, the method comprising:
 at design time in a design-time non-programmer program environment allowing said model creation and modification by said direct user modification of cell contents through said non-programmer spreadsheet program user interface by
 defining a parameter external to the spreadsheet when the spreadsheet is run as a programmatic function in a run-time program environment wherein a run-time caller of the spreadsheet can not modify cell values except through the parameter, and 
 associating the parameter with the spreadsheet to define a parameterized workbook to be called at run time and the spreadsheet run as an automated programmatic function where the only input to the function from a caller is through the parameter and wherein the parameter applies to the spreadsheet as a whole, thereby allowing any formula in the spreadsheet to reference the parameter when the spreadsheet is run in the run-time program environment and wherein said non-programmer user interface allows the defining and associating to be performed by the design-time user without code programming. 
   
     
     
         29 . The method of  claim 28  further comprising:
 at run time in said run-time program environment,
 receiving a value for the parameter from the caller to instantiate the parameterized workbook, wherein the parameter takes on the received value, 
 computing cell values in the spreadsheet that are dependent, directly or indirectly, on the received parameter value, and 
 rendering an output to the caller from the run-time instantiation of the parameterized workbook based on the computed cell values. 
 
 
     
     
         30 . A method for utilizing a conventional spreadsheet embodying a computational model created using a spreadsheet application program for non-programmer users that when opened with said spreadsheet application program visually presents a non-programmer spreadsheet interface with at least one two-dimensional grid of cells each of which can contain data values or formulas that can refer to and be calculated dependent on other cells with relative referencing, which cells can access external data sources when so opened, and which model can be modified by direct user modification of cell contents when so opened, the method comprising:
 at design time in which a user has full access to the spreadsheet in a conventional spreadsheet program user interface,
 defining a parameter external to the spreadsheet whose value is to be supplied externally to the spreadsheet at run time, and 
 associating the parameter with the spreadsheet to define a parameterized workbook to be called at run time as an automated programmatic function where the only input to the function from a caller is through the parameter value and wherein the parameter applies to the spreadsheet as a whole, 
 thereby allowing any formula in the spreadsheet to reference the parameter and wherein a user interface allows the defining and associating to be performed by the user without code programming; and 
   at run time in which a caller does not use a conventional spreadsheet program user interface,
 receiving a value for the parameter from the caller to instantiate the parameterized workbook, wherein the parameter takes on the received value, 
 computing cell values in the spreadsheet that are dependent, directly or indirectly, on the received parameter value, and 
 rendering an output to the caller from the run time instantiation of the parameterized workbook based on the computed cell values.