RPA.Excel.Application

module RPA.Excel.Application

class RPA.Excel.Application.Application

Application(*args, **kwargs)

Excel.Application is a library for controlling the Excel application.

Examples

Robot Framework

Python

from RPA.Excel.Application import Application app = Application() app.open_application() app.open_workbook('workbook.xlsx') app.set_active_worksheet(sheetname='new stuff') app.write_to_cells(row=1, column=1, value='new data') app.save_excel() app.quit_application()

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:

variable APP_DISPATCH

APP_DISPATCH = 'Excel.Application'

variable ROBOT_LIBRARY_DOC_FORMAT

ROBOT_LIBRARY_DOC_FORMAT = 'REST'

variable ROBOT_LIBRARY_SCOPE

ROBOT_LIBRARY_SCOPE = 'GLOBAL'

method add_new_sheet

add_new_sheet(sheetname: str, create_workbook: bool = True)

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

add_new_workbook()

Adds new workbook for Excel application

property app

property app

method close_document

close_document(save_changes: bool = False)

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(data_column: str, operation: str, numberformat: Optional[str] = None)

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:

field_count = excel.create_pivot_field("price", "count", "#") field_avg = excel.create_pivot_field("price", "average", "#0,#0")

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_pivot_table(source_worksheet: str, pivot_worksheet: str, rows: List[str], fields: List[PivotField], sort_field: Optional[PivotField] = None, sort_direction: str = 'descending', data_range: Optional[Any] = None, pivot_name: str = 'PivotTable1', collapse_rows: bool = True, show_grand_total: bool = True)

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:

rows = ["products", "expense_type"] field_count = excel.create_pivot_field("price", "count", "#") field_avg = excel.create_pivot_field("price", "average", "#0,#0") pivottable = excel.create_pivot_table( source_worksheet="data", pivot_worksheet="test!R5C5", rows=rows, fields=[field_count, field_avg] )

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_table(table_name: str, table_range: Optional[Any] = None)

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_as_pdf(pdf_filename: str, excel_filename: Optional[str] = None)

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

find(search_string: str, search_range: Optional[Any] = None, max_results: Optional[int] = None, search_order: SearchOrder = SearchOrder.ROWS, match_case: bool = False, search_type: Optional[str] = None, search_after: Optional[str] = None, exact: bool = False)

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:

ranges = excel.find("32.145.???.1", "IP!E1:E9999", 6) for r in ranges: print(f"ADDR = {r.Address} VALUE = {r.Value}") r.Value = r.Value.replace("32.145.", "192.168.") r.BorderAround()

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_cell(worksheet: Any = None, row: int = 1, column: int = 1)

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_row(worksheet: Optional[Any] = None, row: int = 1, column: int = 1)

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

get_pivot_tables(pivot_table_name: Optional[str] = None, as_list: bool = True)

Return pivot tables in the current worksheet.

Python example:

from RPA.Tables import Tables pivot_tables = excel.get_pivot_tables() for tbl_name, tbl_list in pivot_tables.items(): print(f"TABLE NAME: {tbl_name}") table = Tables().create_table(data=tbl_list[1:], columns=tbl_list[0]) print(table)

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(table_range: str)

Get range object for the given range address.

These object properties and methods can be then called.

Python example:

source = excel.get_range('A1:B2') for r in source: print(f"ADDR = {r.Address} VAL = {r.Value}") r.BorderAround() source.Merge() # Creating a formula and copying it to another range excel.get_range("E4").Formula = "=SUM(C4:D4)" destination = excel.get_range("E5:E10") excel.get_range("E4").Copy(destination)

Robot Framework example:

  • Parameters: table_range – range to return
  • Returns: range object

method list_tables

list_tables()

Return tables in the current worksheet.

  • Returns: list of table names

method merge_range

merge_range(initial_range: Any)

Merges a range of cells.

  • Parameters: initial_range – range of cells to merge

method open_application

open_application(visible: bool = False, display_alerts: bool = False)

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_workbook(filename: str)

Open Excel by filename

By default sets active worksheet to sheet number 1

  • Parameters: filename – path to filename

method quit_application

quit_application(save_changes: bool = False)

Quit the application.

  • Parameters: save_changes – Enable to save changes on quit. (False by default)

method read_from_cells

read_from_cells(worksheet: Any = None, row: int = None, column: int = None)

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

remove_hidden_columns_and_rows(initial_range: Any, worksheet: Optional[str] = None)

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_macro(macro_name: str, *args: Any)

Run Excel macro with given name

Parameters
  • macro_name – macro to run
  • args – arguments to pass to macro

method save_excel

save_excel()

Saves Excel file

method save_excel_as

save_excel_as(filename: str, autofit: bool = False, file_format=None)

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(sheetname: str = None, sheetnumber: int = None)

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_object_property(object_instance, property_name: str, value: str)

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

unmerge_range(initial_range: Any)

Unmerges a range of cells.

  • Parameters: initial_range – range of cells to unmerge

method write_data_to_range

write_data_to_range(target_range: Any, values: Union[Table, List[List]], log_warnings: bool = True)

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:

from RPA.Tables import Tables from RPA.Excel.Application import Application excel = Application() table = Tables().read_table_from_csv("input.csv", header=True) excel.open_workbook("result.xslx) excel.write_data_to_range("A2:P100", table)

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_to_cells(worksheet: Any = None, row: int = None, column: int = None, value: str = None, number_format: str = None, formula: str = None)

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