RPA.Excel.Files

module RPA.Excel.Files

class RPA.Excel.Files.Files

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:

Processing all worksheets in the Excel file and checking row count:

Creating a new Excel file with a dictionary:

Creating a new Excel file with a list:

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()

variable ROBOT_LIBRARY_DOC_FORMAT

ROBOT_LIBRARY_DOC_FORMAT = 'REST'

variable ROBOT_LIBRARY_SCOPE

ROBOT_LIBRARY_SCOPE = 'GLOBAL'

method append_rows_to_worksheet

append_rows_to_worksheet(content: Any, name: Optional[str] = None, header: bool = False, start: Optional[int] = None, formatting_as_empty: Optional[bool] = False)

Append values to the end of the worksheet.

Parameters
  • content – Rows of values to append
  • name – Name of worksheet to append to (optional). Defaults to the active worksheet.
  • header – Set rows according to existing header row
  • start – Start of data, NOTE: Only required when header is True
  • formatting_as_empty – if True, the cells containing only formatting (no values) are considered empty.
  • Returns: 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 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()

method auto_size_columns

auto_size_columns(start_column: Union[int, str], end_column: Optional[Union[int, str]] = None, width: Optional[int] = None)

Auto size column widths.

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

Parameters
  • start_column – column number or name to start from
  • end_column – optional column number or name for last column
  • width – if given will resize columns to this size, otherwise will auto_size
Examples

Robot Framework example.

Python example.

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

method clear_cell_range

clear_cell_range(range_string: str)

Clear cell values for a given range.

  • Parameters: range_string – single cell or range of cells
Examples

Robot Framework example.

Python example.

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

method close_workbook

close_workbook()

Close the active workbook.

Examples

# Close active workbook lib.close_workbook()

method copy_cell_values

copy_cell_values(source_range: str, target: str)

Copy cells from source to target.

Parameters
  • source_range – single cell or range of cells
  • target – copy to this cell
Examples

Robot Framework example.

Python example.

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

method create_workbook

create_workbook(path: Optional[str] = None, fmt: str = 'xlsx', sheet_name: Optional[str] = None)

Create and open a new Excel workbook.

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.

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

# 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()

method create_worksheet

create_worksheet(name: str, content: Optional[Any] = None, exist_ok: Optional[bool] = False, header: Optional[bool] = 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
Examples

# 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()

method delete_columns

delete_columns(start: Union[int, str], end: Optional[Union[int, str]] = None)

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

Parameters
  • start – column number or name to start deletion from
  • end – optional column number or name for last column to delete
Examples

Robot Framework example.

Python example.

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

method delete_rows

delete_rows(start: int, end: Optional[int] = None)

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

Parameters
  • start – row number to start deletion from
  • end – optional row number for last row to delete
Examples

Robot Framework example.

Python example.

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

method find_empty_row

find_empty_row(name: Optional[str] = None)

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

  • Parameters: name – Name of worksheet (optional). Defaults to the active worksheet.
  • Returns: First row number of empty row
Examples

next = lib.find_empty_row()

method get_active_worksheet

get_active_worksheet()

Get the name of the worksheet which is currently active.

  • Returns: Active worksheet name
Examples

Active_Worksheet = lib.get_active_worksheet()

method get_cell_value

get_cell_value(row: int, column: Union[str, int], name: Optional[str] = None)

Get a cell value in the given worksheet.

Parameters
  • row – Index of row to read, e.g. 3
  • column – Name or index of column, e.g. C or 7
  • name – Name of worksheet (optional). Defaults to active worksheet.
  • Returns: Cell value
Examples

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

method get_worksheet_value

get_worksheet_value(row: int, column: Union[str, int], name: Optional[str] = None)

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

method hide_columns

hide_columns(start_column: Union[int, str], end_column: Optional[Union[int, str]] = None)

Hide column or columns in worksheet.

Parameters
  • start_column – column number or name to start from
  • end_column – optional column number or name for last column
Examples

Robot Framework example.

Python example.

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

method insert_columns_after

insert_columns_after(column: Union[int, str], amount: int = 1)

Insert column or columns after a column number/name.

Parameters
  • column – insert after this column
  • amount – number of columns to insert, default 1
Examples

Robot Framework example.

Python example.

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

method insert_columns_before

insert_columns_before(column: Union[int, str], amount: int = 1)

Insert column or columns before a column number/name.

Parameters
  • column – insert before this column
  • amount – number of columns to insert, default 1
Examples

Robot Framework example.

Python example.

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

method insert_image_to_worksheet

insert_image_to_worksheet(row: int, column: Union[int, str], path: str, scale: float = 1.0, name: Optional[str] = None)

Insert an image into the given cell.

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.

Parameters
  • row – Index of row to write
  • column – Name or index of column
  • path – Path to image file
  • scale – Scale of image (optional). Default value is “1.0”.
  • name – Name of worksheet (optional). Defaults to the active worksheet.
Examples

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

method insert_rows_after

insert_rows_after(row: int, amount: int = 1)

Insert row or rows after a row number.

Parameters
  • row – insert after this row
  • amount – number of rows to insert, default 1
Examples

Robot Framework example.

Python example.

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

method insert_rows_before

insert_rows_before(row: int, amount: int = 1)

Insert row or rows before a row number.

Parameters
  • row – insert before this row
  • amount – number of rows to insert, default 1
Examples

Robot Framework example.

Python example.

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

method list_worksheets

list_worksheets()

List all names of worksheets in the given workbook.

  • Returns: List containing the names of the worksheets
Examples

# 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()

method move_range

move_range(range_string: str, rows: int = 0, columns: int = 0, translate: bool = True)

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

Formulas are translated to match new location by default.

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

Parameters
  • range_string – cell range
  • rows – number of rows to move
  • columns – number of columns to move
  • translate – are formulas translated for a new location
Examples

Robot Framework example.

Python example.

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

method open_workbook

open_workbook(path: str, data_only: Optional[bool] = False, read_only: Optional[bool] = False)

Open an existing Excel workbook.

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.

Parameters
  • path – path to Excel file
  • 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.
  • Returns: Workbook object
Examples

# 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)

method read_worksheet

read_worksheet(name: Optional[str] = None, header: Optional[bool] = False, start: Optional[int] = 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 (optional). Defaults to the active worksheet.
  • header – If True, use the first row of the worksheet as headers for the rest of the rows. Default is False.
  • start – Row index to start reading data from (1-indexed). Default value is row 1.
  • Returns: List of dictionaries that represents the worksheet
Examples

# 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)

method read_worksheet_as_table

read_worksheet_as_table(name: Optional[str] = None, header: bool = False, trim: bool = True, start: Optional[int] = None)

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

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

# 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)

method remove_worksheet

remove_worksheet(name: Optional[str] = None)

Remove a worksheet from the active workbook.

  • Parameters: name – Name of worksheet to remove (optional). Defaults to the active worksheet.
Examples

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

method rename_worksheet

rename_worksheet(src_name: str, dst_name: str)

Rename an existing worksheet in the active workbook.

Parameters
  • src_name – Current name of worksheet
  • dst_name – Future name of worksheet
Examples

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

method save_workbook

save_workbook(path: Optional[str] = None)

Save the active workbook.

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

  • Parameters: path – Path to save to. If not given, uses path given when opened or created.
  • Returns: Workbook object
Examples

# 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()

method set_active_worksheet

set_active_worksheet(value: Union[str, int])

Set the active worksheet.

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.

  • Parameters: value – Index or name of worksheet
Examples

# 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)

method set_cell_format

set_cell_format(row: int, column: Union[str, int], fmt: Union[str, float], name: Optional[str] = None)

Set format for cell.

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.

Parameters
  • row – Index of row to write, e.g. 3
  • column – Name or index of column, e.g. C or 7
  • fmt – Format code for cell
  • 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:

FormatExplanation
0.00Number with two decimal precision
0%Percentage without decimals
MM/DD/YYDate with month, day, and year
@Text value
BOOLEANBoolean value
Examples

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

method set_cell_formula

set_cell_formula(range_string: str, formula: str, transpose: bool = False)

Set cell formula for given range of cells.

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.

Parameters
  • range_string – cell range
  • formula – formula for the cell
  • transpose – on True the cell formulas will be transposed
Examples

Robot Framework example.

Python example.

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

method set_cell_value

set_cell_value(row: int, column: Union[str, int], value: Any, name: Optional[str] = None, fmt: Optional[Union[str, float]] = 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 (optional). Defaults to active worksheet.
  • fmt – Format code for cell (optional)
Examples

# 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%")

method set_cell_values

set_cell_values(start_cell: str, values: Union[list, Table], table_heading: bool = False)

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

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

Parameters
  • start_cell – starting cell in a string
  • values – list of lists or a Table
  • 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.

Python example.

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

method set_styles

set_styles(range_string: str, font_name: Optional[str] = None, family: Optional[str] = None, size: Optional[int] = None, bold: bool = False, italic: bool = False, underline: bool = False, strikethrough: bool = False, cell_fill: Optional[str] = None, color: Optional[str] = None, align_horizontal: Optional[str] = None, align_vertical: Optional[str] = None, number_format: Optional[str] = None)

Set styles for range of cells.

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.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
Parameters
  • range_string – single cell or range of cells
  • font_name – name of the font
  • family – font family name
  • size – size for the font
  • bold – font style bold
  • italic – font style italics
  • underline – font style underline
  • strikethrough – font style strikethrough
  • cell_fill – cell fill color, in hex or color name
  • color – font color, in hex or color name
  • align_horizontal – cell horizontal alignment
  • align_vertical – cell vertical alignment
  • number_format – cell number format
Examples

Robot Framework example.

Python example.

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

method set_worksheet_value

set_worksheet_value(row: int, column: Union[str, int], value: Any, name: Optional[str] = None, fmt: Optional[Union[str, float]] = None)

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

method unhide_columns

unhide_columns(start_column: Union[int, str], end_column: Optional[Union[int, str]] = None)

Unhide column or columns in worksheet.

Parameters
  • start_column – column number or name to start from
  • end_column – optional column number or name for last column
Examples

Robot Framework example.

Python example.

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

method worksheet_exists

worksheet_exists(name: str)

Return True if worksheet with given name is in workbook.

  • Parameters: name – Name of worksheet you are looking for
  • Returns: True if the worksheet exists, False otherwise
Examples

Does_Worksheet_Exist = lib.worksheet_exists("Sheet")