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
A Simple Example Spreadsheet
Introduction
Any new program can be daunting to get to grips with. With its mix of spreadsheet interface and user code, Resolver is no exception. This article goes through an example spreadsheet for managing your accounts and explains what it does.
The Data
Central to the spreadsheet are two tables of data, income and outgoings. Each table has three columns: the reason (text), the date of the transaction (a date!), and the amount (a number).
The first three rows have been formatted by selecting cells and using the 'borders dialog' and the bold button. The other lines in the table are set from user code. If you add an extra line (or remove one), you should find that the table extends itself automatically.
Formatting like borders are set on cell objects. We can use the Cells indexer on the worksheet to do this:
# Next let's put nice borders down all the entries automatically
for index in range(4, sheet.MaxRow+1):
# First the Income table
sheet.Cells['A', index].BorderLeft = True
sheet.Cells['B', index].BorderLeft = True
sheet.Cells['B', index].BorderRight = True
sheet.Cells['C', index].BorderRight = True
# Next the outgoings column
sheet.Cells['E', index].BorderLeft = True
sheet.Cells['F', index].BorderLeft = True
sheet.Cells['F', index].BorderRight = True
sheet.Cells['G', index].BorderRight = True
This sets the appropriate borders on every cell from row four, down to the MaxRow. (The range function returns numbers up to, but not including, the last one - so we need to add 1 to the MaxRow.)
From the data in these two tables, we want to be able to produce a summary of income and outgoings.
Creating a CellRange
The CellRange class is ideal for creating and formatting small tables of data. They act as 'view' into a rectangular area of a worksheet. It can be a lot easier to work with a small table than a whole worksheet, particularly if you are creating several of them.
In our spreadsheet we only have one cell range, but you can position it anywhere you want. The user code takes the locations from J1 and J2 to use as the top-left corner of the cell range. (The date in J3 will be used later to filter results - only entries on or after this date will be included.)
The cell range is created and formatted, with the following code:
# co-ordinates in cells J1 and J2
topLeft = sheet.J1, sheet.J2
bottomRight = sheet.J1 + 1, sheet.J2 + 6
results = CellRange(sheet.Cells[topLeft], sheet.Cells[bottomRight])
# We'll make the first column into a header column
# and apply some formatting
results.HeaderCol = results.Cols[1]
results.HeaderRow = results.Rows[1]
results.BackColor = Color.DodgerBlue
results.HeaderCol.Bold = True
results.HeaderRow.Bold = True
# A one dimensional range only needs a single index
results.HeaderCol[2] = 'Income'
results.HeaderCol[3] = 'Outgoings'
results.HeaderCol[4] = 'Balance'
results.HeaderCol[6] = '???? In'
results.HeaderCol[7] = '???? Out'
results.HeaderRow[2] = 'Total'
results.Cols[1].BorderLeft = True
results.Cols[2].BorderRight = True
results.Rows[1].BorderTop = True
results.Rows[results.MaxRow].BorderBottom = True
results.DecimalPlaces = 2
A cell range is created with two cells - the ones which form the top left and the bottom right. This is the line CellRange(sheet[topLeft], sheet[bottomRight]).
Having created the cell range, we give it a HeaderCol and a HeaderRow and do some formatting. We can use the values in the headers to index the cell range (you an also do this with a worksheet), making the code that follows more readable.
Cell ranges share many properties in common with worksheets, like MaxRow and the border properties.
Dates and Errors
When you enter a date into a cell (UK format only currently I'm afraid - day/month/year), Resolver turns it into a Date value. This means that you can do useful things like compare it to other dates. As we need to use the date in J3 for filtering entries from the two tables, we need to show an error message if J3 doesn't contain a date:
if not isinstance(filterDate, Date):
error = Exception("Not a valid date")
sheet.Cells.J3.Error = error
raise error
Setting the Error property on the cell means that the error will be shown in the grid.
The function to filter entries in the table using the date is:
value = sheet[valueColumn, row]
date = sheet[dateColumn, row]
if not value or date < filterDate:
return 0
return value
If the cell value is Empty, or the date is before (less than) our filter date, then it won't be included.
Results in the Cell Range
Having created our cell range, we need to put the results into it.
We need to add up every entry in the tables, calling GetValuesAfterFilterDate on them. Fortunately this is easy:
results['Total', 'Income'] = SUM(GetValuesAfterFilterDate(row, 'B', 'C') for row in range(4, sheet.MaxRow+1))
# Total outgoings
results['Total', 'Outgoings'] = SUM(GetValuesAfterFilterDate(row, 'F', 'G') for row in range(4, sheet.MaxRow+1))
# Balance
results['Total', 'Balance'] = results['Total', 'Income'].Value - results['Total', 'Outgoings'].Value
if results['Total', 'Balance'] < 0:
results.Cells['Total', 'Balance'].BackColor = Color.Red
# and so on...
The results are put into the cell range by indexing with the headers - so the total income is set with results['Total', 'Income'].
See how the balance code sets the BackColor of the number to red if it is negative!
Last edited Mon Dec 10 20:34:32 2007.