Python API¶
Excel.Files¶
-
class
RPA.Excel.Files.
Files
¶ Bases:
object
The Excel.Files library can be used to read and write Excel files without the need to start the actual Excel application.
It supports both legacy .xls files and modern .xlsx files.
Note: To run macros or load password protected worksheets, please use the Excel application library.
Examples
Robot Framework
A common use-case is to load an existing Excel file as a table, which can be iterated over later in a Robot Framework keyword or task:
*** Settings *** Library RPA.Tables Library RPA.Excel.Files *** Keywords *** Read orders as table Open workbook ${ORDERS_FILE} ${worksheet}= Read worksheet header=${TRUE} ${orders}= Create table ${worksheet} [Return] ${orders} [Teardown] Close workbook
Processing all worksheets in the Excel file and checking row count:
*** Settings *** Library RPA.Excel.Files *** Variables *** ${EXCEL_FILE} /path/to/excel.xlsx *** Tasks *** Rows in the sheet [Setup] Open Workbook ${EXCEL_FILE} @{sheets}= List Worksheets FOR ${sheet} IN @{sheets} ${count}= Get row count in the sheet ${sheet} Log Worksheet '${sheet}' has ${count} rows END *** Keywords *** Get row count in the sheet [Arguments] ${SHEET_NAME} ${sheet}= Read Worksheet ${SHEET_NAME} ${rows}= Get Length ${sheet} [Return] ${rows}
Creating a new Excel file with a dictionary:
*** Tasks *** Creating new Excel Create Workbook my_new_excel.xlsx FOR ${index} IN RANGE 20 &{row}= Create Dictionary ... Row No ${index} ... Amount ${index * 25} Append Rows to Worksheet ${row} header=${TRUE} END Save Workbook
Creating a new Excel file with a list:
*** Variables *** @{heading} Row No Amount @{rows} ${heading} *** Tasks *** Creating new Excel Create Workbook my_new_excel.xlsx FOR ${index} IN RANGE 1 20 @{row}= Create List ${index} ${index * 25} Append To List ${rows} ${row} END Append Rows to Worksheet ${rows} Save Workbook
Python
The library can also be imported directly into Python.
from RPA.Excel.Files import Files def read_excel_worksheet(path, worksheet): lib = Files() lib.open_workbook(path) try: return lib.read_worksheet(worksheet) finally: lib.close_workbook()
-
ROBOT_LIBRARY_DOC_FORMAT
= 'REST'¶
-
ROBOT_LIBRARY_SCOPE
= 'GLOBAL'¶
-
append_rows_to_worksheet
(content, name=None, header=False, start=None)¶ Append values to the end of the worksheet.
- Parameters
content – Rows of values to append
name – Name of worksheet to append to
header – Set rows according to existing header row
start – Start of data, NOTE: Only required when headers is True
-
close_workbook
()¶ Close the active workbook.
-
create_workbook
(path=None, fmt='xlsx')¶ Create and open a new Excel workbook.
- Parameters
path – Default save path for workbook
fmt – Format of workbook, i.e. xlsx or xls
-
create_worksheet
(name, content=None, exist_ok=False, header=False)¶ Create a new worksheet in the current workbook.
- Parameters
name – Name of new worksheet
content – Optional content for worksheet
exist_ok – If False, raise an error if name is already in use
header – If content is provided, write headers to worksheet
-
find_empty_row
(name=None)¶ Find the first empty row after existing content.
- Parameters
name – Name of worksheet
-
get_active_worksheet
()¶ Get the name of the worksheet which is currently active.
-
insert_image_to_worksheet
(row, column, path, scale=1.0, name=None)¶ Insert an image into the given cell.
- Parameters
row – Index of row to write
column – Name or index of column
image – Path to image file
scale – Scale of image
name – Name of worksheet
-
list_worksheets
()¶ List all names of worksheets in the given workbook.
-
open_workbook
(path)¶ Open an existing Excel workbook.
- Parameters
path – path to Excel file
-
read_worksheet
(name=None, header=False, start=None)¶ Read the content of a worksheet into a list of dictionaries.
Each key in the dictionary will be either values from the header row, or Excel-style column letters.
- Parameters
name – Name of worksheet to read
header – If True, use the first row of the worksheet as headers for the rest of the rows.
-
read_worksheet_as_table
(name=None, header=False, trim=True, start=None)¶ Read the content of a worksheet into a Table container. Allows sorting/filtering/manipulating using the RPA.Tables library.
- Parameters
name – Name of worksheet to read
header – If True, use the first row of the worksheet as headers for the rest of the rows.
trim – Remove all empty rows from the end of the worksheet
start – Row index to start reading data from (1-indexed)
-
remove_worksheet
(name=None)¶ Remove a worksheet from the active workbook.
- Parameters
name – Name of worksheet to remove
-
rename_worksheet
(src_name, dst_name)¶ Rename an existing worksheet in the active workbook.
- Parameters
src_name – Current name of worksheet
dst_name – Future name of worksheet
-
save_workbook
(path=None)¶ Save the active workbook.
- Parameters
path – Path to save to. If not given, uses path given when opened or created.
-
set_active_worksheet
(value)¶ Set the active worksheet.
- Parameters
value – Index or name of worksheet
-
set_worksheet_value
(row, column, value, name=None)¶ Set a cell value in the given worksheet.
- Parameters
row – Index of row to write, e.g. 3
column – Name or index of column, e.g. C or 7
value – New value of cell
name – Name of worksheet
-
worksheet_exists
(name)¶ Return True if worksheet with given name is in workbook.
-
-
class
RPA.Excel.Files.
XlsWorkbook
(path=None)¶ Bases:
object
Container for manipulating legacy Excel files (.xls)
-
property
active
¶
-
append_worksheet
(name=None, content=None, header=False, start=None)¶
-
close
()¶
-
create
(sheet='Sheet')¶
-
create_worksheet
(name)¶
-
property
extension
¶
-
find_empty_row
(name=None)¶
-
insert_image
(row, column, image, name=None)¶
-
open
(path_or_file=None)¶
-
read_worksheet
(name=None, header=False, start=None)¶
-
remove_worksheet
(name=None)¶
-
rename_worksheet
(title, name=None)¶
-
save
(path=None)¶
-
set_cell_value
(row, column, value, name=None)¶
-
property
sheetnames
¶
-
property
-
class
RPA.Excel.Files.
XlsxWorkbook
(path=None)¶ Bases:
object
Container for manipulating moden Excel files (.xlsx)
-
property
active
¶
-
append_worksheet
(name=None, content=None, header=False, start=None)¶
-
close
()¶
-
create
()¶
-
create_worksheet
(name)¶
-
property
extension
¶
-
find_empty_row
(name=None)¶
-
insert_image
(row, column, image, name=None)¶
-
open
(path=None)¶
-
read_worksheet
(name=None, header=False, start=None)¶
-
remove_worksheet
(name=None)¶
-
rename_worksheet
(title, name=None)¶
-
save
(path=None)¶
-
set_cell_value
(row, column, value, name=None)¶
-
property
sheetnames
¶
-
property
-
RPA.Excel.Files.
ensure_unique
(values)¶ Ensures that each string value in the list is unique. Adds a suffix to each value that has duplicates, e.g. [Banana, Apple, Lemon, Apple] -> [Banana, Apple, Lemon, Apple_2]
-
RPA.Excel.Files.
get_column_index
(column)¶ Get column index from name, e.g. A -> 1, D -> 4, AC -> 29. Reverse of get_column_letter()