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, 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 lo_inst: LoInst

Lo Instance

Return type:

LoInst

property office_doc: OfficeDocumentT

Office Document.

Return type:

OfficeDocumentT