Cell ranges, offsets and headers

The Transactions Worksheet

The 'Transactions' worksheet is a table of data: the transactions themselves. We can use cell ranges to format this data (to improve its appearance) and make it easier to work with the data later.

The usercode that does this is in the Pre-formulae user code.

A CellRange object is created from two cells, the cell at the top-left corner of the cell range and the one at the bottom-right. The data in our transactions worksheet starts in row 2 (the first row has the titles in it). The bottom-right of the transactions data is also the bottom-right cell in the worksheet. We can create a CellRange that holds all of the transactions with the following code:

# Format the data in the 'Transactions' Worksheet
sheet = workbook['Transactions']
transactions = CellRange(sheet.Cells.A2, sheet.BottomRight)
transactions.BackColor = Color.LightCyan

You can see from this code that the background color (BackColor) on a cell range is set in exactly the same way as on a cell, worksheet, row or column. You can use any .NET Color. Here's a useful Color Chart for all the named colors.

Headers provide a very convenient way of accessing the data contained in a cell range. They will allow us to use the titles (like 'Customer' and 'Price') instead of index numbers. We do this by setting the HeaderRow property on our Cellrange:

# Create and format a Header Row, which is outside the transactions range
# But we can use it to index
transactions.HeaderRow = CellRange(sheet.Cells.A1, sheet.Cells.F1)
transactions.HeaderRow.BackColor = Color.LightCyan
transactions.HeaderRow.Bold = True
transactions.HeaderRow.BorderBottom = True

Header rows and columns are also CellRanges. This means you can apply formatting directly to the HeaderRow (or HeaderCol).

To make it prettier, we can set some borders on the 'transactions' cell range and its header. We also want the 'Prices' column of 'transactions' to only display numbers to two decimal places:

# Add some right borders
for i in range(1, transactions.MaxCol):
    transactions.Cols[i].BorderRight = True
    transactions.HeaderRow[i].BorderRight = True

# We only want to display two decimal places in the price column
transactions.Cells.Cols['Price'].DecimalPlaces = 2

See how we can access individual columns in the transactions through the Cols attribute. Cols can be indexed by position (number); if you have set a header, then they can also be indexed by the title in the header row.

The Summary Worksheet

The summary spreadsheet displays a summary of the information from all the transactions. Code in the Post-formulae user code extracts this information.

It does this by extracting the relevant information from each row in the 'transaction' CellRange, and calling the AddTransactionToData defined in the Pre-constants user code:

for transaction in transactions.Rows:
    customer = transaction['Customer']
    symbol = transaction['Symbol']
    price = transaction['Price']
    quantity = transaction['Quantity']

...

# Add each transaction to our symbol_data and customer_data
# Which keep track of totals by customer and by symbol
AddTransactionToData(symbol_data, symbol, price, quantity, direction)
AddTransactionToData(customer_data, customer, price, quantity, direction)

Having collected the information, it is presented in three cell ranges: 'totals', 'customers' and 'symbols'.

All of these cell ranges are created in the same way. Let's look at 'totals'.

Creating CellRanges Using Offset

This cell range presents a summary of the number of transactions, number of shares traded, total value of the transactions and so on.

The location (top-left corner) of the cell range will be calculated from the totals_location value. This is set in user code: totals_location = sheet.Cells.A3

All the other cell ranges are created relative to this location. Try changing the 'totals_location' variable, and see what happens to the 'Summary' worksheet.

CellRanges are created from two Cells: those at the top-left and the bottom-right. The 'totals' CellRange is two cells wide and five cells high, so we can get the bottom-right cell by calling the Offset method on the top-left Cell:

width = 2
height = 5

# We get the bottom right of the CellRange
# by using the offset method of the totals_location cell
totals_bottom_right = totals_location.Offset(width - 1, height - 1)
totals = CellRange(totals_location, totals_bottom_right)

The 'customers' CellRange is positioned below the 'totals' Cellrange. The top-left of 'customers' is two rows below the bottom-left corner of 'totals'; 'customers' should be six columns wide, and should contain one row for every line of data in 'customer_data' (plus a row for the header titles). Again we can create this CellRange using Offset:

width = 6
height = len(customer_data) + 1
customers_location = totals.BottomLeft.Offset(0, 2)
customers_bottom_right = customers_location.Offset(width - 1, height - 1)

customers = CellRange(customers_location, customers_bottom_right)
customers.BackColor = Color.CornflowerBlue

The 'symbols' CellRange is created in exactly the same way, but this time using Offset to put it to the right of 'customers'.

Summary

Cell ranges are a versatile way of handling tables of data.

• CellRange objects are created with two cells: the ones at the top-left and the bottom-right.
• You can format cell ranges by setting 'traits' (like BackColor and Borders) in the same way as on cells, worksheets, rows and columns.
• You can set a HeaderRow and HeaderCol on a CellRange. This should be a one dimensional CellRange, the same width (for a header row) or height (for a header column) as the parent cell range.
• You can index the CellRange by the names in the headers, as well as by coordinate.
• You can set CellRanges' relative locations by using the Offset method on Cell.

Back to Sample Spreadsheets.