System and method for efficiently and flexibly utilizing spreadsheet information

• US 20030110191A1
• Filed: 07/11/2002
• Published: 06/12/2003
• Est. Priority Date: 07/13/2001
• Status: Active Grant

• Current Assignee: Netview Technologies Incorporated
• Original Assignee: Netview Technologies Incorporated
• Inventors: Rasin, Gregory, Knourenko, Andrey, Handsaker, Robert
• Granted Patent: US 7,251,776 B2
• Time in Patent Office: Days
• US Class Current: 715/503
• CPC Class Codes: G06F 40/18 of spreadsheets form-filling G06F40/174

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.

Claims

1. A method for utilizing a spreadsheet, the method comprising:

defining a parameter external to the spreadsheet; and

associating the parameter with the spreadsheet to define a parameterized workbook.

2. The method of claim 1 wherein associating the parameter further comprises:

storing a location of the spreadsheet; and

storing the name of the parameter in the same storage module as the location.

3. The method of claim 1 further comprising defining a result external to the spreadsheet, the result referencing one or more cells within the spreadsheet.

4. The method of claim 3 further comprising:

receiving a value for the parameter; and

generating the result based at least in part on the value and the spreadsheet.

5. The method of claim 3 further comprising defining a format for the result.

6. The method of claim 1 further comprising associating a type with the parameter, the type defining a range of values.

7. The method of claim 1 further comprising associating a type with the parameter, the type defining attributes associated with the parameter.

8. The method of claim 1 further comprising defining a formula within the spreadsheet using the parameter.

9. The method of claim 1 further comprising binding a value of the parameter to an instance of the associated spreadsheet.

10. The method of claim 1 wherein the parameterized workbook is a first parameterized workbook, the method further comprising:

defining a second parameterized workbook; and

referencing a result from the second parameterized workbook in a formula in the first parameterized workbook.

11. The method of claim 1 further comprising:

storing, separate from the spreadsheet and after the spreadsheet closes, an intermediate value used in a calculation for the spreadsheet; and

associating the intermediate value with the spreadsheet and a value of the parameter used to calculate the intermediate value.

12. The method of claim 11 further comprising automatically calculating a result associated with the spreadsheet without opening the spreadsheet by using the stored intermediate value.

13. A method of claim 1 further comprising distributing calculations of the spreadsheet among a plurality of computing devices.

14. The method of claim 13 wherein distributing calculations of the spreadsheet comprises distributing calculations of the spreadsheet among a plurality of computing devices based at least in part on one or more formulas in the spreadsheet.

15. The method of claim 13 wherein distributing calculations of the spreadsheet comprises distributing the calculations of the spreadsheet among a plurality of computing devices based at least in part on one or more stored associations in a database associated with the parameterized workbook.

16. A system for utilizing a spreadsheet, the system comprising:

a spreadsheet comprising one or more associated formulas; and

a list of parameters associated with the spreadsheet, the list of parameters being external to the spreadsheet and one or more parameters within the list referenced in the one or more associated formulas of the spreadsheet.

17. The system of claim 16 further comprising a storage module to store a location of the spreadsheet and store the name of each parameter in the associated list of parameters.

18. The system of claim 16 further comprising a list of results associated with the spreadsheet.

19. The system of claim 16 further comprising a calculation module to receive a value for a first parameter within the list of parameters and generate a first result based at least in part on the value and the spreadsheet.

20. The system of claim 16 further comprising an instantiator module to bind a value of a first parameter within the list of parameters to an instance of the spreadsheet.

21. The system of claim 16 wherein the spreadsheet is a first spreadsheet, the system further comprising a second spreadsheet having one or more formulas referencing a result from the first spreadsheet.

[A Virtual Workbook]

22. A method for utilizing a plurality of spreadsheets, the method comprising:

defining a parameter; and

defining a set of one or more selection rules to select a first spreadsheet from a plurality of spreadsheets based at least in part on a value of the parameter, thereby defining a virtual workbook.

23. The method of claim 22 further comprising selecting a first workbook from the plurality of workbooks based at least in part on a value for the parameter and the set of one or more rules.

24. The method of claim 22 further comprising referencing the virtual workbook in a created spreadsheet as a substitute for a second spreadsheet in the plurality of spreadsheets.

25. The method of claim 22 further comprising defining a result external to the virtual workbook, the result referencing one or more cells within each spreadsheet in the plurality of spreadsheets.

26. The method of claim 25 further comprising:

receiving a value for the parameter; and

generating the result based at least in part on the value and the first spreadsheet.

27. The method of claim 25 further comprising defining a format for the result.

28. The method of claim 22 further comprising associating a type with the parameter, the type defining a range of values.

29. The method of claim 22 further comprising associating a type with the parameter, the type defining attributes associated with the parameter.

30. The method of claim 22 further comprising defining a formula containing the parameter within a spreadsheet in the plurality of spreadsheets.

31. The method of claim 22 further comprising binding a value of the parameter to an instance of the selected first spreadsheet.

32. The method of claim 22 wherein the virtual workbook is a first virtual workbook, the method further comprising:

defining a second virtual workbook; and

referencing a result from the second virtual workbook in a formula in the first virtual workbook.

33. The method of claim 22 further comprising:

defining a parameterized workbook; and

referencing a result from the parameterized workbook in a formula in the virtual workbook.

34. The method of claim 22 further comprising:

defining a parameterized workbook; and

referencing a result from the virtual workbook in a formula in the parameterized workbook.

35. The method of claim 22 further comprising:

storing, separate from the first spreadsheet and after the first spreadsheet closes, an intermediate value used in a calculation for the first spreadsheet; and

associating the intermediate value with the first spreadsheet and a value of the parameter used to calculate the intermediate value.

36. The method of claim 35 further comprising automatically calculating a result associated with the first spreadsheet without opening the first spreadsheet by using the stored intermediate value.

37. A method of claim 22 further comprising distributing calculations of the first spreadsheet among a plurality of computing devices.

38. The method of claim 37 wherein distributing calculations of the first spreadsheet comprises distributing calculations of the first spreadsheet among a plurality of computing devices based at least in part on one or more formulas in the first spreadsheet.

39. The method of claim 37 wherein distributing calculations of the first spreadsheet comprises distributing the calculations of the first spreadsheet among a plurality of computing devices based at least in part on one or more stored associations in a database associated with the virtual workbook.

40. A system for utilizing a plurality of spreadsheets comprising:

a list of parameters; and

a selection module to select a first spreadsheet from a plurality of spreadsheets.

41. The system of claim 40 further comprising a list of results associated with each of spreadsheets in the plurality of spreadsheets.

42. The system of claim 40 further comprising a calculation module to receive a value for a first parameter within the list of parameters and generate a first result based at least in part on the value and the first spreadsheet.

43. The system of claim 40 further comprising an instantiator module to bind a value of a first parameter within the list of parameters to an instance of the first spreadsheet.

44. The system of claim 40 wherein the spreadsheet is a first spreadsheet, the system further comprising a second spreadsheet having one or more formulas referencing a result from the first spreadsheet.

45. The system of claim 40 further comprising a storage module to store a location of each of the spreadsheets in the plurality of spreadsheets and store the name of each parameter in the associated list of parameters.

[A Parameterized Workbook Information Database]

46. A method for storing information associated with a spreadsheet, the method comprising:

storing a location of a reference spreadsheet; and

storing a list of parameters associated with the reference spreadsheet.

47. The method of claim 46 further comprising:

storing, separate from the reference spreadsheet and after the reference spreadsheet closes, an intermediate value used in a calculation for a reference spreadsheet; and

associating the intermediate value with the reference spreadsheet and a value of a parameter from the list used to calculate the intermediate value.

48. The method of claim 47 wherein the automatically performing a calculation further comprises automatically calculating many workbook results by enumerating lists of legal parameter values based on type information associated with each respective parameter.

49. The method of claim 47 further comprising automatically calculating a result associated with the reference spreadsheet without opening the reference spreadsheet by using the stored intermediate value.

50. The method of claim 46 further comprising storing a list of outputs associated with the reference spreadsheet.

51. The method of claim 46 further comprising storing an association of the reference spreadsheet to another spreadsheet upon which the reference spreadsheet depends

52. The method of claim 51 further comprising monitoring the another spreadsheet to detect a change within an output.

53. The method of claim 52 further comprising automatically performing a calculation using the reference spreadsheet in response to detecting the change within the output of the another spreadsheet.

54. The method of claim 46 further comprising storing an association of the reference spreadsheet to a data source upon which a formula in the spreadsheet depends.

55. The method of claim 54 further comprising monitoring the data source to detect a change within the data source.

56. The method of claim of 55 further comprising automatically performing a calculation using the reference spreadsheet in response to detecting the change within the data source.

57. The method of claim 46 further comprising storing version data associated with the reference workbook.

58. The method of claim 46 wherein the location comprises a network address.

59. The method of claim 46 further comprising distributing calculations among a plurality of computing devices based at least in part on one or more stored associations.

60. A method of claim 46 further comprising distributing calculations of the reference spreadsheet among a plurality of computing devices.

61. The method of claim 60 wherein distributing calculations of the reference spreadsheet comprises distributing calculations of the reference spreadsheet among a plurality of computing devices based at least in part on one or more formulas in the reference spreadsheet.

62. The method of claim 60 wherein distributing calculations of the reference spreadsheet comprises distributing the calculations of the reference spreadsheet among a plurality of computing devices based at least in part on one or more stored associations in a database associated with the reference spreadsheet.

[A Data Range]

63. 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.

64. The method of claim 63 further comprising generating an output based at least in part on the results of the data query.

65. 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.

66. The method of claim 65 further comprising generating a data query based at least in part on a parameter associated with the spreadsheet.

67. The method of claim 65 wherein one or more cells within the template contain formulas.

68. The method of claim 67 wherein the formulas are written in spreadsheet formula language.

69. The method of claim 65 wherein generating an output further comprises replicating one or more cells within the template.

70. The method of claim 69 wherein replicating one or more cells further comprises preserving relative cell references.

71. The method of claim 69 wherein replicating one or more cells further comprises replicating formatting of the template cells.

72. The method of claim 69 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.

73. The method of claim 72 further comprising performing special processing on the output when the data query returns no associated values.

74. The method of claim 72 further comprising automatically sorting the output based at least in part on the associated values of the one or more cells in the output.

75. The method of claim 65 further comprising associating a formula language name with the output.

76. The method of claim 65 further comprising automatically updating the output when a change is detected.

77. The method of claim 76 wherein the change comprises a change to i) template cell formulas, ii) template cell formatting, iii) template cell values, or iv) data query parameters.

78. 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.

79. The method of claim 78 further comprising rendering the output range using HTML.

80. The method of claim 78 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.

81. The method of claim 78 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.