RPA.Excel.Application
module RPA.Excel.Application
class RPA.Excel.Application.Application
Excel.Application is a library for controlling the Excel application.
Examples
Robot Framework
Python
Caveats
This library works on a Windows operating system with UI enabled only, and you must
ensure that you open the app first with Open Application
before running any
other relevant keyword which requires to operate on an open app. The application is
automatically closed at the end of the task execution, so this can be changed by
importing the library with the autoexit=${False} setting.
If you’re running the Process by Control Room through a custom self-hosted Worker service, then please make sure that you enable an RDP session by ticking “Use Desktop Connection” under the Step configuration.
If you still encounter issues with opening a document, please ensure that file can be opened first manually and dismiss any alert potentially blocking the process.
Check the documentation below for more info:
- https://robocorp.com/docs/control-room/unattended/worker-setups/windows-desktop
- https://robocorp.com/docs/faq/windows-server-2016
variable APP_DISPATCH
variable ROBOT_LIBRARY_DOC_FORMAT
variable ROBOT_LIBRARY_SCOPE
method add_new_sheet
Add new worksheet to workbook. Workbook is created by default if it does not exist.
Parameters
- sheetname – name for sheet
- create_workbook – create workbook if True, defaults to True
- Raises: ValueError – error is raised if workbook does not exist and create_workbook is False
method add_new_workbook
Adds new workbook for Excel application
property app
method close_document
Close the active document and app (if open).
- Parameters: save_changes – Enable changes saving on quit. (False by default)
method create_pivot_field
Create pivot field object parameters.
Note. At the moment operation “DISTINCT COUNT” is not supported as there seems to be issues in the COM interface, which have not been resolved yet (regarding this library implementation).
Python example:
Robot Framework example:
Parameters
- data_column – name of the data column
- operation – name of the possible operations (SUM, AVERAGE, MAX, MIN, COUNT)
- numberformat – Excel cell number format, by default number format is not set for the field
- Returns: field object
method create_pivot_table
Create a pivot table in the specified worksheet.
This is a initial implementation of the pivot table creation, which might not work in all cases. The alternative way of creating pivot tables is to use a macro an run it.
Python example:
Robot Framework example:
Parameters
- source_worksheet – name of the source worksheet
- pivot_worksheet – name of the pivot worksheet, can be the same as the source worksheet but then cell location of the pivot table needs to be given in the format “R1C1” (R is a column numbe and C is a row number, e.g. “R1C1” is A1)
- rows – columns in the source_worksheet which are used as pivot table rows
- fields – columns for the pivot table data fields
- sort_field – field to sort the pivot table by (one of the fields)
- sort_direction – sort direction (ascending or descending), default is descending
- data_range – source data range, if not given then the whole used range of source_worksheet will be used
- pivot_name – name of the pivot table, if not given then the name is “PivotTable1”
- collapse_rows – if True then the first row will be collapsed
- show_grand_total – if True then the grand total will be shown for the columns
- Returns: created PivotTable object
method create_table
Create a table in the current worksheet.
Parameters
- table_name – name for the table
- table_range – source table range, if not given then the whole used range of source_worksheet will be used
method export_as_pdf
Export Excel as PDF file
If Excel filename is not given, the currently open workbook will be exported as PDF.
Parameters
- pdf_filename – PDF filename to save
- excel_filename – Excel filename to open
method find
Keyword for finding text in the current worksheet.
Wildcard can be used in a search string. The asterisk (*) represents any series of characters, and the question mark (?) represents a single character.
Python example:
Robot Framework example:
Parameters
- search_string – what to search for
- search_range – if not given will search the current worksheet
- max_results – can be used to limit number of results
- search_order – by default search is executed by ROWS, can be changed to COLUMNS
- match_case – if True then the search is case sensitive
- search_type – can be FORMULAS, VALUES, COMMENTS or COMMENTS THREADED
- search_after – search after this cell
- exact – if True then the search is expected to be a exact match
- Returns: list of Range objects
method find_first_available_cell
Find first available free cell
Parameters
- worksheet – worksheet to handle, defaults to active worksheet if None
- row – starting row for search, defaults to 1
- column – starting column for search, defaults to 1
- Returns: tuple (row, column) or (None, None) if not found
method find_first_available_row
Find first available free row
Parameters
- worksheet – worksheet to handle, defaults to active worksheet if None
- row – starting row for search, defaults to 1
- column – starting column for search, defaults to 1
- Returns: row or None
method get_pivot_tables
Return pivot tables in the current worksheet.
Python example:
Robot Framework example:
Parameters
- pivot_table_name – name of the pivot table to return, will return by default all pivot tables
- as_list – if True then the pivot table data is returned as list of lists, if False then the data is returned as list of Range objects
- Returns: dictionary of pivot tables (names as keys and table data as values)
method get_range
Get range object for the given range address.
These object properties and methods can be then called.
Python example:
Robot Framework example:
- Parameters: table_range – range to return
- Returns: range object
method list_tables
Return tables in the current worksheet.
- Returns: list of table names
method merge_range
Merges a range of cells.
- Parameters: initial_range – range of cells to merge
method open_application
Open the application.
Parameters
- visible – Show the window on opening. (False by default)
- display_alerts – Display alert popups. (False by default)
method open_workbook
Open Excel by filename
By default sets active worksheet to sheet number 1
- Parameters: filename – path to filename
method quit_application
Quit the application.
- Parameters: save_changes – Enable to save changes on quit. (False by default)
method read_from_cells
Read value from cell.
Parameters
- worksheet – worksheet to handle, defaults to active worksheet if None
- row – target row, defaults to None
- column – target row, defaults to None
- Raises: ValueError – if cell is not given
method remove_hidden_columns_and_rows
Removes hidden columns and rows from a range and returns a new range.
Parameters
- initial_range – range of cells to remove hidden columns and rows from
- worksheet – set active worksheet (name) before removing hidden columns and rows
- Returns: new range or initial range if no hidden cells found
method run_macro
Run Excel macro with given name
Parameters
- macro_name – macro to run
- args – arguments to pass to macro
method save_excel
Saves Excel file
method save_excel_as
Save Excel with name if workbook is open
Parameters
- filename – where to save file
- autofit – autofit cell widths if True, defaults to False
- file_format – format of file
Note: Changing the file extension for the path does not
affect the actual format. To use an older format, use
the file_format
argument with one of the following values:
https://docs.microsoft.com/en-us/office/vba/api/excel.xlfileformat
method set_active_worksheet
Set active worksheet by either its sheet number or name
Parameters
- sheetname – name of Excel sheet, defaults to None
- sheetnumber – index of Excel sheet, defaults to None
method set_object_property
Set the property of any object.
This is a utility keyword for Robot Framework syntax to set object property values.
Parameters
- object_instance – object instance to set the property
- property_name – property name to set
- value – value to set
method unmerge_range
Unmerges a range of cells.
- Parameters: initial_range – range of cells to unmerge
method write_data_to_range
Writes data to the specified range(s) in the Excel worksheet.
The range width should match the number of columns in the data.
Multiple ranges can be specified by separating them with a semicolon, but still the total width of ranges should match the number of columns in the data.
Python example:
Robot Framework example:
Parameters
- target_range – A1 string presentation of the range(s) to write or Range object.
- values – Table or list of lists to write to the range(s).
- log_warnings – on False will suppress logging warning, default is True (warnings are logged)
method write_to_cells
Write value, number_format and/or formula into cell.
Parameters
- worksheet – worksheet to handle, defaults to active worksheet if None
- row – target row, defaults to None
- column – target row, defaults to None
- value – possible value to set, defaults to None
- number_format – possible number format to set, defaults to None
- formula – possible format to set, defaults to None
- Raises: ValueError – if cell is not given