SolverStudio uses a combination of VBA and C# VSTO code.
SolverStudio saves the Python code for each worksheet as a Custom XML part. You can view these by either (1) renaming your workbook.xlsx as a .zip file, and opening it, or (2) using Microsoft’s Open XML Package Editor Power Tool for Visual Studio 2010. Later versions of SolverStudio have a “View embedded XML” menu to show exactly what we add to your spreadsheet.
Adding new Languages
SolverStudio makes it easy to add new languages; see the Developer Methods page for more details.
Developing for Excel
If you are interested in developing for Excel, you might like the following resources.
Excel XLL add-in libary for writing C++ add-ins using Visual Studio 2010 (incl Express): http://xll.codeplex.com/
Excel-DNA provides .net integration with Excel: http://exceldna.codeplex.com/
PyXLL, a “free for non-commercial and evaluation purposes” Excel extension, allows Python code to be used to add menus and user functions within Excel.
Pycel will compile a spreadsheet into Python. It was developed to allow non-linear optimisation of aircraft design.
PyWorkbooks is an open source Python interface for Excel and GNumeric; it looks very nice, with careful thought given to speed issues (e.g. implenting Generators).
Python for Excel (PFE) is a commercial (?) Python scripting system for Excel that “provides a complete set of tools for building of fully featured applications with Excel front end. It is in-process control of Excel with an out-of-process developement and debugging environment.”
Python, Excel and Optimisation
We use the excellent Scintilla editor, accessed via Scintilla.Net. This requires the full .Net 4.0 framework (not the smaller client framework typically installed on user machines.)
OpenOpt has links to a range of Python optimization tools.
OpenOpt is used in Pycel (available on GitHub) to compile and then optimize spreadsheet models using Python.
VSTO and .Net Challenges
VSTO has its own quirks that I try to file here so I can remember the reasons behind some of SolverStudio’s design decisions.
SolverStudio now has a Cancel button that cancels any Python code that is running, and also cancels any of the processes (such as the CBC solver, cbc.exe) that the Python might have created. To make this work, we run the Python in a separate thread which can be killed using Thread.Abort. Making this work is tricky because the main C# thread must be active to perform the screen updates that are requested (using Invoke) on the worker thread. I tried sychronising things in the standard way with ManualResetEvents and a WaitOne, but this failed because the main thread did NOT process the events requested by the worker thread, which then hung on its Invoke calls. Running a loop on the main thread that included a DoEvents allowed the worker thread’s requests to be actioned, albeit slowly, but this then allowed events thru to Excel which was not what I wanted.
After much experimentation, I ended up with the following arrangement. The main thread shows a modal dialog which has Excel’s main window as its owner. (This is initially 100% transparent, but slowly becomes visible.) This has its own event handling which (1) blocks events from Excel while (2) ensuring the add-in’s main thread is active to handle all GUI updates. The worker thread no longer hangs on the Invoke calls it makes to do GUI updates. The form’s Load event starts up the worker thread that runs the user’s Python code, and then Closes the form (using this.Invoke). Closing the form allows the main thread to continue in the normal way.
The form contains a Cancel button to cancel the run. This works as follows. First, it aborts the worker thread. This is really a “please abort” request because if the thread is in native-code (such as occurs in Python when it waits – using WaitForSingleObject – for an executable such as CBC.exe to finish), then nothing happens! So, after aborting the worker thread, we then kill all processes that have Excel as their owner. This allows the WaitForSingleObject to return, and the thread to then abort. We needed to shut down these processes anyway; the trick is making sure we do it outside the worker thread.
The final piece of the magic is to make the dialog transparent when it is first shown. This means it is invisible but active, so Excel is blocked (because the dialog is modal). We have the ability to use the escape key to cancel the run; this works because the dialog has a designated Cancel button. Five seconds after the dialog loads, we make the dialog opaque so the user can see it and use it in the normal way. The result of this whole process is that we stop the dialog flashing up and then disappearing during short runs, but give the user a cancel button for long runs.
The original design ran my Excel data transfer code (large “range.Value2” read operations) on the background thread, meaning they could be cancelled in the same was as we cancelled the optimisation process. Unfortunately, this made the data transfer 8 times slower than running on the main C# thread. This is something to do with the COM mechanisms, the details of which I don’t want to know about! We now transfer our data on the main thread, and do not allow the user to cancel during this.
I originally started by trying to let the user cancel long runs using Escape, but this failed. Firstly, the VBA escape handling (via On Error) does not seem to have a parallel in VSTO. Secondly, we could theoretically add a filter to the application’s message handler, but as it does not seem possible to do this in VSTO. It may be possible using a COM message filter, but I have not tried this. Using a KeyPreview probably won’t work as the task pane not be visible.
Working with Named Ranges
In the Data Items editor, we allow named ranges to be created (and given associated indices). Thus, the user can tye in A1, and we create a defined name that refers to this cell. We also want to let user stype in formulae, such as =Offset(p5,1,1,1). To make this work, we take the users entered text and first convert it to absolute references. This is important, as a defined name of A1 is a relative reference, where Excel evaluates it as being relative to the current cell on the sheet. (You can see this suing Excel’s Name Manager, which changes the reference shown in this dialog as the active cell changes.) Thus, we do something like
userReference= “offset(g1,1,1,1)” ‘ This can start with an = or not; the ConvertFormula still works
absoluteReference = Application.ConvertFormula(userReference,xlA1,xlA1,True,range(“a1″))
This gives absoluteReference =”OFFSET($G$1,1,1,1)” (or an error if the formula is invalid). We store this as a named range, we do something like
if left(userReference,1,1)<>”=” then userReference=”=” & userReference
Names.Add “Test”, userReference
This gives Names(“test”).RefersTo = “=OFFSET(‘Sheet 1’!$G$1,1,1,1)”, where Excel has inserted the sheet names where required (with single quotes added if needed, and with any internal single quotes doubled). To get the actual cells, we use Names(“test”).RefersToRange or Range(“‘test”).
When we display named ranges back to the user, we strip out any sheet names and any $ to make the formula more compact.
6 thoughts on “Development”