Exotic and Custom Data Types
With Resolver One 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 Systems developer.
It illustrates the ability of Resolver One to store unusual objects in cells, and do calculations with them.
The Resolver One 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 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".
The second worksheet is an example of using complex numbers with Resolver One.
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.
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)
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 operations with other datatypes.
This is only a basic example, for Resolver One to treat user defined types as numbers, which means right-aligning them in the grid like other numbers and being recognised by the SUM spreadsheet function, they must at least implement the __abs__ method. For them to behave fully like numbers you probably want to define all the numeric methods (and raise NotImplementedError for operations your type doesn't support).
Last edited Sat Mar 7 18:43:01 2009.