¶
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 |