com.workfusion.rpa.helpers

Class Excel

java.lang.Object

com.workfusion.rpa.helpers.Excel

public class Excel
extends java.lang.Object

This class adds static methods for work with excel documents.

Driver Type: Desktop

Note:

In order to work with Excel document, it should be opened with openExcel method first

Samples

openExcel("path/to/excel/file");

switchSheet("path/to/excel/file", index);

setCell("path/to/excel/file", row, column, value);

saveExcel("path/to/excel/file");

closeExcel("path/to/excel/file");

Method Summary

Modifier and Type	Method and Description
static void	closeExcel(java.lang.String filePath) Close excel file and remove it from script context.
static void	deleteCell(java.lang.String filePath, ExcelCellPosition position) Clear of value of cell.
static void	deleteCell(java.lang.String filePath, java.lang.String coordinate) Clear of value of cell.
static java.lang.String	getActiveCell(java.lang.String filePath) Get active cell.
static java.lang.String	getCell(java.lang.String filePath, ExcelCellPosition position) Get value of cell and return it as string.
static java.lang.String	getCell(java.lang.String filePath, java.lang.String coordinate) Get value of cell and return it as string.
static java.util.List<java.lang.String>	getColumn(java.lang.String filePath, ExcelColumnRowPosition position) Get column values
static java.util.List<java.lang.String>	getColumn(java.lang.String filePath, ExcelColumnRowPosition position, java.lang.Integer rowFrom, java.lang.Integer rowTo) Get column values
static java.util.List<java.lang.String>	getColumn(java.lang.String filePath, java.lang.String columnLetters) Get column values
static java.util.List<java.lang.String>	getColumn(java.lang.String filePath, java.lang.String columnLetters, java.lang.Integer rowFrom, java.lang.Integer rowTo) Get column values
static java.util.List<java.util.List<java.lang.String>>	getRange(java.lang.String filePath, java.lang.String coordinateFrom) Get values of the range.
static java.util.List<java.util.List<java.lang.String>>	getRange(java.lang.String filePath, java.lang.String coordinateFrom, java.lang.String coordinateTo) Get values of the range.
static java.util.List<java.lang.String>	getRow(java.lang.String filePath, ExcelColumnRowPosition position) Get row values
static java.util.List<java.lang.String>	getRow(java.lang.String filePath, ExcelColumnRowPosition position, java.lang.String columnFrom, java.lang.String columnTo) Get row values
static java.util.List<java.lang.String>	getRow(java.lang.String filePath, int rowNum) Get row values
static java.util.List<java.lang.String>	getRow(java.lang.String filePath, int rowNum, java.lang.String columnFrom, java.lang.String columnTo) Get row values
static void	openExcel(java.lang.String filePath) Read excel file by path, store this in script context.
static void	saveExcel(java.lang.String filePath) Save excel file.
static void	saveExcel(java.lang.String filePath, java.lang.String newFilePath) Save excel as new file.
static void	setActiveCell(java.lang.String filePath, ExcelCellPosition position) Set active cell.
static void	setActiveCell(java.lang.String filePath, java.lang.String coordinate) Set active cell.
static void	setCell(java.lang.String filePath, ExcelCellPosition position, java.math.BigDecimal value) Set value of cell.
static void	setCell(java.lang.String filePath, ExcelCellPosition position, java.lang.String value) Set value of cell.
static void	setCell(java.lang.String filePath, java.lang.String coordinate, java.math.BigDecimal value) Set value of cell.
static void	setCell(java.lang.String filePath, java.lang.String coordinate, java.lang.String value) Set value of cell.
static void	setCells(java.lang.String filePath, java.lang.String coordinate, java.util.List<java.lang.String> values, boolean isVertical) Set value of cell.
static void	setRange(java.lang.String filePath, java.lang.String coordinateFrom, java.util.List<java.util.List<java.lang.String>> values) Get values of the range
static void	setRange(java.lang.String filePath, java.lang.String coordinateFrom, java.lang.String coordinateTo, java.util.List<java.util.List<java.lang.String>> values) Get values of the range
static void	switchSheet(java.lang.String filePath, int index) Select as active sheet by index.
static void	switchSheet(java.lang.String filePath, java.lang.String name) Select as active sheet by index.

Methods inherited from class java.lang.Object

equals, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Method Detail

openExcel

public static void openExcel(java.lang.String filePath)

Read excel file by path, store this in script context.

Driver Type: Desktop

Parameters:

filePath - string representation of path to excel file.

Example

def filePath = 'D://temp//excel.xlsx'

openExcel(filePath)

closeExcel

public static void closeExcel(java.lang.String filePath)

Close excel file and remove it from script context.

Driver Type: Desktop

Parameters:

filePath - string representation of path to excel file.

Example

def filePath = 'D://temp//excel.xlsx'

openExcel(filePath)

closeExcel(filePath)

saveExcel

public static void saveExcel(java.lang.String filePath)

Save excel file.

Parameters:

filePath - string representation of path to excel file.

Example

def filePath = 'D://temp//excel.xlsx'

openExcel(filePath)

saveExcel(filePath)

saveExcel

public static void saveExcel(java.lang.String filePath,
                             java.lang.String newFilePath)

Save excel as new file.

Driver Type: Desktop

Parameters:

filePath - string representation of path to excel file.

newFilePath - string representation of new path to excel file

Example

def filePath = 'D://temp//excel.xlsx'

def newFilePath = 'D://temp//new-excel.xlsx'

openExcel(filePath)

saveExcel(filePath, newFilePath)

switchSheet

public static void switchSheet(java.lang.String filePath,
                               int index)

Select as active sheet by index.

Driver Type: Desktop

Parameters:

filePath - string representation of path to excel file.

index - index of sheet in excel file. Starts from 0

Example

def filePath = 'D://temp//excel.xlsx'

openExcel(filePath)

switchSheet(filePath, 1)

switchSheet

public static void switchSheet(java.lang.String filePath,
                               java.lang.String name)

Select as active sheet by index.

Driver Type: Desktop

Parameters:

filePath - string representation of path to excel file.

name - name of sheet in excel file.

Example

def filePath = 'D://temp//excel.xlsx'

openExcel(filePath)

switchSheet(filePath, 'Names')

getCell

public static java.lang.String getCell(java.lang.String filePath,
                                       ExcelCellPosition position)

Get value of cell and return it as string.

Driver Type: Desktop

Parameters:

filePath - string representation of path to excel file.

position - ExcelCellPosition.

Returns:

string representation of value of cell.

Example

def filePath = 'D://temp//excel.xlsx'

openExcel(filePath)

def cellValue = getCell(filePath, ExcelCellPosition.CURRENT)

See Also:

ExcelCellPosition

getCell

public static java.lang.String getCell(java.lang.String filePath,
                                       java.lang.String coordinate)

Get value of cell and return it as string.

Driver Type: Desktop

Parameters:

filePath - string representation of path to excel file.

coordinate - string representation coordinate of cell (A1).

Returns:

string representation of value of cell.

Example

def filePath = 'D://temp//excel.xlsx'

openExcel(filePath)

def cellValue = getCell(filePath, 'B1')

setCell

public static void setCell(java.lang.String filePath,
                           ExcelCellPosition position,
                           java.lang.String value)

Set value of cell.

Driver Type: Desktop

Parameters:

filePath - string representation of path to excel file.

position - ExcelCellPosition.

value - string representation of value of cell

Example

def filePath = 'D://temp//excel.xlsx'

openExcel(filePath)

setCell(filePath, ExcelCellPosition.CURRENT, 'New Value')

See Also:

ExcelCellPosition

setCell

public static void setCell(java.lang.String filePath,
                           java.lang.String coordinate,
                           java.lang.String value)

Set value of cell.

Driver Type: Desktop

Parameters:

filePath - string representation of path to excel file.

coordinate - string representation coordinate of cell (A1).

value - string representation of value of cell

Example

def filePath = 'D://temp//excel.xlsx'

openExcel(filePath)

setCell(filePath, 'B1', 'New Value')

setCell

public static void setCell(java.lang.String filePath,
                           ExcelCellPosition position,
                           java.math.BigDecimal value)

Set value of cell.

Driver Type: Desktop

Parameters:

filePath - string representation of path to excel file.

position - ExcelCellPosition.

value - BigDecimal value to be set to cell

Example

def filePath = 'D://temp//excel.xlsx'

openExcel(filePath)

setCell(filePath, ExcelCellPosition.CURRENT, new BigDecimal('3.14'))

See Also:

ExcelCellPosition

setCell

public static void setCell(java.lang.String filePath,
                           java.lang.String coordinate,
                           java.math.BigDecimal value)

Set value of cell.

Note: implemented for desktop driver only

Parameters:

filePath - string representation of path to excel file.

coordinate - string representation coordinate of cell (A1).

value - BigDecimal value to be set to cell

Example

def filePath = 'D://temp//excel.xlsx'

openExcel(filePath)

setCell(filePath, 'B1', new BigDecimal('3.14'))

setCells

public static void setCells(java.lang.String filePath,
                            java.lang.String coordinate,
                            java.util.List<java.lang.String> values,
                            boolean isVertical)

Set value of cell.

Note: implemented for desktop driver only

Parameters:

filePath - string representation of path to excel file.

coordinate - string representation coordinate of cell (A1).

values - list of string representation of values of cells

isVertical - boolean representation od direction (true is vertical direction)

Example

def filePath = 'D://temp//excel.xlsx'

List values = ['val1','val2','val3','val4']

openExcel(filePath)

setCells(filePath, 'C3', values, false)

getRow

public static java.util.List<java.lang.String> getRow(java.lang.String filePath,
                                                      ExcelColumnRowPosition position)

Get row values

Driver Type: Desktop

Parameters:

filePath - string representation of path to excel file.

position - ExcelColumnRowPosition.

Returns:

list of values

Example

def filePath = 'D://temp//excel.xlsx'

openExcel(filePath)

List rowValues = getRow(filePath, ExcelColumnRowPosition.FIRST)

See Also:

ExcelColumnRowPosition

getRow

public static java.util.List<java.lang.String> getRow(java.lang.String filePath,
                                                      ExcelColumnRowPosition position,
                                                      java.lang.String columnFrom,
                                                      java.lang.String columnTo)

Get row values

Driver Type: Desktop

Parameters:

filePath - string representation of path to excel file.

position - ExcelColumnRowPosition.

columnFrom - string representation of start column letter

columnTo - string representation of end column letter

Returns:

list of values

Example

def filePath = 'D://temp//excel.xlsx'

openExcel(filePath)

List rowValues = getRow(filePath, ExcelColumnRowPosition.FIRST, 'A', 'D')

See Also:

ExcelColumnRowPosition

getRow

public static java.util.List<java.lang.String> getRow(java.lang.String filePath,
                                                      int rowNum)

Get row values

Driver Type: Desktop

Parameters:

filePath - string representation of path to excel file.

rowNum - int number of row.

Returns:

list of values

Example

def filePath = 'D://temp//excel.xlsx'

openExcel(filePath)

List rowValues = getRow(filePath, 10)

getRow

public static java.util.List<java.lang.String> getRow(java.lang.String filePath,
                                                      int rowNum,
                                                      java.lang.String columnFrom,
                                                      java.lang.String columnTo)

Get row values

Driver Type: Desktop

Parameters:

filePath - string representation of path to excel file.

rowNum - int number of row.

columnFrom - string representation of start column letter

columnTo - string representation of end column letter

Returns:

list of values

Example

def filePath = 'D://temp//excel.xlsx'

openExcel(filePath)

List rowValues = getRow(filePath, 10, 'A', 'D')

getColumn

public static java.util.List<java.lang.String> getColumn(java.lang.String filePath,
                                                         ExcelColumnRowPosition position)

Get column values

Driver Type: Desktop

Parameters:

filePath - string representation of path to excel file.

Returns:

list of values

Example

def filePath = 'D://temp//excel.xlsx'

openExcel(filePath)

List columnValues = getColumn(filePath, ExcelColumnRowPosition.LAST)

See Also:

ExcelColumnRowPosition

getColumn

public static java.util.List<java.lang.String> getColumn(java.lang.String filePath,
                                                         ExcelColumnRowPosition position,
                                                         java.lang.Integer rowFrom,
                                                         java.lang.Integer rowTo)

Get column values

Driver Type: Desktop

Parameters:

filePath - string representation of path to excel file.

position - ExcelColumnRowPosition.

rowFrom - integer representation of start row number

rowTo - integer representation of end row number

Returns:

list of values

Example

def filePath = 'D://temp//excel.xlsx'

openExcel(filePath)

List columnValues = getColumn(filePath, ExcelColumnRowPosition.LAST, 2, 4)

See Also:

ExcelColumnRowPosition

getColumn

public static java.util.List<java.lang.String> getColumn(java.lang.String filePath,
                                                         java.lang.String columnLetters)

Get column values

Driver Type: Desktop

Parameters:

filePath - string representation of path to excel file.

columnLetters - string representation of column letters.

Returns:

list of values

Example

def filePath = 'D://temp//excel.xlsx'

openExcel(filePath)

List columnValues = getColumn(filePath, 'C')

See Also:

ExcelColumnRowPosition

getColumn

public static java.util.List<java.lang.String> getColumn(java.lang.String filePath,
                                                         java.lang.String columnLetters,
                                                         java.lang.Integer rowFrom,
                                                         java.lang.Integer rowTo)

Get column values

Driver Type: Desktop

Parameters:

filePath - string representation of path to excel file.

columnLetters - string representation of column letters.

rowFrom - integer representation of start row number

rowTo - integer representation of end row number

Returns:

list of values

Example

def filePath = 'D://temp//excel.xlsx'

openExcel(filePath)

List columnValues = getColumn(filePath, 'C', 2, 4)

deleteCell

public static void deleteCell(java.lang.String filePath,
                              ExcelCellPosition position)

Clear of value of cell.

Driver Type: Desktop

Parameters:

filePath - string representation of path to excel file.

position - ExcelCellPosition.

Example

def filePath = 'D://temp//excel.xlsx'

openExcel(filePath)

deleteCell(filePath, ExcelCellPosition.CURRENT)

See Also:

ExcelCellPosition

deleteCell

public static void deleteCell(java.lang.String filePath,
                              java.lang.String coordinate)

Clear of value of cell.

Driver Type: Desktop

Parameters:

filePath - string representation of path to excel file.

coordinate - string representation coordinate of cell (A1).

Example

def filePath = 'D://temp//excel.xlsx'

openExcel(filePath)

deleteCell(filePath, 'A1')

setActiveCell

public static void setActiveCell(java.lang.String filePath,
                                 java.lang.String coordinate)

Set active cell.

Driver Type: Desktop

Parameters:

filePath - string representation of path to excel file.

coordinate - string representation coordinate of cell (A1).

Example

def filePath = 'D://temp//excel.xlsx'

openExcel(filePath)

setActiveCell(filePath, 'A1')

setActiveCell

public static void setActiveCell(java.lang.String filePath,
                                 ExcelCellPosition position)

Set active cell.

Driver Type: Desktop

Parameters:

filePath - string representation of path to excel file.

position - ExcelCellPosition.

Example

def filePath = 'D://temp//excel.xlsx'

openExcel(filePath)

setActiveCell(filePath, ExcelCellPosition.CURRENT)

See Also:

ExcelCellPosition

getActiveCell

public static java.lang.String getActiveCell(java.lang.String filePath)

Get active cell.

Driver Type: Desktop

Parameters:

filePath - string representation of path to excel file.

Returns:

string that represent of coordinate

Example

def filePath = 'D://temp//excel.xlsx'

openExcel(filePath)

def activeCell = getActiveCell(filePath)

getRange

public static java.util.List<java.util.List<java.lang.String>> getRange(java.lang.String filePath,
                                                                        java.lang.String coordinateFrom,
                                                                        java.lang.String coordinateTo)

Get values of the range.

Driver Type: Desktop

Parameters:

filePath - string representation of path to excel file.

coordinateFrom - coordinate where range starts

coordinateTo - coordinate where range ends

Returns:

list of list of strings that represents range of cells

Example

def filePath = 'D://temp//excel.xlsx'

openExcel(filePath)

List rangeValues = getRange(filePath, 'F1', 'H3')

getRange

public static java.util.List<java.util.List<java.lang.String>> getRange(java.lang.String filePath,
                                                                        java.lang.String coordinateFrom)

Get values of the range.

Driver Type: Desktop

Parameters:

filePath - string representation of path to excel file.

coordinateFrom - coordinate where range starts

Returns:

list of list of strings that represents range of cells

Example

def filePath = 'D://temp//excel.xlsx'

openExcel(filePath)

List rangeValues = getRange(filePath, 'A4')

setRange

public static void setRange(java.lang.String filePath,
                            java.lang.String coordinateFrom,
                            java.lang.String coordinateTo,
                            java.util.List<java.util.List<java.lang.String>> values)

Get values of the range

Driver Type: Desktop

Parameters:

filePath - string representation of path to excel file.

coordinateFrom - coordinate where range starts

coordinateTo - coordinate where range ends

values - list of lists of strings that represents values of the cells

Example

def filePath = 'D://temp//excel.xlsx'

List values = [['val1','val2','val3','val4']]

openExcel(filePath)

setRange(filePath, 'J10', 'K12', values)

setRange

public static void setRange(java.lang.String filePath,
                            java.lang.String coordinateFrom,
                            java.util.List<java.util.List<java.lang.String>> values)

Get values of the range

Driver Type: Desktop

Parameters:

filePath - string representation of path to excel file.

coordinateFrom - coordinate where range starts

values - list of lists of strings that represents values of the cells

Example

def filePath = 'D://temp//excel.xlsx'

List values = [['val1','val2','val3','val4']]

openExcel(filePath)

setRange(filePath, 'B2', values)