RPA.Excel.Application
Add new worksheet to workbook. Workbook is created by default if it does not exist.
Arguments
Argument | Type | Default value |
---|---|---|
sheetname | str | null |
create_workbook | bool | True |
param sheetname: | name for sheet |
---|---|
param create_workbook: | create workbook if True, defaults to True |
raises ValueError: | error is raised if workbook does not exist and create_workbook is False |
Adds new workbook for Excel application
Close the active document and app (if open).
Arguments
Argument | Type | Default value |
---|---|---|
save_changes | bool | False |
param save_changes: | Enable changes saving on quit. (False by default) |
---|
Create pivot field object parameters.
Arguments
Argument | Type | Default value |
---|---|---|
data_column | str | null |
operation | str | null |
numberformat | str, None | None |
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:
param data_column: | name of the data column |
---|---|
param operation: | name of the possible operations (SUM, AVERAGE, MAX, MIN, COUNT) |
param numberformat: | Excel cell number format, by default number format is not set for the field |
return: | field object |
Create a pivot table in the specified worksheet.
Arguments
Argument | Type | Default value |
---|---|---|
source_worksheet | str | null |
pivot_worksheet | str | null |
rows | List[str] | null |
fields | List[PivotField] | null |
sort_field | PivotField, None | None |
sort_direction | str | descending |
data_range | Any, None | None |
pivot_name | str | PivotTable1 |
collapse_rows | bool | True |
show_grand_total | bool | True |
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:
param source_worksheet: | name of the source worksheet |
---|---|
param 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) |
param rows: | columns in the source_worksheet which are used as pivot table rows |
param fields: | columns for the pivot table data fields |
param sort_field: | field to sort the pivot table by (one of the fields) |
param sort_direction: | sort direction (ascending or descending), default is descending |
param data_range: | source data range, if not given then the whole used range of source_worksheet will be used |
param pivot_name: | name of the pivot table, if not given then the name is "PivotTable1" |
param collapse_rows: | if True then the first row will be collapsed |
param show_grand_total: | if True then the grand total will be shown for the columns |
return: | created PivotTable object |
Create a table in the current worksheet.
Arguments
Argument | Type | Default value |
---|---|---|
table_name | str | null |
table_range | Any, None | None |
param table_name: | name for the table |
---|---|
param table_range: | source table range, if not given then the whole used range of source_worksheet will be used |
Export Excel as PDF file
Arguments
Argument | Type | Default value |
---|---|---|
pdf_filename | str | null |
excel_filename | str, None | None |
If Excel filename is not given, the currently open workbook will be exported as PDF.
param pdf_filename: | PDF filename to save |
---|---|
param excel_filename: | Excel filename to open |
Keyword for finding text in the current worksheet.
Arguments
Argument | Type | Default value |
---|---|---|
search_string | str | null |
search_range | Any, None | None |
max_results | int, None | None |
search_order | SearchOrder | ROWS |
match_case | bool | False |
search_type | str, None | None |
search_after | str, None | None |
exact | bool | False |
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:
param search_string: | what to search for |
---|---|
param search_range: | if not given will search the current worksheet |
param max_results: | can be used to limit number of results |
param search_order: | by default search is executed by ROWS, can be changed to COLUMNS |
param match_case: | if True then the search is case sensitive |
param search_type: | can be FORMULAS, VALUES, COMMENTS or COMMENTS THREADED |
param search_after: | search after this cell |
param exact: | if True then the search is expected to be a exact match |
return: | list of Range objects |
Find first available free cell
Arguments
Argument | Type | Default value |
---|---|---|
worksheet | Any | None |
row | int | 1 |
column | int | 1 |
param worksheet: | worksheet to handle, defaults to active worksheet if None |
---|---|
param row: | starting row for search, defaults to 1 |
param column: | starting column for search, defaults to 1 |
return: | tuple (row, column) or (None, None) if not found |
Find first available free row
Arguments
Argument | Type | Default value |
---|---|---|
worksheet | Any, None | None |
row | int | 1 |
column | int | 1 |
param worksheet: | worksheet to handle, defaults to active worksheet if None |
---|---|
param row: | starting row for search, defaults to 1 |
param column: | starting column for search, defaults to 1 |
return: | row or None |
Return pivot tables in the current worksheet.
Arguments
Argument | Type | Default value |
---|---|---|
pivot_table_name | str, None | None |
as_list | bool | True |
Python example:
Robot Framework example:
param pivot_table_name: | name of the pivot table to return, will return by default all pivot tables |
---|---|
param 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 |
return: | dictionary of pivot tables (names as keys and table data as values) |
Get range object for the given range address.
Arguments
Argument | Type | Default value |
---|---|---|
table_range | str | null |
These object properties and methods can be then called.
Python example:
Robot Framework example:
param table_range: | range to return |
---|---|
return: | range object |
Return tables in the current worksheet.
return: | list of table names |
---|
Merges a range of cells.
Arguments
Argument | Type | Default value |
---|---|---|
initial_range | Any | null |
param initial_range: | range of cells to merge |
---|
Open the application.
Arguments
Argument | Type | Default value |
---|---|---|
visible | bool | False |
display_alerts | bool | False |
param visible: | Show the window on opening. (False by default) |
---|---|
param display_alerts: | Display alert popups. (False by default) |
Open Excel by filename
Arguments
Argument | Type | Default value |
---|---|---|
filename | str | null |
By default sets active worksheet to sheet number 1
param filename: | path to filename |
---|
Quit the application.
Arguments
Argument | Type | Default value |
---|---|---|
save_changes | bool | False |
param save_changes: | Enable to save changes on quit. (False by default) |
---|
Read value from cell.
Arguments
Argument | Type | Default value |
---|---|---|
worksheet | Any | None |
row | int | None |
column | int | None |
param worksheet: | worksheet to handle, defaults to active worksheet if None |
---|---|
param row: | target row, defaults to None |
param column: | target row, defaults to None |
raises ValueError: | if cell is not given |
Run Excel macro with given name
Arguments
Argument | Type | Default value |
---|---|---|
macro_name | str | null |
args | Any | null |
param macro_name: | macro to run |
---|---|
param args: | arguments to pass to macro |
Saves Excel file
Save Excel with name if workbook is open
Arguments
Argument | Type | Default value |
---|---|---|
filename | str | null |
autofit | bool | False |
file_format | None |
param filename: | where to save file |
---|---|
param autofit: | autofit cell widths if True, defaults to False |
param 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
Examples:
Set active worksheet by either its sheet number or name
Arguments
Argument | Type | Default value |
---|---|---|
sheetname | str | None |
sheetnumber | int | None |
param sheetname: | name of Excel sheet, defaults to None |
---|---|
param sheetnumber: | index of Excel sheet, defaults to None |
Set the property of any object.
Arguments
Argument | Type | Default value |
---|---|---|
object_instance | null | |
property_name | str | null |
value | str | null |
This is a utility keyword for Robot Framework syntax to set object property values.
param object_instance: | object instance to set the property |
---|---|
param property_name: | property name to set |
param value: | value to set |
Unmerges a range of cells.
Arguments
Argument | Type | Default value |
---|---|---|
initial_range | Any | null |
param initial_range: | range of cells to unmerge |
---|
Writes data to the specified range(s) in the Excel worksheet.
Arguments
Argument | Type | Default value |
---|---|---|
target_range | Any | null |
values | Table, List[List] | null |
log_warnings | bool | True |
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:
param target_range: | A1 string presentation of the range(s) to write or Range object. |
---|---|
param values: | Table or list of lists to write to the range(s). |
param log_warnings: | on False will suppress logging warning, default is True (warnings are logged) |
Write value, number_format and/or formula into cell.
Arguments
Argument | Type | Default value |
---|---|---|
worksheet | Any | None |
row | int | None |
column | int | None |
value | str | None |
number_format | str | None |
formula | str | None |
param worksheet: | worksheet to handle, defaults to active worksheet if None |
---|---|
param row: | target row, defaults to None |
param column: | target row, defaults to None |
param value: | possible value to set, defaults to None |
param number_format: | possible number format to set, defaults to None |
param formula: | possible format to set, defaults to None |
raises ValueError: | if cell is not given |