Resolver Hacks contains information and code for doing fun and useful things with Resolver One. This site is created and maintained by Michael Foord, not by Resolver Systems. Please read the disclaimer before using any of the code on this site.
IronPython in Action
Follow Michael on:
Bugs, Features and Futures
Note
The ideas and opinions expressed here are mine, and are not the official development priorities of Resolver Systems. Development of Resolver One (including new features and bug fixes) will be driven by real customer feedback. The contents of this article are the ways I think Resolver One can grow.
Introduction
Resolver One is a new program. As well as being young, Resolver One is a program with enormous potential. Business applications can be created using a mixture of the familiar spreadsheet interface for presenting data and hardcore IT systems accessed through .NET. The end result can be exported as Python code and turned into a web application in a matter of hours or days instead of weeks or months.
Resolver One already has features that make working with databases easy - database worksheets [1] and trigger listeners [2], however its not all roses and glory - yet! Although Resolver One has some quite advanced features, it is also missing some basic features. Don't worry, it won't take long to catch up, but to save the developers getting plagued with requests I thought I would list the things we know that Resolver One is missing.
Warning
Resolver One is being developed very rapidly, new features are being added almost every week. I do my best to keep this article up to date, but some of the 'missing features' listed here may already be in Resolver.
Spreadsheets are popular because they are a convenient way of handling and presenting business data. On the flip side, the problems caused by trying to use spreadsheets for things they aren't really suitable for are well known. Some of these problems can be solved with Resolver One, and these are real problems that can cost millions of dollars. Ideas like integrating a unit test framework into Resolver One or providing auditing and versioning tools aren't difficult, and it will be a fun journey seeing how Resolver One grows.
Because it is the most interesting part, let's look at these 'Future' ideas for Resolver One first.
The Future
Unit Testing Spreadsheet Logic
Errors in spreadsheets can be very expensive. Many errors are caused by incorrect formulae - and these should be simple to test. Unit testing is a very powerful paradigm from the programming world and could easily be applied to spreadsheets, which are essentially a programming interface.
With Resolver it is already possible to use the Python standard library module unittest [3] to test functions and support code that you use in user code.
Additionally, you can use RunWorkbook to test the formulae and logic it contains. (You can set test data in the spreadsheet and make assertions about the results that should appear in cells containing formulae.) You can see an example of how to do this in Testing Spreadsheets.
We need to work out the best way of integrating a test framework into Resolver One itself. Perhaps a separate 'test mode' where constants or data normally fetched from databases can be populated with test data, and Resolver One will test the resulting values against a result set that you specify. This could help verify integrity whilst you are building a spreadsheet.
Authentication, Auditing, Versioning
Common problems with spreadsheets include:
- How do I know that this is the latest version?
- Distributing a spreadsheet that can only be modified by authorised individuals
- Being able to check / track who made what changes to a spreadsheet, and perhaps restore portions of it to a previous state
These things aren't currently possible with Resolver One, but they are all technical challenges that have already been solved in various ways.
AJAX Resolver
We have a webserver version of Resolver One that can be used to serve the applications you create across an intranet or the internet. More complicated than just publishing spreadsheets is having a full Resolver One interface (with code box!) as an AJAX application. Fortunately Silverlight could make that task a whole lot easier than it would have been otherwise.
Export as .NET assembly
Resolver One spreadsheets can be exported as Python code or integrated directly into .NET / IronPython applications with RunWorkbook. Integrating with a .NET application is relatively straightforward (the IronPython Engine was designed to be embedded and has a nice API), but if you could export spreadsheets as .NET assemblies it would be even more straightforward. (These assemblies would need to use the IronPython engine, but could provide a spreadsheet API that doesn't require any knowledge of IronPython on the part of the .NET programmer.)
Scripting Resolver One Internals
Although you can do a lot from user code, there are some things you can't do. You can't create new documents or populate worksheets with data that doesn't have to be calculated again at the next recalculation.
Note
As of Resolver One version 1.4 you can fetch and set formulae (which includes just changing data) from code executed inside a button handler. This is the first step in what we are calling 'model-side-scripting', which is what I'm talking about here.
What would be nice would be a full interface for scripting the Resolver interface - preferably both document level (macros attached to a document that can be triggered from buttons or called into from user code) and application level (macros installed into Resolver that can be used with any document).
This could include access to events like:
- Resolver started
- Document loaded
- Document closed
- Recalc started
- Recalc completed
- Resolver closed
This would bring in a whole new world of ways to crash Resolver!
I'd particularly like to be able to execute code when a document is first loaded (this is not too hard to fake-up from user code) and when a document is closed (which I haven't found a way to fake). That way I could automatically start subprocesses that communicate with the document and exit when the document is closed.
A user configurable toolbar would be a good companion for this, allowing you to bind application level macro functions to button.
We will probably start this by exposing a minimal public API for our core classes. That way you will be able to write external tools that can create Resolver documents, or load documents and extract information from then. Exposing that API from within Resolver would be a simple next step from here.
Report Generation
Resolver One supports standard printing, which is often all that you need for spreadsheets. Sometimes spreadsheets are created to be part of business reports. For this a more sophisticated reporting system would be useful, including exporting to Word documents with embedded charts and so on. Fortunately this can be added to Resolver One as a third party extension and is something I might experiment with myself.
Online Repository of Spreadsheet Modules
There are already several online sites stuffed to the gills with Python modules and .NET libraries that might be useful to spreadsheet creators (see the Useful Links page). Whilst not strictly a future feature of Resolver, it would certainly be a good thing to provide a central location where modules useful to creators of Resolver spreadsheets can live and be distributed. Perhaps in my copious spare time...
An equivalent of easy_install for Resolver modules would make a nice companion to the online repository however.
Note
There are already two different approaches to this problem out in the wild:
- The Resolver Exchange allows you to share spreadsheets and code online, but doesn't have automatic integration.
- The Resolver Forge is my proof of concept for automatic downloading of spreadsheet modules.
Excel Interaction
There are various ways that Resolver and Excel could interact. Resolver One could drive Excel, Excel could drive Resolver One, or they could simply chat to each other.
The difficult part is not making it happen, but working out what are the real use cases for this feature and how to build an intuitive interface.
Note
Excel is automated through COM. For examples of various ways to do this, see the IronPython Cookbook Interop Section.
Typed Cells
If you could specify the type of data a cell contains (date, number, text etc), then Resolver One could automatically validate data that you enter - and convert it to the right type for when you access it from formulae and user code.
This is a relatively simple feature, but could be very powerful. It is a natural extension of the 'coloring by type' that Resolver One already has.
External API
It would be really nice to start Resolver One in 'server mode' and expose an API - perhaps by XML-RPC or a similar mechanism (REST and JSON are the buzzwords of the moment, and for good reason).
That way you could interact with spreadsheets programatically from any other language. This could be run locally (one a single machine), or exposed across a network.
As the Resolver One calculation engine can already be embedded in .NET applications through RunWorkbook it wouldn't be too hard to build an XML-RPC layer on top yourself. This wouldn't allow the creation of spreadsheets - but that feature may be built into the calculation engine before we get around to adding an automation API.
Partial Recalculation
This is a controversial one. All major spreadsheets get a performance boost by only recalculating part of the spreadsheet at a time. If you change a cell, then you only need to recalculate cells that depend on it (directly or indirectly).
That isn't very hard to do, but it doesn't rest easily with the Resolver One paradigm of what a spreadsheet is. In Resolver One a spreadsheet is a program, and changing it means that it should be re-executed. User code can do things with cells that we can't track through normal dependency analysis - so only doing a partial recalculate can cause incorrect results.
You actually see this with other spreadsheets. You can make function calls using their scripting languages (VBA for Excel), and those function calls aren't always re-evaluated when you change something. This means that results can be incorrect until you force a full recalc.
Resolver One gets round this by recalculating in the background. Even when a recalculation is in progress, the user interface is still responsive. A patch-up function is executed immediately - so that you see some value straight away - once the full recalculate has finished, the results are synced back to the display.
'The Boss' is reluctant to switch to partial recalculation; because of the 'paradigm breakage'. In terms of major improvements to performance we will probably need to offer it as an alternative recalculation model (I think).
More IDE Features in the Codebox
The code editor in Resolver One is based on a great text editing component. It isn't a full IDE though, and when editing code there are lots of IDE features that would be useful. Fortunately these can be added incrementally to the code editor.
Missing Features
Providing the features in this list is a high priority for the Resolver One developers. If its shown here then it will appear in Resolver One soon - probably!
We have our own internal 'User Story' list of features that we would like to add. This has a couple of thousand (!) outstanding user stories on it (some of which should never see the light of day), now that list of future features will never evaporate.
A Python Console
Ok, so this isn't a vital feature - but given the nature of Resolver One it is an obvious one! A console would be great for experimenting with. You could also do interesting things like run a set of calculations a 100 times to do 'what-if' projections. This will be added to Resolver One soon, and it can't be soon enough in my opinion...
You can already get a lot of the benefits of a console through judicious use of print statements in your code. Add a print statement and press F9 to force a recalculation, and you will see the result of the print in the output pane at the bottom of the user interface.
Charting
This website shows how to do charting with ZedGraph and image worksheets. It won't be long before a full charting solution is built into Resolver One, including floating images on the grid.
Full Undo & Redo in the Code Box
Adding undo and redo for all the spreadsheet actions was a long and painful process, but we did it. 
Unfortunately undo and redo in the code box is a different story. Performing a recalculation means recreating the generated code from the spreadsheet, which blows the undo and redo stacks in the code box. This means that undo and redo history in the code editor doesn't persist between recalculations. Fixing this means that we will have to implement full undo and redo ourselves rather than using the built-in features of the codebox. This is quite a lot of work (but relatively straightforward).
Import and Export CSV
This isn't a difficult feature to add (although you might be surprised by how many corner cases CSV has). Adding this will make Resolver much more flexible in the number of data sources and programs that Resolver can interact with.
Recently Used Documents
A list of recently opened documents should appear somewhere in the file menu.
Write Back to Databases
Resolver One already makes it easy to fetch data from databases. It is possible to write back to databases with user code and buttons, but built-in support would be nicer.
More Controls in the Grid
Resolver One supports buttons and drop down lists in the grid. It is only a matter of time before more controls are added. In fact, being able to create whole application user interfaces as part of Resolver One would be good, but perhaps belongs in The Future section of this page.
A Standard Directory for Spreadsheet Modules
At the moment there is no standard place to import from modules that you have written yourself. Spreadsheets can import from the Library directory (part of the Resolver installation), from the same directory as the spreadsheet, or from any directory on the IRONPYTHONPATH, but there is nowhere convenient to put modules that you want to share between spreadsheets.
A CPython Compatibility Layer
Resolver spreadsheets exported as Python classes use our 'Library' code. This is the Workbook, Worksheet, Cell and so on. This code is currently dependent on IronPython - so you can't export a Resolver spreadsheet as code and use it in a CPython application. Adding a compatibility layer that removes the dependency on IronPython wouldn't be very difficult.
This is something I would really like to happen, and isn't very difficult technically. Of course if you implement this yourself, be sure to let us know.
Drag and Drop
You should be able to open '.rsl' files by dropping them onto the Resolver One interface. I can't tell you the number of times I have tried this and then remembered that we haven't implemented it yet!
R1C1 Format
Whatever the heck R1C1 format is, we need to support it! Actually I do know what it is, I just don't particularly want it myself - but I guess we need it really.
Report Crash Dialog
We haven't currently got a public bug tracker, which we will setup. We also need a default crash handler so that these can be reported automatically (giving the user the choice of course).
The Dreaded Bugs
At Resolver Systems we call the bugs 'defects' and keep track of them. The defects are prioritised along with new features and we always work on the highest priority items.
Defects
These are ordinary bugs in Resolver One - the Crashers are listed separately.
There are a host of minor little quirks (the code box doesn't format triple quoted strings properly for example) that I haven't mentioned here, this is only a list of the significant defects. If you discover any more by the way, please let the development team know.
Excel Files with Embedded Images
Resolver One currently can't import Excel files with embedded images.
Excel Files Created by Open Office
Resolver One currently fails to import some files saved in Excel format from Open Office.
Syntax Error in an Imported Module
If you attempt to import from a module containing a syntax error, then Resolver One doesn't understand the traceback and the error bar will show the exception as having happened in your code. You should still see the correct traceback in the output view, but it can be confusing.
Long Strings in Error Cells
If you have an error set on a cell that contains a long string, then the error colour (the coloured cross) can 'leak' into neighbouring cells. There are a couple of other formatting related bugs that are similar to this.
Redraw Problems
If you have a very tall top rows, then scrolling sometimes has a repaint issue. What is worse (in my opinion) is that you can only scroll in whole cell increments. With wide columns or tall rows, this can be a nuisance.
Crashers
Unfortunately some of the bugs in Resolver One can cause it to crash, meaning that potentially you could lose data. Luckily most of them are pretty obscure, but it is worth knowing about them so that you can avoid them. Hopefully the existence of this list will act as a spur to the development team to make it shrink. (This list is already much shorter than it was when I first created this website.)
Infinite Recursion in User Code
Infinite recursion in user code will kill Resolver. Resolver hasn't set a default limit, which would solve the problem.
Messing with sys.setrecursionlimit
This one definitely comes in the "so don't do that then" category. sys.setrecursionlimit is shared between all Python engines in a process. So, if you do something like sys.setrecursionlimit(1) in your user code, then Resolver will die.
The long term solution is to keep each spreadsheet in its own appdomain, which is a lot of work but will have other benefits.
Using Windows Forms Controls in User Code
This isn't really a bug in Resolver One, just something you shouldn't do! All Windows Forms controls must be created on the same thread, so you can't create them from user code. At some point Resolver One may provide a way of doing this - but more likely it will support using an extended range of controls in the grid rather than creating them directly from user code.
Unfortunately the crashes it can cause propagate back inside Resolver One and it isn't obvious how that can be avoided. (Other than preventing the user from accessing Windows Forms, hmmm...)
Note that there are some controls that are safe to use. These are the modal dialogs like MessageBox, OpenFileDialog, SaveFileDialog and so on - or modal dialogs that you create yourself.
Exceptions in Threads Launched from User Code
If you launch a thread from user code, which isn't usually a good idea but might be useful, then errors on that thread aren't trapped and so Resolver One will crash.
| [1] | Worksheets populated from a database query. |
| [2] | Databases with a trigger or stored procedure can cause Resolver to automatically pull in fresh data when the database is updated. |
| [3] | Resolver itself is tested with a large framework built on top of unittest. |
Last edited Sat Mar 7 19:40:41 2009.