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:
Spreadsheet Functions
Introduction
Resolver One has many built-in functions, which are available to you to use within formulae and from user code. Some of these are 'standard' functions, used by several different spreadsheet programs. Not all of functions provided by other spreadsheets are available in Resolver One yet, which may cause you difficulties when you import spreadsheets.
However, easily adding new code to a spreadsheet (whether it be functions, classes or variables) and using it from formulae is where Resolver shines! In fact, adding new functions that you can use in your formulae is one of the easiest ways that you can get started with extending Resolver One through user code.
This page has a few additional functions implemented. If you find others that you need, let me know as they are generally easy to implement. Also let me know if you write any of your own, and I can put them up here for other people.
To use the functions here, either copy them to the Pre-constants User Code, or put them in a Python module and import them.
Case-Sensitivity
Spreadsheet functions tend to be case-insensitive. So if I write the formulae =sum(A1, A2), it is equivalent to =SUM(A1, A2).
Python is case-sensitive however. To get round this problem, Resolver One knows about its spreadsheet functions, and whenever you use them in a formula it converts them to uppercase in the generated formula code.
(So you can't use the built in Python sum function in Resolver One formulae, Resolver One will always translate it into a call to the Resolver One SUM function. You can get round this, should you really need to, by aliasing the Python sum function with another name. E.g. Put pythonSum = sum in the Pre-constants user code, and you can use pythonSum in your formulae.)
Resolver One won't know that new functions we add are spreadsheet functions, so it won't treat them as case insensitive. You will have to use the same casing in your formulae as you use for your function definitions. In general, the Resolver One spreadsheet functions have all uppercase names, so it is probably a good idea to stick to that convention.
Using Cell Objects and Cell Values
When you write the formula =A1 + A2, what you are saying is that you want the current cell to have its value be the value in cell A1 plus the value stored in A2.
This gets converted to generated formula code something like this:
Accessing cells on worksheets like this is the easiest way of using or setting the values contained in cells. Sometimes you want to access the cell object, rather than just fetching or setting the value. This might be to call the Offset method on the cell, or setting traits like the BackColor, Bold or borders. You can access cell objects using the Cells attribute of worksheets:
workbook['Sheet1'].Cells.A3.Error = Exception("Something went wrong!")
If you define your own function then calling that function with cell references inside formulae will automatically have the values contained in the cell passed in. The formula =function(A1, A2) will converted into the following generated code:
If you need function that handles cell ranges (or even whole worksheets) as well as cells values, they will have to be a bit cleverer.
If you do need any of the functions here to do more than just take values from cells, then let me know. It is slightly more complicated, but not very difficult.
POWER
The POWER function takes two numbers and returns the first number raised to the power of the second.
USAGE: =POWER(2, 10), =POWER(A1, B4)
Note
These first two examples have provided mathematical functions using System.Math from .NET. This has a wide range of function that you might want to use. You can also use the Python standard library math module, which has a similar (but not identical) range of mathematical functions.
PRODUCT
def PRODUCT(*args):
return reduce(mul, args)
The PRODUCT function takes any number of input arguments, and returns the value obtained by multiplying them together.
USAGE: =PRODUCT(1, 3, 0.5, 7), =PRODUCT(2.1, B2, C9)
Last edited Sun Mar 8 00:37:53 2009.