Saltar a contenido

botcity.plugins.ms365.excel.plugin.MS365ExcelPlugin

active_sheet: WorkSheet property writable

The reference to the active Worksheet.

You can use this property to perform operations directly on this worksheet.

excel_file: WorkBook property writable

The Workbook element referencing the Excel file.

You can use this property to access Excel functionality.

__init__(self, service_account) special

MS365ExcelPlugin.

Parameters:

Name Type Description Default
service_account MS365CredentialsPlugin

The authenticated Microsoft365 account. The authentication process must be done through the credentials plugin.

 required

add_column(self, column_values, col_range='', sheet=None)

Add a new column to the worksheet.

You can add a new column at the right end of the worksheet or at a specific range.

Parameters:

Name Type Description Default
column_values List[object]

A list with the cell values.

 required
col_range str

The column range to be used, in A1 format. Example: 'B1:B10'. If no range is specified, the new column will be added at the right end.

 ''
sheet Worksheet

If a worksheet is provided, it'll be used by this method instead of the active_sheet. Defaults to None.

 None

add_row(self, row_values, row_range='', sheet=None)

Add a new row to the worksheet.

You can add a new row at the end of the worksheet or at a specific range.

Parameters:

Name Type Description Default
row_values List[object]

A list with the cell values.

 required
row_range str

The row range to be used, in A1 format. Example: 'A3:D3'. If no range is specified, the new row will be added at the end.

 ''
sheet Worksheet

If a worksheet is provided, it'll be used by this method instead of the active_sheet. Defaults to None.

 None

as_list(self, sheet=None)

Return the contents of an entire sheet in a list of lists format.

Parameters:

Name Type Description Default
sheet Worksheet

If a worksheet is provided, it'll be used by this method instead of the active_sheet. Defaults to None.

 None

Returns:

Type Description
List[List[object]]

A list of rows. Each row is a list of cell values.

clear(self, range_='', only_values=True, sheet=None)

Clear a specific range or the entire worksheet content.

Parameters:

Name Type Description Default
range_ str

The range to be cleared, in A1 format. Example: 'A1:B2'. If no range is informed, the entire worksheet content will be considered.

 ''
only_values(bool, optional

If True, only the values will be removed and the sheet formatting will remain. If False, values and formatting will be removed.

 required
sheet Worksheet

If a worksheet is provided, it'll be used by this method instead of the active_sheet. Defaults to None.

 None

create_sheet(self, sheet_name)

Create a new worksheet.

Parameters:

Name Type Description Default
sheet_name str

The name of the worksheet to be created.

 required

get_cell(self, column, row, sheet=None)

Return the value of a single cell.

Parameters:

Name Type Description Default
column str

The cell's letter-indexed column name ('a', 'A', 'AA').

 required
row int

The cell's 1-indexed row number.

 required
sheet Worksheet

If a worksheet is provided, it'll be used by this method instead of the active_sheet. Defaults to None.

 None

Returns:

Type Description
object

The cell's value.

get_column(self, column, sheet=None)

Return the contents of an entire column in a list format.

Parameters:

Name Type Description Default
column str

The letter-indexed column name ('a', 'A', 'AA').

 required
sheet Worksheet

If a worksheet is provided, it'll be used by this method instead of the active_sheet. Defaults to None.

 None

Returns:

Type Description
List[object]

The values of all cells within the column.

get_excel_file(self, file_or_path, active_sheet='')

Get the Excel file using the File object or the file path in Drive.

The file path must be used in the pattern: /path/to/file

Parameters:

Name Type Description Default
file_or_path File | str

The file path to fetch or the File object.

 required
active_sheet str

The name of the worksheet to be used by default. Defaults to the first sheet.

 ''

get_range(self, range_, sheet=None)

Return the contents of a specific range.

Parameters:

Name Type Description Default
range_ str

The range to be used, in A1 format. Example: 'A1:B2'.

 required
sheet Worksheet

If a worksheet is provided, it'll be used by this method instead of the active_sheet. Defaults to None.

 None

Returns:

Type Description
List[List[object]]

A list of rows. Each row is a list of cell values.

get_row(self, row, sheet=None)

Return the contents of an entire row in a list format.

Parameters:

Name Type Description Default
row int

The 1-indexed row number.

 required
sheet Worksheet

If a worksheet is provided, it'll be used by this method instead of the active_sheet. Defaults to None.

 None

Returns:

Type Description
List[object]

The values of all cells within the row.

get_worksheet(self, sheet_name)

Get a Worksheet object using it's name.

You can use this object to set the active sheet.

Parameters:

Name Type Description Default
sheet_name str

The name of the worksheet to be fetched.

 required

Returns:

Type Description
Worksheet

The Worksheet object.

list_sheets(self)

Return a list with the Worksheet object for each worksheet in the file.

Returns:

Type Description
List[Worksheet]

A list of Worksheet objects.

remove_column(self, column, sheet=None)

Remove a single column from the worksheet.

Parameters:

Name Type Description Default
column str

The letter-indexed column name ('a', 'A', 'AA').

 required
sheet Worksheet

If a worksheet is provided, it'll be used by this method instead of the active_sheet. Defaults to None.

 None

remove_columns(self, columns, sheet=None)

Remove columns from the sheet.

Parameters:

Name Type Description Default
columns List[str]

A list of the letter-indexed names of the columns to be removed.

 required
sheet Worksheet

If a worksheet is provided, it'll be used by this method instead of the active_sheet. Defaults to None.

 None

remove_row(self, row, sheet=None)

Remove a single row from the worksheet.

Parameters:

Name Type Description Default
row int

The 1-indexed row number.

 required
sheet Worksheet

If a worksheet is provided, it'll be used by this method instead of the active_sheet. Defaults to None.

 None

remove_rows(self, rows, sheet=None)

Remove rows from the sheet.

Parameters:

Name Type Description Default
rows List[int]

A list of the 1-indexed numbers of the rows to be removed.

 required
sheet Worksheet

If a worksheet is provided, it'll be used by this method instead of the active_sheet. Defaults to None.

 None

remove_sheet(self, sheet_name)

Remove a worksheet.

Keep in mind that if you remove the active_sheet, you must set another sheet as active.

Parameters:

Name Type Description Default
sheet_name str

The name of the worksheet to be removed.

 required

set_active_sheet(self, sheet_name='')

Set the active worksheet that will be used by default.

If no worksheet is informed, the first worksheet in the file will be considered.

Parameters:

Name Type Description Default
sheet_name str

The name of the worksheet to be used.

 ''

set_cell(self, column, row, value, sheet=None)

Insert a value in a single cell.

Parameters:

Name Type Description Default
column str

The cell's letter-indexed column name ('a', 'A', 'AA').

 required
row int

The cell's 1-indexed row number.

 required
value object

The value to be entered into the cell.

 required
sheet Worksheet

If a worksheet is provided, it'll be used by this method instead of the active_sheet. Defaults to None.

 None

set_range(self, range_, values, sheet=None)

Insert values in a given range.

Parameters:

Name Type Description Default
range_ str

The range to be used, in A1 format. Example: 'A1:B2'.

 required
values List[List[object]]

A list of rows. Each row is a list of cell values.

 required
sheet Worksheet

If a worksheet is provided, it'll be used by this method instead of the active_sheet. Defaults to None.

 None