Python Programming, news on the Voidspace Python Projects and all things techie.
Resolver One 1.2: Spreadsheet Modularity
Resolver One 1.2 has been released!
As always, Resolver One is free to try and to use non-commercially and for open source projects. Even better, there is now no need to register on the website in order to download it.
As always the updates in the new release are a combination of standard spreadsheet functionality, bugfixes and features that build on the unique programmability of Resolver One. This time there is a special feature, which I think has great potential for changing the way spreadsheets are used.
The headline new features are:
- Referencing cells in other Resolver One spreadsheets in formulae (you have been able to reference cells in Excel spreadsheets for a while)
- Creating dropdown lists populated from cellranges, Python lists or other iterables from user code
- Paste special: control over pasting formulae / values / formatting / comments
- Drag fill - auto-incrementing dates, numbers and formulae where appropriate
- Percent formatting of cells and use of % in formulae and constants
- Cached worksheets that retain data across recalcs
- Header lookups on worksheets and cellranges with any type (not just strings)
- Ability to freeze first few rows / columns
Minor new features:
- Slicing of .Rows and .Cols on worksheets and cellranges
- RepaintGrid function to update display whilst a button handler is executing (a wait cursor will also show whilst a button handler is executing - both are useful for long running button handlers)
- An API to access cells / rows / cols / worksheets / cellranges by index (this bypasses headers where you might have integers as headers and want to lookup by index number)
- Custom numeric types (those that define __abs__) in cells will be treated properly as numbers (right aligned in the grid etc)
- A host of new spreadsheet function
- Improvements to the bloomberg integration for the Financial Edition
The important bugfixes:
- Button click handlers swallow exceptions
- Formulae not visible or editable in cell when it is marked with an error cross (previously you had to use the formula bar)
- Inserting / deleting rows did not always update locations of names
- A few rare crashers fixed
The most important new feature is missing from these lists: RunWorkbook.
There are lots of problems with spreadsheets, largely because they are so successful and get used everywhere. Some of the problems include the need to input data into several different places to keep complex spreadsheet systems in sync, and duplicated spreadsheet logic in multiple places to perform similar operations on different datasets.
Some of the problems can be solved by backing data in a database and using spreadsheets for analysis and reporting. Resolver One works very well with databases, but now offers a new solution - spreadsheet modularity through the RunWorkbook function.
RunWorkbook (which can be used from formulae or user code) allows you to load up (as an object) another Resolver One spreadsheet. As well as referencing values in the loaded workbook, you can pass in values to override data in the workbook. These values will be used for the calculation, allowing you to treat spreadsheets as functions. You can use it to create multiple different reports that share the same data (contained in a single spreadsheet), or create data-analysing spreadsheets where you pass data in to the RunWorkbook call.
This will probably be easier to understand if I show you some simple examples. The basic way to call Runworkbook (a user code example) is:
some_sheet = new_workbook['Some Sheet']
value = some_sheet.A1
data = CellRange(some_sheet.Cells.A1, some_sheet.Cells.ZZ10)
Runworkbook returns a workbook instance that you can use in the same way as the standard Workbook.
As the workbook, or the worksheets it contains can be put in cells you are free to access them from formulae. You can also use the CopyRange function to copy blocks of data out of the loaded workbook.
data_sheet = new_workbook['Data']
source = CellRange(data_sheet.Cells.A1, data_sheet.BottomRight)
CopyRange(source, workbook['Loaded Data'].Cells.A1)
You can pass in values, either by specifying the location or by using names you have defined in the spreadsheet, and then pull values back out. This allows spreadsheets to act as functions. The values you pass in are set in the workbook, the calculation is then done and you can pull out the results. You can even pass in whole blocks of data (cellranges).
If the loaded workbook defines any names (through the 'Edit Names' dialog) then you can access those names as attributes on the loaded workbook: for example new_workbook.result.
- You can also pass in overrides that will be used when the workbook is calculated. There are several ways to pass in overrides, but the easiest is to define names that either point to a cellrange or a single cell. You can then use those names as keyword arguments in the call to RunWorkbook:
- result = RunWorkbook('spreadsheet.rsl', input=23, source_data=some_cellrange).result
As well as passing in single values (for names that point to single cells), you can pass in whole blocks of data as cellranges. Cellranges are copied into the workbook before being calculated.
This makes spreadsheet modularity much easier (it makes it possible!) - complex spreadsheets can be broken down into smaller spreadsheets, which are then simpler to understand and can be reused. It also allows you to test your spreadsheet logic by passing in test data and checking that the spreadsheet produces the correct results. We are building systems on top of this to makes spreadsheet testing even easier.
We've already started work on version 1.3, and hope that the release cycle will be much quicker this time. We're building some custom solutions for customers on top of Resolver One. Using your product doesn't half concentrate the mind on what features you need to add!
This work is licensed under a Creative Commons Attribution-Share Alike 2.0 License.