Resolver One Library Documentation

API for CellRange

Contents

Classes

Class CellRange

class CellRange(object):
    def __init__(self, start, end, createDefaultHeaders=False):

CellRanges represent a 'view' into an area of a worksheet. They do not directly contain cell objects, but provide a convenient way of accessing a range of cells within a worksheet, and formatting them.

You can create named cell ranges from the drop-down menu (Data -> Edit Names...) in the user interface, or you can create them in your user code.

A cell range is created with two cells, which must both live on the same worksheet [1].

sheet1 = workbook['Sheet1']

myRange = CellRange(sheet1.Cells.A1, sheet1.Cells.C3)

[1]	Passing in cells from different worksheets will raise a `ValueError`.

Setting Traits

You can set traits on a cell range, just like you can for cells, cols, rows and worksheets. On a cell range, this has exactly the same effect as setting the trait for every individual cell in the cell range. As a cell range is only a view into the collection of cells it covers, you can't 'get' traits from them.

Accessing Cells

You can access the cells in a CellRange using its Cells attribute:

myRange.Cells[1, 1].Value = 53
myRange.Cells[1, 1].Bold = True

You can also access the cells' values using an abbreviated form of the above syntax:

myRange[1, 1] = 5

The Cells attribute of a CellRange is iterable. The following code iterates over the cells in a CellRange, printing out the boldness of each:

for cell in myRange.Cells:
    print cell.Location, cell.Bold

You can also iterate over the values of the cells in the CellRange using an abbreviated form of the above syntax; the following will just print out every value in a CellRange:

for value in myRange:
    print value

In addition to this, single dimension ranges can be indexed with just a single value. The following code creates a range one cell high and six cells wide, and then sets the value on each cell from one to six and makes the cell display in bold:

sheet1 = workbook['Sheet1']

myRange = CellRange(sheet1.Cells.A1, sheet1.Cells.A6)

for i in range(1, 7):
    myRange[i] = i  # or, equivalently, myRange.Cells[i].Value = i
    myRange.Cells[i].Bold = True

Attempting to access a two-dimensional range with a single index will raise a TypeError.

Passing in an invalid index will raise an IndexError. This includes indices that are outside the dimensions of the range.

Accessing Columns and Rows

You can index the Cols and Rows attributes of cell ranges. These return one dimensional cell ranges that cover the specified 'slice' of the cell range. The index must be a positive integer that is contained within the cell range.

This makes formatting cell ranges very easy. The following code creates a cell range, then sets the top row to Bold with a light grey background and adds up all the values in the second row, putting the result at the start of the third:

sheet_1 = workbook['Sheet1']

myRange = CellRange(sheet_1.Cells.A1, sheet_1.Cells.D6)

myRange.Rows[1].Bold = True
myRange.Rows[1].BackColor = Color.LightGray

total = 0
for cell in myRange.Rows[2].Cells:
    total += cell.Value

myRange[1, 3] = total

Invalid indexes for 'Cols' and 'Rows' will raise an IndexError.

The 'Width' and 'Height' properties provide read-only access to the dimensions of the cellrange.

The 'Rows' and 'Cols' attribute of CellRanges are iterable. This means that you can iterate over the rows and columns in a cell range. The following code iterates over the columns in a cell range, building up a list of the sum of each column. This list can then be printed in the output pane (if followed by print columnSums) or used elsewhere in the program:

columnSums = []
for col in myRange.Cols:
    columnSums.append(SUM(col))

Headers

Just like Worksheets, a CellRange can have header rows and columns. If you want the header row to be the top row and the header column to be the left column, you can create the cell range with the optional createDefaultHeaders parameter set to True.

sheet1 = workbook['Sheet1']

myRange = CellRange(sheet1.Cells.A1, sheet1.Cells.C3, createDefaultHeaders=True)

Alternatively, you can set the HeaderRow and HeaderCol properties manually. Once these are set, they can be used to access cells, rows or columns in a CellRange:

totalSales = SUM(myRange.Rows["Sales"])
januaryAmount = SUM(myRange.Cols["January"])
januarySales = myRange.Cells["January", "Sales"].Value
# ...or...
januarySales = myRange["January", "Sales"]

Numerical Methods

CellRanges have all the Python numerical methods available. This is so that 1x1 ranges (ranges which cover a single cell) can be used directly in calculations.

These methods delegate to the Value of the underlying cell. This means that in calculations, the cell range will behave like the value of the cell it contains. (If the cell has a value of 3, then the range will behave like the number 3.)

These methods use the Value property of the cell range, so attempting to use them with multi-cell ranges will raise a ValueError.

Properties

BackColor

BackColor = property(set)

The background colour to display in the grid. This must be a Color from System.Drawing.

Bold

Bold = property(set)

This specifies whether the cell's content is bold. The default is False.

BorderBottom

BorderBottom = property(set)

This specifies whether the cell has a border along its bottom edge. It must be either True or False.

BorderLeft

BorderLeft = property(set)

This specifies whether the cell has a border along its left edge. It must be either True or False.

BorderRight

BorderRight = property(set)

This specifies whether the cell has a border along its right edge. It must be either True or False.

BorderTop

BorderTop = property(set)

This specifies whether the cell has a border along its top edge. It must be either True or False.

Bottom

Bottom = property(get)

A get only property that returns the integer index of the bottom edge of the cell range. This is the highest indexed row that is part of the cell range.

BottomLeft

BottomLeft = property(get)

A get only property, returning the cell at the bottom left of the range.

BottomRight

BottomRight = property(get)

A get only property, returning the cell at the bottom right of the range.

ColHidden

ColHidden = property(set)

This specifies whether the column is hidden. Has no effect when set on a row or a cell. Setting it on a worksheet is not advised.

ColWidth

ColWidth = property(set)

This specifies the width of a column, in pixels. It must be a non- negative integer. Has no effect when set on a row or a cell.

Color

Color = property(set)

The colour to display text in the cells of the grid. This must be a: Color from System.Drawing.

ContentCols

ContentCols = property(get)

A get only property that returns an iterator over the cols in the cell range without the header col.

ContentRows

ContentRows = property(get)

A get only property that returns an iterator over the rows in the cell range without the header row.

Currency

Currency = property(set)

This specifies the currency formatting of a cell (if any). It must be a value from the Currency from Library.Currency.

DecimalPlaces

DecimalPlaces = property(set)

This specifies how many decimal places should be displayed for numbers. Any integer from 0 to 15 can be used.

FontFamily

FontFamily = property(set)

The FontFamily used to display values. This is a string representing the font to use, for example "Tahoma".

Formatter

Formatter = property(set)

A callable (e.g., function) to provide a custom text representation for values in the grid. It is used every time a value for a cell needs to be displayed in the grid.

When your custom formatter is called, it will be passed two arguments. These are, the value in the cell (which can be an object of any type) and the cellFormatter. The cellFormatter is the formatter that would have been used if you hadn't provided a custom formatter. Your custom formatter can use the cellFormatter to generate a String from the value, and then modify it. Alternatively your function can ignore the cellFormatter if you don't need it. The cellFormatter takes into account other traits that affect the String representation, like Currency, StripZeros and DecimalPlaces. The cellFormatter takes one argument and returns a String.

To display numbers in the column C as kilometers:

def customFormatter(v, cellFormatter):
    return cellFormatter(v) + ' km'
workbook['Sheet1'].Cols['C'].Formatter = customFormatter

or the same in just one line of code, using lambda:

workbook['Sheet1'].Cols['C'].Formatter = lambda v, cf: cf(v) + ' km'

Your custom formatter should return a String.

HeaderCol

HeaderCol = property(get, set)

This is a get and set property to access the header column on the cell range and set or reset it as desired.

If no HeaderCol is set then None is returned and you can remove the current HeaderCol by setting it to None.

The HeaderCol must be a one dimensional vertical cell range (column),whose top and bottom position and properties match the current cell range. It need not be inside the cell range. This way a header column can be to the left or to the right of the cell range, so long as it is parallel to it.

The easiest way to set the HeaderCol is by indexing the 'Cols' attribute of the cell range. The following code sets the HeaderCol to be the first column in a cell range:

myRange.HeaderCol = myRange.Cols[1]

You can then access the HeaderCol directly. The following code sets the HeaderCol to display cells in Bold:

myRange.HeaderCol.Bold = True

Like worksheets, you can then index the range using the text value of cells in the HeaderCol instead of integer indexes:

cell = myRange.Cells["prices", 3]

Attempting to set 'HeaderCol' to a cell range that has the incorrect dimensions or location will raise a ValueError. Setting the HeaderCol to any other invalid kind of object will cause a number of possible errors.

HeaderRow

HeaderRow = property(get, set)

This is a get and set property to access the header row on the cell range and set or reset it as desired.

If no HeaderRow is set then None is returned and you can remove the current HeaderRow by setting it to None.

The HeaderRow returned will be a one dimensional horizontal cell range. When you set the HeaderRow, it must be a one dimensional horizontal cell range, whose left and right position and properties match the current cell range, but it need not be inside the cell range. This way a header row can be to the top or to the bottom of the cell range, so long as it is parallel to it.

The easiest way to set the HeaderRow is by indexing the 'Rows' attribute of the cell range. The following code sets the HeaderRow to be the first row in a cell range:

myRange.HeaderRow = myRange.Rows[1]

You can then access the HeaderRow directly. The following code sets the HeaderRow to display cells with a lavender BackColor:

myRange.HeaderRow.BackColor = Color.Lavender

Like worksheets, you can then index the range using the text value of cells in the HeaderRow instead of integer indexes:

cell = myRange.Cells[3, "google"]

Attempting to set 'HeaderRow' to a cell range that has the incorrect dimensions or location will raise a ValueError. Setting the HeaderRow to any other invalid kind of object will cause a number of possible errors.

Italic

Italic = property(set)

This specifies whether the cell's content is italic. The default is False.

Left

Left = property(get)

A get only property that returns the integer index of the left-hand edge of the cell range. This is the lowest indexed column that is part of the cell range.

MaxCol

MaxCol = property(get)

A get only property returning the maximum valid column index. This is effectively the width of the cell range.

MaxRow

MaxRow = property(get)

A get only property returning the maximum valid row index. This is effectively the height of the cell range.

MinCol

MinCol = property(get)

A get only returning the minimum valid column index, which will always be 1.

MinRow

MinRow = property(get)

A get only returning the minimum valid row index, which will always be 1.

NegativeTextColor

NegativeTextColor = property(set)

This is the colour used to display negative numbers. This must be a Color from System.Drawing.

Right

Right = property(get)

A get only property that returns the integer index of the right-hand edge of the cell range. This is the highest indexed column that is part of the cell range.

RowHeight

RowHeight = property(set)

This specifies the height of a row, in pixels. It must be a non- negative integer. Has no effect when set on a column or a cell.

RowHidden

RowHidden = property(set)

This specifies whether the row is hidden. Has no effect when set on a column or a cell. Setting it on a worksheet is not advised.

ShowNegativeSymbol

ShowNegativeSymbol = property(set)

This specifies whether or not to display the negative symbol for negative numbers. It is set to True or False.

ShowSeparators

ShowSeparators = property(set)

This specifies whether or not to show the thousand separator commas in numbers. It is set to True or False.

Strikethrough

Strikethrough = property(set)

This specifies whether the cell's content is struck through. The default is False.

StripZeros

StripZeros = property(set)

This specifies whether or not to strip trailing zeros (after the decimal point) for numbers. It is set to True or False.

Top

Top = property(get)

A get only property that returns the integer index of the top edge of the cell range. This is the lowest indexed row that is part of the cell range.

TopLeft

TopLeft = property(get)

A get only property, returning the cell at the top left of the range.

TopRight

TopRight = property(get)

A get only property, returning the cell at the top right of the range.

Underline

Underline = property(set)

This specifies whether the cell's content is underlined. The default is False.

Value

Value = property(get)

A get only property. For zero dimensional cell ranges (cell ranges that cover one cell only), this will return the Value property of the underlying cell. (So that you can use them in places where you would normally use a cell in formulae.)

For multi-cell ranges, attempting to access the Value property will raise a ValueError.

If the the range has only one cell and it is not populated then this property returns the Empty instance, which behaves like the value 0.0 when used in calculations.

WebEditable

WebEditable = property(set)

This specifies whether or not a cell will be editable when viewed from the Resolver Server.

Wrap

Wrap = property(set)

This specifies whether or not long text should wrap within a cell. It is set to True or False.

Methods

repr

def __repr__(self):

Returns a formatted string representation of a cell range. This will always be in the form:

Sheet1!A1:D9