Excel

To use this feature, you must have Microsoft Excel installed.

Run Excel

By using the function exec_excel_app you can launch MS Excel application. This function launches Excel after finding the Excel exe on the system. As an optional parameter to this function you can pass a path to a file to be opened when the Excel app launches. This method returns an object of the class AppExcel. This class inherits methods from the class App and is enhanced with methods that make it easier to work with Excel.

In the first example, we run Excel and open a new workbook in it. The new workbook is accessed by the method add_workbook. By calling this method, you can open any number of new workbooks.

import urpa
def main():
    excel_app = urpa.exec_excel_app()
    excel_app.add_workbook()
    excel_app.set_auto_close(False)

Finding the active workbook or worksheet

If you open more than one workbooks in Excel, you can find the active one using the methods active_workbook or active_worksheet. The first method returns an object of the class ExcelWorkbook, representing an active Excel workbook. The second method returns an object of the class ExcelWorksheet, representing an active worksheet. If you have multiple workbooks or tabs open, both methods return the one that is currently active. Keeping track of whether you are working with Excel, a workbook or a worksheet can be confusing at first, so it is advisable to choose variable names that keep the script clear.

import urpa
def main():
    excel_app = urpa.exec_excel_app()
    excel_app.add_workbook()
    workbook = excel_app.active_workbook()
    excel_app.add_workbook()
    worksheet = excel_app.active_worksheet()
    excel_app.set_auto_close(False)

List of workbooks and tabs

You can obtain a list of all currently opened workbooks by using the method workbooks on an instance of the class AppExcel. You can obtain a list of all tabs by using the method sheets on an object of the class ExcelWorkbook.

In the following script, all of the worksheets and all of their sheets are iterated and the name of the workbook is printed first, via the method full_name, followed by the name of the worksheet, via the method name.

import urpa
def main():
    excel_app = urpa.exec_excel_app()
    excel_app.add_workbook()
    excel_app.add_workbook()
    workbooks = excel_app.workbooks()
    for workbook in workbooks:
        worksheets = workbook.sheets()
        for worksheet in worksheets:
            print(workbook.full_name(), worksheet.name())
    excel_app.set_auto_close(False)

You can also try the above example on an existing workbook that has various sheets in it. You may use the method open to work with the existing sheets in a file.

Working with a workbook

In UltimateRPA, a workbook is represented by the class ExcelWorkbook. In the previous example, we demonstrated two methods that belong to this class, namely sheets and full_name.

A workbook can be activated by using the method activate, which brings the selected workbook to the foreground. In our case, two new workbooks are created when Excel is run. In the following example two workbooks are added and the first workbook is activated and brought to the foreground.

import urpa
def main():
    excel_app = urpa.exec_excel_app()
    excel_app.add_workbook()
    excel_app.add_workbook()
    workbooks = excel_app.workbooks()
    workbooks[0].activate()
    excel_app.set_auto_close(False)

If you wish to obtain an instance of a worksheet from the workbook, use the method active_worksheet. This method returns an object of the class ExcelWorksheet, representing the currently active sheet.

Working with a worksheet

The class ExcelWorksheet represents a worksheet in UltimateRPA. In this class, we have already demonstrated the method name, which returns the worksheet name. As with a workbook, worksheets can be activated by using the method activate.

There are methods that are used to select the cells in an excel sheet to be worked on. The simplest of these is the method cells. This returns an object of the class ExcelCells, which represents all of the cells in the sheet in question.

For accessing one cell you can specify a row and a column as parameters to the method cell. This method returns an object of the class ExcelCells, representing the selected cell. Unlike in Excel, columns are not indicated by letters of the alphabet, but by a number indicating the order of the column. The numbering starts with 1 (not 0). This means that, for example, if you wish to obtain cell A1 (the cell in the first column and the first line), use this expression: cell_A1 = worksheet.cell(1, 1).

used_range is the another method in this class that returns an instance of the class ExcelCells. In this case, this is a block of all of the cells used.

import urpa
def main():
    excel_app = urpa.exec_excel_app()
    excel_app.add_workbook()
    worksheet = excel_app.active_worksheet()
    all_cells = worksheet.cells()
    cell_A1 = worksheet.cell(1, 1)
    used_cells = worksheet.used_range()
    excel_app.set_auto_close(False)

Working with cells

The class ExcelCells lets you access the Excel cells. A value can be entered in a cell by using the method set_value.

import urpa
def main():
    excel_app = urpa.exec_excel_app()
    excel_app.add_workbook()
    worksheet = excel_app.active_worksheet()
    cell_A1 = worksheet.cell(1, 1)
    cell_A2 = worksheet.cell(2, 1)
    cell_A1.set_value(10)
    cell_A2.set_value("Robot")
    excel_app.set_auto_close(False)

To determine the value contained in a cell, use the method value. If the cell is empty, the method returns an empty string. The value returned is always a string type, even if an integer is present in the cell.

import urpa
def main():
    excel_app = urpa.exec_excel_app()
    excel_app.add_workbook()
    worksheet = excel_app.active_worksheet()
    cell_A1 = worksheet.cell(1, 1)
    print(cell_A1.value())
    cell_A1.set_value(10)
    print(cell_A1.value())
    print(type(cell_A1.value()))
    excel_app.set_auto_close(False)

To find the column and row where a cell is located, use the method column for the column and the method row for the row. Both return an integer with the column or row number. The numbering starts from 1.

import urpa
def main():
    excel_app = urpa.exec_excel_app()
    excel_app.add_workbook()
    worksheet = excel_app.active_worksheet()
    cell_J10 = worksheet.cell(10, 10)
    print(cell_J10.column())
    print(cell_J10.row())
    excel_app.set_auto_close(False)

To count the number of columns and rows, use the methods columns_count and rows_count. Both return an integer.

import urpa
def main():
    excel_app = urpa.exec_excel_app()
    excel_app.add_workbook()
    worksheet = excel_app.active_worksheet()
    all_cells = worksheet.cells()
    print(all_cells.columns_count())
    print(all_cells.rows_count())
    excel_app.set_auto_close(False)

The following example shows the ExcelCells class being used to access a single cell or a range of cells. The cell method is used to access an excel cell and item method is used to access an excel cell at a particular row/column. In the first part of the code, the diagonal values entered into the cells are from 1 to 10. The script then waits for 3 seconds before detecting the range of columns and rows occupied. In the next for loop, the value 1 is entered in cells occupied.

from time import sleep
import urpa
def main():
    excel_app = urpa.exec_excel_app()
    excel_app.add_workbook()
    worksheet = excel_app.active_worksheet()
    for index in range(1, 11):
        worksheet.cell(index, index).set_value(str(index))
    sleep(3)
    used_cells = worksheet.used_range()
    columns = used_cells.columns_count()
    rows = used_cells.rows_count()
    for column in range(1, columns + 1):
        for row in range(1, rows +1):
            used_cells.item(column, row).set_value(str(1))
    excel_app.set_auto_close(False)

Other actions

Excel controls are accessible as in any other application, so you can control Excel using the mouse or keyboard shortcuts. For example, you can save a workbook via the robot in this way.