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()
orget_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:
See also
- __len__()[source]
Gets the number of sheets in the document.
- Returns:
Number of sheet in the document.
- Return type:
int
- 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:
- 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:
- 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 asMySpecialCell
.- 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:
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, otherwiseFalse
.- 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:
- 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 beNone
.
- 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. Ifidx
is available then argsevent_data["fn_type"]
is set to a valueidx
; Otherwise, set to a valuename
.
- 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 component: com.sun.star.sheet.Spreadsheets
Spreadsheets Component
- Return type:
Spreadsheets
- property office_doc: OfficeDocumentT
Office Document.
- Return type: