Data-Provider Module

This section will discuss the Appian RPA Data-Provider module (jidoka-data-provider-api). The Data-Provider module enables access to data providers such as Excel.

To use the Data-Provider module in our projects, we should include the dependency:

1
2
3
4
5
<dependency>
    <groupId>com.novayre.jidoka.module</groupId>
    <artifactId>jidoka-data-provider-api</artifactId>
    <version>${jidoka.version}</version>
</dependency>

in the file pom.xml, where ${jidoka.version} specifies the current Appian RPA version.

This module essentially offers two interfaces to handle Excel files:

And one interface to handle ZIP files:

IJidokaExcelDataProvider assumes that the Excel file already exists and each row will be used as an item. Other operations are delegated to the provider, such as knowing whether there are more rows (items) or not, which item is the current item, how to get an its information and how to put it into Excel when we want to update a row. An example could be a robot that reads a list of employees from an Excel file to perform some actions on them: salary payments, holydays scheduling, etc.

IExcel will be used when the Excel does not contain the items to be processed. It publishes the methods used by IJidokaExcelDataProvider to actually access Excel.

IJidokaZipDataProvider assumes that the ZIP file already exists and each compressed file will be used as an item. They can be filtered by the extension. An example could be a robot that reads a list of XML files compressed within a ZIP file and send them to a web service to process them.

IExcel

IExcel enables Excel files handling. It uses Apache POI to perform this handling. It inherits from AutoCloseable, so you should use a try-with-resources whenever possible.

In this section, we will discuss the interface IExcel. We will see some examples of its most important methods and, finally, we will join all the examples together in a robot to demonstrate the use of the interface IExcel. You should bear in mind that they are use case examples, that is, they may use certain variables not previously declared, which should be assumed to exist.

Obtaining an instance of IExcel

We can obtain an instance of the interface IExcel this way:

1
IExcel excel = IExcel.getExcelInstance(this);

Where this refers to the class that implements the interface IRobot, that is, it represents our Appian RPA Robot.

Initializing an existing Excel file

Now it's time to initialize the instance with an Excel file that already exists using the init method (String url), where url is the path to the file we want to open.

IExcel inherits from AutoCloseable, so, as we said previously, the appropriate thing whenever possible is to use a try-with-resources:

1
2
3
4
5
6
try(IExcel excel = IExcel.getExcelInstance(this)) {
excel.init(excelPath);
    // TODO: do something with Excel
} catch(Exception e) {
    // TODO: manage the exceptions
}

Now it's time to initialize the instance with an existing Excel file through the method init(String url), where url is the path to the file that we want to open.

IExcel inherits from AutoCloseable, therefore, as mentioned earlier, we should use whenever possible a try-with-resources:

Creating the file with IExcel

So far, we have assumed that the Excel file already exists. But, how can we use IExcel to work with a file that does not exist yet? What context could lead us to this situation?

Maybe our robot must process a list of items, read from any data source, and save a record of each processing into an Excel file. For example, a robot that gets a list of products from a database and then it must save in an Excel file those products whose expiration date is before a given date. For this task, we need to create previously the file in which we are going to save the data.

IExcel provides the method create to directly create the file, saving us from interacting with Apache POI for this.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
// Get the robot working directory
String robotDir = server.getCurrentDir();

// Create a unique name for the Excel file
String name = String.valueOf(new Date().getTime()) + ".xlsx";

// Final path of the file
File file = Paths.get(robotDir, name).toFile();
// Create a new document
Workbook workBook = new XSSFWorkbook();
try (FileOutputStream fileOut = new FileOut-putStream(file.getAbsolutePath())) {
    workBook.write(fileOut);
} catch (Exception e) {
    // TODO Manage the exception
}

With this code, the Workbook is created with POI. This way, we wouldn't have access to IExcel, unless we initialize the file that we have just created through the method init, which was seen in Obtaining an instance ofIExcel.

We could create the file easier directly with IExcel:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
// Get the robot working directory
String robotDir = server.getCurrentDir();

// Create a unique name for the Excel file
String name = String.valueOf(new Date().getTime()) + ".xlsx";

// Final path of the file
File file = Paths.get(robotDir, name).toFile();
excelPath = file.getAbsolutePath();// Inicializar la instancia de IEx-cel 
try(IExcel excel = IExcel.getExcelInstance(this)) {
    // Create the file and initialize excel
    excel.create(excelPath, EExcelType.XLSX);
    // Perform task with Excel here
} catch(Exception e) {
    // manage exceptions here
}

Once we have created the file this way, we will have access to it with IExcel.

Apart from the extensive use that we could make of IExcel through its methods, it also provides direct access to work with the classes of Apache POI.

We assume a basic knowledge of Apache POI, though we will briefly discuss the interface Workbook.

The interface Workbook is the highest-level representation of an Excel workbook in Apache POI. XSSFWorkbook and HSSFWorkbook are two classes of Apache POI that implement Workbook.
As of Excel 2007, Microsoft adopts a new Excel format, called OOXML (Office Open XML), adding an "x" to the previous extension of Office documents (*.xls → *.xlsx, *.doc → *.docx, …), that has become an international standard in the present. Formerly, Microsoft used its own proprietary format.

According to the Apache POI documentation itself:

  • HSSFWorkbook (HSSF): suitable for Excel files handling, previous to the OOXML format.
  • XSSFWorkbook (XSSF): the chosen one for Excel files handling in OOXML format.

As we have stated before, IExcel greatly simplifies the task of creating and initializing an Excel file, providing us the method:

1
void create(String url, EExcelType type) throws Exception;

It directly creates the Excel file, based on the path provided by the parameter url, and initializes the instance of IExcel.

In the previous example, the method create of IExcel creates the file and initializes IExcel. Again, we use a try-with-resources when we obtain the instance of IExcel, though it won't be always possible depending on the task to be performed by the robot (and so, the necessary code).

The method create(String url, EExcelType type) receives the following parameters:

  • url: which is the path to the Excel file to be created. In the example, we have used a unique name, since IExcel will overwrite the file in case it already exists.
  • type: this parameter accepts one of the values of the enum type EExcelType, which are:
    • XLS: for files *.xls.
    • XLSX: for files *.xlsx.

As we can see, this method is quite more agile than the previous option.

Adding a Sheet to the Workbook

Once we have initialized the instance of IExcel, we will add a sheet to the workbook.

To do so, we will use the method boolean createSheet(String sheetName), which will add a new sheet to the workbook, using the specified name.

Let's add a sheet called "Sheet 1":

1
excel.createSheet("Sheet 1");

The method excel.createSheet("Sheet 1") will return true if everything went well. We should check the returned value before entering data into the sheet:

1
2
3
if(excel.createSheet("Sheet 1")) {
    // The sheet was added
}

This method is overloaded and allows you to create a sheet in a specific position:

1
boolean createSheet(String sheetName, int index)

The interface Sheet (org.apache.poi.ss.usermodel.Sheet) represents a sheet in the Excel workbook. It inherits from Iterable<T> (Iterable<Row>).

It provides a wide range of methods that cover most of the needs that we will have when handling an Excel sheet, such as creating rows, deleting rows, getting a specific row or setting measurements, dimensions and margins. We recommend checking the Apache POI documentation for Sheet.

Other sheet-related methods provided by IExcel allow you to copy and clone sheets, as well as getting and setting the working sheet.

Entering data

Now let's see how to enter data within the sheet "Sheet 1" that we have just created.

Suppose our robot must read from some data source (INPUT) the books stock of a bookstore, and once all items have been processed (for example, requesting more stock for some of the books, or changing the price), saving the list of books into an Excel sheet (OUTPUT). We won't go into details neither about the data source, nor about each book (item) processing. We will focus on how to write the data into the Excel file.

The information that will be received by our robot is a list of books, of which we know the title, author, price and stock available:

1
2
3
4
5
private String[] books = new String[]{
"The World As I See It#Albert Einstein#8.95#10",
"The Grand Design#Stephen Hawking#10.5#5",
"El Universo para Ulises#Juan Carlos Ortega#9.95#3" 
}; 

After processing each item, these data should be written in the Excel file.

Now we will inform the server about the items to be processed. In our case, to focus on the data writing on Excel, we will use the number of books that should be written in Excel.

A real robot should use each book as an item, since they are read from the data source, and an item processing should be:

Reading – Working with the item (sending an order, etc.) – saving the output.

We insist that, for clarity, we will ignore this process and we will focus on writing the data to Excel. Therefore, in our example the total number of items is 3:

1
2
// Notify the number of items to the server
server.setNumberOfItems(books.length);

The file that we will use is the one that we have previously created and initialized, to which we have already added a sheet called "Sheet 1". So, we check that the Workbook is opened and that sheet exists:

1
2
3
4
5
6
// Check that the workbook is initialized and contains a sheet
if (excel.isOpen() && excel.getWorkbook().getSheet("Sheet 1") != null) {
    // Work...
} else {
    throw new Exception("Could not create Sheet");
}

Once checked, let's enter the data.

Creating the header and applying a style

Ideally, we should first create a row for the header, that is, which will properly define the content of each column cells. Let's create a header with the columns: Title – Author – Price – Stock. To do so, we will create a row in the sheet "Sheet 1".

1
2
// Create the header row
Row row = excel.getSheet().createRow(0);

We create an object Row. The interface Row (org.apache.poi.ss.usermodel.Row) represents an Excel row. In the same way that Sheet inherits from Iterable<T> (Iterable<Row>), Row inherits from Iterable<Cell>, which allows you to iterate on the row's cells. We have created a row in the only sheet we have. The method getSheet returns the sheet "Sheet 1". An alternative method, especially when we have several sheets, is:

1
Row row = excel.getWorkbook().getSheet("Sheet 1").createRow(0);

Once we have created the row, we will enter the values into the cells. We should create four cells, for Title, Author, Price and Stock.

1
2
3
4
5
// Header content
row.createCell(0, CellType.STRING).setCellValue("Title");
row.createCell(1, CellType.STRING).setCellValue("Author");
row.createCell(2, CellType.STRING).setCellValue("Price");
row.createCell(3, CellType.STRING).setCellValue("Stock");

With this code, we have created the header by using the method of the class Row:

1
createCell(int column, int type)

which returns the object Cell (org.apache.poi.ss.usermodel.Cell) to which we have assigned a value with:

1
setCellValue(...)

The method createCell receives the following parameters:

  • int column: which indicates the column on which the cell will be entered (zero-based)
  • int type: which sets the kind of content of the cell. In this case, we have used the content type CELL_TYPE_STRING. The types available are: CELL_TYPE_NUMERIC, CELL_TYPE_STRING, CELL_TYPE_FORMULA, CELL_TYPE_BLANK, CELL_TYPE_BOOLEAN and CELL_TYPE_ERROR.

It returns, as we have mentioned, an object Cell which represents a cell. Another option would have been creating each cell independently and setting its content type lately:

1
2
3
Cell cell = row.createCell(0);
cell.setCellValue("Title");
cell.setCellType(CellType.STRING);

The first option is faster, but we have mentioned this alternative because we often need to work with the cell independently. Our Excel workbook, at this point, will have one sheet called "Sheet 1", and a header row with the columns "Title", "Author", "Price" and "Stock". It may be a good idea to see how it looks like (following image).

data-provider-sheet-1.png

Well, our code has served its purpose. The text that we have entered appears correctly in the row and cells specified, though its aspect can be improved. For example, the cell width could adjust to the content, and we could even want to center the text within the cell. To do so, let's apply a style to the row.

We can achieve this by using the class XSSFCellStyle, which represents a style that we will apply to our workbook. Remember that we have created it as XSSFWorkbook. Another option is HSSFCellStyle for HSSFWorkbook. Both classes implement the interface CellStyle.

Let's see a basic example of using XSSFCellStyle, though we recommend checking the Apache POI documentation for further details about these classes. We will create a style and we will apply it to the header row:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
// Create header row style
XSSFCellStyle style = (XSSFCellStyle)
        excel.getWorkbook().createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);
style.setFillForegroundColor(new XSSFColor(new java.awt.Color(255, 0, 0)));
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

// Create the header row font
XSSFFont font = (XSSFFont) excel.getWorkbook().createFont();
font.setColor(IndexedColors.WHITE.getIndex());
font.setFontName("Verdana");
font.setFontHeight(10);
style.setFont(font);

// Apply style
row.getCell(0).setCellStyle(style);
row.getCell(1).setCellStyle(style);
row.getCell(2).setCellStyle(style);
row.getCell(3).setCellStyle(style);

// Define the column width
excel.getSheet().setDefaultColumnWidth(20);

We have created a new XSSFCellStyle through the Workbook. Once created, we have set the desired alignment with XSSFCellStyle.ALIGN_CENTER. Then we set a background color and the desired pattern with XSSFCellStyle.SOLID_FOREGROUND, and then we have created a customized font ("Verdana", size 10pt and white color) and have applied it to the style. Finally, we have applied the style to all the cells.

The last line sets the sheet's default column width. Another option, often used, is setting each cell width based on its content:

1
excel.getSheet().autoSizeColumn(0);

For example, the previous line makes the first column (0, remember it uses zero-based indexing) adjust its width to its content. This should be done with all the columns whose width we want to adjust. Apache POI recommends doing this at the end, when we have already entered all the data, because it could slow the process.

Let's see how our header looks like. We have rescaled it for proper visualization (following image).

2230798.png

The code above has applied the style to all the cells in the row with content. Of course, It exists the possibility to apply the style only to determinate cells:

1
2
3
4
row.getCell(0).setCellStyle(style);
row.getCell(1).setCellStyle(style);
row.getCell(2).setCellStyle(style);
row.getCell(3).setCellStyle(style);

This would apply the style only to the first four cells (following image).

data-provider-sheet-2.png

Entering the records

It's time to enter the records. Each record is an item, as we mentioned at the beginning of this example.

We will focus on data entering. We assume that the array contains the books with the right format, that is, each array item is a book and its properties (title, author, price and stock) are separated by a pound sign ("#").

We are going to retrieve each book, we will read its properties and write them in the Excel file, each book in a single row. We will use two variables as counters:

  • itemIndex: as the books array index (zero-based). Its initial value is 0.
  • currentItem: which represents the current item. Its initial value is 1.
1
2
3
4
5
6
// Get info
String[] book = books[itemIndex].split("#");
String title = book[0];
String author = book[1];
String price = book[2];
String num = book[3];

Now that we have the data, we can create a new row in our workbook:

1
2
// Create row
Row row = excel.getSheet().createRow(currentItemIndex + 1);

currentItem is the variable that refers to the current item. Its initial value is 1. Since our first row is the header row, the first book will be entered in the second row.

Now we can write the content of the row:

1
2
3
4
row.createCell(0, CellType.STRING).setCellValue(title);
row.createCell(1, CellType.STRING).setCellValue(author);
row.createCell(2, CellType.NUMERIC).setCellValue(price);
row.createCell(3, CellType.NUMERIC).setCellValue(num);

We have specified here each cell's value type. The first two cells are string type cells, and the last two are numeric.

We increase the counters:

1
2
itemIndex++;
currentItemIndex++;

Important note: when we finish entering the data and using IExcel, all resources used by IExcel should be closed and released by using the method close, which closes the file and releases the resources. If it had been already closed earlier, this method will cause no effect. If our robot task is compatible with initializing IExcel with a try-with-resources, then you don't need to use the method close because the JVM will call it for us. This method should be called in some robot's action, preferably in the method cleanUp, which is an excellent point to release resources. That's why it doesn't show up in the examples.

Obtaining cells and rows through CellReference

IExcel allows you to obtain cells and rows from a cell reference.

This reference is the class CellReference in Apache POI.

Obtaining a cell through CellReference

There are two methods of IExcel that allows you to obtain a cell from a CellReference:

  • Cell getCell(CellReference reference)
  • Cell getCellWithCreation(CellReference reference)

The difference between them is that the second one will create the cell in case it does not exist. You can initialize CellReference in several ways. For example, we can obtain the cell from the row and column on which it is:

1
Cell cell = excel.getCell(new CellReference(2, 3));

Or creating the cell if it doesn't exist:

1
Cell cell = excel.getCellWithCreation(new CellReference(2, 3));

In these two examples, we have created the CellReference through the row (2) and column (3) on which the cell we want to obtain is. Another option is to obtain the reference from the cell representation in Excel. For example:

1
Cell cell = excel.getCellWithCreation(new CellReference("B3"));

This code obtains a cell from a CellReference based on its position within the sheet, in this case, cell B3 (column B and row 3).

If we already have the cell and we want to obtain its CellReference:

1
CellReference cReference = new CellReference(cell);

We can also indicate the sheet:

1
2
CellReference cReference =
new CellReference("Sheet 1", 3, 2, true, true);

In the previous code, we have used the constructor:

1
CellReference(String pSheetName, int pRow, int pCol, boolean pAbsRow, boolean pAbsCol)

For further information about this class, we recommend checking CellReference in Apache POI.

Obtaining a row through CellReference

Similarly, we can obtain rows from a reference to one of its cells:

1
Row r = excel.getRow(new CellReference("B3"));

In this case, we have also available the method:

1
Row r = excel.getRowWithCreation(new CellReference("B3"));

which will create the row in case it does not exist.

Obtaining or setting a cell value

IExcel also provides methods to get or set a cell value, determined by CellReference, by its name or by its row and column. For example, we can set the value "This is the cell B3" to the cell B3:

1
excel.setCellValueByName("B3", "This is the cell B3");

Frequently, we need to obtain the cells of a row, for example, to apply a certain style to all the cells of the row, or to read their content.

The method getEntireRow returns a list of cells (List<Cell>) of a row according to the row number or directly from an object Row:

  • List<Cell> getEntireRow(int rowIndex)
  • List<Cell> getEntireRow(Row row)
1
excel.setCellValueByRef(new CellReference("B3"), "This is the cell B3");

Similarly, we can obtain a cell value by using its name:

1
excel.getCellValueByName("B3");

Or through a CellReference:

1
excel.getCellValueByRef(new CellReference("B3"));

There are more ways of getting a cell value. Some examples follow:

1
2
3
4
5
6
7
8
9
10
11
// Get value as String
String valor = excel.getCellValueAsString(2, 3);

// Get value as String from a cell identified by its name
String valor = excel.getCellValueAsString(excel.getCellByName("B3"));

// Get value as Date
Date date = excel.getCellValueAsDate(2, 3);

// Get value as Date from a cell identified by its name
Date date = excel.getCellValueAsDate(excel.getCellByName("B3"));

Another interesting option provided by IExcel is the choice of inserting a link into a cell:

1
2
// Link to Appian.com
excel.setCellLink("B3", "http://www.appian.com");

Now the cell B3 will contain a link to Appian.com with the text that we have specified.

Inserting images

To end with, we can also insert images into the cells.

IExcel provides the method insertImage which is overloaded and allows you to insert an image determined by its location, into a cell determined by its name. As we have mentioned, the method is overloaded and allows you to specify several information about the image, such as its height/width or its vertical and horizontal alignment.

1
2
// Insert an image
excel.insertImage(servidor.getCurrentDir() + "/resources/icon.png", "B3");

The code above will insert the image "icon.png", which is located on the robot's working directory subfolder "resources" (following image).

appian.png

Other methods from IExcel

IExcel includes other interesting methods that can be very helpful to us.

Obtaining a list with all the cells of a row

Frequently, we need to obtain the cells of a row, for example, to apply a certain style to all the cells of the row, or to read their content.

The method getEntireRow returns a list of cells (List<Cell>) of a row according to the row number or directly from an object Row:

  • List<Cell> getEntireRow(int rowIndex)
  • List<Cell> getEntireRow(Row row)

Entering formulas and using FormulaEvaluator

Of course, we can work with formulas when necessary. IExcel provides easy access to FormulaEvaluator (org.apache.poi.ss.usermodel.FormulaEvaluator) through its method FormulaEvaluator getFormulaEvaluator().

To enter a formula into a cell:

1
2
Cell cell = excel.getCellWithCreation(new CellReference("D8"));
cell.setCellFormula("SUM(D2:D7)");

To evaluate a formula, we obtain an instance of FormulaEvaluator:

1
2
FormulaEvaluator fEvaluator = excel.getFormulaEvaluator();
CellValue cv = fEvaluator.evaluate(excel.getCell(new CellReference("E8")));

And evaluate the formula with the method CellValue evaluate(Cell cell) of FormulaEvaluator.

We will not stop here for a deep overview of FormulaEvaluator. You can get further information on the Apache POI documentation about FormulaEvaluator.

However, we want to underline that the Appian RPA platform allows you to use any Java library quite easily.

Excel example robotic process

We will develop a robot as an example of IExcel, whose operation will consist of entering the list of books that we saw earlier into an Excel workbook.

The tasks that it should perform are:

  1. Creating the header, applying a style.
  2. Entering each book's data into a row.
  3. Inserting the Jidoka logo at the end, as a signature.

Source Code

You can get the source code of the robot from the following link:

Description Link
Source Code robot-developmentguide-data-provider.zip
Image Support File appian.png

Workflow

It is clear that we can identify each item as a book record.

The list of items has been entered directly in the class, though the robot could obtain it from a data source (INPUT), for example, another Excel file, a text file or a database.

Typically, we use the method of the first action to initialize the necessary variables, so we will do the same in this robot.

The second action will create the listing header and will create a style and apply it.

The third action will be a conditional action and will determine whether there are records left to include.

If so, another action will enter the next book, and if there are not any items left, the action that inserts the Jidoka logo will be performed.

We just have to close the Excel file and end the process.

As usual, the method cleanUp will ensure that everything is left in the same status as it was when the robot started running, and the generated Excel file will be uploaded to the server.

data-provider-workflow-1.png

Implementation

Creating the class and defining the workflow

We already know that the first thing we should do is to create the class that implements IRobot, preceded by the annotation @Robot.

This class represents our robot, and we will call it DataProviderRobot.

1
2
@Robot
public class DataProviderRobot implements IRobot {

We have established, through the console, that the method of the first action to be executed is init.

We declare some necessary variables:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
/**
 * IExcel
 */
private IExcel excel; 

/**
 * Server
 */
private IJidokaServer<?> server;

// Counters
private int currentItemIndex = 1;
private int itemIndex = 0;

// List of books
private String[] books = new String[]{
        "The World As I See It#Albert Einstein#8.95#10",
        "The Grand Design#Stephen Hawking#10.5#5",
        "El Universo para Ulises#Juan Carlos Ortega#9.95#3" 
};

// Path to the file
private String excelPath;

Apart from the instances of IExcel and IJidokaServer, we declare two variables, currentItem and itemIndex, which will help us control de item in process and access the list of items (books).

The initial value of currentItemIndex is 1 because it refers to the item currently being processed. This means that it is the variable that we will use to send the notifications to the server. The value of itemIndex is 0 because we will use it as the index to access the item from the list of items.

We will also create the list of items in this example, although it could have been retrieved by the robot from another data source.

Finally, we will store the path to the file in the variable excelPath, to send the generated file to the server at the end of the process, with the method cleanUp.

Next step is the implementation of the method init, which corresponds to the first action, and which we will use to initialize the necessary instances and variables.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
public void init() throws Exception {
    
    // Initialize the server
    server = (IJidokaServer<?>) JidokaFactory.getServer();
    
    // Initialize IExcel
    excel = IExcel.getExcelInstance(this);
    
    // Get the robot working directory
    String robotDir = server.getCurrentDir();
    
    // Create a unique name for the Excel file
    String name = String.valueOf(new Date().getTime()) + ".xlsx";
    
    // Final path of the file
    File file = Paths.get(robotDir, name).toFile();
    excelPath = file.getAbsolutePath();
    
    // Create the file and initialize excel
    excel.create(excelPath, EExcelType.XLSX);

    // Add a sheet
    excel.createSheet("Sheet 1");
    server.info("IExcel example - Data Provider");
    
    // Notify the number of items to the server
    server.setNumberOfItems(books.length);
}

We already know how to initialize the server and Excel.

We will save the Excel file in the robot's working directory. So, the first thing we do is to obtain this path and establish a unique name, with which we will create an object File. In addition, we store the path to the file in the variable excelPath, to make it accessible.

We could have saved code and created the object File directly, but we have preferred to separate the directory collection from the unique name generation, for clarity.

The method create establishes the Excel file and initializes the instance of IExcel, in this case by creating a XSSFWorkbook.

We create and add a new sheet, which will be called "Sheet 1".

We enter some information in the execution log and notify the server about the total number of items to be processed.

Next action's method will create the header of the list of books:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
public void createHeading() throws Exception {
    
    server.info("Creating heading");
    
    // Check that the workbook is initialized and contains a sheet
    if (excel.isOpen() && excel.getWorkbook().getSheet("Sheet 1") != null) {
        
        // Create the header row
        Row row = excel.getSheet().createRow(0);
        
        // Header content
        row.createCell(0, CellType.STRING).setCellValue("Title");
        row.createCell(1, CellType.STRING).setCellValue("Author");
        row.createCell(2, CellType.STRING).setCellValue("Price");
        row.createCell(3, CellTy-pe.STRING).setCellValue("Stock");
        
        // Create header row style
        XSSFCellStyle style = (XSSFCellStyle)
                excel.getWorkbook().createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setFillForegroundColor(
                new XSSFColor(new java.awt.Color(255, 0, 0)));
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        
        // Create the header row font
        XSSFFont font = (XSSFFont) ex-cel.getWorkbook().createFont();
        font.setColor(IndexedColors.WHITE.getIndex());
        font.setFontName("Verdana");
        font.setFontHeight(10);
        style.setFont(font);
        
        // Apply style
        row.getCell(0).setCellStyle(style);
        row.getCell(1).setCellStyle(style);
        row.getCell(2).setCellStyle(style);
        row.getCell(3).setCellStyle(style);
        
        // Define the column width
        excel.getSheet().setDefaultColumnWidth(20);
        
    } else {
        throw new Exception("Could not create Sheet");
    }
}

We inform the server that the header is being created.

Then, we check that the Excel file is ready, that is, the workbook is opened and contains the sheet. Otherwise we will throw an exception that will make the robot end.

Next, we create the first row, which will contain the header. We create a style for the row, in this case a XSSFCellStyle, and specify the text alignment within the cells, the background and text color, as well as the font type. After that, we apply the style to every cell in the header row.

As we mentioned earlier, the style can be applied directly to the row, but we prefer to apply the style only to the cells with a content.

To finish the method, we establish the default cell width in the sheet.

Checking whether there are items left to add

Now we will write the method of a conditional action, which will determine if there are items left to include:

1
2
3
4
5
6
7
8
public String hasMore() throws Exception {
    
    if (itemIndex < books.length) {
        return "yes";
    }
    
    return "no";
}

Within the method hasMore we check whether we still have to add items to the Excel sheet. If so, the workflow will go to the method addData. If all items have been entered, we should just include the logo.

Entering records

The method addData will retrieve the item to be processed. It gets the information about the book to be included from the array books, which was declared at the beginning, and informs the server that a new item is being processed.

The cells that will contain title, author, price and stock of each book, will be created depending on the content type. The two first will be of string type, and the last two will be numeric.

For numeric values, we enter the content providing the right type to the method setCellValue, that is, for the price we use as a parameter:

1
Double.valueOf(precio);

This way, we prevent Excel from processing the value as text, since it could not be evaluated within a formula. This is what would happen if we use the parameter as String (a hidden apostrophe is usually added to make it considered text).

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
public void addData() throws Exception {
    // Get info
    String[] book = books[itemIndex].split("#");
    String title = book[0];
    String author = book[1];
    String price = book[2];
    String num = book[3];
    
    // Notify the server the start of the item processing
    server.setCurrentItem(currentItemIndex, title);
    
    // Create row
    Row row = excel.getSheet().createRow(currentItemIndex + 1);
    
    // Row content
    row.createCell(0, CellType.STRING).setCellValue(title);
    row.createCell(1, CellType.STRING).setCellValue(author);
    row.createCell(2, CellType.STRING).setCellValue(
            Double.valueOf(price));
    row.createCell(3, CellTy-pe.STRING).setCellValue(Integer.valueOf(num));
    
    server.setCurrentItemResultToOK(title);
    itemIndex++;
    currentItemIndex++;
}

Finally, we increase the counters.

The method insertLogo inserts the Appian logo once all records have been added.

1
2
3
4
5
6
7
8
public void insertLogo() throws Exception {
    
    // Insert the logo in the following row to the last one
    int rowLogo = excel.getSheet().getPhysicalNumberOfRows() + 2;
    String cellName = "A" + rowLogo;
    excel.insertImage(server.getCurrentDir() +
            "/resources/icon.png", cellName);
}

We insert the image icon.png, which is located within the robot's working directory, in the subfolder resources.

To specify the name of the cell in which the image will be inserted, we determine the last row on which a record has been added. This is accomplished with the code:

1
excel.getSheet().getPhysicalNumberOfRows()

which returns the number of rows that have been added. We add two more rows to place the image a little further down. We decide to use the column A.

And this is the result:

data-provider-sheet-3.png

Closing Excel and releasing resources

The next method is closeExcel, which will close the Excel file and release the resource in use by the instance of IExcel.

1
2
3
4
public void closeExcel() throws Exception {
    excel.close();
    excel = null;
}

Last action and method cleanUp

We have included the method end to represent the end of the Workflow.

The method cleanUp ensures that the Excel file is closed and the resources assigned to the instance of IExcel are released.

If everything went well, it will upload the generated file to the server.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
public void end() throws Exception {
}

@Override
public String[] cleanUp() throws Exception {
if (excel != null) {
excel.close();
excel = null;
}

    if (rutaExcel != null) {
        
        return new String[];
    }
    
    return null;

}

IJidokaExcelDataProvider

IJidokaExcelDataProvider makes it easy to work with an Excel file as a data source for our items. By using this data provider, we delegate the control of the items to Appian RPA.

When we need to access an item, or know its position, we will have to access the methods provided by IJidokaExcelDataProvider to obtain the information.

Obtaining an instance of the data provider

We can obtain an instance of IJidokaExcelDataProvider this way:

1
IJidokaDataProvider.getInstance(this, Provider.EXCEL)

Where this refers to the class that implements the interface IRobot, that is, the class representing our Appian RPA Robot.

Initializing the data provider

Now we must initialize the instance by calling the method init (String url, String sheetName, Integer firstRow, IRowMapper<IExcel, T> mapper). The parameters are as follows:

  • url: the path to the Excel file that will be used as data source.
  • sheetName: Excel file sheet that will be used as data input.
  • firstRow: indicates which is the Excel header's last row, so that the next row will be the first row to work on.
  • mapper: instance of IRowMapper.

It is very important to initialize the instance before using the methods it provides, otherwise exceptions may occur.

IRowMapper definition

In the previous section, we have seen that it is necessary to use an instance of IRowMapper to initialize IJidokaExcelProvider. Now we are going to see the purpose of this interface.

The interface IRowMapper defines a contract on how to transform an Excel row (retrieved as an input) into a Java object representing the information of that row. It provides methods to update a specific row and to indicate which is the last Excel row to be read.

This behavior is specified by creating a class which implements the interface. This interface uses generics to determine the data type. Specifically, two:

  • The first one represents the object that operates directly the Excel data.
  • The second one represents the object built from each Excel row.

We can filter the rows to be processed using the musBeProcessed method, this will receive as parameter an IRowMapper instance, that determines which rows have been processed.

Core methods

Once the instance has been initialized and the interface IRowMapper understood, let's see the core methods provided by IJidokaExcelDataProvider:

  • nextRow: this method tells us whether the data provider has a "next" row. If so, it is placed on the row.
  • getCurrentItem: this one returns the object associated with the data provider's current position. It is important to call the method nextRow before using this method. Otherwise an exception will be thrown, for being out of range.
  • getCurrentItemNumber: this method returns the data provider's current position.
  • updateItem: this method allows you to update an item in the data provider. It uses the IRowMapper internally to do so.
  • flush: It allows to perform a save of the sheet, in case of someone would open the file during the robot execution it could see the progress reached.

Example robotic process

We will develop an example robot whose goal will be: Reading a column from an Excel file, send the information to the server, and marking the row as processed on another column. The robot will have an instruction with the input Excel file, called inputFile.

The Excel file will consist of a sheet called "companies", which will have a header row and two columns. The first one will start at position 0, and corresponds to a company name, and the second one will start at position 1, and corresponds to the row process status.

Source Code

You can get the source code of the robot from the following link:

Description Link
Source Code robot-developmentguide-excel-dataprovider.zip
Example input file inputFile.xlsx

Workflow

As we have mentioned, IJidokaDataProvider considers each Excel file row as an item, until the interface IRowMapper thinks right (this may vary depending on each robot's functional specifications).

The workflow will consist of an initial action which will initialize the robot. Then there will be a conditional action to determine whether there are more items. Then, there will be a third action which will process the item, and finally, an ending action.

The third action will be called only while there is another item to process. Otherwise, the final action will be performed.

The following table shows the actions and the associated methods:

Action Method
Init init
More elements? hasMoreItems
Process element processItem
End end

The next image shows the defined workflow.

data-provider-workflow-2.png

Implementation

We will begin by defining our robot class and its attributes. This time we will define three attributes: the instance of the Appian RPA server, the instance of the data provider and the path to the input file.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
/**
 * Robot to use IJidokaExcelDataProvider.
 * 
 * @author Jidoka
 *
 */
@Robot
public class ExcelDataProviderRobot implements IRobot {

    /**
     * Server instance.
     */
    private IJidokaServer<?> server;

    /**
     * Data-Provider instance.
     */
    private IJidokaExcelDataProvider<Company> dataProvider;

    /**
     * Input file path.
     */
    private String inputFile;

Next, we will analyze the conditional action implemented by the method hasMoreItems.

1
2
3
4
5
6
7
8
9
/**
 * Action to check if robot has more items to process.
 * 
 * @return "yes" when has more items, "no" otherwise.
 */
public String hasMoreItems() {

    return dataProvider.nextRow() ? "yes" : "no";
}

As you can see, we use the data provider's method nextRow to check whether there are more items to process. If so, we return the literal "yes", otherwise we return the literal "no".

Now we will see the method processItem, which processes each item from the Excel file.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
/**
 * Action to process item.
 */
public void processItem() {

    Company currentItem = dataProvider.getCurrentItem();
    server.setCurrentItem(dataProvider.getCurrentItemNumber(), 
currentItem.getCompanyName());

    // Process item
    currentItem.setStatus("PROCESSED");
    dataProvider.updateItem(currentItem);
    server.info(String.format("Company processed: %s",
currentItem.getCompanyName()));

    server.setCurrentItemResultToOK();
}

The use of the data provider's methods getCurrentItem, getCurrentItemNumber and updateItem should be highlighted in this method, to interact with the item currently being processed.

Finally, we will analyze the method end and our robot's method cleanUp.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
/**
 * Action to end the robot.
 */
public void end() {

    server.info("End process");
}

/**
 * @see com.novayre.jidoka.client.api.IRobot#cleanUp()
 */
@Override
public String[] cleanUp() throws Exception {
    
    if (dataProvider != null) {
        dataProvider.close();
        dataProvider = null;
    }

    // Output file as the input file modified with items status
    return new String[];
}

In these methods, it is worth highlighting the data provider's closing and the robot's result, which corresponds with the same input file, but with the update performed by the method processItem.

Once we have analyzed our robot's class, we will analyze the auxiliary classes that are necessary to work with IJidokaExcelDataProvider.

The class Company represents the row of the input Excel file to be processed. In this case, the row consists of two fields. The first field corresponds with the company name, and the second field is the row status.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
/**
 * Class that represents a row in the Excel sheet.
 * 
 * @author Jidoka
 *
 */
public class Company implements Serializable {

    /**
     * Serial.
     */
    private static final long serialVersionUID = 1L;

    /**
     * Company name
     */
    private String companyName;

    /**
     * Item status.
     */
    private String status;

    public String getCompanyName() {
        return companyName;
    }

    public void setCompanyName(String companyName) {
        this.companyName = companyName;
    }

    public String getStatus() {
        return status;
    }

    public void setStatus(String status) {
        this.status = status;
    }
}

Now we will analyze the class CompanyRowMapper, which transforms the Excel file's row into an instance of Company and vice versa. It also determines the last row of the Excel file.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
/**
 * Class to transform a Excel row in object and vice versa.
 * 
 * @author Jidoka
 *
 */
public class CompanyRowMapper implements IRowMapper<IExcel, Company> {

    /**
     * @see com.novayre.jidoka.data.provider.api.IRowMapper#map(java.lang.Object,
     *      int)
     */
    @Override
    public Company map(IExcel data, int rowNum) {

        Company item = new Company();
        item.setCompanyName(data.getCellValueAsString(rowNum, 0));
        item.setStatus(data.getCellValueAsString(rowNum, 1));
        return item;
    }

    /**
     * @see com.novayre.jidoka.data.provider.api.IRowMapper#update(java.lang.Object,
     *      int, java.lang.Object)
     */
    @Override
    public void update(IExcel data, int rowNum, Company rowData) {

        // Update status
        data.setCellValueByRef(new CellReference(rowNum, 1),
rowData.getStatus());
    }

    /**
     * @see com.novayre.jidoka.data.provider.api.IRowMapper#isLastRow(java.lang.Object)
     */
    @Override
    public boolean isLastRow(Company item) {

        return item == null
|| StringUtils.isBlank(item.getCompanyName());
    }
}

In this case, we can see that we evaluate that we have reached the last row when we receive an item with an empty company name, or an item whose value is null.

In addition, we can see that the company name corresponds to the column 0, whereas the row status corresponds to the column 1.

IJidokaZipDataProvider

IJidokaZipDataProvider makes it easy to work with a ZIP file as a data source for our items. By using this data provider, we delegate the control of the items to Appian RPA.

When we need to access an item, or know its position, we should access the methods provided by IJidokaZipDataProvider to obtain the information.

We can filter the files we want to process in the data provider. We can filter by one or more extensions. In case no filter is specified, all files contained in the ZIP file will be processed.

Obtaining an instance of the data provider

We can obtain an instance of IJidokaZipDataProvider this way:

1
IJidokaDataProvider.getInstance(this, Provider.ZIP)

Where this refers to the class that implements the interface IRobot, that is, the class representing our Appian RPA Robot.

Initializing the data provider

Now, we should initialize the instance by calling the method init (String url, String... extensionsToExtract). The parameters are as follows:

  • url: this is the path to the ZIP file that will be used as data source.
  • extensionsToExtract: these are the file extensions to be processed from the ZIP file.

Core methods

Once we have initialized the instance, let's see the core methods provided by IJidokaZipDataProvider:

  • nextRow: this method tells us whether there are more files to process. If so, it is placed on it.
  • getCurrentItem: this method returns the file associated with the data provider's current position. It is important to call the method nextRow before using this method. Otherwise, an exception will occur for being out of range.
  • getCurrentItemNumber: this method returns the data provider's current position.
  • updateItem: this method allows you to update a file in the data provider.

Example Robot

We will develop an example robot whose task will be: reading a ZIP file and processing all the text files (extension .txt) that it contains. The robot will have an instruction with the input ZIP file, called inputFile.

The process will just notify each file name to be processed to the server.

Source code

You can get the source code of the robot from the following link:

Description Link
Source Code robot-developmentguide-zip-dataprovider.zip
Example input file inputFile.zip

Workflow

The Workflow will consist of an initial action, which will initialize the robot and its attributes, a conditional action which will check whether all files within the input ZIP file have been processed, a simple action which will process each file, and finally, a final action which will just display a message on the console.

Action Method
Init init
More files? hasMoreItems
Process file processItem
End end

You can see the defined workflow in the following image.

data-provider-workflow-3.png

We will begin with the definition of our robot class and its attributes. In this robot, there will be only two attributes: the instance of the Appian RPA server and the instance of the data provider.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
/**
 * Robot to use IJidokaZipDataProvider.
 * 
 * @author Jidoka
 *
 */
@Robot
public class ZipDataProviderRobot implements IRobot {

    /**
     * Server instance.
     */
    private IJidokaServer<?> server;

    /**
     * Data-Provider instance.
     */
    private IJidokaZipDataProvider<File> dataProvider;

Now we will analyze the method init. As in the previous example, it will just initialize the robot's attributes and will notify the number of items to the server.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
/**
 * Action to init robot.
 * 
 * @throws Exception
 */
public void init() throws Exception {
    
    server = JidokaFactory.getServer();
    dataProvider = IJidokaDataProvider.getInstance(this, Provider.ZIP);

    // Input file path
    String inputFile = Paths.get(server.getCurrentDir(), server.getParameters().get("inputFile")).toString();
    
    // Init data provider with input file and filter by txt extension
    dataProvider.init(inputFile, "txt");
    
    server.setNumberOfItems(dataProvider.count());
}

Next, we will analyze the conditional action implemented by the method hasMoreItems.

1
2
3
4
5
6
7
8
9
/**
 * Action to check if robot has more items to process.
 * 
 * @return "yes" when has more items, "no" otherwise.
 */
public String hasMoreItems() {
    
    return dataProvider.nextRow() ? "yes" : "no";
}

As you can see, the method is identical to the one in the previous example, and we use the method nextRow to determine whether we should return the literal "yes" or the literal "no".

Now we will discuss the method processItem, responsible for processing each item from the ZIP file.

1
2
3
4
5
6
7
8
9
10
11
12
13
/**
 * Action to process item.
 */
public void processItem() {
    
    File currentItem = dataProvider.getCurrentItem();
    server.setCurrentItem(dataProvider.getCurrentItemNumber(), currentItem.getName());

    // Process item
    server.info(String.format("File processed: %s", currentItem.getName()));

    server.setCurrentItemResultToOK();
}

In this method, we are using the data provider to obtain the information related to the current item and notify it to the server. It is worthy highlighting here that in this kind of data provider, the item is an object java.io.File.

Finally, we will analyze the methods end and cleanUp.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
/**
 * Action to end the robot.
 */
public void end() {
    
    server.info("End process");
}

/**
 * @see com.novayre.jidoka.client.api.IRobot#cleanUp()
 */
@Override
public String[] cleanUp() throws Exception {
    
    if (dataProvider != null) {
        dataProvider.close();
        dataProvider = null;
    }
    
    return new String[0];
}

In these methods, as it was in the previous example, the only remarkable thing is the data provider closing.

Use these tutorials to learn more about the Data Provider module:

Open in Github Built: Fri, Nov 12, 2021 (02:39:09 PM)

On This Page

FEEDBACK