Class CalcSheets

Introduction

The CalcSheets class represents the collection of sheets in a Calc document.

This class contains several python magic methods to make it behave like a collection.

Getting Number of Sheets

To get the number of pages in a draw document, use the built in len() method:

>>> doc = CalcDoc(Calc.create_doc(loader))
>>> len(doc.sheets)
1

Getting a sheet

There are several ways to get a sheet from a Calc document. The simplest is to use the [] method:

Get Sheet by Index.

>>> doc = CalcDoc(Calc.create_doc(loader))
>>> doc.sheets[0]
<ooodev.calc.CalcSheet object at 0x7f8b1c0b4a90>

Get Sheet by Name.

>>> doc = CalcDoc(Calc.create_doc(loader))
>>> doc.sheets['Sheet1']
<ooodev.calc.CalcSheet object at 0x7f8b1c0b4a90>

To get the last sheet in a document, use the -1 index:

>>> doc = CalcDoc(Calc.create_doc(loader))
>>> doc.insert_sheet(name="Sheet2", idx=1)
>>> doc.sheets[-1]
<ooodev.calc.CalcSheet object at 0x7f8b1c0b4a90>

Deleting a sheet

To delete a sheet, use the del keyword:

Delete by sheet index.

>>> doc = CalcDoc(Calc.create_doc(loader))
>>> del doc.sheets[0]

Delete by sheet name.

>>> doc = CalcDoc(Calc.create_doc(loader))
>>> del doc.sheets['Sheet1']

Iterating over sheets

To iterate over the sheets in a document, use the for keyword:

>>> doc = CalcDoc(Calc.create_doc(loader))
>>> doc.insert_sheet(name="Sheet2")
>>> doc.insert_sheet(name="Sheet3")
>>> for sheet in doc.sheets:
...     print(sheet.name)
Sheet1
Sheet2
Sheet3

Class Declaration

class ooodev.calc.CalcSheets(owner, sheets, lo_inst=None)[source]

Bases: LoInstPropsPartial, SpreadsheetsComp, CellRangeAccessPartial, NameReplacePartial[Spreadsheet], QiPartial, ServicePartial, TheDictionaryPartial, ElementIndexPartial, CalcDocPropPartial

Class for managing Calc Sheets.

This class is Enumerable and returns CalcSheet instance on iteration.

for sheet in doc.sheets:
    sheet["A1"].set_val("test")
    assert sheet["A1"].get_val() == "test"

This class also as index access and returns CalcSheet instance.

sheet = doc.sheets["Sheet1"]
# or set the value of cell A2 to TEST
doc.sheets[0]["A2"].set_val("TEST")

# get the last sheet of the document
last_sheet = doc.sheets[-1]

# get the second last sheet of the document
second_last_sheet = doc.sheets[-2]

# get the number of sheets
num_sheets = len(doc.sheets)

Changed in version 0.17.13: - Added negative index access. - Added __len__ method.

New in version 0.17.11.

__delitem__(_item)[source]

Removes a sheet from the document.

Parameters:

_item (int | str, CalcSheet) – Index, name, or sheet to remove.

Raises:

TypeError – If the item is not a supported type.

Return type:

None

__getitem__(key)[source]

Gets the sheet at the specified index or name.

This is short hand for get_by_index() or get_by_name().

Parameters:

key (key, str, int) – The index or name of the sheet. When getting by index can be a negative value to get from the end.

Returns:

The sheet with the specified index or name.

Return type:

CalcSheet

See also

__init__(owner, sheets, lo_inst=None)[source]

Constructor

Parameters:

  • owner (CalcDoc) – Owner Document

  • sheet (XSpreadsheet) – Sheet instance.

  • sheets (XSpreadsheets) –

  • lo_inst (LoInst | None) –

Return type:

None

__len__()[source]

Gets the number of sheets in the document.

Returns:

Number of sheet in the document.

Return type:

int

__next__()[source]

Gets the next sheet.

Returns:

The next sheet.

Return type:

CalcSheet

copy_by_name(name, copy, idx)[source]

Copies the sheet with the specified name.

Parameters:

  • name (str) – The name of the sheet to be copied.

  • copy (str) – The name of the copy of the spreadsheet.

  • idx (int, optional) – The index of the copy in the collection. idx can be a negative value to index from the end of the collection.

Return type:

None

create_enumeration()

Creates an enumeration of the container’s elements.

Return type:

XEnumeration

get_active_sheet()[source]

Gets the active sheet

Returns:

Active Sheet if found; Otherwise, None

Return type:

CalcSheet | None

get_by_index(idx)[source]

Gets the element at the specified index.

Parameters:

idx (int) – The Zero-based index of the element. Idx can be a negative value to index from the end of the list. For example, -1 will return the last element.

Returns:

The element at the specified index.

Return type:

CalcSheet

get_by_name(name)[source]

Gets the element with the specified name.

Parameters:

name (str) – The name of the element.

Raises:

MissingNameError – If sheet is not found.

Returns:

The element with the specified name.

Return type:

CalcSheet

get_cell_by_position(col, row, idx)

Returns the cell at the specified position.

Parameters:

  • col (int) – The column index of the cell inside the sheet.

  • row (int) – The row index of the cell inside the sheet.

  • idx (int) – the sheet index of the sheet inside the document.

Returns:

The single cell within the range.

Return type:

XCell

get_cell_range_by_position(start_col, start_row, end_col, end_row, idx)

Returns the cell range at the specified position.

Parameters:

  • start_col (int) – The column index of the first cell inside the sheet.

  • start_row (int) – The row index of the first cell inside the sheet.

  • end_col (int) – The column index of the last cell inside the sheet.

  • end_row (int) – The row index of the last cell inside the sheet.

  • idx (int) – The sheet index of the sheet inside the document.

Returns:

The cell range.

Return type:

XCellRange

get_cell_ranges_by_name(name)

Returns a sub-range of cells within the range.

The sub-range is specified by its name. The format of the range name is dependent of the context of the table. In spreadsheets valid names may be Sheet1.A1:C5 or $Sheet1.$B$2 or even defined names for cell ranges such as MySpecialCell.

Parameters:

name (str) – The name of the range.

Returns:

The cell ranges.

Return type:

tuple[XCellRange, …]

get_count()

Gets the number of elements contained in the container.

Returns:

The number of elements.

Return type:

int

get_element_names()

Gets the names of all elements contained in the container.

Returns:

The names of all elements.

Return type:

tuple[str, …]

get_element_type()

Gets the type of the elements contained in the container.

Returns:

The type of the elements. None means that it is a multi-type container and you cannot determine the exact types with this interface.

Return type:

Any

get_index_by_name(name)

Gets the element index by name.

Parameters:

name (str) – The name of the element.

Returns:

The index of the element if found; Otherwise -1.

Return type:

int

get_services()

Gets service names for the instance.

Returns:

service names

Return type:

List[str]

get_sheet()[source]
get_sheet(idx: int)
get_sheet(sheet_name: str)
get_sheet(sheet: com.sun.star.sheet.XSpreadsheet)
get_sheet(*args, **kwargs)

Gets a sheet of spreadsheet document

Parameters:

  • idx (int, optional) – Zero based index of spreadsheet. Idx can be a negative value to index from the end of the list. For example, -1 will return the last element.

  • sheet_name (str, optional) – Name of spreadsheet

Raises:

  • MissingNameError – If spreadsheet is not found by name.

  • IndexError – If spreadsheet is not found by index.

Returns:

Spreadsheet at index.

Return type:

CalcSheet

Changed in version 0.20.0: - Added support for XSpreadsheet.

get_sheet_names()[source]

Gets names of all existing spreadsheets in the spreadsheet document.

Returns:

Tuple of sheet names.

Return type:

Tuple[str, …]

has_by_name(name)

Checks if the container has an element with the specified name.

Parameters:

name (str) – The name of the element.

Returns:

True if the container has an element with the specified name, otherwise False.

Return type:

bool

has_elements()

Determines whether the container has elements.

Return type:

bool

insert_by_name(name, element)

Inserts the element with the specified name.

Parameters:

  • name (str) – The name of the element to be inserted.

  • element (T) – The new element.

Return type:

None

insert_new_by_name(name, idx)[source]

Inserts a new sheet with the specified name.

Parameters:

  • name (str) – The name of the sheet to be inserted.

  • idx (int, optional) – The index of the new sheet. idx can be a negative value to index from the end of the collection.

Return type:

None

insert_sheet(name: str)[source]
insert_sheet(name: str, idx: int)
insert_sheet(name, idx=-1)

Inserts a spreadsheet into document sheet collections.

Parameters:

  • name (str) – Name of sheet to insert

  • idx (int, optional) – zero-based index position of the sheet to insert. Can be a negative value to insert from the end of the collection. Default is -1 which inserts at the end of the collection.

Raises:

  • Exception – If unable to insert spreadsheet

  • CancelEventError – If SHEET_INSERTING event is canceled

Returns:

The newly inserted sheet

Return type:

CalcSheet

Events:
move_by_name(name, idx)[source]

Moves the sheet with the specified name.

Parameters:

  • name (str) – The name of the sheet to be moved.

  • idx (int) – The new index of the sheet. idx can be a negative value to index from the end of the collection.

Return type:

None

qi(atype, raise_err=False)

Generic method that get an interface instance from an object.

Parameters:

  • atype (T) – Interface type to query obj for. Any Uno class that starts with ‘X’ such as XInterface

  • raise_err (bool, optional) – If True then raises MissingInterfaceError if result is None. Default False

Raises:

MissingInterfaceError – If ‘raise_err’ is ‘True’ and result is None

Returns:

instance of interface if supported; Otherwise, None

Return type:

T | None

Note

When raise_err=True return value will never be None.

remove_by_name(name)

Removes the element with the specified name.

Parameters:

name (str) – The name of the element to be removed.

Return type:

None

remove_sheet(sheet_name: str)[source]
remove_sheet(idx: int)
remove_sheet(*args, **kwargs)

Removes a sheet from document

Parameters:

  • sheet_name (str) – Name of sheet to remove

  • idx (int) – Zero based index of sheet to remove. Can be a negative value to insert from the end of the list.

Returns:

True of sheet was removed; Otherwise, False

Return type:

bool

Events:

Note

Event args event_data is set to a dictionary. If idx is available then args event_data["fn_type"] is set to a value idx; Otherwise, set to a value name.

replace_by_name(name, element)

Replaces the element with the specified name.

Parameters:

  • name (str) – The name of the element to be replaced.

  • element (T) – The new element.

Return type:

None

set_active_sheet(sheet: com.sun.star.sheet.XSpreadsheet) → None[source]
set_active_sheet(sheet: ooodev.calc.calc_sheet.CalcSheet) → None
set_active_sheet(sheet)

Sets the active sheet

Parameters:

sheet (XSpreadsheet) – Sheet to set active

Events:
Return type:

None

Note

Event arg properties modified on SHEET_ACTIVATING it is reflected in this method.

Return type:

None

Parameters:

sheet (XSpreadsheet | mCalcSheet.CalcSheet) –

support_service(*service)

Gets if instance supports a service.

Parameters:

*service (str) – Variable length argument list of UNO namespace strings such as com.sun.star.configuration.GroupAccess

Returns:

True if instance supports any passed in service; Otherwise, False

Return type:

bool

property calc_doc: CalcDoc

Calc Document.

Return type:

CalcDoc

property component: com.sun.star.sheet.Spreadsheets

Spreadsheets Component

Return type:

Spreadsheets

property extra_data: TheDict

Extra Data Key Value Pair Dictionary.

Properties can be assigned properties and access like a dictionary and with dot notation.

Note

This is a dictionary object that can be used to store key value pairs. Generally speaking this data is not part of the object’s main data structure and is not saved with the object (document).

This property is used to store data that is not part of the object’s main data structure and can be used however the developer sees fit.

Return type:

TheDict

property lo_inst: LoInst

Lo Instance

Return type:

LoInst

property office_doc: OfficeDocumentT

Office Document.

Return type:

OfficeDocumentT