xl.range - Referencing Excel data

class xl.range.ColumnVector(xlRange, with_hidden, as_matrix=False)

exception xl.range.ExcelRangeError

Raised when - a requested range / named range / table column is invalid or cannot be found - usage of a Range instance fails due to its dimensions, e.g. Range.set() with too many values

class xl.range.Matrix(xlRange, with_hidden, as_matrix=False)

get()

Returns a list-of-lists representation in the form:

[[row values,...], [row values,...], ...]

All row lists have the same length. The number of rows / columns returned may be less than num_rows / num_columns; the fetch is clipped to the ‘used range’ of the worksheet

set(data)

Updates the matrix’s cells. data should be of the form:

[[row values, ...], [row values,...], ...]

Not all rows and columns need to be specified; the given values fill the top-left corner of the matrix, and the remaining cells are unchanged. For example:

workbook.get("A:C").set([[1,2], [3,4]])

only modifies A1:B2

class xl.range.Range(xlRange, with_hidden, as_matrix=False)

Represents a contiguous range of cells in Excel, ex. A1:B20. The contents (in Excel) can be read and written to (see the get and set methods).

Ranges are usually obtained using the xl.sheet.Workbook.get() or xl.sheet.Workbook.range() method. The returned range behaves according to its ‘shape,’ which is reflected in its type as well as the shape property:

>>> type(workbook.get("A:A"))
<class 'xl.range.ColumnVector'>

A range’s shape may be ColumnVector, RowVector, Scalar, or Matrix. The Vector type (a base for RowVector and ColumnVector) allows detection of either vector variety:

>>> isinstance(workbook.get("A:A"), xl.Vector)
True

The shape subclasses differ in the rules and types involved in accessing the backing Excel data, e.g. the return type of get. See help(shape class) for specifics.

By default, a Range excludes ‘hidden’ cells - those not visible in Excel due to an Excel-level filter, or manual hiding by the user. The including_hidden and excluding_hidden properties permit explicit control of this behavior:

>>> workbook.get("A1:A3")
<ColumnVector range object for $A$1,$A$3 (visible only)>
>>> workbook.get("A1:A3").including_hidden
<ColumnVector range object for $A$1:$A$3 (includes hidden)>

Note that un-filtered dimensions determine shape, e.g. workbook.get("A1:B2") is a Matrix, even if column B is hidden

as_matrix

Returns a version of this range that is always a Matrix (even if shaped differently). This is useful for utility functions that wish to avoid a special case per range shape

column

Returns the sheet-wide column index of the left-most unfiltered cells

column_vector(idx)

Returns one of the column-vectors comprising this Range by index, i.e. column_vector(0) gives the first column

containing_table

If this range is partially or fully contained in a Table, returns the table Otherwise, returns None

dimensions

Gives the tuple (num rows, num columns). If applicable for this range, hidden cells are excluded

excluding_hidden

Returns a new Range identical this one, but with hidden cells filtered away. This is reversible, e.g. range.exluding_hidden.including_hidden

includes_hidden_cells

including_hidden

Returns a new Range identical this one, but with hidden cells included. This is reversible, e.g. range.including_hidden.excluding_hidden

intersects(rangeOther)

itercells()

Returns a generator yielding the single cell ranges comprising this scalar / vector. Thus, range.get() == [c.get() for c in range.itercells()]

normalize

Return a normalized version of this range. The returned range is reduced to encompass only in-use areas of the worksheet, and (if applicable) the data area of its table

num_columns

num_rows

row

Returns the sheet-wide row index of the top-most unfiltered cells

row_vector(idx)

Returns one of the row-vectors comprising this Range by index, i.e. row_vector(0) gives the first row

shape

Returns the shape class of this range (e.g. RowVector). Equivalent to type(self)

with_filter

Returns a range with the specified inclusion / exclusion of hidden cells. See the including_hidden / excluding_hidden properties for less verbose shortcuts

class xl.range.RowVector(xlRange, with_hidden, as_matrix=False)

class xl.range.Scalar(xlRange, with_hidden, as_matrix=False)

get()

Returns the single value in the scalar range.

set(data)

Updates this scalar range’s cell. The data parameter may be a single value, or a (non-string) iterable that returns one item.

class xl.range.Vector(xlRange, with_hidden, as_matrix=False)

get()

Returns a list representation of this vector’s values (containing values for low indices first)

The number of elements returned may be less than num_rows / num_columns; the fetch is clipped to the ‘used range’ of the worksheet

set(*args, **kwargs)

Updates this vector’s cells. The data parameter should be an iterable returning cell values. Not all cells need to be specified. Values are filled in from the top-left of the vector, and additional cells are left unmodified. For example:

workbook.get("A:A").set([1,2,3])

sets the first 3 values of column A