Excel

2019-04-15 Note: Support for workbooks stored in OneDrive Consumer platform is still not available. At this time, only the files stored in business platform is supported by Excel REST APIs.

exception O365.excel.FunctionException[source]

class O365.excel.NamedRange(parent=None, session=None, **kwargs)[source]

Represents a defined name for a range of cells or value

__init__(parent=None, session=None, **kwargs)[source]

Object initialization

Parameters:

protocol (Protocol) – A protocol class or instance to be used with this connection
main_resource (str) – main_resource to be used in these API communications

comment

The comment associated with this name.

Type: str

data_type

The type of reference is associated with the name. Possible values are: String, Integer, Double, Boolean, Range.

Type: str

get_range()[source]: Returns the Range instance this named range refers to

name

The name of the object.

Type: str

object_id

Id of the named range

Type: str

scope

Indicates whether the name is scoped to the workbook or to a specific worksheet.

Type: str

update(*, visible=None, comment=None)[source]: Updates this named range :param bool visible: Specifies whether the object is visible or not :param str comment: Represents the comment associated with this name :return: Success or Failure

value

The formula that the name is defined to refer to. For example, =Sheet14!$B$2:$H$12 and =4.75.

Type: str

visible

Indicates whether the object is visible.

Type: bool

class O365.excel.Range(parent=None, session=None, **kwargs)[source]

An Excel Range

__init__(parent=None, session=None, **kwargs)[source]

Object initialization

Parameters:

protocol (Protocol) – A protocol class or instance to be used with this connection
main_resource (str) – main_resource to be used in these API communications

address

Represents the range reference in A1-style. Address value contains the Sheet reference (for example, Sheet1!A1:B4).

Type: str

address_local

Represents range reference for the specified range in the language of the user.

Type: str

cell_count

Number of cells in the range.

Type: int

clear(apply_to='all')[source]

Clear range values, format, fill, border, etc.

Parameters:: apply_to (str) – Optional. Determines the type of clear action. The possible values are: all, formats, contents.

column_count

Represents the total number of columns in the range.

Type: int

property column_hidden

Indicates whether all columns of the current range are hidden.

Getter:: get the column_hidden
Setter:: set the column_hidden
Type:: bool

column_index

Represents the column number of the first cell in the range. Zero-indexed.

Type: int

delete(shift='up')[source]

Deletes the cells associated with the range.

Parameters:: shift (str) – Optional. Specifies which way to shift the cells. The possible values are: up, left.

property formulas

Represents the formula in A1-style notation.

Getter:: get the formulas
Setter:: set the formulas
Type:: any

property formulas_local

Represents the formula in A1-style notation, in the user’s language and number-formatting locale. For example, the English “=SUM(A1, 1.5)” formula would become “=SUMME(A1; 1,5)” in German.

Getter:: get the formulas_local
Setter:: set the formulas_local
Type:: list[list]

property formulas_r1_c1

Represents the formula in R1C1-style notation.

Getter:: get the formulas_r1_c1
Setter:: set the formulas_r1_c1
Type:: list[list]

get_bounding_rect(address)[source]: Gets the smallest range object that encompasses the given ranges. For example, the GetBoundingRect of “B2:C5” and “D10:E15” is “B2:E16”. :param str address: another address to retrieve it’s bounding rect

get_cell(row, column)[source]: Gets the range object containing the single cell based on row and column numbers. :param int row: the row number :param int column: the column number :return: a Range instance

get_column(index)[source]: Returns a column whitin the range :param int index: the index of the column. zero indexed :return: a Range

get_columns_after(columns=1)[source]: Gets a certain number of columns to the right of the given range. :param int columns: Optional. The number of columns to include in the resulting range.

get_columns_before(columns=1)[source]: Gets a certain number of columns to the left of the given range. :param int columns: Optional. The number of columns to include in the resulting range.

get_entire_column()[source]: Gets a Range that represents the entire column of the range.

get_format()[source]: Returns a RangeFormat instance with the format of this range

get_intersection(address)[source]

Gets the Range that represents the rectangular intersection of the given ranges.

Parameters:: address – the address range you want ot intersect with.
Returns:: Range

get_last_cell()[source]: Gets the last cell within the range.

get_last_column()[source]: Gets the last column within the range.

get_last_row()[source]: Gets the last row within the range.

get_offset_range(row_offset, column_offset)[source]

Gets an object which represents a range that’s offset from the specified range.: The dimension of the returned range will match this range. If the resulting range is forced outside the bounds of the worksheet grid, an exception will be thrown.

Parameters:

row_offset (int) – The number of rows (positive, negative, or 0) by which the range is to be offset.
column_offset (int) – he number of columns (positive, negative, or 0) by which the range is to be offset.

Returns:

Range

get_resized_range(rows, columns)[source]

Gets a range object similar to the current range object, but with its bottom-right corner expanded (or contracted) by some number of rows and columns.

Parameters:

rows (int) – The number of rows by which to expand the bottom-right corner, relative to the current range.
columns (int) – The number of columns by which to expand the bottom-right corner, relative to the current range.

Returns:

Range

get_row(index)[source]: Gets a row contained in the range. :param int index: Row number of the range to be retrieved. :return: Range

get_rows_above(rows=1)[source]

Gets a certain number of rows above a given range.

Parameters:: rows (int) – Optional. The number of rows to include in the resulting range.
Returns:: Range

get_rows_below(rows=1)[source]

Gets a certain number of rows below a given range.

Parameters:: rows (int) – Optional. The number of rows to include in the resulting range.
Returns:: Range

get_used_range(only_values=True)[source]

Returns the used range of the given range object.

Parameters:: only_values (bool) – Optional. Defaults to True. Considers only cells with values as used cells (ignores formatting).
Returns:: Range

get_worksheet()[source]: Returns this range worksheet

hidden

Represents if all cells of the current range are hidden.

Type: bool

insert_range(shift)[source]

Inserts a cell or a range of cells into the worksheet in place of this range, and shifts the other cells to make space.

Parameters:: shift (str) – Specifies which way to shift the cells. The possible values are: down, right.
Returns:: new Range instance at the now blank space

merge(across=False)[source]

Merge the range cells into one region in the worksheet.

Parameters:: across (bool) – Optional. Set True to merge cells in each row of the specified range as separate merged cells.

property number_format

Represents Excel’s number format code for the given cell.

Getter:: get the number_format
Setter:: set the number_fromat
Type:: list[list]

object_id

The id of the range.

Type: str

row_count

Returns the total number of rows in the range.

Type: int

property row_hidden

Indicates whether all rows of the current range are hidden.

Getter:: get the row_hidden
Setter:: set the row_hidden
Type:: bool

row_index

Returns the row number of the first cell in the range. Zero-indexed.

Type: int

text

Text values of the specified range.

Type: str

to_api_data(restrict_keys=None)[source]

Returns a dict to communicate with the server

Parameters:: restrict_keys – a set of keys to restrict the returned data to
Return type:: dict

unmerge()[source]: Unmerge the range cells into separate cells.

update()[source]: Update this range

value_types

Represents the type of data of each cell. The possible values are: Unknown, Empty, String, Integer, Double, Boolean, Error.

Type: list[list]

property values

Represents the raw values of the specified range. The data returned can be of type string, number, or a Boolean. Cell that contains an error returns the error string.

Getter:: get the number_format
Setter:: set the number_fromat
Type:: list[list]

class O365.excel.RangeFormat(parent=None, session=None, **kwargs)[source]

A format applied to a range

__init__(parent=None, session=None, **kwargs)[source]

Object initialization

Parameters:

protocol (Protocol) – A protocol class or instance to be used with this connection
main_resource (str) – main_resource to be used in these API communications

auto_fit_columns()[source]: Changes the width of the columns of the current range to achieve the best fit, based on the current data in the columns

auto_fit_rows()[source]: Changes the width of the rows of the current range to achieve the best fit, based on the current data in the rows

property background_color

The background color of the range

Getter:: get the background_color
Setter:: set the background_color
Type:: UnsentSentinel

property column_width

The width of all columns within the range

Getter:: get the column_width
Setter:: set the column_width
Type:: float

property font

Returns the font object defined on the overall range selected

Getter:: get the font
Setter:: set the font
Type:: RangeFormatFont

property horizontal_alignment

The horizontal alignment for the specified object. Possible values are: General, Left, Center, Right, Fill, Justify, CenterAcrossSelection, Distributed.

Getter:: get the vertical_alignment
Setter:: set the vertical_alignment
Type:: string

range

The range of the range format.

Type: range

property row_height

The height of all rows in the range.

Getter:: get the row_height
Setter:: set the row_height
Type:: float

session

The session for the range format.

Type: str

set_borders(side_style='')[source]: Sets the border of this range

to_api_data(restrict_keys=None)[source]

Returns a dict to communicate with the server

Parameters:: restrict_keys – a set of keys to restrict the returned data to
Return type:: dict

update()[source]: Updates this range format

property vertical_alignment

The vertical alignment for the specified object. Possible values are: Top, Center, Bottom, Justify, Distributed.

Getter:: get the vertical_alignment
Setter:: set the vertical_alignment
Type:: string

property wrap_text

Indicates whether Excel wraps the text in the object

Getter:: get the wrap_text
Setter:: set the wrap_text
Type:: bool

class O365.excel.RangeFormatFont(parent)[source]

A font format applied to a range

__init__(parent)[source]

property bold

property color

The color of the range format font

Getter:: get the color
Setter:: set the color
Type:: str

property italic

Is range format font in italics

Getter:: get the italic
Setter:: set the italic
Type:: bool

property name

The name of the range format font

Getter:: get the name
Setter:: set the name
Type:: str

parent

The parent of the range format font.

Type: parent

property size

The size of the range format font

Getter:: get the size
Setter:: set the size
Type:: int

to_api_data(restrict_keys=None)[source]

Returns a dict to communicate with the server

Parameters:: restrict_keys – a set of keys to restrict the returned data to
Return type:: dict

property underline

Is range format font underlined

Getter:: get the underline
Setter:: set the underline
Type:: bool

class O365.excel.Table(parent=None, session=None, **kwargs)[source]

An Excel Table

__init__(parent=None, session=None, **kwargs)[source]

Object initialization

Parameters:

protocol (Protocol) – A protocol class or instance to be used with this connection
main_resource (str) – main_resource to be used in these API communications

add_column(name, *, index=0, values=None)[source]: Adds a column to the table :param str name: the name of the column :param int index: the index at which the column should be added. Defaults to 0. :param list values: a two dimension array of values to add to the column

add_rows(values=None, index=None)[source]

Add rows to this table.

Multiple rows can be added at once. This request might occasionally receive a 504 HTTP error. The appropriate response to this error is to repeat the request.

Parameters:

values (list) – Optional. a 1 or 2 dimensional array of values to add
index (int) – Optional. Specifies the relative position of the new row. If null, the addition happens at the end.

Returns:

clear_filters()[source]: Clears all the filters currently applied on the table.

convert_to_range()[source]: Converts the table into a normal range of cells. All data is preserved.

delete()[source]: Deletes this table

delete_column(id_or_name)[source]: Deletes a Column by its id or name :param id_or_name: the id or name of the column :return bool: Success or Failure

delete_row(index)[source]: Deletes a Row by it’s index :param int index: the index of the row. zero indexed :return bool: Success or Failure

get_column(id_or_name)[source]: Gets a column from this table by id or name :param id_or_name: the id or name of the column :return: WorkBookTableColumn

get_column_at_index(index)[source]: Returns a table column by it’s index :param int index: the zero-indexed position of the column in the table

get_columns(*, top=None, skip=None)[source]: Return the columns of this table :param int top: specify n columns to retrieve :param int skip: specify n columns to skip

get_data_body_range()[source]: Gets the range object associated with the data body of the table

get_header_row_range()[source]: Gets the range object associated with the header row of the table

get_range()[source]: Gets the range object associated with the entire table

get_row(index)[source]: Returns a Row instance at an index

get_row_at_index(index)[source]: Returns a table row by it’s index :param int index: the zero-indexed position of the row in the table

get_rows(*, top=None, skip=None)[source]: Return the rows of this table :param int top: specify n rows to retrieve :param int skip: specify n rows to skip :rtype: TableRow

get_total_row_range()[source]: Gets the range object associated with the totals row of the table

get_worksheet()[source]: Returns this table worksheet

highlight_first_column

Indicates whether the first column contains special formatting.

Type: bool

highlight_last_column

Indicates whether the last column contains special formatting.

Type: bool

legacy_id

A legacy identifier used in older Excel clients.

Type: str

name

The name of the table.

Type: str

object_id

The unique identifier for the table in the workbook.

Type: str

parent

Parent of the table.

Type: parent

reapply_filters()[source]: Reapplies all the filters currently on the table.

session

Session of the table.

Type: session

show_banded_columns

Indicates whether the columns show banded formatting in which odd columns are highlighted differently from even ones to make reading the table easier.

Type: bool

show_banded_rows

The name of the table column.

Type: str

show_filter_button

Indicates whether the rows show banded formatting in which odd rows are highlighted differently from even ones to make reading the table easier.

Type: bool

show_headers

Indicates whether the header row is visible or not

Type: bool

show_totals

Indicates whether the total row is visible or not.

Type: bool

style

A constant value that represents the Table style

Type: str

update(*, name=None, show_headers=None, show_totals=None, style=None)[source]: Updates this table :param str name: the name of the table :param bool show_headers: whether or not to show the headers :param bool show_totals: whether or not to show the totals :param str style: the style of the table :return: Success or Failure

class O365.excel.TableColumn(parent=None, session=None, **kwargs)[source]

An Excel Table Column

__init__(parent=None, session=None, **kwargs)[source]

Object initialization

Parameters:

protocol (Protocol) – A protocol class or instance to be used with this connection
main_resource (str) – main_resource to be used in these API communications

apply_filter(criteria)[source]

Apply the given filter criteria on the given column.

Parameters:

criteria (str) –

the criteria to apply

Example:

{
    "color": "string",
    "criterion1": "string",
    "criterion2": "string",
    "dynamicCriteria": "string",
    "filterOn": "string",
    "icon": {"@odata.type": "microsoft.graph.workbookIcon"},
    "values": {"@odata.type": "microsoft.graph.Json"}
}

clear_filter()[source]: Clears the filter applied to this column

delete()[source]: Deletes this table Column

get_data_body_range()[source]: Gets the range object associated with the data body of the column

get_filter()[source]: Returns the filter applie to this column

get_header_row_range()[source]: Gets the range object associated with the header row of the column

get_range()[source]: Gets the range object associated with the entire column

get_total_row_range()[source]: Gets the range object associated with the totals row of the column

index

TThe index of the column within the columns collection of the table. Zero-indexed.

Type: int

name

The name of the table column.

Type: str

object_id: Id of the Table Column|br| Type: str

session

session of the table column..

Type: session

table

Parent of the table column.

Type: parent

update(values)[source]: Updates this column :param values: values to update

values

Represents the raw values of the specified range. The data returned could be of type string, number, or a Boolean. Cell that contain an error will return the error string.

Type: list[list]

class O365.excel.TableRow(parent=None, session=None, **kwargs)[source]

An Excel Table Row

__init__(parent=None, session=None, **kwargs)[source]

Object initialization

Parameters:

protocol (Protocol) – A protocol class or instance to be used with this connection
main_resource (str) – main_resource to be used in these API communications

delete()[source]: Deletes this row

get_range()[source]: Gets the range object associated with the entire row

index

The index of the row within the rows collection of the table. Zero-based.

Type: int

object_id

Id of the Table Row

Type: str

session

Session of table row

Type: session

table

Parent of the table row.

Type: parent

update(values)[source]: Updates this row

values

The raw values of the specified range. The data returned could be of type string, number, or a Boolean. Any cell that contain an error will return the error string.

Type: list[list]

class O365.excel.WorkBook(file_item, *, use_session=True, persist=True)[source]

__init__(file_item, *, use_session=True, persist=True)[source]

Create a workbook representation

Parameters:

file_item (File) – the Drive File you want to interact with
use_session (Bool) – Whether or not to use a session to be more efficient
persist (Bool) – Whether or not to persist this info

add_named_range(name, reference, comment='', is_formula=False)[source]: Adds a new name to the collection of the given scope using the user’s locale for the formula :param str name: the name of this range :param str reference: the reference for this range or formula :param str comment: a comment to describe this named range :param bool is_formula: True if the reference is a formula :return: NamedRange instance

add_worksheet(name=None)[source]: Adds a new worksheet

delete_worksheet(worksheet_id)[source]: Deletes a worksheet by it’s id

get_named_range(name)[source]: Retrieves a Named range by it’s name

get_named_ranges()[source]: Returns the list of named ranges for this Workbook

get_table(id_or_name)[source]: Retrieves a Table by id or name :param str id_or_name: The id or name of the column :return: a Table instance

get_tables()[source]: Returns a collection of this workbook tables

get_workbookapplication()[source]

get_worksheet(id_or_name)[source]: Gets a specific worksheet by id or name

get_worksheets()[source]: Returns a collection of this workbook worksheets

invoke_function(function_name, **function_params)[source]: Invokes an Excel Function

name

The name of the workbook.

Type:**str

object_id

The id of the workbook.

Type: str**

session

The session for the workbook.

Type: WorkbookSession

class O365.excel.WorkSheet(parent=None, session=None, **kwargs)[source]

An Excel WorkSheet

__init__(parent=None, session=None, **kwargs)[source]

Object initialization

Parameters:

protocol (Protocol) – A protocol class or instance to be used with this connection
main_resource (str) – main_resource to be used in these API communications

add_named_range(name, reference, comment='', is_formula=False)[source]: Adds a new name to the collection of the given scope using the user’s locale for the formula :param str name: the name of this range :param str reference: the reference for this range or formula :param str comment: a comment to describe this named range :param bool is_formula: True if the reference is a formula :return: NamedRange instance

add_table(address, has_headers)[source]: Adds a table to this worksheet :param str address: a range address eg: ‘A1:D4’ :param bool has_headers: if the range address includes headers or not :return: a Table instance

delete()[source]: Deletes this worksheet

get_cell(row, column)[source]: Gets the range object containing the single cell based on row and column numbers.

get_named_range(name)[source]: Retrieves a Named range by it’s name

get_range(address=None)[source]: Returns a Range instance from whitin this worksheet :param str address: Optional, the range address you want :return: a Range instance

get_table(id_or_name)[source]: Retrieves a Table by id or name :param str id_or_name: The id or name of the column :return: a Table instance

get_tables()[source]: Returns a collection of this worksheet tables

get_used_range(only_values=True)[source]

Returns the smallest range that encompasses any cells that have a value or formatting assigned to them.

Parameters:: only_values (bool) – Optional. Defaults to True. Considers only cells with values as used cells (ignores formatting).
Returns:: Range

name

The display name of the worksheet.

Type: str

object_id

The unique identifier for the worksheet in the workbook.

Type: str

position

The zero-based position of the worksheet within the workbook.

Type: int

static remove_sheet_name_from_address(address)[source]: Removes the sheet name from a given address

session

Thesession of the worksheet.

Type: session

update(*, name=None, position=None, visibility=None)[source]: Changes the name, position or visibility of this worksheet

visibility

The visibility of the worksheet. The possible values are: Visible, Hidden, VeryHidden.

Type: str

workbook

The parent of the worksheet.

Type: parent

class O365.excel.WorkbookApplication(workbook)[source]

__init__(workbook)[source]

Create A WorkbookApplication representation

Parameters:: workbook – A workbook object, of the workboook that you want to interact with

get_details()[source]: Gets workbookApplication

parent

The application parent.

Type: Workbook

run_calculations(calculation_type)[source]: Recalculate all currently opened workbooks in Excel.

class O365.excel.WorkbookSession(*, parent=None, con=None, persist=True, **kwargs)[source]

See https://docs.microsoft.com/en-us/graph/api/resources/excel?view=graph-rest-1.0#sessions-and-persistence

__init__(*, parent=None, con=None, persist=True, **kwargs)[source]

Create a workbook session object.

Parameters:

parent – parent for this operation
con (Connection) – connection to use if no parent specified
persist (Bool) – Whether or not to persist the session changes

close_session()[source]: Close the current session

create_session()[source]: Request a new session id

delete(*args, **kwargs)[source]

get(*args, **kwargs)[source]

inactivity_limit

The inactivity limit.

Type: timedelta

last_activity

The time of last activity.

Type: datetime

patch(*args, **kwargs)[source]

persist

Whether or not the session changes are persisted.

Type: bool

post(*args, **kwargs)[source]

prepare_request(kwargs)[source]: If session is in use, prepares the request headers and checks if the session is expired.

put(*args, **kwargs)[source]

refresh_session()[source]: Refresh the current session id

session_id

The session id.

Type: str