Skip to content

Excel actions

Excel actions exposed to the robot allow opening, reading, editing and saving Excel files.

Important note: only methods decorated with @decorators.robotaction are user actions and documented here. Action returns are mapped to robot state variables — e.g. if an action returns {'workbook': workbook} you access it in the flow with $workbook.

Exposed methods

Each section describes the action, parameters, return keys (which become state variables) and notes.

excel.open

Open an Excel file and (optionally) try to update calculated values by opening Excel locally before loading.

Parameters: - filepath — path to the file in assets. - use_formula (optional, default False) — when True returns formulas instead of evaluated values. - auto_update (optional, default True) — when True attempts to open Excel to refresh formulas before loading. - excel_visible (optional, default False) — when True shows the Excel application during update.

Returns: - workbook — openpyxl Workbook object. - worksheet — active Worksheet.

Notes / Exceptions: - FileNotFoundException if the file does not exist in assets. - InvalidExcelFile when the file is in an incompatible or corrupted format (e.g. .xls). Use excel.convert_to_xlsx when applicable.

excel.create_new

Create a new blank workbook.

Parameters: none.

Returns: - workbook — new Workbook. - worksheet — active Worksheet created.

Note: use excel.save to persist to disk.

excel.list_sheets

Return sheet names and active sheet.

Parameters: - workbook — Workbook object.

Returns: - sheets — list of sheet names. - active_sheet — name of active sheet.

excel.create_sheet

Create a new sheet in the workbook.

Parameters: - workbook — Workbook. - sheet_name — new sheet name.

Returns: - workbook — updated Workbook. - worksheet — the created Worksheet.

excel.rename_sheet

Rename given worksheet.

Parameters: - worksheet — Worksheet to rename. - new_name — new name.

Returns: - worksheet — updated Worksheet.

excel.select_sheet

Select a sheet from the workbook.

Parameters: - workbook — Workbook. - sheet_name (optional) — sheet name; if omitted returns the active sheet.

Returns: - workbook — Workbook (same object provided). - worksheet — selected Worksheet.

excel.get_cell

Return a cell or range content.

Parameters: - worksheet — Worksheet. - range — Excel range string (e.g. A1 or A1:C3).

Returns: - first_col, first_row, last_col, last_row, values — values is a matrix with rows and columns (e.g. values[0][1]).

Exception: - InvalidRange if the range is invalid (implementation detail).

excel.update_cell

Update a cell value.

Parameters: - worksheet — Worksheet. - value — value to insert. - cell — cell reference (e.g. A1).

Returns: - same format as excel.get_cell (first_col, first_row, last_col, last_row, values).

excel.append

Append a single row or multiple rows to the worksheet.

Parameters: - worksheet — Worksheet. - line_values (optional) — list representing a row. - grid_values (optional) — list of lists to append multiple rows.

Returns: none.

excel.save

Save workbook to disk.

Parameters: - workbook — Workbook. - filepath — destination path (including filename).

Returns: none.

excel.last_line

Return the last used row number.

Parameters: - worksheet — Worksheet.

Returns: - last_line — integer with last row number.

excel.get_dimensions

Return the used range of the sheet.

Parameters: - worksheet — Worksheet.

Returns: - range — Excel range string (e.g. A1:F150). - first_cell — first cell reference. - last_cell — last cell reference.

excel.convert_to_xlsx

Convert a file (e.g. .xls) to .xlsx. Requires Excel installed on the robot host.

Parameters: - filepath — path in assets. - excel_visible (optional) — shows Excel during conversion.

Returns: none. The converted file is created next to the original with .xlsx extension.

excel.delete_rows

Remove rows from start to end inclusive. If end is omitted only start is removed.

Parameters: - worksheet, start, end (optional).

Returns: none.

excel.execute_macro

Execute a macro via COM (requires Excel installed).

Parameters: - file_path — file containing the macro. - macro — macro identifier/name. - save_after_execution (optional, default True) — save after execution.

Returns: none. Macro errors may be raised.

excel.create_style_template

Create a reusable style template.

Parameters: - font_name, font_size, bold, italic, underline, font_color, background_color, border_style, border_color.

Returns: - template — object usable by excel.set_template.

excel.set_template

Apply a template (from excel.create_style_template) to a range.

Parameters: - worksheet, range, template.

Returns: none.

excel.set_table_format

Apply header/odd/even line styles to a range.

Parameters: - worksheet, range, header_style, odd_line_style, even_line_style (optional).

Returns: none.

Exception: WrongVarType if range is not a valid range.

excel.set_style

Apply style properties to a cell or range.

Parameters: - worksheet, range and style properties (font_name, font_size, bold, italic, underline, font_color, background_color, border_style, border_color).

Returns: none.

If you want, I can add short practical examples for each action (how to call and how to read the state variables), or propose small safe code improvements in excel.py (error handling, cleanup of imports, etc.).

Excel Actions

Excel Actions are actions to interact with Excel files.

Actions

excel.append

Append new lines to the end of spreadsheet.

Parameters:

worksheet - worksheet to be used for cell getting.

line_values (optional) - array with each index been a different column, example: 

['Col 1', 'Col 2', 'Col 3']

grid_values (optional) - a grid where each line will represent a line and each line element will represent a column, example: 

[
    ['Line 1 - Col1', 'Line 1 - Col2', 'Line 1 - Col3'],
    ['Line 2 - Col1', 'Line 2 - Col2', 'Line 2 - Col3']
]

Returns:

This action has no return.

Exceptions:

This action returns no Exception

excel.convert_to_xlsx

Convert any kind of file that Excel could open (for example: XLS, CSV), to XLSX file. The converted file will be saved at the same path and with the same name, with .xlsx extension.

Important: To use this action, the Excel software must be installed on the machine and all Excel windows should be closed.

Parameters:

filepath - Path to the file that will be converted.

Returns:

This action has no return.

Exceptions:

This action returns no Exception

excel.create_new

Create a completelly new spreadsheet.

Important: to save this spreadsheet on a folder, the excel.save action should be called.

Parameters:

This actions has no parameters.

Returns:

workbook - Workbook to be used with other Excel actions

worksheet - Active worksheet of the file

Exceptions:

This action returns no Exception

excel.create_sheet

Create a new sheet on Excel file (Workbook).

Important: to save this new sheet folder, the excel.save action should be called.

Parameters:

workbook - Workbook that has been updated.

sheet_name - name to be given to the new sheet

Returns:

workbook - Workbook to be used with other Excel actions

worksheet - Active worksheet of the file

Exceptions:

This action returns no Exception

excel.delete_rows

Deletes the entire rows of between the start row number and the end row number.

Parameters:

worksheet - Worksheet to be used for deleting rows.

start - number of the first row to be deleted.

end (optional) - number of the last row to be deleted. If just a single row need to be deleted, this parameter doesn't need to be informed.

Returns:

This action has no return.

Exceptions:

This action returns no Exception

excel.get_cell

Returns the value of a cell or a range of cells.

Parameters:

worksheet - Worksheet to be used for cell getting.

range - Range of the cells to get value using Excel pattern, ex: A1:C3

Returns:

first_col - Letter of the first column returned

first_row - Number of the first row returned

last_col - Letter of the last column returned

last_row - Number of the last row returned

values - Array of values, where the first index if the row, and the second if the column, ex: values[0][1] will get the values of the first line, second column.

Exceptions:

InvalidRange: when a wrong or invalid range if given

excel.get_dimensions

Return the worksheet dimensions as Excel range.

Parameters:

workshet - Worksheet to return the last line.

Returns:

range - Range of the worksheet used area, as Excel pattern. Ex: A1: F150.

first_cell - Reference of the first cell on range, ex: A1.

last_cell - Reference of the last cell on range, ex: F150.

Exceptions:

This action returns no Exception

excel.last_line

Return the last line of the worksheet

Parameters:

workshet - Worksheet to return the last line.

Returns:

last_line - Number of the last line of the worksheet

Exceptions:

This action returns no Exception

excel.open

Open an given Excel file to start interacting with other Excel Actions.

Parameters:

filepath - path to Excel file.

Returns:

workbook - Workbook to be used with other Excel actions

worksheet - Active worksheet of the file

Exceptions:

FileNotFoundException: if the file path is wrong or does not exists.

excel.save

Saves the Workbook with all updates.

Parameters:

workbook - Workbook that has been updated.

filepath - File name with full file path, to save location.

Returns:

This actions has no returns

Exceptions:

FileNotFoundException: if the given file path could not be used.

excel.select_sheet

Selects a new sheet to use with Excel file.

Parameters:

workbook - Workbook returned be excel.open.

sheet_name (optional) - Name of the sheet to be returned. If no sheet name is given will return the active sheet

Returns:

workbook - Workbook to be used with other Excel actions

worksheet - Worksheet with the given name, or active worksheet of the file if no sheet_name is given.

Exceptions:

This action doesn't return any exceptions

excel.update_cell

Updates the value of a given cell on Excel file.

Parameters:

worksheet - Worksheet to be used for cell getting.

value - Value to be inserted on the given cell.

cell - Reference of the cell, using Excel pattern, ex: A1

Returns:

first_col - Letter of the first column returned

first_row - Number of the first row returned

last_col - Letter of the last column returned

last_row - Number of the last row returned

values - Array of values, where the first index if the row, and the second if the column, ex: values[0][1] will get the values of the first line, second column.

Exceptions:

InvalidRange: when a wrong or invalid range if given

excel.create_style_template

Creates a styled template for excel.

Parameters:

font_name - Name of the font.

font_size - Size of the font.

bold - Boolean value for bold.

italic - Boolean value for italic.

underline - Boolean value for underline.

font_color - Color of the font.

background_color - Color for the background.

border_style - Style for the border.

border_color - Color for the border.

Returns:

template - returns the excel template

Exceptions:

This action doesn't return any exceptions

excel.execute_macro

Executes macro.

Parameters:

file_path - The path for the file.

macro - macro that will be executed.

save_after_execution - Boolean to save after execution.

Returns:

This action returns void.

Exceptions:

This action doesn't return any exceptions

excel.list_sheets

Returns a list of all sheets of the file and which one is active.

Parameters:

workbook - The workbook to check.

Returns:

sheets - Workbook sheet names.

active_sheet - The active sheet from the workbook.

Exceptions:

This action doesn't return any exceptions

excel.rename_sheet

Renames the given sheet.

Parameters:

worksheet - The worksheet to be renamed.

new_name - A new name to be given to worksheet.

Returns:

worksheet - A worksheet updated with new name.

Exceptions:

This action doesn't return any exceptions

excel.set_style

Sets a style for a worksheet.

Parameters:

worksheet - The worksheet to set the style.

range - The range that will be set the style.

font_name - The name of the font.

font_size - The size of the font.

bold - Boolean value for bold.

italic - Boolean value for italic.

underline - It can be any of the following 'single', 'singleAccounting', 'doubleAccounting', 'double'.

font_color - The color of the font.

background_color - The color of the background.

border_style - It can be any of the following 'thin', 'none', 'double', 'thick', 'dashed', 'medium', 'dotted'.

border_color - The color of the border.

Returns:

This action returns void.

Exceptions:

This action doesn't return any exceptions.

excel.set_table_format

Sets a format for the table.

Parameters:

worksheet - The worksheet that has the table to be formatted.

range - The range that the table will be formatted.

header_style - The name of the font.

odd_line_style - The style for the odd line.

even_line_style - The style for the even line.

Returns:

This action returns void.

Exceptions:

WrongVarType: if the given var is invalid.

excel.set_template

Sets the template for the worksheet.

Parameters:

worksheet - The worksheet that the template will be set at.

range - The range that the worksheet will have the template set.

template - The template to be set.

Returns:

This action returns void.

Exceptions:

This action doesn't return any exceptions.