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.
