Resolver One Library Documentation

API for Cell

Contents

Classes

Class Cell

class Cell(SpreadsheetElement):
    def __init__(self, worksheet, col, row, expression=None):

The Cell object represents individual cells in a spreadsheet. It owns the cell's value and location, and provides an API for accessing information about the cell's 'traits', such as whether or not it is Bold, its BackColor and so on. Each cell can have a variety of different properties.

It is not intended that you yourself should create new cell instances (individual cell objects from the cell class). The workbook will create all your cells for you. Cell objects are returned by accessing cells' locations on a worksheet via its Cells property: for example workbook['Sheet1'].Cells.A1 returns you the cell from location A1 on worksheet 'Sheet1'.

Each cell object has a number of properties associated with the cell and methods that function within the cell class.

Properties

ActualTextColor

ActualTextColor = property(get)

A get only property that returns the actual color used to display the value for a cell. Normally the text is black [1], but if the cell's value is a negative number then this property will return the NegativeTextColor for the cell.

This must be a Color from System.Drawing [2].

[1]	Actually uses `SystemColors.WindowText` which may depend on the current 'theme' that Windows is using.

[2]	See http://msdn2.microsoft.com/en-us/library/system.drawing.color(vs.80).aspx

BackColor

BackColor = property(get, set)

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

Bold

Bold = property(get, set)

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

BorderBottom

BorderBottom = property(get, set)

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

BorderLeft

BorderLeft = property(get, set)

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

BorderRight

BorderRight = property(get, set)

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

BorderTop

BorderTop = property(get, set)

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

Col

Col = property(get)

A get only property returning the column of a cell on its worksheet. This value will be an integer.

ColHidden

ColHidden = property(get, 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(get, 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(get, set)

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

Comment

Comment = property(get, set)

A get and set property used to define a user comment for the cell.

If the mouse is held over the cell a tooltip will appear displaying this comment. If this property is set to a non-string object str will be used to convert it into a string. This property should be set to None if the cell has no comment.

Currency

Currency = property(get, 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(get, set)

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

Error

Error = property(get, set)

A get and set property used to indicate an error state on the cell. If the evaluation of an expression in a cell results in an error, then this property will be set to the exception instance (error object containing an error message - a string of text indicating error type) that was raised. The error will be visible in the grid as a colour-coded cross within the cell.

If there is no error on the cell, this property returns None and the query x = cell.Error will return 'None'. You can set this property to None (by typing cell.Error = None) to overwrite any error that has occurred.

FontFamily

FontFamily = property(get, set)

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

FormattedValue

FormattedValue = property(get)

A get only property that returns the text representation of the cell's value i.e. the value that is shown in the cell in the grid rather than the true value or what my be shown in the formula bar if the value has been formatted in the grid. This always returns a string, and if the value is a number it will obey the settings for the number of decimal places, whether to include commas, strip trailing zeros etc.

Formatter

Formatter = property(get, 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.

IsNumeric

IsNumeric = property(get)

A get only property returning True or False. IsNumeric returns true if the cell's value is numerical (an integer or floating point) or Empty. (When used in calculations, Empty behaves like a floating point number with a value of 0.0.)

Italic

Italic = property(get, set)

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

Location

Location = property(get)

A get only property returning the location of a cell. The location of a cell is defined as a tuple containing (worksheet, col, row).

NegativeTextColor

NegativeTextColor = property(get, set)

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

Row

Row = property(get)

A get only property returning the row of a cell of a cell on its worksheet. This value will be an integer.

RowHeight

RowHeight = property(get, 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(get, 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(get, set)

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

ShowSeparators

ShowSeparators = property(get, set)

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

Strikethrough

Strikethrough = property(get, set)

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

StripZeros

StripZeros = property(get, set)

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

Underline

Underline = property(get, set)

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

Value

Value = property(get, set)

A get and set property for accessing the value of a cell. The value of a cell can be any object (a number, a string of text, a function and so on). A cell that has had no value set, will have the special value Empty. You can test for this by checking if a cell value is Empty. The following code checks the value of A1 on 'Sheet1' and, if the value is found to be Empty, the value is set to 3.

cell = workbook['Sheet1'].Cells.A1
if cell.Value is Empty:
    cell.Value = 3

WebEditable

WebEditable = property(get, set)

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

Worksheet

Worksheet = property(get)

A get only property returning the worksheet that contains the cell. This value will be an instance of the Worksheet class.

Wrap

Wrap = property(get, set)

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

Methods

Offset

def Offset(self, colDelta, rowDelta):

Offset can be used to return a cell object to a location (i.e. placed in a new cell on a worksheet), offset from another cell by a defined number of columns and/or rows. The function takes two integers (which can be negative): one for the column offset and one for the row offset. The function returns the cell object relative to another cell in the given offset location. It is a useful function to use to tile ranges on a worksheet that have been copied from other worksheets in the workbook.

In the example below, the range A1 to D4 on Sheet2 is copied to Sheet1 with the top left corner of the range being placed at A1 on Sheet1. Then the range H8 to M14 on Sheet3 is copied to Sheet1 with the top left hand corner of this range being offset by two columns and no rows (2, 0) from the top right hand corner of the previously copied data from Sheet2. The advantage of doing this is that the shape of the data imported from Sheet2 can change and no matter how many extra columns or rows it has, there will always be the data from Sheet3 next to it, separated by a blank column.

Sheet2_data = CopyRange(CellRange(workbook["Sheet2"].Cells.A1, workbook["Sheet2"].Cells.D4), workbook["Sheet1"].Cells.A1)
Sheet3_data = CopyRange(CellRange(workbook["Sheet3"].Cells.H8, workbook["Sheet3"].Cells.M14), Sheet2_data.TopRight.Offset(2, 0))

If the location of the resulting cell has either a row or column reference of less than 1 (i.e. the offset cell is off the grid), then an IndexError will be raised.

Offset can also be useful for creating CellRanges without having to calculate the position of the bottom-right corner explicitly. The following example creates a cell range of size 10x10 cells, with the top-left at D3.

topLeft = workbook['Sheet1'].Cells.D3
bottomRight = topLeft.Offset(9, 9)
cellRange = CellRange(topLeft, bottomRight)

UseNaturalDecimalPlaces

def UseNaturalDecimalPlaces(self):

Called without arguments, and does not return anything.

This is used on spreadsheet elements to restore the element to using the default values for number of DecimalPlaces and whether to StripZeros. The default values for these traits are 15 and True respectively.

eq

def __eq__(self, other):

This method checks whether a cell is equal to another object and will answer True or False. A cell is only equal to an object if the other object is a cell with the same location and value. In user code, this should only ever be true if the cell is compared with itself!

hash

def __hash__(self):

This function raises a TypeError exception. Cells are 'mutable', so this prevents them being used as keys in dictionaries. This behaviour may change in future and should not be relied upon.

ne

def __ne__(self, other):

This method checks whether a cell is not equal to another object and will answer True or False. It is always the opposite of __eq__ and should always be true in user code unless a cell is compared with itself.

str

def __str__(self):

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

Cell(Sheet1!A1)