.. _tableobjects:
Table Objects
=============
Sheets Class
------------
.. class:: Sheets
The :class:`Sheets` manages all :class:`Table` objects in
spreadsheet-documents. (`sheet` is a synonym for `table`)
.. warning::
Don't create instances of this class by yourself, every spreadsheet-document
has a :attr:`sheets` attribute.
Methods
~~~~~~~
.. method:: Sheets.__len__()
Get count of sheets.
.. method:: Sheets.__iter__()
Iterate over all :class:`Table` objects.
.. method:: Sheets.__getitem__(key)
Get sheet by `key`, `key` is either the numerical index of the sheet or
the name of the sheet.
.. method:: Sheets.__setitem__(key, sheet)
Replace sheet `key` by `sheet`, `key` is either the numerical index of the
sheet or the name of the sheet.
.. method:: Sheets.__delitem__(key)
Delete sheet by `key`, `key` is either the numerical index of the sheet or
the name of the sheet.
.. method:: Sheets.__iadd__(sheet)
``+=`` operator, alias for :meth:`~Sheets.append`.
.. method:: Sheets.append(sheet)
Append `sheet` as last sheet of spreadsheet-document.
.. method:: Sheets.index(sheet)
Get index of `sheet`.
.. method:: Sheets.insert(index, sheet)
Insert `sheet` at position `index`.
.. method:: Sheets.names()
Get list of sheet names.
Table Class
-----------
.. class:: Table(name="NEWTABLE", size=(10, 10), xmlnode=None)
The :class:`Table` object represents a fixed sized table with `size[0]` rows
and `size[1]` columns. Every cell contains a :class:`Cell` object, even empty
cells (`value` and `value_type` of empty cells are `None`).
Reference cells by (row, col) tuples or by classic spreadsheet cell references
like ``'A1'``. The letters represent the column (``'A'`` = column(0), ``'B'``
= column(1), ...), the numbers represent the row (``'1'`` = row(0), ``'2'``
= row(1), ...).
Attributes
~~~~~~~~~~
.. attribute:: Table.name (read/write)
Specifies the name of the table, should be unique, and can contain spaces.
.. attribute:: Table.style_name (read/write)
References a table style.
.. attribute:: Table.protected (read/write)
The :attr:`~Table.protected` attribute specifies whether or not a table is
protected from editing. If a table is protected, all of the table elements
and the cell elements with a :attr:`~Cell.protected` attribute set to `True`
are protected.
Methods
~~~~~~~
.. method:: Table.__getitem__(key)
Get cell by `key` as :class:`Cell` object, `key` is either a
(`row, col`) tuple or a classic spreadsheet reference like ``'A1''``.
.. method:: Table.__setitem__(key, cell)
Set cell referenced by `key` to `cell`, `cell` has to be a :class:`Cell`
object and `key` is either a (`row, col`) tuple or a classic spreadsheet
reference like ``'A1''``.
.. method:: Table.ncols()
Get count of table columns.
.. method:: Table.nrows()
Get count of table rows.
.. method:: Table.reset(size=(10, 10))
Delete table content and set new table metrics.
.. method:: Table.row(index)
Get cells of row `index` as list of :class:`Cell` objects.
.. method:: Table.rows(index)
Iterate over rows, where every row is a list of :class:`Cell` objects.
.. method:: Table.col(index)
Get cells of column `index` as list of :class:`Cell` objects.
.. method:: Table.columns(index)
Iterate over columns, where every column is a list of :class:`Cell` objects.
.. method:: Table.row_info(index)
Get row-info of row `index` as :class:`TableRow` object.
.. method:: Table.column_info(index)
Get column-info of column `index` as :class:`TableColumn` object.
.. method:: Table.append_rows(count=1)
Append `count` empty rows.
.. method:: Table.insert_rows(index, count=1)
Insert `count` empty rows at `index`. **CAUTION:** This operation breaks cell
references in formulas
.. method:: Table.delete_rows(index, count=1)
Delete `count` rows at `index`. **CAUTION:** This operation breaks cell
references in formulas
.. method:: Table.append_columns(count=1)
Append `count` empty columns.
.. method:: Table.insert_columns(index, count=1)
Insert `count` empty columns at `index`. **CAUTION:** This operation breaks
cell references in formulas
.. method:: Table.delete_columns(index, count=1)
Delete `count` columns at `index`. **CAUTION:** This operation breaks cell
references in formulas
.. method:: Table.set_cell_span(pos, size)
Set cell span for cell at position `pos` to `size`, `pos` can be a
(row, column) tuple or a reference string, `size` has to be a (nrows, ncols)
tuple, where nrows and ncols are >= 1. Spanning is not possible if the
spanning area contains other spanning cells.
The cell span value is an attribute of the :class:`Cell` class. To request
the span value use::
if table['A1'].span == (3, 2):
print("cell 'A1' spans over three rows and two columns")
.. method:: Table.remove_cell_span(pos)
Removes spanning for cell at position `pos`, `pos` can be a
(row, column) tuple or a reference string.
Sheet Class
-----------
.. class:: Sheet
Alias for :class:`Table` class.
Cell Class
----------
.. class:: Cell(value=None, value_type=None, currency=None, style_name=None, xmlnode=None)
Creates a new cell object. If `value_type` is None, the type will be determined
by the type of `value`. `value` and `value_type` of empty cells are `None`.
================ ===============================================================
Value Type Description
================ ===============================================================
``'string'`` Text content (python strings)
``'float'`` Floating point numbers (python float)
``'percentage'`` Floating point numbers, where 1.0 = 100% (python float)
``'currency'`` Floating point numbers (python float)
``'boolean'`` `True` or `False` (python bool)
``'date'`` date value as string, form: ``'yyyy-mm-dd'`` or
``'yyyy-mm-ddThh:mm:ss'``
``'time'`` time period as string, form: ``'PThhHmmMss,ffffS``'
================ ===============================================================
Automatic typing:
===================== =======================
Python type of Value value_type of cell
===================== =======================
str ``'string'``
float/int ``'float'``
bool ``'boolean'``
===================== =======================
examples for setting table values::
# create new cell as float
table['A1'] = Cell(100.)
# or modify existing cell (preserves existing properties)
table['A1'].set_value(100.)
# set as currency
table['B1'].set_value(100, currency='EUR')
# set as string
table['C1'].set_value("Text")
# append text to string-cells
table['C1'].append_text("\nLine 2")
# set as date
table['D1'].set_value("2011-02-05", 'date')
example for getting cell values::
cell = Cell(3.141592)
pi = cell.value
Attributes
~~~~~~~~~~
.. attribute:: Cell.value (read)
Get converted cell values, numerical values as `float`, boolean values as
`bool` and all others as `str`.
.. attribute:: Cell.value_type (read)
.. attribute:: Cell.currency (read)
Get currency as `string`, if :attr:`Cell.value_type` is ``'currency'``
else `None`.
.. attribute:: Cell.style_name (read/write)
References a table-cell style.
.. attribute:: Cell.formula (read/write)
Formulas allow calculations to be performed within table cells. Typically,
the formula itself begins with an equal (=) sign and can include the following
components:
- Numbers
- Text
- Named ranges
- Operators
- Logical operators
- Function calls
- Addresses of cells that contain numbers
.. attribute:: Cell.content_validation_name (read/write)
.. attribute:: Cell.protected (read/write)
Protects the table cell. Users can not edit the content of a cell
that is marked as protected. This attribute is not related to the
:attr:`Table.protected` attribute for table elements.
.. attribute:: Cell.span (read)
Get cell spanning as (row, col) tuple.
Specify the number of rows and columns that a cell spans.
When a cell covers another cell because of a column or row span value
greater than one, the :attr:`~Cell.covered` attribute of the covered
cell is `True`.
.. attribute:: Cell.covered (read)
`True` if cell is covered by other cells.
.. attribute:: Cell.display_form (read/write)
Display form of cell as `str`, set by other programs like LibreOffice or
OpenOffice. **ezodf** does not set the display form.
Methods
~~~~~~~
.. method:: Cell.set_value(value, value_type=None, currency=None)
Set new cell velues.
.. method:: Cell.plaintext()
Get the plain text representation as `str`.
.. method:: Cell.append_text()
Append text to cells of type ``'string'``.
TableRow Class
--------------
.. class:: TableRow
Attributes
~~~~~~~~~~
.. attribute:: TableRow.style_name (read/write)
References a table-row style.
.. attribute:: TableRow.visibility (read/write)
Specifies whether the row is ``'visible'``, ``'filtered'``, or ``'collapsed'``.
Filtered and collapsed rows are not visible. Filtered rows are invisible,
because a filter is applied to the table that does not select the table
row. Collapsed rows have been made invisible by user directly.
.. attribute:: TableRow.default_cell_style_name (read/write)
References the default table-cell style.
TableColumn Class
-----------------
Attributes
~~~~~~~~~~
.. class:: TableColumn
.. attribute:: TableColumn.style_name (read/write)
References a table-column style.
.. attribute:: TableColumn.visibility (read/write)
Specifies whether the row is ``'visible'``, ``'filtered'``, or ``'collapsed'``.
Filtered and collapsed columns are not visible. Filtered columns are invisible,
because a filter is applied to the table that does not select the table
column. Collapsed columns have been made invisible by user directly.
.. attribute:: TableColumn.default_cell_style_name (read/write)
References the default table-cell style.