¶
botcity.plugins.excel.plugin.BotExcelPlugin
¶
__init__(self, active_sheet=None)
special
¶
Class stores the data in a Excel-like (sheets) format.
This plugin supports multiple sheets into a object of this class. To access sheets other than the first, either pass the sheet index or name, or change the default sheet this class will point to with the set_active_sheet() method.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
active_sheet |
str, Optional |
The name of the sheet this class will be created with. Defaults to 'sheet1'. |
None |
Attributes:
| Name | Type | Description |
|---|---|---|
active_sheet |
str, Optional |
The default sheet this class's methods will work with. Defaults to 'sheet1'. |
active_sheet(self)
¶
Return to active sheet.
Returns:
| Type | Description |
|---|---|
str |
Active sheet name |
add_column(self, column, sheet=None)
¶
Add a new column to the sheet.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
column |
List[object] |
A list of cells. |
required |
sheet |
str, Optional |
If a sheet is provided, it'll be used by this method instead of the Active Sheet. |
None |
Defaults to None.
Returns:
| Type | Description |
|---|---|
BotExcelPlugin |
self (allows Method Chaining). |
add_columns(self, columns, sheet=None)
¶
Add new columns to the sheet.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
columns |
List[List[object]] |
A list of columns. Each column is a list of cells. |
required |
sheet |
str, Optional |
If a sheet is provided, it'll be used by this method instead of the Active Sheet. |
None |
Defaults to None.
Returns:
| Type | Description |
|---|---|
BotExcelPlugin |
self (allows Method Chaining). |
add_row(self, row, sheet=None)
¶
Add a new row to the bottom of the sheet.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
row |
List[object] |
A list of cell values. |
required |
sheet |
str, Optional |
If a sheet is provided, it'll be used by this method instead of the Active Sheet. Defaults to None. |
None |
Returns:
| Type | Description |
|---|---|
BotExcelPlugin |
self (allows Method Chaining). |
add_rows(self, rows, sheet=None)
¶
Add new rows to the sheet.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
rows |
List[List[object]] |
A list of rows. |
required |
sheet |
str, Optional |
If a sheet is provided, it'll be used by this method instead of the Active Sheet. |
None |
Defaults to None.
Returns:
| Type | Description |
|---|---|
BotExcelPlugin |
self (allows Method Chaining). |
as_dataframe(self, sheet=None)
¶
Return the contents of an entire sheet in a Pandas DataFrame format.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
sheet |
str, Optional |
If a sheet is provided, it'll be used by this method instead of the Active Sheet. Defaults to None. |
None |
Returns:
| Type | Description |
|---|---|
DataFrame |
A Pandas DataFrame object. |
as_list(self, sheet=None)
¶
Return the contents of an entire sheet in a list of lists format.
This is equivalent to get_range("", sheet).
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
sheet |
str, Optional |
If a sheet 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, sheet=None)
¶
Delete the entire content of the sheet.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
sheet |
str, Optional |
If a sheet is provided, it'll be used by this method instead of the Active Sheet. |
None |
Defaults to None.
Returns:
| Type | Description |
|---|---|
BotExcelPlugin |
self (allows Method Chaining). |
clear_range(self, range_=None, sheet=None)
¶
Clear the provided area of the sheet.
Keep in mind that this method will not remove any rows or columns, only erase their values.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
range_ |
str, Optional |
The range to be cleared, in A1 format. Example: 'A1:B2', 'B', '3', 'A1'. If None, the entire sheet will be used as range. Defaults to None. |
None |
sheet |
str, Optional |
If a sheet is provided, it'll be used by this method instead of the Active Sheet. Defaults to None. |
None |
Returns:
| Type | Description |
|---|---|
BotExcelPlugin |
self (allows Method Chaining) |
create_sheet(self, sheet)
¶
Create a new sheet.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
sheet |
str |
The new sheet's name. |
required |
Returns:
| Type | Description |
|---|---|
BotExcelPlugin |
self (allows Method Chaining) |
get_cell(self, column, row, sheet=None)
¶
Return the value of a single cell.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
column |
str |
The letter-indexed column name ('a', 'A', 'AA'). |
required |
row |
int |
The 1-indexed row number. |
required |
sheet |
str, Optional |
If a sheet 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.
Please note that altering the values in this list will not alter the values in the original sheet.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
column |
str |
The letter-indexed column name ('a', 'A', 'AA'). |
required |
sheet |
str, Optional |
If a sheet 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_range(self, range_, sheet=None)
¶
Return the values of all cells within an area of the sheet in a list of list format.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
range_ |
str |
The range (minus the sheet) to be retrieved, in a A1 format. Example: 'A1:B2', 'B', '3', 'A1'. |
required |
sheet |
str, Optional |
If a sheet 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 with the recovered rows. Each row is a list of objects. |
get_row(self, row, sheet=None)
¶
Return the contents of an entire row in a list format.
Please note that altering the values in this list will not alter the values in the original sheet.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
row |
int |
The 1-indexed number of the row to be removed. |
required |
sheet |
str, Optional |
If a sheet 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. |
list_sheets(self)
¶
Return a list with the name of all the sheets in this spreadsheet.
Returns:
| Type | Description |
|---|---|
List[str] |
A list of sheet names. |
read(self, file_or_path)
¶
Read an Excel file.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
file_or_path |
Either a buffered Excel file or a path to it. |
required |
Returns:
| Type | Description |
|---|---|
BotExcelPlugin |
self (allows Method Chaining). |
remove_column(self, column, sheet=None)
¶
Remove single column from the sheet.
Keep in mind that the columns to its right will be moved to the left.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
column |
str |
The letter-indexed name ('a', 'A', 'AA') of the column to be removed. |
required |
sheet |
str, Optional |
If a sheet is provided, it'll be used by this method instead of the Active Sheet. Defaults to None. |
None |
Returns:
| Type | Description |
|---|---|
BotExcelPlugin |
self (allows Method Chaining). |
remove_columns(self, columns, sheet=None)
¶
Remove a list of columns from the sheet.
Keep in mind that each column removed will cause the columns to their right to be moved left after they are all removed.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
columns |
List[str] |
A list of the letter-indexed names of the columns to be removed. |
required |
sheet |
str, Optional |
If a sheet is provided, it'll be used by this method instead of the Active Sheet. Defaults to None. |
None |
Returns:
| Type | Description |
|---|---|
BotExcelPlugin |
self (allows Method Chaining). |
remove_row(self, row, sheet=None)
¶
Remove a single row from the sheet.
Keep in mind that the rows below will be moved up.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
row |
int |
The 1-indexed number of the row to be removed. |
required |
sheet |
str, Optional |
If a sheet is provided, it'll be used by this method instead of the Active Sheet. Defaults to None. |
None |
Returns:
| Type | Description |
|---|---|
BotExcelPlugin |
self (allows Method Chaining). |
remove_rows(self, rows, sheet=None)
¶
Remove rows from the sheet.
Keep in mind that each row removed will cause the rows below it to be moved up after they are all removed.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
rows |
List[int] |
A list of the 1-indexed numbers of the rows to be removed. |
required |
sheet |
str, Optional |
If a sheet is provided, it'll be used by this method instead of the Active Sheet. Defaults to None. |
None |
Returns:
| Type | Description |
|---|---|
BotExcelPlugin |
self (allows Method Chaining) |
remove_sheet(self, sheet)
¶
Remove a sheet.
Keep in mind that if you remove the active_sheet, you must set another sheet as active before using trying to modify it!
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
sheet |
str |
The sheet's name. |
required |
Returns:
| Type | Description |
|---|---|
BotExcelPlugin |
self (allows Method Chaining) |
rename_sheet(self, new_name, sheet)
¶
Rename a sheet.
Keep in mind that in doing so the new sheet will be reordered to the last position.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
new_name |
str |
The sheet will be renamed to this. |
required |
sheet |
str, Optional |
If a sheet is provided, it'll be used by this method instead of the Active Sheet. Defaults to None. |
required |
Returns:
| Type | Description |
|---|---|
BotExcelPlugin |
self (allows Method Chaining) |
set_active_sheet(self, sheet=None)
¶
Set to active sheet.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
sheet |
str, Optional |
If a sheet is provided, it'll be used by this method instead of the Active Sheet. Defaults to None. |
None |
Returns:
| Type | Description |
|---|---|
BotExcelPlugin |
self (allows Method Chaining) |
set_cell(self, column, row, value, sheet=None)
¶
Replace 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 |
value |
object |
The new value of the cell. |
required |
sheet |
str, Optional |
If a sheet is provided, it'll be used by this method instead of the Active Sheet. Defaults to None. |
None |
Returns:
| Type | Description |
|---|---|
BotExcelPlugin |
self (allows Method Chaining) |
set_nan_as(self, value='', sheet=None)
¶
Set the NaN values.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
value |
str or int |
(str, Optional): The value to replace the NaN values. Defaults to "" |
'' |
sheet |
str, Optional |
If a sheet is provided, it'll be used by this method instead of the Active Sheet. Defaults to None. |
None |
Returns:
| Type | Description |
|---|---|
BotExcelPlugin |
self (allows Method Chaining). |
set_range(self, values, range_=None, sheet=None)
¶
Replace the values within an area of the sheet by the values supplied.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
values |
List[List[object]] |
A list of rows. Each row is list of cell values. |
required |
range_ |
str, Optional |
The range (minus the sheet) to have its values replaced, in A1 format. Ex: 'A1:B2', 'B', '3', 'A1'. If None, the entire sheet will be used as range. Defaults to None. |
None |
sheet |
str |
(str, Optional): If a sheet is provided, it'll be used by this method instead of the Active Sheet. Defaults to None. |
None |
Returns:
| Type | Description |
|---|---|
BotExcelPlugin |
self (allows Method Chaining) |
sort(self, by_columns, ascending=True, start_row=2, end_row=None, sheet=None)
¶
Sorts the sheet's rows according to the columns provided.
Unless the start and end point are provided, all rows minus the first one will be sorted!
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
by_columns |
Union[str, List[str]] |
Either a letter-indexed column name to sort the rows by, or a list of them. In case of a tie, the second column is used, and so on. |
required |
ascending |
bool, Optional |
Set to False to sort by descending order. Defaults to True. |
True |
start_row |
str, Optional |
The 1-indexed row number where the sort will start from. Defaults to 2. |
2 |
end_row |
str, Optional |
The 1-indexed row number where the sort will end at (inclusive). Defaults to None. |
None |
sheet |
str, Optional |
If a sheet is provided, it'll be used by this method instead of the Active Sheet. Defaults to None. |
None |
Returns:
| Type | Description |
|---|---|
BotExcelPlugin |
self (allows Method Chaining) |
write(self, file_or_path)
¶
Write this class's content to a file.
Parameters:
| Name | Type | Description | Default |
|---|---|---|---|
file_or_path |
Either a buffered Excel file or a path to it. |
required |
Returns:
| Type | Description |
|---|---|
BotExcelPlugin |
self (allows Method Chaining). |