Class CalcSheet

Introduction

The CalcSheet class represents a Calc spreadsheet document.

This class has index access to cells using the [] method. The index can be:

  • a string in the form “A1” or “B2” (column letter followed by row number)

  • a tuple (column, row) where column is an integer and row is an integer

  • a CellObj object

  • a UNO CellAddress object

  • a UNO XCell object

Getting access via cell name

>>> doc.sheets[0]["A2"].get_val()
1.0

Getting access via column and row

Column and row are 0-based indexes.

>>> doc.sheets[0][(0, 1)].get_val()
1.0

Getting access via CellObj

CellObj is 1-based for row.

>>> doc.sheets[0][CellObj("A", 2)].get_val()
1.0

Class Declaration

class ooodev.calc.CalcSheet(owner, sheet, lo_inst=None)[source]

Bases: LoInstPropsPartial, SpreadsheetComp, SheetCellPartial, EventsPartial, QiPartial, PropPartial, ServicePartial, StylePartial, CalcDocPropPartial, CalcSheetPropPartial

Class for managing Calc Sheet

Parameters:
  • owner (CalcDoc) –

  • sheet (XSpreadsheet) –

  • lo_inst (LoInst | None) –

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

Constructor

Parameters:
  • owner (CalcDoc) – Owner Document

  • sheet (XSpreadsheet) – Sheet instance.

  • lo_inst (LoInst | None) –

Return type:

None

add_event_chart_data_change_event_events_disposing(cb)

Adds a listener for an event.

Event is invoked when the broadcaster is about to be disposed.

The callback EventArgs.event_data will contain a UNO com.sun.star.lang.EventObject struct.

Return type:

None

Parameters:

cb (Any) –

add_event_chart_data_changed(cb)

Adds a listener for an event.

Event is invoked when chart data changes in value or structure.

The callback EventArgs.event_data will contain a UNO com.sun.star.chart.ChartDataChangeEvent struct.

Return type:

None

Parameters:

cb (Any) –

add_event_modified(cb)

Adds a listener for an event.

Event is invoked when something changes in the object.

The callback EventArgs.event_data will contain a UNO com.sun.star.lang.EventObject struct.

Return type:

None

Parameters:

cb (Any) –

add_event_modify_events_disposing(cb)

Adds a listener for an event.

Event is invoked when the broadcaster is about to be disposed.

The callback EventArgs.event_data will contain a UNO com.sun.star.lang.EventObject struct.

Return type:

None

Parameters:

cb (Any) –

add_event_observers(*args)

Adds observers that gets their trigger method called when this class trigger method is called.

Parameters:

args (EventObserver) – One or more observers to add.

Return type:

None

Note

Observers are removed automatically when they are out of scope.

add_event_property_change(name, cb)

Adds a listener for an event.

Event is invoked when property is changed.

The callback EventArgs.event_data will contain a com.sun.star.beans.PropertyChangeEvent struct.

Parameters:
  • name (str) – Property Name

  • cb (EventArgsCallbackT) – Callback

Return type:

None

add_event_property_change_events_disposing(name, cb)

Adds a listener for an event.

Event is invoked when the property listener is about to be disposed.

The callback EventArgs.event_data will contain a UNO com.sun.star.lang.EventObject struct.

Parameters:
  • name (str) – Property Name

  • cb (EventArgsCallbackT) – Callback

Return type:

None

add_event_vetoable_change(name, cb)

Adds a listener for an event.

Event is invoked when property is changed.

The callback EventArgs.event_data will contain a com.sun.star.beans.PropertyChangeEvent struct.

Parameters:
  • name (str) – Property Name

  • cb (EventArgsCallbackT) – Callback

Return type:

None

add_event_vetoable_change_events_disposing(name, cb)

Adds a listener for an event.

Event is invoked when the property listener is about to be disposed.

The callback EventArgs.event_data will contain a UNO com.sun.star.lang.EventObject struct.

Parameters:
  • name (str) – Property Name

  • cb (EventArgsCallbackT) – Callback

Return type:

None

apply_styles(*styles, **kwargs)

Applies style to component.

Parameters:
  • obj. (styles expandable list of styles object such as Font to apply to) –

  • kwargs (Any, optional) – Expandable list of key value pairs.

  • styles (StyleT) –

Return type:

None

change_style(style_name: str, cell_range: com.sun.star.table.XCellRange)[source]
change_style(style_name: str, range_name: str)
change_style(style_name: str, range_obj: ooodev.utils.data_type.range_obj.RangeObj)
change_style(style_name: str, start_col: int, start_row: int)
change_style(*args, **kwargs)

Changes style of a range of cells.

Parameters:
  • style_name (str) – Name of style to apply.

  • cell_range (XCellRange) – Cell range to apply style to.

  • range_name (str) – Range to apply style to such as A1:E23.

  • range_obj (RangeObj) – Range Object.

  • start_col (int) – Zero-base start column index.

  • start_row (int) – Zero-base start row index.

  • end_col (int) – Zero-base end column index.

  • end_row (int) – Zero-base end row index.

Returns:

True if style has been changed; Otherwise, False.

Return type:

bool

clear_cells(cell_range: com.sun.star.table.XCellRange)[source]
clear_cells(cell_range: com.sun.star.table.XCellRange, cell_flags: ooo.dyn.sheet.cell_flags.CellFlagsEnum)
clear_cells(range_name: str)
clear_cells(range_name: str, cell_flags: ooo.dyn.sheet.cell_flags.CellFlagsEnum)
clear_cells(range_val: ooodev.utils.data_type.range_obj.RangeObj, cell_flags: ooo.dyn.sheet.cell_flags.CellFlagsEnum)
clear_cells(cr_addr: ooo.lo.table.cell_range_address.CellRangeAddress)
clear_cells(cr_addr: ooo.lo.table.cell_range_address.CellRangeAddress, cell_flags: ooo.dyn.sheet.cell_flags.CellFlagsEnum)
clear_cells(*args, **kwargs)

Clears the specified contents of the cell range.

If cell_flags is not specified then cell range of types VALUE, DATETIME and STRING are cleared.

Parameters:
  • cell_range (XCellRange) – Cell range.

  • range_name (str) – Range name such as A1:G3.

  • range_val (RangeObj) – Range object.

  • cr_addr (CellRangeAddress) – Cell Range Address.

  • cell_flags (CellFlagsEnum) – Flags that determine what to clear.

Raises:

MissingInterfaceError – If XSheetOperation interface cannot be obtained.

Events:
Returns:

True if cells are cleared; Otherwise, False.

Return type:

bool

Hint

  • CellFlagsEnum can be imported from ooo.dyn.sheet.cell_flags

Note

Events arg for this method have a cell type of XCellRange.

Events arg event_data is a dictionary containing cell_flags.

create_cursor()[source]

Creates a cell cursor including the whole spreadsheet.

Returns:

_description_

Return type:

_type_

create_cursor_by_range(*, range_name: str)[source]
create_cursor_by_range(*, range_obj: ooodev.utils.data_type.range_obj.RangeObj)
create_cursor_by_range(*, cell_obj: ooodev.utils.data_type.cell_obj.CellObj)
create_cursor_by_range(*, cr_addr: ooo.lo.table.cell_range_address.CellRangeAddress)
create_cursor_by_range(*, cell_range: com.sun.star.table.XCellRange)
create_cursor_by_range(*, col_start: int, row_start: int)
create_cursor_by_range(**kwargs)

Creates a cell cursor to travel in the given range context.

Parameters:
  • range_name (str) – Range Name such as A1:D5.

  • range_obj (RangeObj) – Range Object.

  • cell_obj (CellObj) – Cell Object.

  • cr_addr (CellRangeAddress) – Cell range Address.

  • cell_range (XCellRange) – Cell Range. If passed in then the same instance is returned.

  • col_start (int) – Start Column.

  • row_start (int) – Start Row.

  • col_end (int) – End Column.

  • row_end (int) – End Row.

Returns:

Cell cursor

Return type:

CalcCellCursor

delete_cells(cell_range: com.sun.star.table.XCellRange, is_shift_left: bool)[source]
delete_cells(cr_addr: ooo.lo.table.cell_range_address.CellRangeAddress, is_shift_left: bool)
delete_cells(range_name: str, is_shift_left: bool)
delete_cells(range_obj: ooodev.utils.data_type.range_obj.RangeObj, is_shift_left: bool)
delete_cells(col_start: int, row_start: int, col_end: int)
delete_cells(*args, **kwargs)

Deletes Cells in a spreadsheet.

Parameters:
  • cell_range (XCellRange) – Cell range to insert.

  • cr_addr (CellRangeAddress) – Cell range Address.

  • range_name (str) – Range Name such as ‘A1:D5’.

  • range_obj (RangeObj) – Range Object.

  • col_start (int) – Start Column.

  • row_start (int) – Start Row.

  • col_end (int) – End Column.

  • row_end (int) – End Row.

  • is_shift_left (bool) – If True then cell are shifted left; Otherwise, cells are shifted up.

Events:
Returns:

True if cells are deleted; Otherwise, False.

Return type:

bool

Note

Events args for this method have a cell type of XCellRange

Event args event_data is a dictionary containing is_shift_right.

delete_column(idx, count=1)[source]

Delete a column from a spreadsheet.

Parameters:
  • idx (int) – Zero base of index of column to delete.

  • count (int) – Number of columns to delete.

Events:
Returns:

True if column is deleted; Otherwise, False.

Return type:

bool

delete_row(idx, count=1)[source]

Deletes a row from spreadsheet.

Parameters:
  • idx (int) – Zero based index of row to delete.

  • count (int) – Number of rows to delete.

Events:
Returns:

True if row is deleted; Otherwise, False.

Return type:

bool

deselect_cells()[source]

Deselects cells in a Spreadsheet.

Return type:

None

dispatch_recalculate()[source]

Dispatches recalculate command to the current sheet.

Also useful when needing to refresh a chart.

Return type:

None

extract_col(vals, col_idx)[source]

Extract column data and returns as a list

Parameters:
  • vals (Table) – 2-d table of data

  • col_idx (int) – column index to extract

Returns:

Column data if found; Otherwise, empty list.

Return type:

List[Any]

extract_row(vals, row_idx)[source]

Extracts a row from a table

Parameters:
  • vals (Table) – Table of data

  • row_idx (int) – Row index to extract

Raises:

IndexError – If row_idx is out of range.

Returns:

Row of data

Return type:

Row

find_all(srch, sd)[source]

Searches spreadsheet and returns a list of Cell Ranges that match search criteria

Parameters:
  • srch (XSearchable) – Searchable object

  • sd (XSearchDescriptor) – Search description

Returns:

A list of cell ranges on success; Otherwise, None

Return type:

List[XCellRange] | None

Example
from ooodev.loader.lo import Lo
from ooodev.office.calc import Calc
from com.sun.star.util import XSearchable

doc = Calc.create_doc(loader)
sheet = Calc.get_sheet(doc=doc, index=0)
Calc.set_val(value='test', sheet=sheet, cell_name="A1")
Calc.set_val(value='test', sheet=sheet, cell_name="C3")
srch = Lo.qi(XSearchable, sheet)
sd = srch.createSearchDescriptor()
sd.setSearchString('test')
results = Calc.find_all(srch=srch, sd=sd)
assert len(results) == 2
find_function(func_nm: str) Tuple[PropertyValue] | None[source]
find_function(idx: int) Tuple[PropertyValue] | None
find_function(*args, **kwargs)

Finds a function

Parameters:
  • func_nm (str) – function name

  • idx (int) – Index of function

Returns:

Function properties as tuple on success; Otherwise, None

Return type:

Tuple[PropertyValue, …] | None

find_used()[source]
find_used(range_name: str)
find_used(range_obj: ooodev.utils.data_type.range_obj.RangeObj)
find_used(cr_addr: ooo.lo.table.cell_range_address.CellRangeAddress)
find_used(*args, **kwargs)

Find used range

Parameters:
  • range_name (str) – Range Name such as ‘A1:D5’

  • range_obj (RangeObj) – Range Object

  • cr_addr (CellRangeAddress) – Cell range Address

Returns:

Cell range

Return type:

CalcCellRange

find_used_cursor(cursor)[source]

Find used cursor

Parameters:

cursor (CalcCellRange) – Sheet Cursor

Raises:

MissingInterfaceError – if unable to find interface

Returns:

Cell range

Return type:

XCellRange

find_used_range()[source]
find_used_range(range_name: str)
find_used_range(range_obj: ooodev.utils.data_type.range_obj.RangeObj)
find_used_range(cr_addr: ooo.lo.table.cell_range_address.CellRangeAddress)
find_used_range(*args, **kwargs)

Find used range

Parameters:
  • range_name (str) – Range Name such as ‘A1:D5’

  • range_obj (RangeObj) – Range Object

  • cr_addr (CellRangeAddress) – Cell range Address

Returns:

Cell Range.

Return type:

CalcCellRange

find_used_range_obj()[source]
find_used_range_obj(range_name: str)
find_used_range_obj(range_obj: ooodev.utils.data_type.range_obj.RangeObj)
find_used_range_obj(cr_addr: ooo.lo.table.cell_range_address.CellRangeAddress)
find_used_range_obj(*args, **kwargs)

Find used range

Parameters:
  • range_name (str) – Range Name such as ‘A1:D5’

  • range_obj (RangeObj) – Range Object

  • cr_addr (CellRangeAddress) – Cell range Address

Returns:

Range object

Return type:

RangeObj

get_address(*, cell_range: com.sun.star.table.XCellRange)[source]
get_address(*, range_name: str)
get_address(*, range_obj: ooodev.utils.data_type.range_obj.RangeObj)
get_address(*, start_col: int, start_row: int)
get_address(**kwargs)

Gets Range Address.

Parameters:
  • cell_range (XCellRange) – Cell Range.

  • range_name (str) – Range name such as ‘A1:D7’.

  • range_obj (RangeObj) – Range Object.

  • start_col (int) – Zero-base start column index.

  • start_row (int) – Zero-base start row index.

  • end_col (int) – Zero-base end column index.

  • end_row (int) – Zero-base end row index.

Returns:

Cell Range Address.

Return type:

CellRangeAddress

get_array(*, cell_range: com.sun.star.table.XCellRange)[source]
get_array(*, range_name: str)
get_array(*, range_obj: ooodev.utils.data_type.range_obj.RangeObj)
get_array(*, cell_obj: ooodev.utils.data_type.cell_obj.CellObj)
get_array(**kwargs)

Gets Array of data from a spreadsheet.

Parameters:
  • cell_range (XCellRange) – Cell range to get data from..

  • range_name (str) – Range of data to get such as A1:E16.

  • range_obj (RangeObj) – Range object.

  • cell_obj (CellObj) – Cell Object.

Returns:

Resulting data array.

Return type:

TupleArray

get_cell(*args, **kwargs)

Gets a cell

Parameters:
  • addr (CellAddress) – Cell Address

  • cell_name (str) – Cell Name such as ‘A1’

  • cell_obj – (CellObj): Cell object

  • cell_range (XCellRange) – Cell Range

  • col (int) – Cell column

  • row (int) – cell row

  • cell (XCell) – Cell

Returns:

cell

Return type:

CalcCell

get_cell_address(*, cell: com.sun.star.table.XCell)[source]
get_cell_address(*, cell_name: str)
get_cell_address(*, cell_obj: ooodev.utils.data_type.cell_obj.CellObj)
get_cell_address(*, addr: com.sun.star.table.CellAddress)
get_cell_address(*, col: int, row: int)
get_cell_address(**kwargs)

Gets Cell Address.

Parameters:
  • cell (XCell) – Cell.

  • cell_name (str) – Cell name such as A1.

  • cell_obj (CellObj) – Cell object.

  • addr (CellAddress) – Cell Address.

  • col (int) – Zero-base column index.

  • row (int) – Zero-base row index.

Returns:

Cell Address.

Return type:

CellAddress

get_col(*, calc_cell_range: ooodev.calc.calc_cell_range.CalcCellRange)[source]
get_col(*, cell_range: com.sun.star.table.XCellRange)
get_col(*, col_name: str)
get_col(*, col_idx: int)
get_col(*, range_name: str)
get_col(*, cell_obj: ooodev.utils.data_type.cell_obj.CellObj)
get_col(*, range_obj: ooodev.utils.data_type.range_obj.RangeObj)
get_col(**kwargs)

Gets a column of data from spreadsheet.

Parameters:
  • calc_cell_range (CalcCellRange) – Calc cell range to get column data from.

  • cell_range (XCellRange) – Cell range to get column data from.

  • col_name (str) – column name such as A.

  • col_idx (int) – Zero base column index such as 0 for column A.

  • range_name (str) – Range such as A1:A12.

  • range_obj (RangeObj) – Range Object.

  • cell_obj (CellObj) – Cell Object.

Returns:

1-Dimensional List.

Return type:

List[Any]

get_col_range(idx)[source]

Get Column by index

Parameters:

idx (int) – Zero-based column index

Raises:

MissingInterfaceError – if unable to find interface

Returns:

Cell range

Return type:

CalcTableCol

get_float_array(*, cell_range: com.sun.star.table.XCellRange)[source]
get_float_array(*, range_name: str)
get_float_array(*, range_obj: ooodev.utils.data_type.range_obj.RangeObj)
get_float_array(*, cell_obj: ooodev.utils.data_type.cell_obj.CellObj)
get_float_array(**kwargs)

Gets a 2-Dimensional List of floats.

Parameters:
  • cell_range (XCellRange) – Cell range to get data from.

  • range_name (str) – Range to get array of floats from such as ‘A1:E18’.

  • range_obj (RangeObj) – Range object.

  • cell_obj (CellObj) – Cell Object.

Returns:

2-Dimensional List of floats.

Return type:

FloatTable

get_num(*, cell: com.sun.star.table.XCell)[source]
get_num(*, cell_name: str)
get_num(*, cell_obj: ooodev.utils.data_type.cell_obj.CellObj)
get_num(*, addr: com.sun.star.table.CellAddress)
get_num(*, col: int, row: int)
get_num(**kwargs)

Get cell value a float.

Parameters:
  • cell (XCell) – Cell to get value of.

  • cell_name (str) – Cell name such as ‘B4’.

  • cell_obj (CellObj) – Cell Object.

  • addr (CellAddress) – Cell Address.

  • col (int) – Cell zero-base column number.

  • row (int) – Cell zero-base row number.

Returns:

Cell value as float. If cell value cannot be converted then 0.0 is returned.

Return type:

float

get_pilot_tables()[source]

Gets pivot tables (formerly known as DataPilot) for a sheet.

Returns:

Pivot tables

Return type:

XDataPilotTables

get_property(name, default=<object object>)

Get property value

Parameters:
  • name (str) – Property Name.

  • default (Any, optional) – Return value if property value is None.

Returns:

Property value or default.

Return type:

Any

get_range(*, range_name: str)[source]
get_range(*, cr_addr: ooo.lo.table.cell_range_address.CellRangeAddress)
get_range(*, range_obj: ooodev.utils.data_type.range_obj.RangeObj)
get_range(*, cell_obj: ooodev.utils.data_type.cell_obj.CellObj)
get_range(*, cell_range: com.sun.star.table.XCellRange)
get_range(*, col_start: int, row_start: int)
get_range(**kwargs)

Gets a range Object representing a range.

Parameters:
  • range_name (str) – Cell range as string.

  • cell_range (XCellRange) – Cell Range.

  • cr_addr (CellRangeAddress) – Cell Range Address.

  • cell_obj (CellObj) – Cell Object.

  • range_obj (RangeObj) – Range Object. If passed in the same RangeObj is returned.

  • col_start (int) – Zero-based start column index.

  • row_start (int) – Zero-based start row index.

  • col_end (int) – Zero-based end column index.

  • row_end (int) – Zero-based end row index.

Returns:

Range object.

Return type:

RangeObj

get_row(*, calc_cell_range: ooodev.calc.calc_cell_range.CalcCellRange)[source]
get_row(*, cell_range: com.sun.star.table.XCellRange)
get_row(*, row_idx: int)
get_row(*, range_name: str)
get_row(*, cell_obj: ooodev.utils.data_type.cell_obj.CellObj)
get_row(*, range_obj: ooodev.utils.data_type.range_obj.RangeObj)
get_row(**kwargs)

Gets a row of data from spreadsheet

Parameters:
  • calc_cell_range (CalcCellRange) – Calc cell range to get row data from.

  • cell_range (XCellRange) – Cell range to get row data from.

  • row_idx (int) – Zero base row index such as 0 for row 1

  • range_name (str) – Range such as ‘A1:A12’

  • cell_obj (CellObj) – Cell Object

  • range_obj (RangeObj) – Range Object

Returns:

1-Dimensional List of values on success; Otherwise, None

Return type:

Row

get_row_range(idx)[source]

Get Row by index

Parameters:
  • sheet (XSpreadsheet) – Spreadsheet

  • idx (int) – Zero-based column index

Raises:

MissingInterfaceError – if unable to find interface

Returns:

Cell range

Return type:

CalcCellRange

get_row_used_first_index()[source]

Gets the index of the row of the top edge of the used sheet range.

Returns:

Zero based index of first row used on the sheet.

Return type:

int

get_row_used_last_index()[source]

Gets the index of the row of the bottom edge of the used sheet range.

Returns:

Zero based index of last row used on the sheet.

Return type:

int

get_selected()[source]

Gets selected cell range.

Returns:

Selected cell range

Return type:

CalcCellRange

get_selected_addr()[source]

Gets select cell range addresses

Raises:
  • Exception – if unable to get document model

  • MissingInterfaceError – if unable to get interface XCellRangeAddressable

Returns:

Cell range addresses.

Return type:

CellRangeAddress

get_selected_cell()[source]

Gets selected cell.

Raises:

CellError – if active selection is not a single cell

Returns:

Selected cell

Return type:

CalcCell

get_selected_range()[source]

Gets select cell range

Raises:
  • Exception – if unable to get document model

  • MissingInterfaceError – if unable to get interface XCellRangeAddressable

Returns:

Cell range addresses

Return type:

RangeObj

get_services()

Gets service names for the instance.

Returns:

service names

Return type:

List[str]

get_sheet_index()[source]

Gets index if sheet

Returns:

Sheet Index

Return type:

int

get_sheet_name(safe_quote=True)[source]

Gets the name of a sheet

Parameters:

safe_quote (bool, optional) – If True, returns quoted (in single quotes) sheet name if the sheet name is not alphanumeric. Defaults to True.

Raises:

MissingInterfaceError – If unable to access spreadsheet named interface

Returns:

Name of sheet

Return type:

str

get_val(*, cell: com.sun.star.table.XCell) Any[source]
get_val(*, addr: com.sun.star.table.CellAddress) Any
get_val(*, cell_name: str) Any
get_val(*, cell_obj: ooodev.utils.data_type.cell_obj.CellObj) Any
get_val(*, col: int, row: int) Any
get_val(**kwargs)

Gets cell value.

Parameters:
  • cell (XCell) – cell to get value of.

  • addr (CellAddress) – Address of cell.

  • cell_name (str) – Name of cell such as ‘B4’.

  • cell_obj (CellObj) – Cell Object.

  • col (int) – Cell zero-based column.

  • row (int) – Cell zero-base row.

Returns:

Cell value cell has a value; Otherwise, None.

Return type:

Any | None

goal_seek(gs, cell_name, formula_cell_name, result)[source]

Calculates a value which gives a specified result in a formula.

Parameters:
  • gs (XGoalSeek) – Goal seeking value for cell

  • cell_name (str | CellObj) – cell name

  • formula_cell_name (str | CellObj) – formula cell name

  • result (int, float) – float or int, result of the goal seek

Raises:

GoalDivergenceError – If goal divergence is greater than 0.1

Returns:

result of the goal seek

Return type:

float

goto_cell(cell_name: str)[source]
goto_cell(cell_obj: ooodev.utils.data_type.cell_obj.CellObj)
goto_cell(*args, **kwargs)

Go to a cell

Parameters:
  • cell_name (str) – Cell Name such as ‘B4’.

  • cell_obj (CellObj) – Cell Object.

Returns:

Cell Object.

Return type:

CalcCell

Attention

dispatch_cmd() method is called along with any of its events.

Dispatch command is GoToCell.

insert_cells(cell_range: com.sun.star.table.XCellRange, is_shift_right: bool)[source]
insert_cells(cr_addr: ooo.lo.table.cell_range_address.CellRangeAddress, is_shift_right: bool)
insert_cells(range_name: str, is_shift_right: bool)
insert_cells(range_obj: ooodev.utils.data_type.range_obj.RangeObj, is_shift_right: bool)
insert_cells(col_start: int, row_start: int, col_end: int)
insert_cells(*args, **kwargs)

Inserts Cells into a spreadsheet.

Parameters:
  • cell_range (XCellRange) – Cell range to insert.

  • cr_addr (CellRangeAddress) – Cell range Address.

  • range_name (str) – Range Name such as ‘A1:D5’.

  • range_obj (RangeObj) – Range Object.

  • col_start (int) – Start Column.

  • row_start (int) – Start Row.

  • col_end (int) – End Column.

  • row_end (int) – End Row.

  • is_shift_right (bool) – If True then cell are inserted to the right; Otherwise, inserted down.

Events:
Returns:

True if cells are inserted; Otherwise, False.

Return type:

bool

Note

Events args for this method have a cell type of XCellRange

Event args event_data is a dictionary containing is_shift_right.

insert_column(idx, count=1)[source]

Inserts a column into spreadsheet.

Parameters:
  • idx (int) – Zero based index of column to insert.

  • count (int) – Number of columns to insert.

Events:
Returns:

True if column is inserted; Otherwise, False.

Return type:

bool

insert_row(idx, count=1)[source]

Inserts a row into spreadsheet.

Parameters:
  • idx (int) – Zero based index of row to insert.

  • count (int) – Number of rows to insert.

Events:
Returns:

True if row is inserted; Otherwise, False.

Return type:

bool

insert_scenario(range_name, vals, name, comment)[source]

Insert a scenario into sheet

Parameters:
  • range_name (str | RangeObj) – Range name

  • vals (Table) – 2d array of values

  • name (str) – Scenario name

  • comment (str) – Scenario description

Returns:

the newly created scenario

Return type:

XScenario

Note

A LibreOffice Calc scenario is a set of cell values that can be used within your calculations. You assign a name to every scenario on your sheet. Define several scenarios on the same sheet, each with some different values in the cells. Then you can easily switch the sets of cell values by their name and immediately observe the results. Scenarios are a tool to test out “what-if” questions.

See also

Using Scenarios

is_merged_cells(*, cell_range: com.sun.star.table.XCellRange)[source]
is_merged_cells(*, range_name: str)
is_merged_cells(*, range_obj: ooodev.utils.data_type.range_obj.RangeObj)
is_merged_cells(*, cr_addr: ooo.lo.table.cell_range_address.CellRangeAddress)
is_merged_cells(*, col_start: int, row_start: int)
is_merged_cells(**kwargs)

Gets is a range of cells is merged.

Parameters:
  • range_name (str) – Range Name such as A1:D5.

  • range_obj (RangeObj) – Range Object.

  • cr_addr (CellRangeAddress) – Cell range Address.

  • cell_range (XCellRange) – Cell Range.

  • col_start (int) – Start Column.

  • row_start (int) – Start Row.

  • col_end (int) – End Column.

  • row_end (int) – End Row.

Returns:

True if range is merged; Otherwise, False

Return type:

bool

is_sheet_protected()[source]

Gets whether a sheet is protected

Returns:

True if protected; Otherwise, False

Return type:

bool

New in version 0.10.0.

make_constraint(*, num: int | float, op: str)[source]
make_constraint(*, num: int | float, op: ooo.lo.sheet.solver_constraint_operator.SolverConstraintOperator)
make_constraint(*, num: int | float, op: str)
make_constraint(*, num: int | float, op: str)
make_constraint(*, num: int | float, op: ooo.lo.sheet.solver_constraint_operator.SolverConstraintOperator)
make_constraint(**kwargs)

Makes a constraint for a solver model.

Parameters:
  • num (Number) – Constraint number such as float or int.

  • op (str | SolverConstraintOperator) – Operation such as <=.

  • addr (CellAddress) – Cell Address.

  • cell_name (str) – Cell name such as A1.

  • cell_obj (CellObj) – Cell Object.

Returns:

Solver constraint that can be use in a solver model.

Return type:

SolverConstraint

Hint

  • SolverConstraintOperator can be imported from ooo.dyn.sheet.solver_constraint_operator

merge_cells(*, cell_range: com.sun.star.table.XCellRange)[source]
merge_cells(*, cell_range: com.sun.star.table.XCellRange, center: bool)
merge_cells(*, range_name: str)
merge_cells(*, range_name: str, center: bool)
merge_cells(*, range_obj: ooodev.utils.data_type.range_obj.RangeObj)
merge_cells(*, range_obj: ooodev.utils.data_type.range_obj.RangeObj, center: bool)
merge_cells(*, cr_addr: ooo.lo.table.cell_range_address.CellRangeAddress)
merge_cells(*, cr_addr: ooo.lo.table.cell_range_address.CellRangeAddress, center: bool)
merge_cells(*, col_start: int, row_start: int)
merge_cells(**kwargs)

Merges a range of cells

Parameters:
  • center (bool) – Determines if the merge will be a merge and center. Default False.

  • range_name (str) – Range Name such as A1:D5.

  • range_obj (RangeObj) – Range Object.

  • cr_addr (CellRangeAddress) – Cell range Address.

  • cell_range (XCellRange) – Cell Range.

  • col_start (int) – Start Column.

  • row_start (int) – Start Row.

  • col_end (int) – End Column.

  • row_end (int) – End Row.

Return type:

None

protect_sheet(password)[source]

Protects a Spreadsheet

Parameters:

password (str) – Password to protect sheet with.

Returns:

True on success; Otherwise, False

Return type:

bool

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_event_chart_data_change_event_events_disposing(cb)

Removes a listener for an event

Return type:

None

Parameters:

cb (Any) –

remove_event_chart_data_changed(cb)

Removes a listener for an event

Return type:

None

Parameters:

cb (Any) –

remove_event_modified(cb)

Removes a listener for an event

Return type:

None

Parameters:

cb (Any) –

remove_event_modify_events_disposing(cb)

Removes a listener for an event

Return type:

None

Parameters:

cb (Any) –

remove_event_observer(observer)

Removes an observer

Parameters:

observer (EventObserver) – One or more observers to add.

Returns:

True if observer has been removed; Otherwise, False.

Return type:

bool

remove_event_property_change(name)

Removes a listener for an event

Parameters:

name (str) – Property Name

Return type:

None

remove_event_property_change_events_disposing(name)

Removes a listener for an event

Parameters:

name (str) – Property Name

Return type:

None

remove_event_vetoable_change(name)

Removes a listener for an event

Parameters:

name (str) – Property Name

Return type:

None

remove_event_vetoable_change_events_disposing(name)

Removes a listener for an event

Parameters:

name (str) – Property Name

Return type:

None

rng(range_name: str)[source]
rng(cell_range: com.sun.star.table.XCellRange)
rng(cr_addr: ooo.lo.table.cell_range_address.CellRangeAddress)
rng(range_obj: ooodev.utils.data_type.range_obj.RangeObj)
rng(cell_obj: ooodev.utils.data_type.cell_obj.CellObj)
rng(cell_range: com.sun.star.table.XCellRange)
rng(col_start: int, row_start: int, col_end: int)
rng(*args, **kwargs)

Makes a range object.

Parameters:
  • sheet (XSpreadsheet) – Spreadsheet

  • range_name (str) – Range name such as ‘A1:D7’.

  • range_obj (RangeObj) – Range object.

  • start_col (int) – Zero-base start column index.

  • start_row (int) – Zero-base start row index.

  • end_col (int) – Zero-base end column index.

  • end_row (int) – Zero-base end row index.

Returns:

Range object.

Return type:

RangeObj

select_cells_addr(range_val)[source]

Selects cells in a Spreadsheet.

Parameters:

range_val (str | RangeObj) – Range name

Returns:

Cell range address of the current selection if successful, otherwise None

Return type:

CellRangeAddress | None

select_cells_calc_cell_range(range_val)[source]

Selects cells in a Spreadsheet.

Parameters:

range_val (str | RangeObj) – Range name

Returns:

Cell range of the current selection if successful, otherwise None

Return type:

CalcCellRange | None

select_cells_range(range_val)[source]

Selects cells in a Spreadsheet.

Parameters:

range_val (str | RangeObj) – Range name

Returns:

Cell range of the current selection if successful, otherwise None

Return type:

RangeObj | None

See also

set_active()[source]

Sets the current sheet as active in the document.

Return type:

None

set_array(*, values: Any, cell_range: com.sun.star.table.XCellRange)[source]
set_array(*, values: Any, cell_range: com.sun.star.table.XCellRange)
set_array(*, values: Any, name: str)
set_array(*, values: Any, name: str)
set_array(*, values: Any, range_obj: ooodev.utils.data_type.range_obj.RangeObj)
set_array(*, values: Any, range_obj: ooodev.utils.data_type.range_obj.RangeObj)
set_array(*, values: Any, cell_obj: ooodev.utils.data_type.cell_obj.CellObj)
set_array(*, values: Any, cell_obj: ooodev.utils.data_type.cell_obj.CellObj)
set_array(*, values: Any, addr: com.sun.star.table.CellAddress)
set_array(*, values: Any, addr: com.sun.star.table.CellAddress)
set_array(*, values: Any, col_start: int)
set_array(*, values: Any, col_start: int)
set_array(**kwargs)

Inserts array of data into spreadsheet

Parameters:
  • values (Table) – A 2-Dimensional array of value such as a list of list or tuple of tuples.

  • cell_range (XCellRange) – Range in spreadsheet to insert data.

  • name (str) – Range name such as ‘A1:D4’ or cell name such as ‘B4’.

  • range_obj (RangeObj) – Range Object.

  • cell_obj (CellObj) – Cell Object.

  • addr (CellAddress) – Address to insert data.

  • col_start (int) – Zero-base Start Column.

  • row_start (int) – Zero-base Start Row.

  • col_end (int) – Zero-base End Column.

  • row_end (int) – Zero-base End Row.

  • styles (Sequence[StyleT], optional) – One or more styles to apply to cell range.

Return type:

None

set_array_cell(range_name: str, values: Any)[source]
set_array_cell(range_name: str, values: Any, *, styles: Sequence[ooodev.proto.style_obj.StyleT])
set_array_cell(cell_obj: ooodev.utils.data_type.cell_obj.CellObj, values: Any)
set_array_cell(cell_obj: ooodev.utils.data_type.cell_obj.CellObj, values: Any, *, styles: Sequence[ooodev.proto.style_obj.StyleT])
set_array_cell(*args, **kwargs)

Inserts array of data into spreadsheet

Parameters:
  • range_name (str) – Range to insert data such as ‘A1:E12’.

  • cell_obj (CellObj) – Range Object.

  • values (Table) – A 2-Dimensional array of value such as a list of list or tuple of tuples.

  • styles (Sequence[StyleT], optional) – One or more styles to apply to cell range.

Return type:

None

Return type:

None

set_array_range(*, range_name: str, values: Any)[source]
set_array_range(*, range_name: str, values: Any)
set_array_range(*, range_obj: ooodev.utils.data_type.range_obj.RangeObj, values: Any)
set_array_range(*, range_obj: ooodev.utils.data_type.range_obj.RangeObj, values: Any)
set_array_range(**kwargs)

Inserts array of data into spreadsheet

Parameters:
  • sheet (XSpreadsheet) – Spreadsheet.

  • range_name (str) – Range to insert data such as ‘A1:E12’.

  • range_obj (RangeObj) – Range Object.

  • values (Table) – A 2-Dimensional array of value such as a list of list or tuple of tuples.

  • styles (Sequence[StyleT], optional) – One or more styles to apply to cell range.

Return type:

None

set_cell_range_array(cell_range: com.sun.star.table.XCellRange, values: Any) None[source]
set_cell_range_array(cell_range: com.sun.star.table.XCellRange, values: Any, styles: Sequence[ooodev.proto.style_obj.StyleT]) None
set_cell_range_array(cell_range, values, styles=None)

Inserts array of data into spreadsheet

Parameters:
  • cell_range (XCellRange) – Cell Range

  • values (Table) – A 2-Dimensional array of value such as a list of list or tuple of tuples.

  • styles (Sequence[StyleT], optional) – One or more styles to apply to cell range.

Return type:

None

set_col(values: Any, cell_name: str)[source]
set_col(values: Any, cell_obj: ooodev.utils.data_type.cell_obj.CellObj)
set_col(values: Any, col_start: int, row_start: int)
set_col(*args, **kwargs)

Inserts a column of data into spreadsheet.

Parameters:
  • sheet (XSpreadsheet) – Spreadsheet.

  • values (Column) – Column Data.

  • cell_name (str) – Name of Cell to begin the insert such as ‘A1’.

  • cell_obj (CellObj) – Cell Object.

  • col_start (int) – Zero-base column index.

  • row_start (int) – Zero-base row index.

Return type:

None

set_col_width(width, idx)[source]

Sets column width. width is in mm, e.g. 6

Parameters:
  • width (int, UnitT) – Width in mm units or Class UnitT.

  • idx (int) – Index of column.

Raises:

CancelEventError – If SHEET_COL_WIDTH_SETTING event is canceled.

Returns:

Column cell range that width is applied on or None if column width <= 0

Return type:

CalcTableCol | None

Events:

Note

Event args index is set to idx value, event_data is set to width value (mm100 units).

set_property(**kwargs)

Set property value

Parameters:

**kwargs (Any) – Variable length Key value pairs used to set properties.

Return type:

None

set_row(values: Any, cell_name: str)[source]
set_row(values: Any, cell_obj: ooodev.utils.data_type.cell_obj.CellObj)
set_row(values: Any, col_start: int, row_start: int)
set_row(*args, **kwargs)

Inserts a row of data into spreadsheet

Raises:

MissingInterfaceError – if unable to obtain interface

Parameters:
  • sheet (XSpreadsheet) – Spreadsheet.

  • values (Row) – Row Data.

  • cell_obj (CellObj) – Cell Object.

  • cell_name (str) – Name of Cell to begin the insert such as ‘A1’.

  • col_start (int) – Zero-base column index.

  • row_start (int) – Zero-base row index.

Return type:

None

set_row_height(height, idx)[source]

Sets column width. height is in mm, e.g. 6

Parameters:
  • height (int, UnitT) – Width in mm units or Class UnitT.

  • idx (int) – Index of Row

Raises:

CancelEventError – If SHEET_ROW_HEIGHT_SETTING event is canceled.

Returns:

Row cell range that height is applied on or None if height <= 0

Return type:

CalcTableRow | None

Events:

Note

Event args index is set to idx value, event_data is set to height value (mm100 units).

set_sheet_name(name)[source]

Sets the name of a spreadsheet.

Parameters:

name (str) – New name for spreadsheet.

Returns:

True on success; Otherwise, False

Return type:

bool

set_style_range(*, range_name: str, styles: Sequence[ooodev.proto.style_obj.StyleT])[source]
set_style_range(*, range_obj: ooodev.utils.data_type.range_obj.RangeObj, styles: Sequence[ooodev.proto.style_obj.StyleT])
set_style_range(*, cell_obj: ooodev.utils.data_type.cell_obj.CellObj, styles: Sequence[ooodev.proto.style_obj.StyleT])
set_style_range(*, cr_addr: ooo.lo.table.cell_range_address.CellRangeAddress, styles: Sequence[ooodev.proto.style_obj.StyleT])
set_style_range(*, cell_range: com.sun.star.table.XCellRange, styles: Sequence[ooodev.proto.style_obj.StyleT])
set_style_range(*, col_start: int, row_start: int)
set_style_range(**kwargs)

Set style/formatting on cell range.

Parameters:
  • range_name (str) – Range Name such as A1:D5.

  • range_obj (RangeObj) – Range Object.

  • cell_obj (CellObj) – Cell Object.

  • cr_addr (CellRangeAddress) – Cell range Address.

  • cell_range (XCellRange) – Cell Range. If passed in then the same instance is returned.

  • col_start (int) – Start Column.

  • row_start (int) – Start Row.

  • col_end (int) – End Column.

  • row_end (int) – End Row.

  • styles (Sequence[StyleT], optional) – One or more styles to apply to cell range.

Return type:

None

set_val(*, value: object, cell: com.sun.star.table.XCell)[source]
set_val(*, value: object, cell: com.sun.star.table.XCell)
set_val(*, value: object, cell_name: str)
set_val(*, value: object, cell_name: str)
set_val(*, value: object, cell_obj: ooodev.utils.data_type.cell_obj.CellObj)
set_val(*, value: object, cell_obj: ooodev.utils.data_type.cell_obj.CellObj)
set_val(*, value: object, col: int)
set_val(*, value: object, col: int)
set_val(**kwargs)

Sets the value of a cell

Parameters:
  • value (object) – Value for cell.

  • cell (XCell) – Cell to assign value.

  • cell_name (str) – Name of cell to set value of such as ‘B4’.

  • cell_obj (CellObj) – Cell Object.

  • col (int) – Cell column as zero-based integer.

  • row (int) – Cell row as zero-based integer.

  • styles (Sequence[StyleT], optional) – One or more styles to apply to cell.

Return type:

None

split_window(cell_name)[source]

Splits window

Parameters:

cell_name (str) – Cell to preform split on. e.g. ‘C4’

Return type:

None

subscribe_event(event_name, callback)

Add an event listener to current instance.

Parameters:
  • event_name (str) – Event Name.

  • callback (EventCallback) – Callback of the event listener.

Return type:

None

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

trigger_event(event_name, event_args)

Trigger an event on current instance.

Parameters:
  • event_name (str) – Event Name.

  • event_args (EventArgsT) – Event Args.

Return type:

None

unmerge_cells(*, cell_range: com.sun.star.table.XCellRange)[source]
unmerge_cells(*, range_name: str)
unmerge_cells(*, range_obj: ooodev.utils.data_type.range_obj.RangeObj)
unmerge_cells(*, cr_addr: ooo.lo.table.cell_range_address.CellRangeAddress)
unmerge_cells(*, col_start: int, row_start: int)
unmerge_cells(**kwargs)

Removes merging from a range of cells

Parameters:
  • range_name (str) – Range Name such as A1:D5.

  • range_obj (RangeObj) – Range Object.

  • cr_addr (CellRangeAddress) – Cell range Address.

  • cell_range (XCellRange) – Cell Range.

  • col_start (int) – Start Column.

  • row_start (int) – Start Row.

  • col_end (int) – End Column.

  • row_end (int) – End Row.

Return type:

None

unprotect_sheet(password)[source]

Unprotect a Spreadsheet.

If sheet is not protected, this method will still return True.

If incorrect password is provided, this method will return False.

Parameters:

password (str) – Password to unprotect sheet with.

Returns:

True on success; Otherwise, False

Return type:

bool

New in version 0.10.0.

unsubscribe_event(event_name, callback)

Remove an event listener from current instance.

Parameters:
  • event_name (str) – Event Name.

  • callback (EventCallback) – Callback of the event listener.

Return type:

None

property calc_doc: CalcDoc

Calc Document.

Return type:

CalcDoc

property calc_sheet: CalcSheet

Calc Sheet.

Return type:

CalcSheet

property charts: CalcCharts

Gets charts.

Returns:

Calc Charts

Return type:

CalcCharts

property code_name: str

Gets the code name of the sheet.

If the sheet is renamed this value remains the same. This value is used in macros and is persisted in the document.

Returns:

Code Name.

Return type:

str

Note

The code name may contain any character except for the following: []:*?/\.

Code name are added the the sheet when the sheet is created. If the sheet is renamed the code name will not change. Generally the code name will match the original sheet name.

New in version 0.44.1.

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

Spreadsheet Component

Return type:

Spreadsheet

property draw_page: SpreadsheetDrawPage[CalcSheet]

Gets draw page.

Returns:

Draw Page

Return type:

SpreadsheetDrawPage

property event_observer: EventObserver

Gets/Sets The Event Observer for this instance.

Return type:

EventObserver

property events_listener_chart_data_change_event: ChartDataChangeEventListener

Returns listener

Return type:

ChartDataChangeEventListener

property events_listener_modify: ModifyListener

Returns listener

Return type:

ModifyListener

property lo_inst: LoInst

Lo Instance

Return type:

LoInst

property name: str

Gets/Sets the sheet Name.

Returns:

Sheet name

Return type:

str

property office_doc: OfficeDocumentT

Office Document.

Return type:

OfficeDocumentT

property sheet_index: int

Gets the sheet index.

Returns:

Sheet index

Return type:

int

property sheet_name: str

Gets/Sets the sheet Name.

Returns:

Sheet name

Return type:

str

property unique_id: str

Gets the unique name of the sheet.

Returns:

Unique Name

Return type:

str

Note

The unique name is a is different then the sheet name and code_name. Unlike the code_name the unique name will only contain lower case alpha characters.

The unique name is not added to a sheet until this property is access for the first time. After a unique name is added to the sheet it will not change and is persisted with document saves. This means that the unique name will not change when the sheet is renamed or moved.

New in version 0.44.1.