Saltar a contenido

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).