Python API

Excel.Application

class RPA.Excel.Application.Application(*args, **kwargs)

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

Examples

Robot Framework

*** Settings ***
Library             RPA.Excel.Application
Task Setup          Open Application
Task Teardown       Quit Application

*** Tasks ***
Manipulate Excel application
    Open Workbook           workbook.xlsx
    Set Active Worksheet    sheetname=new stuff
    Write To Cells          row=1
    ...                     column=1
    ...                     value=my data
    Save Excel

Run Excel Macro
    Open Workbook   orders_with_macro.xlsm
    Run Macro       Sheet1.CommandButton1_Click

Export Workbook as PDF
    Open Workbook           workbook.xlsx
    Export as PDF           workbook.pdf

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.

*** Settings ***
Library     RPA.Excel|Outlook|Word.Application    autoexit=${False}

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

APP_DISPATCH = 'Excel.Application'

ROBOT_LIBRARY_DOC_FORMAT = 'REST'

ROBOT_LIBRARY_SCOPE = 'GLOBAL'

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

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

add_new_workbook() → None

Adds new workbook for Excel application

property app

close_document(save_changes: bool = False) → None

Close the active document and app (if open).

Parameters

save_changes – Enable changes saving on quit. (False by default)

create_pivot_field(data_column: str, operation: str, numberformat: Optional[str] = None) → PivotField

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:

${field_sum}=    Create Pivot Field    price    sum    #,#0
${field_max}=    Create Pivot Field    price    max    #,#0

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

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) → Any

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:

@{rows}=    Create List    products    expense_type
${field_sum}=    Create Pivot Field    price    sum    #,#0
${field_max}=    Create Pivot Field    price    max    #,#0
@{fields}=   Create List   ${field_sum}    ${field_max}
${pivottable}=    Create Pivot Table
...    source_worksheet=data
...    pivot_worksheet=test!R5C5
...    rows=${rows}
...    fields=${fields}

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

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

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

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) → List[Any]

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:

${ranges}=    Find
...    search_string=32.145.*
...    search_range=IP!A1:A9999
...    max_results=6
...    search_order=COLUMNS

FOR    ${ranges}    IN    @{ranges}
    ${value}=    Set Variable    ${range.Value}
    Log to console    ADDR = ${range.Address} VALUE = ${value}
    ${new_value}=    Replace String    ${value}    32.145.    192.168.
    Set Object Property    ${range}    Value    ${new_value}
    Call Method    ${range}    BorderAround
END

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

find_first_available_cell(worksheet: Any = None, row: int = 1, column: int = 1) → Any

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

find_first_available_row(worksheet: Optional[Any] = None, row: int = 1, column: int = 1) → Any

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

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

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:

${pivots}=    Get Pivot Tables
FOR    ${tablename}    ${pivot}    IN    &{pivots}
    Log To Console    ${tablename}
    ${table}=    RPA.Tables.Create Table
    ...   data=${{$pivot[1:]}}
    ...   columns=${{$pivot[0]}}
    Log To Console    ${table}
END

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)

get_range(table_range: str) → Any

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:

${range}=    Get Range    data!A1:A4
FOR    ${r}    IN    @{range}
    Log To Console    ADDR = ${r.Address} VAL = ${r.Value}
    Call Method  ${r}    BorderAround
END
Call Method    ${range}    Merge

Parameters

table_range – range to return

Returns

range object

list_tables() → List[str]

Return tables in the current worksheet.

Returns

list of table names

merge_range(initial_range: Any) → None

Merges a range of cells.

Parameters

initial_range – range of cells to merge

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

Open the application.

Parameters

• visible – Show the window on opening. (False by default)

• display_alerts – Display alert popups. (False by default)

open_workbook(filename: str) → None

Open Excel by filename

By default sets active worksheet to sheet number 1

Parameters

filename – path to filename

quit_application(save_changes: bool = False) → None

Quit the application.

Parameters

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

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

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

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

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

run_macro(macro_name: str, *args: Any) → None

Run Excel macro with given name

Parameters

• macro_name – macro to run

• args – arguments to pass to macro

save_excel() → None

Saves Excel file

save_excel_as(filename: str, autofit: bool = False, file_format=None) → 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

Examples:

# Save workbook in modern format
Save excel as    orders.xlsx

# Save workbook in Excel 97 format (format from above URL)
Save excel as    legacy.xls   file_format=${56}

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

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.

${new_value}=    Replace String    ${value}    10.132.    5511.11.
Set Object Property    ${result}    Value    ${new_value}

Parameters

• object_instance – object instance to set the property

• property_name – property name to set

• value – value to set

unmerge_range(initial_range: Any) → None

Unmerges a range of cells.

Parameters

initial_range – range of cells to unmerge

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:

${input_table}=    Read table from CSV    input.csv    header=True
Open Workbook      result.xlsx
Write Data To Range    A2:L21    ${input_table}   # Single range
Write Data To Range    C2:E21;G2:I21    ${input_table}   # Multiple ranges

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)

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