Excel DataProvider Robot Template

The Excel DataProvider template shows you how to work with an Excel file in a robotic process. The robotic process generated with this template contains an Excel file example. The objective for this template is to show the basic mechanisms for:

  • Opening the Excel file
  • Iterating over each of its rows
  • Changing cells
  • Saving the file and returning it as a result of the robot

This template shows how a robotic process can interact with one program and execute multiple actions in one interface. This page shows how to use each method in the module. When you use the module in a robotic process, call the methods in an order that makes the most sense for your use case.

Learn more about the Data Provider module.

Workflow

The template workflow includes four actions: Start, More items? decision, Process Item, and End. The process loops when "More items?" is determined to be true. When there are no more items to process, the robotic process ends.

excel-robot-workflow.png

File example

The robotic process will process an Excel file, which is included as a support file in the generated robotic process.

excel-sp.png

The contents of the file appears below. The file contains four columns:

data-provider-file.png

The robotic process will go through each row of the file, updating the Result column with the text resulting from concatenation of Column 1, Column 2 and Column 3.

Implementation

This section explains the three classes included in the project:

ExcelRow

This Java class encapsulates the data contained in a row in a single object. It contains four String variables (col1, col2, col3 and result), and its respective getter and setter methods.

ExcelRowMapper

This is the most important class to be implemented when using the ExcelDataProvider functionality.

The ExcelDataProvider instance will use this implementation to iterate over each row, make modifications, and know when it should be finished. To do this, implement three methods defined in the IRowMapper interface (each is described in more detail below):

  • map: To define how to transform a row into a JavaBean class instance.
  • update: To define how to modify a line in the Excel file.
  • isLastRow: To tell the ExcelDataProvider which is the last row of the file.

First, define the numerical constants to specify the column in which each field is located.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
/**
 * First column.
 */
private static final int COL_1 = 0;

/**
 * Second column.
 */
private static final int COL_2 = 1;

/**
 * Third column. 
 */
private static final int COL_3 = 2;

/**
 * Column with the result.
 */
private static final int RESULT_COL = 3;

The map method has two input parameters: (1) the IExcel instance with the data from the Excel file and (2) the row number to read. The output must be a JavaBean instance with the data contained in the row. The implementation creates a new ExcelRow instance, reads each column of the line received as parameter, and fills the Java class with the data read.

Use the methods defined in IExcel to read the row.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
/**
 * @see com.novayre.jidoka.data.provider.api.IRowMapper#map(java.lang.Object, int)
 */
@Override
public ExcelRow map(IExcel data, int rowNum) {

	ExcelRow r = new ExcelRow();

	r.setCol1(data.getCellValueAsString(rowNum, COL_1));
	r.setCol2(data.getCellValueAsString(rowNum, COL_2));
	r.setCol3(data.getCellValueAsString(rowNum, COL_3));
	r.setResult(data.getCellValueAsString(rowNum, RESULT_COL));

	return isLastRow(r) ? null : r;
}

The update method has three input parameters: (1) the IExcel instance with the data from the Excel file, (2) the row number to be updated, and (3) the JavaBean class with the new data to be saved in the row.

Use the methods defined in IExcel to update the row.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
/**
 * @see com.novayre.jidoka.data.provider.api.IRowMapper#update(java.lang.Object, int, java.lang.Object)
 */
@Override
public void update(IExcel data, int rowNum, ExcelRow rowData) {

	data.setCellValueByRef(new CellReference(rowNum, COL_1), rowData.getCol1());
	data.setCellValueByRef(new CellReference(rowNum, COL_2), rowData.getCol2());
	data.setCellValueByRef(new CellReference(rowNum, COL_3), rowData.getCol3());
	data.setCellValueByRef(new CellReference(rowNum, RESULT_COL), rowData.getResult());

	// Auto size the column
	for (int i = 1; i < 5; i++) {
		data.getSheet().autoSizeColumn(i);
	}
}

The isLastRow method has one input parameter: the JavaBean class with the new data read from a row. The output is a boolean value to indicate if we are at the end of the file. If true, there are no more rows to be processed.

1
2
3
4
5
6
7
8
9
10
11
12
/**
 * The last row is determined when the first row without content in the first
 * column is detected.
 * 
 * Another possibility could be to check also the second and the third columns.
 * 
 * @see com.novayre.jidoka.data.provider.api.IRowMapper#isLastRow(java.lang.Object)
 */
@Override
public boolean isLastRow(ExcelRow instance) {
	return instance == null || StringUtils.isBlank(instance.getCol1());
}

Optionally, we can overwrite the mustBeProcessed method. This method is used to tell the ExcelDataProvider whether a row should be processed or not. The default implementation returns true.

1
2
3
4
5
6
7
8
9
10
/**
 * Checks if the current instance must be considered a valid item, only
 * valid items are counted and processed
 * 
 * @param instance
 * @return
 */
default boolean mustBeProcessed(T instance) {
	return true;
}

RobotDataProviderExcelTemplate

As we have already mentioned, the template contains a complete example of the ExcelDataProvider implementation. Let's review:

Declaring variables and constants

For this robotic process, we have defined the following:

  1. Constant with the Excel FileName to process. This file is included in the template. You can change this definition using an instruction file in subsequent executions.

    1
    2
    
     /** The Constant EXCEL_FILENAME. */
     private static final String EXCEL_FILENAME = "robot-dataprovider-excel-inputfile.xlsx"
    
  2. Declare the server variable to manage the interaction with the server:

    1
    2
    
     /** Server. */
     private IJidokaServer<?> server;
    
  3. Declare the variables to manage the ExcelDataProvider and the iteration:

    1
    2
    3
    4
    5
    6
    7
    8
    
     /** The data provider. */
     private IJidokaExcelDataProvider<ExcelRow> dataProvider;
        
     /** The current item. */
     private ExcelRow currentItem;
        
     /** The excel file. */
     private String excelFile;
    

startUp() method

The startUp() method initializes the Appian RPA modules used by the robotic process. Here, we start the IJidokaDataProvider module.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
/**
 * Action "startUp".
 * 
 * This method is overridden to initialize the Appian RPA modules instances.
 */
@Override
public boolean startUp() throws Exception {

	server = (IJidokaServer<?>) JidokaFactory.getServer();
	
	dataProvider = IJidokaDataProvider.getInstance(this, Provider.EXCEL);
	
	return IRobot.super.startUp();
}

start() method

The start() method initializes all applications that the robotic process will use. In this case, it only initializes ExcelDataProvider. You can get detailed information about the ExcelDataProvider initialization in the documentation.

After the initialization, we can now get the number of items to treat by invoking the count() method.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
/**
 * Initializes the data provider.
 * 
 * Action "start".
 *
 * @throws Exception
 *             in case any exception is thrown during the initialization
 */
public void start() throws Exception {
	
	server.info("Initializing Data Provider with file: " + EXCEL_FILENAME);
	
	// Path (String) to the file containing the items to process
	excelFile = Paths.get(server.getCurrentDir(), EXCEL_FILENAME).toString();
	
	// Initialization of the Data Provider module using the RowMapper implemented
	dataProvider.init(excelFile, null, 0, new ExcelRowMapper());
	
	// Set the number of items relying on the Data Provider module
	server.setNumberOfItems(dataProvider.count());
}

hasMoreItems() method

The hasMoreItems() method iterates through the rows of the Excel sheet. The nextRow() method positions the reading pointer in the next row. It will return true if there is a new row and false otherwise.

1
2
3
4
5
6
7
8
9
/**
 * Checks for more items.
 *
 * @return the string representing the wire name in the workflow to follow.
 */
public String hasMoreItems() {	
	// To get the next row, we rely again on the Data Provider module
	return dataProvider.nextRow() ? "yes" : "no";
}

processItem() method

Once positioned in the row of the Excel file, the robotic process gets the information to treat. The processItem() method will read each row by mapping its content to an instance of the ExcelRow class. Later, it will update the cell corresponding to the Result column in the 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
/**
 * Processes an item.
 * 
 * In this template example, the processing consists of concatenating the first
 * 3 columns to get the string result and update the last column.
 */
public void processItem() {

	// Get the current item through Data Provider
	currentItem = dataProvider.getCurrentItem();
		
	// The key to use is the literal "row" plus the number of the item
	String itemKey = "row " + dataProvider.getCurrentItemNumber();
	server.setCurrentItem(dataProvider.getCurrentItemNumber(), itemKey);
		
	// The process is very simple: to concatenate the 3 columns to get the result
	String result = currentItem.getCol1() + currentItem.getCol2() + currentItem.getCol3();
	currentItem.setResult(result);
		
	// Update the item in the Excel file through Data Provider
	dataProvider.updateItem(currentItem);
		
	// We consider this item is OK
	server.setCurrentItemResultToOK(currentItem.getResult());
}

end() method

The end() method is the last action called in the workflow. In this template, we send a message to the server that the process is over.

1
2
3
4
5
6
/**
 * Action to end the robot.
 */
public void end() {
	server.info("End process");
}

cleanUp() and closeDataProvider() methods

In this template, we close the ExcelDataProvider in the cleanUp() method to ensure that it is always closed, even if there are exceptions. We do that by invoking the closeDataProvider() method.

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
/**
 * Method to close the data provider.
 * 
 * It's a private method to be called from the
 * {@link #cleanUp()} method to assure the data provider is correctly closed.
 * 
 * @throws IOException
 *             if an I/O error occurs
 */
private void closeDataProvider() throws IOException {
	
	if (dataProvider != null)  {
		
		server.info("Closing Data Provider...");
		
		dataProvider.close();
		dataProvider = null;
	}
}

/**
 * Clean up.
 * 
 * Besides returning the updated Excel file, it tries to close the data
 * provider. This is useful to assure that executions with problems close it
 * too.
 *
 * @return an array with the paths of the files to return
 * @throws Exception
 *             in case any exception is thrown
 * 
 * @see com.novayre.jidoka.client.api.IRobot#cleanUp()
 */
@Override
public String[] cleanUp() throws Exception {
	
	closeDataProvider();
	if (new File(excelFile).exists()) {
		return new String[] { excelFile };
	}

	return IRobot.super.cleanUp();
}
Open in Github

On This Page

FEEDBACK