Resolver One Library Documentation

API for Worksheet

Access cells, columns, rows and properties of worksheets.

Contents

Classes
- Class Worksheet
  - Accessing Cells
  - Accessing Columns and Rows
  - Properties
    - Alignment
    - BackColor
    - Bold
    - BorderBottom
    - BorderLeft
    - BorderRight
    - BorderTop
    - BottomLeft
    - BottomRight
    - Bounds
    - Cells
    - CellsByIndex
    - ColHidden
    - ColWidth
    - Color
    - Cols
    - ColsByHeader
    - ColsByIndex
    - ContentCols
    - ContentRows
    - Currency
    - CurrentCells
    - DecimalPlaces
    - Error
    - FontFamily
    - FontSize
    - Formatter
    - FreezePoint
    - HeaderCol
    - HeaderHeight
    - HeaderRow
    - HeaderSize
    - HeaderWidth
    - ImagePlacement
    - Italic
    - MaxCol
    - MaxRow
    - MinCol
    - MinRow
    - Name
    - NegativeTextColor
    - RowHeight
    - RowHidden
    - Rows
    - RowsByHeader
    - RowsByIndex
    - ShowBounds
    - ShowGrid
    - ShowNegativeSymbol
    - ShowSeparators
    - Strikethrough
    - StripZeros
    - TopLeft
    - TopRight
    - Underline
    - Unit
    - VerticalAlignment
    - Visible
    - WebEditable
    - Wrap
  - Methods

Classes

Class Worksheet

class Worksheet(BaseWorksheet, SpreadsheetElement):
    def __init__(self, name, traitDefaults=None):

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. Special care should be taken when storing numbers in headers.

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.

To access cells bypassing header lookup use the CellsByIndex property. This property accepts column and row numbers.

a3Cell = sheet1.CellsByIndex[1, 3]

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.

ColsByIndex and RowsByIndex bypass header lookup. They allow indexing when there are numbers in headers, which would otherwise take precedence over rows and columns indexes.

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.

You can also use the slice syntax to access rows and columns in a specific range with a specific step:

workbook['Sheet1'].Rows[start:stop:step] will return every row starting from start, with intervals of size step, up to but not including stop. If step is negative the ordering is reversed. All arguments are optional; if start or stop are not provided, the largest possible list is returned. A missing step is assumed to be 1. start and stop must not be negative.

Example:

sheet = workbook['Sheet1']

rows_1 = sheet.Rows[5:10] # will return rows 5, 6, 7, 8, 9
rows_2 = sheet.Rows[5:10:2] # will return rows 5, 7, 9
rows_3 = sheet.Rows[3:] # will return every row from 3 downwards, including the last one

cols_1 = sheet.Cols[1:4] # will return columns A, B, C
cols_2 = sheet.Cols['G':'B':-1] # will return columns G, F, E, D, C
cols_3 = sheet.Cols[:5] # will return columns A, B, C, D

If stop is not provided, the returned list will include everything from start up to and including MaxRow or MaxCol. MinRow and MinCol are not taken into account.

If Header rows or columns are defined, you can use their values in the slice syntax, as strings:

sheet = workbook['Sheet1']

sheet.Rows[1][1] = 2005
sheet.Rows[1][2] = 2006
sheet.Rows[1][3] = 2007

sheet.HeaderRow = sheet.Rows[1]

cols = sheet.Cols["2005":"2007"] # will return columns A, B

Properties

Alignment

Alignment = property(get, set)

Specifies how cell contents should be aligned within cells. If it is not None, this trait overrides the default alignment of cell values (numbers and dates right-aligned, all other values left-aligned). Otherwise it must be one of the values from the Alignment enumeration (Alignment.Left, Alignment.Right or Alignment.Center).

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.

Cells

Cells = property(get)

Used to access cells of the worksheet. See the Worksheet class documentation for examples.

CellsByIndex

CellsByIndex = property(get)

Returns a cell given a location. It is useful if you have numbers in headers and want to access a cell by location rather than the header.It does not perform a header lookup.

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.

Cols

Cols = property(get)

Columns collection, see Worksheet class documentation for examples.

ColsByHeader

ColsByHeader = property(get)

Same as Cols, but only uses the headers.

ColsByIndex

ColsByIndex = property(get)

Same as Cols, but ignores the header row.

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 for a cell (if any). It must be an instance of the Currency class from Library.Currency. The Currency class has attributes for different currencies (e.g Currency.USD). This property is deprecated, please use the Unit trait instead.

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.

FreezePoint

FreezePoint = property(get, set)

A get and set property that specifies the area of the grid that does not scroll. It can be set to a row, a column, a cell or None. The area to the above and to the left of the specified point will stay in place as the grid is scrolled. If it is set to None, there will be no frozen area.

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 entries are not case-sensitive. Having set a header col, you can then use the values of cells in a header col as an index on 'Cols' or the worksheet. Almost any Python value can be used in a header col, but values that are not hashable (lists, dictionaries, sets and other mutable types) will be slower to use than hashable values (numbers, strings and objects).

Dates in a header col can be referenced using a Date object or the local short format string. So the following two lines are equivalent:

sheet['11/12/2008', 1]
sheet[Date('11/12/2008'), 1]

If the column specified has any duplicate entries, 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 entries are not case-sensitive. Having set a header row, you can then use the values of cells in a header row as an index on 'Rows' or the worksheet. Almost any Python value can be used in a header row, but values that are not hashable (lists, dictionaries, sets and other mutable types) will be slower to use than hashable values (numbers, strings and objects).

Dates in a header row can be referenced using a Date object or the local short format string. So the following two lines are equivalent:

sheet[1, '11/12/2008']
sheet[1, Date('11/12/2008')]

If the row specified has any duplicate entries, 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.

ImagePlacement

ImagePlacement = property(get, set)

This specifies the layout of the image within the cell. Must be one of the values from the ImagePlacement enumeration (ImagePlacement.Normal, ImagePlacement.Center or ImagePlacement.Stretch).

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.

Rows

Rows = property(get)

Rows collection, see Worksheet class documentation for examples.

RowsByHeader

RowsByHeader = property(get)

Same as Rows, but only uses the headers.

RowsByIndex

RowsByIndex = property(get)

Same as Rows, but ignores the header column.

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.

Unit

Unit = property(get, set)

This specifies the formatting for a cell. It can be any of the Currency values from Library.Currency or Unit.Percent. Every Currency on Library.Currency is also present in Library.Unit.

VerticalAlignment

VerticalAlignment = property(get, set)

Specifies how cell contents should be aligned within cells. VerticalAlignment must be one of the values from the VerticalAlignment enumeration (Alignment.Top, Alignment.Bottom or Alignment.Center).

Visible

Visible = property(get, set)

A read-write property that controls whether the worksheet appears in the worksheet tabs. By default, worksheets are visible, but this flag can be used to hide them. At least one worksheet must be visible; if there are no worksheets with Visible = True, the first worksheet will be shown.

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

AllColsFromIndex

def AllColsFromIndex(self, startIndex=1)

Returns an infinite generator of columns starting at startIndex (defaulting to 1). startIndex must be an integer greater than 0 or a valid column identifier. No header lookup is done on the startIndex.

The Cols iterator only returns columns up to the worksheet bounds, while AllColsFromIndex provides access to columns that have not yet been populated.

Note: This is an infinite generator, so calling list on it is a bad idea - you will run out of memory.

from itertools import islice
colGenerator = workbook['Sheet1'].AllColsFromIndex('D')
threeColsFromD = islice(colGenerator, 3)

AllRowsFromIndex

def AllRowsFromIndex(self, startIndex=1)

Returns an infinite generator of rows starting at startIndex (defaulting to 1). startIndex must be an integer greater than 0. No header lookup is done on the startIndex.

The Rows iterator only returns rows up to the worksheet bounds, while AllRowsFromIndex provides access to rows that have not yet been populated.

Note: This is an infinite generator, so calling list on it is a bad idea - you will run out of memory.

from itertools import islice
rowGenerator = workbook['Sheet1'].AllRowsFromIndex(3)
sixRowsFrom3 = islice(rowGenerator, 6)

Clear

def Clear(self)

Clears all contents of the worksheet, including cells, headers, arrays and formatting.

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, like A1, 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 may change in a future version.

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

Comments

If you have comments, questions or suggestions about any of the Resolver One documentation, please post them to the Documentation Suggestions Forum.