Cell ranges, offsets and headers

Before you begin

Do one of the following to open the example workbook:

• In Windows, select Start, Resolver One, Samples, Cell ranges, offsets and headers.
• Download the file from the Resolver Exchange.

The Transactions worksheet

The 'Transactions' worksheet contains a long list of individual transactions. Each transaction is a trade of shares (either a buy or a sell) for a particular customer. Each share is represented by a different symbol.

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:

1. Creates a CellRange that holds all of the transactions:

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

• The CellRange object requires two cells, top-left and bottom-right.
• The data in our transactions worksheet starts in row 2 as the first row has the titles in it.
• The bottom-right of the transactions data is also the bottom-right cell in the worksheet.
• 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.

2. Defines a header row 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 = sheet.Cells.A1.to.F1
   transactions.HeaderRow.BackColor = Color.LightCyan
   transactions.HeaderRow.Bold = True
   transactions.HeaderRow.BorderBottom = True
   

• A header row enables us to use handy titles (like 'Customer' and 'Price') instead of index numbers. This header row is outside the transactions cell range—a useful trick that allows you to work with the data in the cell range without having to worry about explicitly ignoring the header.

3. Improves the look by setting some borders on the 'transactions' cell range and its header.

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

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

4. Formats the 'Prices' column of 'transactions' to display numbers to no more than two decimal places:

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

• 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 worksheet displays a summary of the information from all the transactions, as extracted by code in the Post-formulae user code.

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'.

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)

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

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.