RPA.Excel.Application library | Robocorp documentation

RPA.Excel.Application

Add New Sheet

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

Close Document

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

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:

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

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 Pivot Table

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:

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}

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 Table

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 As Pdf

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

Find

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

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

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
return:	list of Range objects

Find First Available Cell

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 Row

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

Get Pivot Tables

Return pivot tables in the current worksheet.

Arguments

Argument	Type	Default value
pivot_table_name	str, None	None
as_list	bool	True

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

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

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:

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

param table_range:	range to return
return:	range object

List Tables

Return tables in the current worksheet.

return:	list of table names

Merge Range

Merges a range of cells.

Arguments

Argument	Type	Default value
initial_range	Any	null

param initial_range:	range of cells to merge

Open Application

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 Workbook

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 Application

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 From Cells

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

Remove Hidden Columns And Rows

Removes hidden columns and rows from a range and returns a new range.

Arguments

Argument	Type	Default value
initial_range	Any	null
worksheet	str, None	None

param initial_range:	range of cells to remove hidden columns and rows from
param worksheet:	set active worksheet (name) before removing hidden columns and rows
return:	new range or initial range if no hidden cells found

Run Macro

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

Save Excel

Saves Excel file

Save Excel As

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:

# 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

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 Object Property

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.

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

param object_instance:	object instance to set the property
param property_name:	property name to set
param value:	value to set

Unmerge Range

Unmerges a range of cells.

Arguments

Argument	Type	Default value
initial_range	Any	null

param initial_range:	range of cells to unmerge

Write Data To Range

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:

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

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 To Cells

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