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
Easy Cell Ranges
Introduction
CellRanges are very powerful and easy to use. Colored Rows provides an easy way to color alternate rows of a cellrange. Here are two more functions for working with cellranges.
The Code
endCell = startCell.Offset(width-1, height-1)
return CellRange(startCell, endCell)
def FormatCellRangeBorders(cellRange):
cellRange.BorderRight = True
cellRange.BorderTop = True
cellRange.Cols[1].BorderLeft = True
cellRange.Rows[cellRange.MaxRow].BorderBottom = True
CellRangeFromDimensions
Cellranges are created with two cells: the top left corner and the bottom right corner. This is straightforward enough, but what if you have a chunk of data that you want to place into a cellrange? Manually calculating the bottom right corner is not exactly difficult, but can still be tedious. CellRangeFromDimensions makes it easier, by allowing you to specify the width (number of columns) and height (number of rows).
The example spreadsheet uses the same data as Colored Rows, but stored as a list of tuples:
("0001", "A37", "MSFT", 237, 48.1, "Buy"),
("0002", "G45", "GOOG", 1250, 123.5, "Sell"),
("0003", "D98", "BSY", 658, 79.5, "Sell"),
("0004", "A01", "ETI", 998, 86.12, "Buy"),
("0005", "C12", "HNS", 117, 0.56, "Sell"),
("0006", "X14", "GOOG", 4, 156.2, "Buy"),
("0007", "O39", "HNS", 1740, 0.8, "Buy"),
("0008", "A37", "BSY", 345, 82.45, "Buy"),
("0009", "C12", "MSFT", 2501, 43, "Sell"),
("0010", "O39", "MSFT", 1799, 42.99, "Sell"),
]
So, the number of columns is the number of records in each entry. The number of rows is the number of entries. We also need an extra row for a header. Creating a correctly sized cellrange is then as simple as:
cellRange = CellRangeFromDimensions(sheet.Cells.A1, len(data[0]), len(data) + 1)
sheet.A1 determines the top-left corner of the cellrange - its location.
This function is very short. It uses the cell method Offset which returns a new cell based on the location of the cell it is called on, plus the offset you specify. This method can be used in all sorts of clever ways; for example tiling cellranges dynamically
Hint
cellRange.TopRight gives you the top right hand cell in a cell range. If you offset one (or more) to the right of that, then you can create a cellrange that is immediately next to the last one.
FormatCellRangeBorders
As you can see from the screenshot, the cellrange has all the borders around the data. Try adding or removing lines to the 'data' variable, and the borders will grow or shrink correctly.
This is achieved with a single function call:
Populating the Cellrange
It's not strictly part of this example, but creating the header row and filling the cellrange with the data contained in the 'data' variable is very easy:
cellRange.HeaderRow.Bold = True
for index, header in enumerate(["Reference", "Customer", "Symbol", "Number", "Price", "Direction"]):
cellRange.HeaderRow[index + 1] = header
cellRange.HeaderCol = cellRange.Cols[1]
cellRange.HeaderCol.Bold = True
for index, row in enumerate(data):
reference, customer, symbol, number, price, direction = row
cellRange.Cols["Reference"][index + 2] = reference
cellRange.Cols["Customer"][index + 2] = customer
cellRange.Cols["Symbol"][index + 2] = symbol
cellRange.Cols["Number"][index + 2] = number
cellRange.Cols["Price"][index + 2] = price
cellRange.Cols["Direction"][index + 2] = direction
This code uses the enumerate function twice, which is particularly useful when working with data structures and spreadsheets. Because enumerate starts from zero, and row one is the header row, we put data into the row at index + 2.
Notice also how the cellrange 'Cols' attribute can be indexed by the names in the header row: cellRange.Cols["Reference"].
Last edited Mon Dec 10 21:28:14 2007.