Resolver One, aka the Python Spreadsheet, has a beautiful design at its core, which I would like to present to the programmer community. No knowledge of Python is necessary to understand this article.
The goal for the application is to provide seamless integration of spreadsheet (formulae in the cells) with sequential code. In MS Excel the VBA integration is anything but seamless. In the rest of this post I explain how Resolver One integrates Python with spreadsheet functionality.
Spreadsheet
To ensure that we are on the same page, I'll pinpoint some facts about spreadsheets. In a traditional spreadsheet there are two types of content a cell can hold:
- constant: like number, date or text
- formula: an expression usually referencing other cells
A user can enter this content by typing to a cell. Additionally, a user can specify formatting for a cell by various GUI means; for example, make the cell's font bold by clicking a toolbar button.
How do you add sequential code to it? Resolver's solution is to turn the spreadsheet into sequential program. User's custom code merges with the rest of the spreadsheet thus expressed.
Spreadsheet as a program
In Resolver One, what you see in the grid is the result of executing the code displayed in the coding pane.
Following is the code that appears in a document created with File | New command. I have pruned it by dropping comments and not required import statements.
from Library.Workbook import Workbook
workbook = Workbook()
workbook.AddWorksheet("Sheet1")
workbook.AddWorksheet("Sheet2")
workbook.AddWorksheet("Sheet3")
Constants = {}
Formatting = {}
workbook.Populate(Constants, Formatting)
The above featured code creates a workbook with three empty worksheets.
Where did the code come from? Resolver One generated it from the model. By model I mean the data structure that remembers constants, formatting and formulae input by the user through the GUI interface.
The drill is: Resolver One generates code from the model, executes the program, takes the resulting workbook object and displays it in the tabbed grid view. This is called recalculation.
Every time the user changes the model through the GUI commands, a new recalculation is triggered. The next snippet presents code after me changing the model by typing to some cells in the grid, I want to give you a feel of the generated code. I put number 1 into A1 and formula =A1*2 into B1.
from Library.Workbook import Workbook
workbook = Workbook()
workbook.AddWorksheet("Sheet1")
workbook.AddWorksheet("Sheet2")
workbook.AddWorksheet("Sheet3")
Constants = {
'Sheet1': {
(1, 1): 1,
},
}
Formatting = {}
workbook.Populate(Constants, Formatting)
workbook["Sheet1"].B1 = workbook["Sheet1"].A1*2
User Code
All the code generated from the model is divided among three uneditable sections in the coding pane:
- imports and worksheet creation
- constants and formatting
- formulae
In addition, there are three editable sections for user code, one after each of the generated ones. The user code is "on the same rights" with the generated code which is creating the workbook to display; the merge is perfect :).
Buttons
That would have been all and buttons aren't strictly necessary to discuss here, but they fit quite well. A button can be created in the user code and set as the value of a cell. Of course a button has a click handler that a user would define as a function. This handler is not executed during the recalculation of course. So when is it executed, and more importantly what can it do?
The first question is quite easy. The handler gets called when user clicks the button. He can't click it until he can see it, which is after the recalculation is finished the workbook is displayed on the grid.
There is nothing magic in what a button can do. Python has lexical scopes, so the handler has access to anything that was in scope where the function was defined, in particular this means the workbook object. For instance, handler could create two hundred new buttons and place them all over the grid.
When a user clicks a button the handler is called after which Resolver One refreshes the grid to display any changes made to the displayed workbook. There is no recalculation, because the model didn't change. This also means, that any changes made by the button handler will disappear after the next recalculation is finished, because there will be simply different workbook object being displayed.
Note: Resolver One 1.4, next major release, will provide means for changing the model from a button handler.
Wrapup
So that is the architecture of Resolver One, brought to you by one of the programmers on the team. Every other feature in the application revolves around this foundation. Of course the model contains more stuff, for which code needs to be generated; but it all ends up in one of the three sections in coding pane. To fully grok different features in Resolver One, you need to understand recalculation.
