Resolver One Library Documentation

API for Worksheet

Contents

Classes

Class Worksheet

class Worksheet(SpreadsheetElement):
    def __init__(self, name):

The 'Worksheet' class represents worksheets that are part of a workbook. A spreadsheet consists of a workbook, and (possibly) multiple worksheets. Worksheets contain cells, columns and rows.

It is not intended that you create worksheet instances directly, but instead call appropriate methods on the workbook to create them. These methods return the newly created worksheet, or you can use 'dictionary-like' syntax to access any of the worksheets contained in a workbook.

The following code segment creates a new worksheet (called 'New Worksheet').:

newSheet = workbook.AddWorksheet('New Worksheet')

Accessing Cells

Worksheets provide three ways to access all the cells that they contain. These use the Cells property:

sheet1 = workbook['Sheet1']
a3 = sheet1.Cells.A3
a3 = sheet1.Cells["A3"]
a3 = sheet1.Cells[1, 3]   # alternatively, sheet1.Cells["A", 3]

These all return Cell objects, on which you can set properties ranging from the Value to the BackColor.

If you specifically want to set or get the Value of a cell, there is an alternative shortcut:

sheet1 = workbook['Sheet1']
a3Value = sheet1.A3
a3Value = sheet1["A3"]
a3Value = sheet1[1, 3]   # alternatively, sheet1["A", 3]

If the header col or header row is set, then the header titles may be used for indexing. (The titles are case insensitive.) If there is a clash between a column header name and a real column, then the index will be assumed to refer to the header column (for example 'HELLO' is both a possible header name and a real column on the far right of the spreadsheet).

Cell indexes can be integers greater than zero, column indentifier strings or valid header titles. Any other object will raise a TypeError. A non-existent header title, will raise an IndexError.

You can also iterate over all the cells in a worksheet. The following example calculates the sum of a worksheet by adding up the values of all the cells it contains (note that the 'sum' property would be a better way of doing this).:

total = 0
for cell in workbook['Sheet1'].Cells:
    total += cell.Value

This iterates over every location in the worksheet, from the TopLeft to the BottomRight. The order of iteration is from left to right and top to bottom. This code will fail if the worksheet contains any strings of text.

To iterate over only populated cells use the CurrentCells property.

total = 0
for cell in workbook['Sheet1'].CurrentCells:
    total += cell.Value

There is also a shortcut for iterating over the values of the worksheet's cells:

total = 0
for value in workbook['Sheet1']:
    total += value

Accessing Columns and Rows

The worksheet provides access to all its columns and rows through the 'Cols' and 'Rows' attributes. These are indexable by number (integers greater than zero), column identifier strings (letters) or header titles. These return 'Col' and 'Row' objects. The following code segments fetch column C (column 3) and row 3 and set their bold property to True:

sheet_1 = workbook['Sheet1']

col_3 = sheet_1.Cols['C']
col_3.Bold = True

row_3 = sheet_1.Rows[3]
row_3.Bold = True

If the index is invalid then an IndexError will be raised.

You can also iterate over the 'Rows' and 'Cols' attributes on worksheets. This returns (ordered from lowest index to highest), all the rows or columns that are within the Bounds of the worksheet. When iterating over 'Cols', this will return every column between MinCol and MaxCol. When iterating over 'Rows', this will return every row between MinRow and MaxRow. To iterate over all the rows except for the header row use the ContentRows property.

Properties

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.

BottomLeft

BottomLeft = property(get)

A get only property. This fetches the cell at location (MinCol, MaxRow). (Or None if no cells are populated.)

BottomRight

BottomRight = property(get)

A get only property. This fetches the cell at location (MaxCol, MaxRow). (Or None if no cells are populated.)

Bounds

Bounds = property(get, set)

Bounds should only be used as a get-only property. A setter is exposed so that the generated code can set Bounds to the appropriate value when the Worksheet is being created. If there are any cells set in the worksheet, Bounds returns a tuple of (MinCol, MinRow, MaxCol, MaxRow). If no cells are populated, this property returns None.

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.

ContentCols

ContentCols = property(get)

A get only property that returns an iterator over the columns in the worksheet without the header column.

ContentRows

ContentRows = property(get)

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

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.

CurrentCells

CurrentCells = property(get)

A get only property that returns the populated cells in the worksheet.

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. This is used to indicate if a worksheet level error has occurred. It defaults to None which indicates no error. If there has been an exception raised in a worksheet level formula, then the Error property will be set to the exception instance.

If 'Error' is set to an exception instance, then the top left corner of the grid (cell 0, 0) will be displayed in the appropriate error colour. Moving the mouse over this will show a tooltip with the text of the exception message.

FontFamily

FontFamily = property(get, set)

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

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.

HeaderCol

HeaderCol = property(get, set)

A get and set property. This is used to set a header column on a worksheet. It takes (or returns) a column (Col) object.

sheet = workbook['Sheet1']

sheet.HeaderCol = sheet.Cols['A']

value = sheet["Prices", 1]

Header names are not case-sensitive. Having set a header column, you can then use the text values of cells in the header column as an index on 'Cols' and the worksheet.

If the column specified has any duplicate names, then the Error attribute of those cells will be set to DuplicateHeaderWarning (which will be visible in the grid).

HeaderHeight

HeaderHeight = property(get, set)

A get and set property. This is the height of the row that displays column identifiers. The default is -1, and should be set to an integer of -1 or more.

HeaderRow

HeaderRow = property(get, set)

A get and set property. This is used to set a header row on a worksheet. It takes (or returns) a row (Row) object.

sheet = workbook['Sheet1']

sheet.HeaderRow = sheet.Rows[1]

value = sheet[2, "Google"]

Header names are not case-sensitive. Having set a header row, you can then use the text values of cells in a header row as an index on 'Rows' or the worksheet.

If the row specified has any duplicate names, then the Error attribute of those cells will be set to DuplicateHeaderWarning (which will be visible in the grid).

HeaderSize

HeaderSize = property(get, set)

A get and set property. This takes (and returns) a tuple of (HeaderWidth,HeaderHeight). These refer to the size of the row and column which display indices in the grid.

HeaderWidth

HeaderWidth = property(get, set)

A get and set property. This is the width of the column that displays row numbers. The default is -1, and should be set to an integer of -1 or more.

Italic

Italic = property(get, set)

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

MaxCol

MaxCol = property(get)

A get only property returning the number (integer) of the highest column in the worksheet that has a populated cell. (Or None if no cells are populated.)

MaxRow

MaxRow = property(get)

A get only property returning the number (integer) of the highest row in the worksheet that has a populated cell. (Or None if no cells are populated.)

MinCol

MinCol = property(get)

A get only property returning the number (integer) of the lowest column in the worksheet that has a populated cell. (Or None if no cells are populated.)

MinRow

MinRow = property(get)

A get only property returning the number (integer) of the lowest row in the worksheet that has a populated cell. (Or None if no cells are populated.)

Name

Name = property(get)

A get only property that returns the worksheet name as a string of text.

NegativeTextColor

NegativeTextColor = property(get, set)

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

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.

ShowBounds

ShowBounds = property(get, set)

A get and set property (True or False). Determines whether the bounds should be visible in the grid. If True then the populated area of the grid (the rectangle from TopLeft to BottomRight) will be shown in white, the rest of the grid will be light grey. The default is False.

ShowGrid

ShowGrid = property(get, set)

A get and set property (True or False). Determines whether the grid lines should be visible in the grid. If True then the grid lines will be visible. The default is True.

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.

TopLeft

TopLeft = property(get)

A get only property. This fetches the cell at location (MinCol, MinRow). (Or None if no cells are populated.)

TopRight

TopRight = property(get)

A get only property. This fetches the cell at location (MaxCol, MinRow). (Or None if no cells are populated.)

Underline

Underline = property(get, set)

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

WebEditable

WebEditable = property(get, set)

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

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

FillAreaWithError

def FillAreaWithError(self, error, left, top, right, bottom):

Attaches an exception to all the cells in the bounds defined by left, top, right and bottom.

FillRange

def FillRange(self, source, *destRect):

Copies values from the source to the cells within the given bounds. source is clipped if it is larger than the destination.

If source is a CellRange or a 2D .net Array then the cells are filled such that source[x,y] is copied to the destination cell with relative index x,y.

Otherwise, source is treated as an iterable and its values are copied to the destination cells. The copying proceeds by first filling the top row, then the rows below until no items remain. This means that an iterable can be unpacked into a row or column of cells by selecting the appropriate 1 x n or n x 1 area.

If source is not an iterable or is of type dict or is an Array with more than 2 dimensions an ArrayError is raised.

GetCell

def GetCell(self, col, row):

If the cell exists it returns the cell object, otherwise it returns None. This is a useful way of fetching cells without creating them (using worksheet attributes or indexing a worksheet always creates the cell, even if it didn't exist previously).

col and row should be integers greater than zero.

Currently you can't use header titles or column identifiers as indexes, and invalid indexes will return None rather than raising an IndexError. This is likely to change in a future version to make GetCell consistent with other ways of accessing cells on worksheets.

RemoveCell

def RemoveCell(self, col, row):

Remove the cell at the specified location.

col and row should be integers greater than zero.

This method doesn't return anything.

Currently you can't use header titles or column identifiers as indexes, and invalid indexes will not raise an IndexError. This is likely to change in a future version to make RemoveCell consistent with other ways of accessing cells on worksheets.

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):

Returns True if the object being compared is a worksheet with the same name. This should only be true if the other worksheet is the same worksheet.

hash

def __hash__(self):

This hashes the worksheet on its name. Worksheets are considered immutable, so they can be used as dictionary keys.

ne

def __ne__(self, other):

The opposite of 'equals' (returns False if the worksheet is compared with itself, otherwise returns True).

str

def __str__(self):

Returns a nicely formatted string representation of the worksheet object. For example, a worksheet named 'Sheet1' will look like:

Worksheet("Sheet1")