RPA.Excel.Files library | Robocorp documentation

RPA.Excel.Files

Append Rows To Worksheet

Append values to the end of the worksheet.

Arguments

Argument	Type	Default value
content	Any	null
name	str, None	None
header	bool	False
start	int, None	None
formatting_as_empty	bool, None	False

param content:	Rows of values to append
param name:	Name of worksheet to append to (optional). Defaults to the active worksheet.
param header:	Set rows according to existing header row
param start:	Start of data, NOTE: Only required when header is True
param formatting_as_empty:	if True, the cells containing only formatting (no values) are considered empty.
return:	List of dictionaries that represents the worksheet

The content argument can be of any tabular format. Typically, this is a Table object created by the RPA.Tables library, but it can also be a list of lists, or a list of dictionaries.

If the header flag is enabled, the existing header in the worksheet is used to insert values in the correct columns. This assumes that that source data has this data available.

If the header is not on the first row of the worksheet, the start argument can be used to give the correct row index.

Examples:

# Append an existing Table object # Create a new table using a Dictionary of Lists @{table_name}= Create List Sara Beth Amy @{table_age}= Create List ${48} ${21} ${57} &{table}= Create Dictionary name=${table_name} age=${table_age} Create Table ${table} Append rows to worksheet ${table} Save Workbook # Append to a worksheet with headers on row 5 # Create a new table using a Dictionary of Lists @{table_name}= Create List Sara Beth Amy @{table_age}= Create List ${48} ${21} ${57} &{table}= Create Dictionary name=${table_name} age=${table_age} Create Table ${table} Append rows to worksheet ${table} header=${TRUE} start=5 Save Workbook

# Append an existing Table object table = { "name": ["Sara", "Beth", "Amy"], "age": [ 48, 21, 57], } lib.append_rows_to_worksheet(table) lib.save_workbook() # Append to a worksheet with headers on row 5 table = { "name": ["Sara", "Beth", "Amy"], "age": [ 48, 21, 57], } lib.append_rows_to_worksheet(table, header=True, start=5) lib.save_workbook()

Auto Size Columns

Auto size column widths.

Arguments

Argument	Type	Default value
start_column	int, str	null
end_column	int, str, None	None
width	int, None	None

Note. non-default font sizes might cause auto sizing issues

param start_column:	column number or name to start from
param end_column:	optional column number or name for last column
param width:	if given will resize columns to this size, otherwise will auto_size

Examples:

Robot Framework example.

Auto Size Columns A D # will try auto size Auto Size Columns B D 16 # will set A-D columns sizes to 16 Auto Size Columns A width=24 # will set column A size to 24

Python example.

lib.auto_size_columns("A", "D") lib.auto_size_columns("C", width=40)

Clear Cell Range

Clear cell values for a given range.

Arguments

Argument	Type	Default value
range_string	str	null

param range_string:	single cell or range of cells

Examples:

Robot Framework example.

# area of cells Clear Cell Range A9:A100 # single cell Clear Cell Range A2

Python example.

lib.clear_cell_range("A1") lib.clear_cell_range("B2:B50")

Close Workbook

Close the active workbook.

Examples:

# Close active workbook Close Workbook

# Close active workbook lib.close_workbook()

Copy Cell Values

Copy cells from source to target.

Arguments

Argument	Type	Default value
source_range	str	null
target	str	null

param source_range:	single cell or range of cells
param target:	copy to this cell

Examples:

Robot Framework example.

Copy Cell Values A1:D4 G10

Python example.

lib.copy_cell_values("A1:D4", "G10")

Create Workbook

Create and open a new Excel workbook.

Arguments

Argument	Type	Default value
path	str, None	None
fmt	str	xlsx
sheet_name	str, None	None

Automatically also creates a new worksheet with the name sheet_name. (defaults to "Sheet")

Note: Must be paired with the Save Workbook keyword or the newly created workbook will be deleted upon robot completion.

Note: The file name/path must be set in either the Create Workbook keyword or the Save Workbook keyword and must include the file extension.

param path:	Save path for workbook; defaults to robot root if not provided.
param fmt:	Format of workbook, i.e. xlsx or xls; Defaults to xlsx if not provided.
param sheet_name:	Custom name for the initial sheet.
return:	Workbook object.

Examples:

# Create modern format workbook. Create Workbook Save Workbook orders.xlsx # Create modern format workbook with custom sheet name. Create Workbook sheet_name=MyCustomSheetName Save Workbook orders.xlsx # Create modern format workbook with a path set. Create Workbook path=${OUTPUT_DIR}${/}orders.xlsx Save Workbook # Create legacy format workbook. Create Workbook fmt=xls Save Workbook orders.xls # Create legacy format workbook with a path set. Create Workbook path=${OUTPUT_DIR}${/}orders.xls fmt=xls Save Workbook

# Create modern format workbook with defaults. lib = Files() lib.create_workbook() lib.save_workbook("orders.xlsx") # Create modern format workbook with a path set. lib = Files() lib.create_workbook(path="./output/orders.xlsx", fmt="xlsx") lib.save_workbook() # Create legacy format workbook. lib = Files() lib.create_workbook(fmt="xls") lib.save_workbook("orders.xls") # Create legacy format workbook with a path set. # Note that the file name must be set in the Create Workbook keyword # if the path is used. lib = Files() lib.create_workbook(path="./output/orders.xls", fmt="xls") lib.save_workbook()

Create Worksheet

Create a new worksheet in the current workbook.

Arguments

Argument	Type	Default value
name	str	null
content	Any, None	None
exist_ok	bool, None	False
header	bool, None	False

param name:	Name of new worksheet
param content:	Optional content for worksheet
param exist_ok:	If False, raise an error if name is already in use
param header:	If content is provided, write headers to worksheet

Examples:

# Create a new blank worksheet named "Customers" Create Worksheet Customers # Create a new worksheet with headers and contents using &{Employees_Row1}= Create Dictionary name=Mark age=${58} &{Employees_Row2}= Create Dictionary name=John age=${22} &{Employees_Row3}= Create Dictionary name=Adam age=${67} @{Worksheet_Data}= Create List ... ${Worksheet_Data_row1} ... ${Worksheet_Data_row2} ... ${Worksheet_Data_row3} Create Worksheet ... name=Employees ... content=${Worksheet_Data} ... header=True Save Workbook # Create a new workseet using a Dictionary of Lists @{Employees_name}= Create List Mark John Adam @{Employees_age}= Create List ${58} ${22} ${67} &{Worksheet_Data}= Create Dictionary ... name=${Worksheet_Data_name} ... age=${Worksheet_Data_age} Create Worksheet ... name=Employees ... content=${Worksheet_Data} ... header=True Save Workbook

# Create a new blank worksheet named "Customers" lib.create_worksheet("Customers") # Create a new workseet using a List of Dictionaries # Don't forget to Save Workbook once your changes are complete Worksheet_Data = [ {"name": "Mark", "age": 58}, {"name": "John", "age": 22}, {"name": "Adam", "age": 67}, ] lib.create_worksheet(name="Employees",content=Worksheet_Data,header=True) lib.save_workbook() # Create a new workseet using a Dictionary of Lists # Don't forget to Save Workbook once your changes are complete Worksheet_Data = { "name": ["Mark", "John", "Adam"], "age": [ 58, 22, 67], } lib.create_worksheet(name="Employees",content=Worksheet_Data,header=True) lib.save_workbook()

Delete Columns

Delete column or columns beginning from start column number/name to possible end column number/name.

Arguments

Argument	Type	Default value
start	int, str	null
end	int, str, None	None

param start:	column number or name to start deletion from
param end:	optional column number or name for last column to delete

Examples:

Robot Framework example.

Delete Columns C # delete column C Delete Columns 3 # delete column 3 (same as C) Delete Columns E AA # delete rows E-AA

Python example.

lib.delete_columns("D") lib.delete_rows(1, "JJ")

Delete Rows

Delete row or rows beginning from start row number to possible end row number.

Arguments

Argument	Type	Default value
start	int	null
end	int, None	None

param start:	row number to start deletion from
param end:	optional row number for last row to delete

Examples:

Robot Framework example.

Delete Rows 2 # delete row 2 Delete Rows 5 10 # delete rows 5-10

Python example.

lib.delete_rows(2) lib.delete_rows(5,10)

Find Empty Row

Find the first empty row after existing content, and return the row number.

Arguments

Argument	Type	Default value
name	str, None	None

param name:	Name of worksheet (optional). Defaults to the active worksheet.
return:	First row number of empty row

Examples:

${next}= Find empty row

next = lib.find_empty_row()

Get Active Worksheet

Get the name of the worksheet which is currently active.

return:	Active worksheet name

Examples:

${Active_Worksheet}= Get Active Worksheet

Active_Worksheet = lib.get_active_worksheet()

Get Cell Value

Get a cell value in the given worksheet.

Arguments

Argument	Type	Default value
row	int	null
column	str, int	null
name	str, None	None

param row:	Index of row to read, e.g. 3
param column:	Name or index of column, e.g. C or 7
param name:	Name of worksheet (optional). Defaults to active worksheet.
return:	Cell value

Examples:

# Read header names ${column1}= Get cell value 1 A ${column2}= Get cell value 1 B ${column3}= Get cell value 1 C

# Read header names column1 = lib.get_cell_value(1, "A") column2 = lib.get_cell_value(1, "B") column3 = lib.get_cell_value(1, "C")

Get Worksheet Value

Alias for keyword Get cell value, see the original keyword for documentation.

Arguments

Argument	Type	Default value
row	int	null
column	str, int	null
name	str, None	None

Hide Columns

Hide column or columns in worksheet.

Arguments

Argument	Type	Default value
start_column	int, str	null
end_column	int, str, None	None

param start_column:	column number or name to start from
param end_column:	optional column number or name for last column

Examples:

Robot Framework example.

Hide Columns A D # hide columns A-D Hide Columns A # hide column A

Python example.

lib.hide_columns("A", "D") lib.hide_columns("A")

Insert Columns After

Insert column or columns after a column number/name.

Arguments

Argument	Type	Default value
column	int, str	null
amount	int	1

param column:	insert after this column
param amount:	number of columns to insert, default 1

Examples:

Robot Framework example.

Insert Columns After C # insert 1 column after column C Insert Columns Before A 3 # insert 3 columns after column A

Python example.

lib.insert_columns_after("C") lib.insert_columns_after("A", 3)

Insert Columns Before

Insert column or columns before a column number/name.

Arguments

Argument	Type	Default value
column	int, str	null
amount	int	1

param column:	insert before this column
param amount:	number of columns to insert, default 1

Examples:

Robot Framework example.

Insert Columns Before C # insert 1 column before column C Insert Columns Before A 3 # insert 3 columns before column A

Python example.

lib.insert_columns_before("C") lib.insert_columns_before("A", 3)

Insert Image To Worksheet

Insert an image into the given cell.

Arguments

Argument	Type	Default value
row	int	null
column	int, str	null
path	str	null
scale	float	1.0
name	str, None	None

The path argument should be a local file path to the image file.

By default the image is inserted in the original size, but it can be scaled with the scale argument. It's scaled with a factor where the value 1.0 is the default.

param row:	Index of row to write
param column:	Name or index of column
param path:	Path to image file
param scale:	Scale of image (optional). Default value is "1.0".
param name:	Name of worksheet (optional). Defaults to the active worksheet.

Examples:

Insert image to worksheet ${last_row} A screenshot.png

lib.insert_image_to_worksheet(last_row, "A", "screenshot.png")

Insert Rows After

Insert row or rows after a row number.

Arguments

Argument	Type	Default value
row	int	null
amount	int	1

param row:	insert after this row
param amount:	number of rows to insert, default 1

Examples:

Robot Framework example.

Insert Rows After 3 # insert 1 row after row 3 Insert Rows After 1 3 # insert 3 rows after row 1

Python example.

lib.insert_rows_after(1) lib.insert_rows_after(1, 3)

Insert Rows Before

Insert row or rows before a row number.

Arguments

Argument	Type	Default value
row	int	null
amount	int	1

param row:	insert before this row
param amount:	number of rows to insert, default 1

Examples:

Robot Framework example.

Insert Rows Before 3 # insert 1 row before row 3 Insert Rows Before 1 3 # insert 3 rows before row 1

Python example.

lib.insert_rows_before(1) lib.insert_rows_before(1, 3)

List Worksheets

List all names of worksheets in the given workbook.

return:	List containing the names of the worksheets

Examples:

# List Worksheets will read the worksheet names into a list variable @{sheets}= List Worksheets

# List Worksheets will read the worksheet names into a list variable # The variable should be declared with the List type "@" when being used # to store the sheet names from the List Worksets keyword sheets = lib.list_worksheets()

Move Range

Move range of cells by given amount of rows and columns.

Arguments

Argument	Type	Default value
range_string	str	null
rows	int	0
columns	int	0
translate	bool	True

Formulas are translated to match new location by default.

Note. There is a bug in the openpyxl on moving negative rows/columns.

param range_string:	cell range
param rows:	number of rows to move
param columns:	number of columns to move
param translate:	are formulas translated for a new location

Examples:

Robot Framework example.

# move range 4 rows down Move Range E2:E10 rows=4 # move range 2 rows down, 2 columns right Move Range E2:E10 rows=2 columns=2

Python example.

lib.move_range("E2:E10", rows=4) lib.move_range("E2:E10", rows=2, columns=2)

Open Workbook

Open an existing Excel workbook.

Arguments

Argument	Type	Default value
path	str	null
data_only	bool, None	False
read_only	bool, None	False

Opens the workbook in memory and sets it as the active workbook. This library can only have one workbook open at a time, and any previously opened workbooks are closed first.

The file can be in either .xlsx or .xls format.

param path:	path to Excel file
param data_only:	controls whether cells with formulas have either the formula (default, False) or the value stored the last time Excel read the sheet (True). Affects only `.xlsx` files.
return:	Workbook object

Examples:

# Open workbook with only path provided Open Workbook path/to/file.xlsx # Open workbook with path provided and reading formulas in cells Open Workbook path/to/file.xlsx data_only=True

# Open workbook with only path provided lib.open_workbook(path="path/to/file.xlsx") # Open workbook with path provided and reading formulas in cells # as the value stored # Note: Can only be used with XLSX workbooks lib.open_workbook(path="path/to/file.xlsx", data_only=True)

Read Worksheet

Read the content of a worksheet into a list of dictionaries.

Arguments

Argument	Type	Default value
name	str, None	None
header	bool, None	False
start	int, None	None

Each key in the dictionary will be either values from the header row, or Excel-style column letters.

param name:	Name of worksheet to read (optional). Defaults to the active worksheet.
param header:	If True, use the first row of the worksheet as headers for the rest of the rows. Default is False.
param start:	Row index to start reading data from (1-indexed). Default value is row 1.
return:	List of dictionaries that represents the worksheet

Examples:

# The most simple form. Column keys will be Column letters. ${rows}= Read Worksheet # Since header=True the keys will be the header values ${rows}= Read Worksheet header=True # Uses the header values as keys and starts reading at row 3 ${rows}= Read Worksheet header=True start=${3}

# The most simple form. Keys will be Column letters. rows = lib.read_worksheet() # Since header=True the keys will be the header values rows = lib.read_worksheet(header=True) # Uses the header values as keys and starts reading at row 3 rows = lib.read_worksheet(header=True, start=3)

Read Worksheet As Table

Read the contents of a worksheet into a Table container. Allows sorting/filtering/manipulating using the RPA.Tables library.

Arguments

Argument	Type	Default value
name	str, None	None
header	bool	False
trim	bool	True
start	int, None	None

param name:	Name of worksheet to read (optional). Defaults to the active worksheet.
param header:	If True, use the first row of the worksheet as headers for the rest of the rows. Default value is False.
param trim:	Remove all empty rows from the end of the worksheet. Default value is True.
param start:	Row index to start reading data from (1-indexed). Default value is row 1.
return:	Table object that represents the worksheet

Examples:

# The most simple form. Column keys will be Column letters. ${table}= Read Worksheet As Table # Since header=True the keys will be the header values ${table}= Read Worksheet As Table header=True # Uses the header values as keys and starts reading at row 3 ${table}= Read Worksheet As Table header=True start=${3}

# The most simple form. Keys will be Column letters. table = lib.read_worksheet_as_table() # Since header=True the keys will be the header values table = lib.read_worksheet_as_table(header=True) # Uses the header values as keys and starts reading at row 3 table = lib.read_worksheet_as_table(header=True, start=3)

Remove Worksheet

Remove a worksheet from the active workbook.

Arguments

Argument	Type	Default value
name	str, None	None

param name:	Name of worksheet to remove (optional). Defaults to the active worksheet.

Examples:

# Remove last worksheet ${sheets}= List worksheets Remove worksheet ${sheets}[-1] # Remove worksheet by name Remove Worksheet Sheet

# Remove last worksheet sheets = lib.list_worksheets() lib.remove_worksheet(sheets[-1]) # Remove worksheet by name lib.remove_worksheet("Sheet")

Rename Worksheet

Rename an existing worksheet in the active workbook.

Arguments

Argument	Type	Default value
src_name	str	null
dst_name	str	null

param src_name:	Current name of worksheet
param dst_name:	Future name of worksheet

Examples:

Rename worksheet Sheet Orders

lib.rename_worksheet("Sheet","Orders")

Save Workbook

Save the active workbook.

Arguments

Argument	Type	Default value
path	str, None	None

Note: No changes to the workbook are saved to the actual file unless this keyword is called.

param path:	Path to save to. If not given, uses path given when opened or created.
return:	Workbook object

Examples:

# Saving the active workbook to a new location/filename or saving to Save Workbook path=${OUTPUT_DIR}${/}orders.xlsx # Saving the active workbook changes if location/filename were set Save Workbook

# Saving the active workbook to a new location/filename or saving to # a new location/filename # Note: You cannot use Save Workbook to convert from XLSX to XLS # or vice-versa lib.save_workbook(path="./output/orders.xlsx") # Saving the active workbook changes if location/filename were set # in Create Workbook or Open Workbook lib.save_workbook()

Set Active Worksheet

Set the active worksheet.

Arguments

Argument	Type	Default value
value	str, int	null

This keyword can be used to set the default worksheet for keywords, which removes the need to specify the worksheet name for each keyword. It can always be overridden on a per-keyword basis.

param value:	Index or name of worksheet

Examples:

# Set using the name of the worksheet Set Active Worksheet Customers # Set using the index of the worksheet Set Active Worksheet 2

# Set using the name of the worksheet lib.set_active_worksheet("Customers") # Set using the index of the worksheet # Worksheet index begings at 0 lib.set_active_worksheet(2)

Set Cell Format

Set format for cell.

Arguments

Argument	Type	Default value
row	int	null
column	str, int	null
fmt	str, float	null
name	str, None	None

Does not affect the values themselves, but changes how the values are displayed when opening with an external application such as Microsoft Excel or LibreOffice Calc.

param row:	Index of row to write, e.g. 3
param column:	Name or index of column, e.g. C or 7
param fmt:	Format code for cell
param name:	Name of worksheet (optional). Defaults to active worksheet.

The fmt argument accepts all format code values that are supported by the aforementioned applications.

Some examples of valid values:

Format	Explanation
0.00	Number with two decimal precision
0%	Percentage without decimals
MM/DD/YY	Date with month, day, and year
@	Text value
BOOLEAN	Boolean value

Examples:

# Set value to have one decimal precision Set cell format 2 B 00.0

# Set value to have one decimal precision lib.set_cell_format(2, "B", 00.0)

Set Cell Formula

Set cell formula for given range of cells.

Arguments

Argument	Type	Default value
range_string	str	null
formula	str	null
transpose	bool	False

If transpose is set then formula is set for first cell of the range and the rest of cells will transpose the function to match to that cell.

Otherwise (by default) all cells will get the same formula.

param range_string:	cell range
param formula:	formula for the cell
param transpose:	on True the cell formulas will be transposed

Examples:

Robot Framework example.

# all cells will have same formula Set Cell Formula E2:E10 =B2+5 # cells will have transposed formulas Set Cell Formula E2:E10 =B2+5 True

Python example.

lib.set_cell_formula("E2:E10", "=B2+5") lib.set_cell_formula("E2:E10", "=B2+5", True)

Set Cell Value

Set a cell value in the given worksheet.

Arguments

Argument	Type	Default value
row	int	null
column	str, int	null
value	Any	null
name	str, None	None
fmt	str, float, None	None

param row:	Index of row to write, e.g. 3
param column:	Name or index of column, e.g. C or 7
param value:	New value of cell
param name:	Name of worksheet (optional). Defaults to active worksheet.
param fmt:	Format code for cell (optional)

Examples:

# Set a value in the first row and column Set cell value 1 1 Some value Set cell value 1 A Some value # Set a value with cell formatting Set cell value 2 B ${value} fmt=0%

# Set a value in the first row and column lib.set_cell_value(1, 1, "Some value") lib.set_cell_value(1, "A", "Some value") # Set a value with cell formatting lib.set_cell_value(2, "B", value, fmt="0%")

Set Cell Values

Set cell values given as list of lists or as a RPA.Tables.Table.

Arguments

Argument	Type	Default value
start_cell	str	null
values	list, Table	null
table_heading	bool	False

Note. Will overwrite cells if table structure causes cells to overlap.

param start_cell:	starting cell in a string
param values:	list of lists or a Table
param table_heading:	if values are given as a Table, this parameter defines if Table headings should be inserted as a row

Examples:

Robot Framework example.

@{all_rows}= Create List ${headers}= Create List first second third fourth FOR ${num} IN RANGE 1 2000 @{row}= Create List ${num} ${num+1} ${num*2} ${num*4} Append To List ${all_rows} ${row} END # Set Cell Values from Table (include headers) ${table}= Create Table ${all_rows} columns=${headers} Set Cell Values G1 ${table} True # Set Cell Values from a list of lists Set Cell Values M1 ${all_rows} # Simplest form of adding values @{values}= Evaluate [[1,2,3],[4,5,6],['a','b','c','d']] Set Cell Values A1 ${values}

Python example.

data = [[1,2,3],[4,5,6],['a','b','c','d']] lib.set_cell_values("E2", data)

Set Styles

Set styles for range of cells.

Arguments

Argument	Type	Default value
range_string	str	null
font_name	str, None	None
family	str, None	None
size	int, None	None
bold	bool	False
italic	bool	False
underline	bool	False
strikethrough	bool	False
cell_fill	str, None	None
color	str, None	None
align_horizontal	str, None	None
align_vertical	str, None	None
number_format	str, None	None

Possible values for the align_horizontal:

general

left

center

right

fill

justify

centerContinuous

distributed

Possible values for the align_vertical:

top

center

bottom

justify

distributed

Some examples for number_formats:

General

0

0.00

#,##0

#,##0.00

"$"#,##0_);("$"#,##0)

"$"#,##0_);[Red]("$"#,##0)

0%

0.00%

0.00E+00

# ?/?

# ??/??

mm-dd-yy

d-mmm-yy

d-mmm

h:mm AM/PM

h:mm:ss AM/PM

h:mm

h:mm:ss

m/d/yy h:mm

param range_string:	single cell or range of cells
param font_name:	name of the font
param family:	font family name
param size:	size for the font
param bold:	font style bold
param italic:	font style italics
param underline:	font style underline
param strikethrough:	font style strikethrough
param cell_fill:	cell fill color, in hex or color name
param color:	font color, in hex or color name
param align_horizontal:	cell horizontal alignment
param align_vertical:	cell vertical alignment
param number_format:	cell number format

Examples:

Robot Framework example.

Set Styles A1:D4 ... bold=True ... cell_fill=lightblue ... align_horizontal=center ... number_format=h:mm AM/PM Set Styles E2 ... strikethrough=True ... color=FF0000

Python example.

lib.set_styles("A1:D4", bold=True, font_name="Arial", size=24)

Set Worksheet Value

Alias for keyword Set cell value, see the original keyword for documentation.

Arguments

Argument	Type	Default value
row	int	null
column	str, int	null
value	Any	null
name	str, None	None
fmt	str, float, None	None

Unhide Columns

Unhide column or columns in worksheet.

Arguments

Argument	Type	Default value
start_column	int, str	null
end_column	int, str, None	None

param start_column:	column number or name to start from
param end_column:	optional column number or name for last column

Examples:

Robot Framework example.

Unhide Columns A D # unhide columns A-D Unhide Columns A # unhide column A

Python example.

lib.unhide_columns("A", "D") lib.unhide_columns("A")

Worksheet Exists

Return True if worksheet with given name is in workbook.

Arguments

Argument	Type	Default value
name	str	null

param name:	Name of worksheet you are looking for
return:	True if the worksheet exists, False otherwise

Examples:

# To use Worksheet Exists in a conditional statement set it to ${Does_Worksheet_Exist}= Worksheet Exists Sheet

Does_Worksheet_Exist = lib.worksheet_exists("Sheet")