Introduction

Resolver spreadsheets give full access to the spreadsheet via user code. You can create new worksheets, fill them with data from any source, and then slice-and-dice that data and change how it is presented.

Central to this is the workbook, through it you have access to worksheets, rows, columns and cells and can set values or attributes on them.

The data you have entered into the grid in Resolver, is transformed into 'generated code' and then executed along with user code to produce the results. Effectively, a Resolver spreadsheet is an IronPython program [1]. So... anything you do in IronPython you can do in Resolver, and that means that you have both the .NET Framework and the Python Standard Library available to you.

Silly Colors

To get you started, this little piece of code sets the back ground color of a block of cells to a random color using Python and .NET library code.

The code produces output that looks something like this:

You should see the same, if you copy and paste the following code into any of the user code sections:

If you prefer, you can download this as a spreadsheet, Silly_colors.rsl.

Using .NET Classes

We create the color using the Color.FromArgb method, which turns numbers for RGB values into a .NET Color.

We don't need to explicitly import Color, because it is already imported in the 'Import code' section.

For examples of some of the things you can do with the .NET framework, see the IronPython Cookbook.

Using Python Functions

The random numbers to create the colors are generated in the line: Color.FromArgb(random()*256, random()*256, random()*256). random is a function from the Python standard library random module.

The locations to set a color on, are generated in a loop using the built-in Python function range:

You could also import random, and use it in cell formulae. Try adding from random import random, and then set a cell formula to =random(). You should see the value change every time you recalculate. (You can trigger a recalculate by pressing F9.)

The Resolver Spreadsheet Objects

Inside the body of the loop, we change the cells at location (x, y). We use the Resolver spreadsheet objects to do this.

• sheet1 = worbook['Sheet1']

  This gives us access to the worksheet called 'Sheet1'.

• cell = worksheet.Cells[(x, y)]

  This gives us access to the cell at location (x, y).

• cell.BackColor = RandomColor()

  This sets the background color of the cell to whatever is returned by the RandomColor function.

• cell.Value = str((x, y))

  This sets the value of the cell (what is displayed) to a string containing the x and y co-ordinates of the cell. It is the equivalent of sheet[x, y] = str((x, y)).

• cell.Bold = True

  With all the colored funkyness, the text can be hard to read - so we make it Bold.

Obviously there is a lot more to the spreadsheet API than what we have touched on here, but this should give you a taste of it. You can read the API documentation to see what else is available. If you purchase Resolver (instead of using the free version), then you will get a source distribution and be able to look at the source for the Library objects - these are a very good reference for writing code that interacts with spreadsheet objects!