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
Exotic and Custom Data Types
Introduction
With Resolver you aren't limited to numbers, dates and text - you can store anything you want in cells, including any of the rich set of built-in types that Python provides. You can also create custom types for representing data.
This article is based on a spreadsheet created by Jonathan Hartley, a Resolver developer.
It illustrates the ability of Resolver to store unusual objects in cells, and do calculations with them.
Tuples
The Resolver formula language is based on Python expressions (mixed with custom spreadsheet extensions). This means that you can use any of the rich set of built in types that Python provides.
The first sheet in the 'exotic datatypes' spreadsheets is an example of using tuples. Tuples are one of Python's 'container' types, they hold other values.
Tuples are defined by separating values with commas, and are usually surrounded by parentheses. You can set a cell's value to be a tuple with a formula that looks something like (don't forget the '=' to start the formula):
=("Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday")
You can then access the members of the tuple by indexing. If the formula above is in A1, then =A1[0] will return "Monday".
You can of course index a tuple with an index held in another cell. If A2 holds the number 3, then =A1[A2] will return "Thursday".
Complex Numbers
The second worksheet is an example of using complex numbers with Resolver.
The following code in the 'Pre-formulae user code', draws a circle using the complex numbers plotted in the grid:
from System.Drawing import Color
sheet = workbook["complex"]
steps = 64
centreX, centreY = 17, 13
sheet.Rows[centreY].BackColor = Color.LightGray
sheet.Cols[centreX].BackColor = Color.LightGray
radius = 11.0
for i in range(steps):
f = 4*i/steps
sheet[1, i+2] = f
c = (1j)**f
sheet[2, i+2] = c
sheet[3, i+2] = c.real
sheet[4, i+2] = c.imag
x = int(round(c.real * radius)) + centreX
y = int(round(c.imag * radius)) + centreY
sheet.Cells[x, y].BackColor = Color.Gold
It uses the constants pi and e from the Python math module. This also contains functions that you can import and use as spreadsheet functions.
Custom Datatypes
Sometimes the standard datatypes just don't have what you need. In the user code (or imported from external libraries) you can create custom datatypes and use them within your spreadsheets.
This includes controlling the underlying data that they hold, how they are displayed in the grid, and how they take part in numerical operations.
How objects are displayed is controlled by providing a __str__ or __repr__ methods. You control how custom types behave in numerical operations by overloading the numerical operators like __add__ and __radd__ for addition, __mul__ and __rmul__ for multiplication and so on.
For more details, see the customization page of the Python documentation.
The third worksheet of the exotic datatypes spreadsheet uses a custom Mag type that Jonathan has defined in the user code:
def __init__(self, magnitude, spread):
self.mag = magnitude
self.spread = spread
def __add__(self, other):
mag = self.mag + other.mag
spread = self.spread + other.spread
return Mag(mag, spread)
def __mul__(self, other):
mag = self.mag * other.mag
spread = (
self.spread * other.spread +
self.mag * other.spread +
self.spread * other.mag
)
return Mag(mag, spread)
def __str__(self):
return u"%s\u00b1%s" % (self.mag, self.spread)
The Mag type represents values with a magnitude and a spread. You can see from the next image how they are displayed in the grid, and the results of performing calculations with them:
Because the class is defined in user code (it could equally be imported), you can create new 'mags' in formulae. For example:
=Mag(10, 2) + Mag(5, 1)
Would be displayed as 15±3.
This implementation of Mag only knows how to add (or multiply) itself to other Mag types, which may be what is required. It is possible to define more complex types which can take part in numerical oeprations with other datatypes.
There is currently a minor limitation of working with user defined types; the built in SUM spreadsheet function doesn't recognise them as being able to be summed and will skip them. This will be addressed in a future version of Resolver.
Last edited Mon Dec 10 21:40:05 2007.