Excel class

The Excel class is intended for automating Excel spreadsheet manipulations, such as getting/setting cell values, switching between sheets, saving, etc. All the Excel Actions are executed in the background, so the application window does not appear on the screen.

See the Excel action group in RPA Recorder for better understanding the Excel API. For a quick start, do as follows.

Create a script with files/folders manipulations in RPA Recorder.
Export this script as a Bot task or export to Groovy code and analyze the auto-generated code which uses the Resource API.

tip

To create advanced Excel automation, you can use the Apache POI library.

To start using Excel methods, it is needed to understand cell, row, and column position concept:

Scheme with description: the Excel action
Javadocs: Cell Position, Row/Column Position
When you get/set a cell/row/column, the currently active cell is changed

Examples

Excel class usage

This example performs the following actions:

Downloads an excel file from S3 file storage
Switches to the sheet by its name and sets an active cell
Gets row and column values
Searches for a cell with a particular value
Copies a range between sheets
Saves as a new file and closes the file

Expand to see the example

<?xml version="1.0" encoding="UTF-8"?>
<config xmlns="http://web-harvest.sourceforge.net/schema/1.0/config"
    scriptlang="groovy">

    <robotics-flow>
        <robot driver="universal" close-on-completion="true"
            start-in-private="false">
            <capability name="SEARCH_ALL_WINDOWS" value="true" />
            <script><![CDATA[
                def s3Path = "https://pub_demo.s3.amazonaws.com/trainings/Tickers.xlsx"
                def filePath = downloadFileOnAgent(s3Path)
                def newFilePath = "D:/temp/new-excel.xlsx"

                openExcel(filePath)
                switchSheet(filePath, "Fact")

                if (getActiveCell(filePath) != 'A1') {
                    setActiveCell(filePath, ExcelCellPosition.START_OF_DOCUMENT)
                }

                // Getting Row and Column values

                def firstColumn = getColumn(filePath, 'A', 2, 5)
                def secondRow = getRow(filePath, ExcelColumnRowPosition.CURRENT)

                // Searching for a cell with a particular value

                def temp = getCell(filePath, 'B1')
                int counter = 0
                int maxRowsCount = 100
                while (temp != 'Schlumberger Limited' && counter < maxRowsCount) {
                    temp = getCell(filePath, ExcelCellPosition.CELL_BELOW)
                    counter++
                }
                def price = getCell(filePath, ExcelCellPosition.CELL_TO_THE_RIGHT)
                deleteCell(filePath, ExcelCellPosition.CURRENT)

                // Copying a range between sheets

                def tempRange = getRange(filePath, 'A4')
                switchSheet(filePath, 1)
                setRange(filePath, 'A4', tempRange)

                // Saving as new file and closing file

                saveExcel(filePath, newFilePath)
                closeExcel(filePath)

                sys.defineVariable("firstColumn", firstColumn)
                sys.defineVariable("secondRow", secondRow)
                sys.defineVariable("price", price)


            ]]></script>
        </robot>
    </robotics-flow>
    <export include-original-data="true">
        <single-column name="firstColumn" value="${firstColumn}"/>
        <single-column name="secondRow" value="${secondRow}"/>
        <single-column name="price" value="${price}"/>
    </export>
</config>

Copying range between 2 files

This example performs the following actions:

Downloads two excel files from S3 file storage
Opens the first file and copies a range to a temp variable
Opens the second file and sets a range using the temp variable value
Saves the second excel as a new file and closes all files

Expand to see the example

<?xml version="1.0" encoding="UTF-8"?>
<config xmlns="http://web-harvest.sourceforge.net/schema/1.0/config"
    scriptlang="groovy">

    <robotics-flow>
        <robot driver="universal" close-on-completion="true"
            start-in-private="false">
            <capability name="SEARCH_ALL_WINDOWS" value="true" />
            <script><![CDATA[
                def s3Path = "https://pub_demo.s3.amazonaws.com/trainings/Tickers.xlsx"
                def s3Path2 = "https://pub_demo.s3.amazonaws.com/trainings/other-excel.xlsx"
                def sourceFile = downloadFileOnAgent(s3Path)
                def destinationFile = downloadFileOnAgent(s3Path2)
                def newFilePath = "D:/temp/copied-excel.xlsx"

                // opening the 1st excel file

                openExcel(sourceFile)
                switchSheet(sourceFile, "Fact")

                // Copying a range

                def tempRange = getRange(sourceFile, 'A1')

                // opening another excel file and pasting the range from the 1st one

                openExcel(destinationFile)
                setRange(destinationFile, 'A2', tempRange)

                // Saving the 2nd excel as new file and closing all files

                saveExcel(destinationFile, newFilePath)
                closeExcel(destinationFile)
                closeExcel(sourceFile)

            ]]></script>
        </robot>
    </robotics-flow>
    <export include-original-data="true"/>
</config>

Excel class methods

The Excel class has the following methods.

Type	Method	Description
void	closeExcel(String filePath)	Closes excel file and removes it from script context
void	deleteCell(String filePath, ExcelCellPosition position)	Clears cell value
void	deleteCell(String filePath, String coordinate)	Clears cell value
String	getActiveCell(String filePath)	Gets active cell
String	getCell(String filePath, ExcelCellPosition position)	Gets cell value and returns it as string
String	getCell(String filePath, String coordinate)	Gets cell value and returns it as string
List<String>	getColumn(String filePath, ExcelColumnRowPosition position)	Gets column values as List
List<String>	getColumn(String filePath,[ExcelColumnRowPosition](https://workfusion-docs.s3.amazonaws.com/rpa-simplified-api/latest/com/workfusion/rpa/helpers/ExcelColumnRowPosition.html) position, Integer rowFrom, Integer rowTo)	Gets column values as List
List<String>	getColumn(String filePath, String columnLettes)	Gets column values as List
List<String>	getColumn(String filePath, String columnLettes, Integer rowFrom, Integer rowTo)	Gets column values as List
List<List<String>>	getRange(String filePath, String coordinateFrom)	Gets range values
List<List<String>>	getRange(String filePath, String coordinateFrom, String coordinateTo)	Gets range values
List<String>	getRow(String filePath, ExcelColumnRowPosition position)	Gets row values as List
List<String>	getRow(String filePath, ExcelColumnRowPosition position, String columnFrom, String columnTo)	Gets row values as List
List<String>	getRow(String filePath, int rowNum)	Gets row values as List
List<String>	getRow(String filePath, int rowNum, String columnFrom, String columnTo)	Gets row values as List
void	openExcel(String filePath)	Reads excel file by path, stores this in script context
void	saveExcel(String filePath)	Saves excel file
void	saveExcel(String filePath, String newFilePath)	Saves excel as new file
void	setActiveCell(String filePath, ExcelCellPosition position)	Sets active cell
void	setActiveCell(String filePath, String coordinate)	Sets active cell
void	setCell(String filePath, ExcelCellPosition position, String value)	Sets cell value
void	setCell(String filePath, String coordinate, String value)	Sets cell value
void	setCells(String filePath, String coordinate, List<String> values, boolean isVertical)	Sets cell value
void	setRange(String filePath, String coordinateFrom, \ List<List<String>> values)	Gets range values
void	setRange(String filePath, String coordinateFrom, \String coordinateTo, List<List<String>> values)	Gets range values
void	switchSheet(String filePath, int index)	Selects as active sheet by index
void	switchSheet(String filePath, String name)	Selects as active sheet by index

WorkFusion Documentation

9.0

Robotics API

tip

Examples

Excel class usage

Copying range between 2 files

Excel class methods