Excel Data Provider Robot Template

The Excel Data Provider template details how to create a robotic process that can interact with data from an Excel file. In this template, the robotic process will view the data from each row in a sample Excel file, concatenate the values from each row, and then update the file with the results of the concatenation.

To access data from an Excel file, this template uses the Data Provider module. Once the robotic process has access to the file, each Excel row is used as an item in your workflow. An item is a single element to be evaluated by the robotic process. In other robotic processes, an item could be a person, an ID, a bank account, an invoice, a job identifier, etc.

When you use a template to create your robotic process, you are provided with a ZIP file containing your source code and a pre-built workflow in the robotic process configuration page.

In the source code of this template, the robotic process references a sample Excel file. This file is included as a support file in your process's configuration page. The robotic process will iterate over each row of the file, updating the Results column with text.

excel-sp.png

This page explains the contents of the workflow and source code generated by the Excel Data Provider template.

Workflow

excel-robot-workflow.png

The Excel Data Provider template's workflow contains four actions: Start, More Items?, Process Item, and End. Similar to an Appian process model, each workflow has a start and end action.

The two other actions are related to processing the items, or rows, in the Excel file. Since there are five rows that need to be processed, the workflow includes a loop to ensure each row has been treated. Loops utilize conditional actions to determine what the process should do if there are more items, or if all available items have been processed.

If you click the list icon 951644.png on any action, you'll see it is associated with a method. These methods are in the source code generated from the template. Within the source code, Appian has provided an outline of how you can configure each method.

Source code

Within the downloaded ZIP file, you will see three files: ExcelRow.java, ExcelRowMapper.java, and RobotDataProviderExcelTemplate.java.

  • ExcelRow is a JavaBean class containing the data for each row of the Excel file. The data for each row is stored in a variable.
  • ExcelRowMapper implements the IRowMapper interface for the robotic process. Learn more about this interface in the Data-Provider module.
  • RobotDataProviderExcelTemplate implements of IRobot interface and contains the methods associated with the actions in the workflow.

ExcelRow

The ExcelRow class encapsulates the data contained in each row of the Excel file. It contains four private string variables: col1, col2, col3, and result. Each variable corresponds to one of the columns in the sample Excel file.

data-provider-file.png

To access the encapsulated data, this class also contains the get and set methods for each variable. This class will be referenced in the two other classes when they need to call the object that represents an Excel row.

ExcelRowMapper

The ExcelRowMapper class implements the IRowMapper interface. This interface determines how to transform an Excel row into a Java object that represents the information in that row. The IRowMapper interface has two inputs: the object that operates directly with Excel data (IExcel) and the object built from each Excel row (ExcelRow). The rows must be transformed to Java objects in order for the robotic process to make modifications to the information.

The IRowMapper interface will be used to iterate over each row in the sheet, make modifications, and indicate the last Excel row to be read. This class is necessary to initialize the IJidokaExcelDataProvider interface in the RobotDataProviderExcelTemplate class.

The ExcelRowMapper class includes the constants that specify each column in the Excel file, and implements the methods defined in the IRowMapper interface:

Method Description
map() Defines how to transform a row into a JavaBean class instance.
update() Defines how to modify a line in the Excel file.
isLastRow() Informs IJidokaExcelDataProvider about which row is the last row to read.

For more information on the methods included in this interface, open the Appian RPA Console and click Help > Javadoc in the left menu.

Constants

The first few lines in the template's source code reference numerical constants. These constants specify the column associated with each field. This value is 0-based, so the first column in the Excel file, Column 1, is 0.

15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
public class ExcelRowMapper implements IRowMapper<IExcel, ExcelRow> {

	/**
	 * 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;

map() method

The map() method defines how to transform a row into a JavaBean class instance.

This method receives two input parameters: the IExcel instance with the data from the Excel file, and the row number to read. The output of this method is a new ExcelRow instance with the data contained in the row.

  • ExcelRow r = new ExcelRow(); (line 43) creates a new ExcelRow instance.
  • r.setCol1(data.getCellValueAsString(rowNum, COL_1)); (line 45) gets the string value from a cell in the first column of the Excel file and assigns the value to the col1 variable. This is performed for each variable.
    • The getCellValueAsString() method is from the IExcel interface. This method reads and returns a cell value as a string. For other methods provided in the IExcel interface, see the Javadoc.
  • return isLastRow(r) ? null : r; (line 50) provides logic for when the process should stop creating new ExcelRow instances. In this case, the process will stop when the map() method returns an item with a value of null.
41
42
43
44
45
46
47
48
49
50
51
52
	@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;
	}

update() method

The update() method defines how to modify a line in the Excel file.

This method receives three input parameters: the IExcel instance with the data from the Excel file, the row number to update, and the JavaBean class with the new data to save into the specified row (ExcelRow).

  • data.setCellValueByRef(new CellReference(rowNum, COL_1), rowData.getCol1()); (line 59) assigns the cell located in the row and column listed in CellReference with the value from the col1 variable. This is performed for each variable.
    • The setCellValueByRef() method is from the IExcel interface. This method sets a new cell value. For other methods provided in the IExcel interface, see the Javadoc.
  • data.getSheet().autoSizeColumn(i); (line 66) sets the default column width for each column based on their contents.
56
57
58
59
60
61
62
63
64
65
66
67
68
	@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);
		}
	}

isLastRow() method

The isLastRow() method indicates if the current instance is the last row. This method receives one input parameter: the JavaBean class with the new data read from the row (ExcelRow).

isLastRow(ExcelRow instance) (line 79) determines that the last line of the file has been reached when it returns a value of true.

78
79
80
81
	@Override
	public boolean isLastRow(ExcelRow instance) {
		return instance == null || StringUtils.isBlank(instance.getCol1());
	}

RobotDataProviderExcelTemplate

The RobotDataProviderExcelTemplate class implements the IRobot interface and is annotated by @Robot. Every Appian RPA robotic process must have the IRobot interface and @Robot annotation to allow the Appian RPA API to identify the class as a robot.

RobotDataProviderExcelTemplate includes several methods and attributes. Some of the methods are directly associated with workflow actions; however, the template also generates additional methods that are called by the platform to prepare a robotic process or clean up a resource after the process ends.

The class contains the following methods:

Method Description Associated Action
startUp() Called by the platform prior to the execution of the actions in the workflow. -
start() Provides initial information to the process. Start
hasMoreItems() Implements the workflow-guided loop. More items?
processItem() Processes and sets the result status of each item. Process Item
end() Provides any final information to the process. End
cleanUp() Called by the platform after all workflow actions have been executed, regardless of the execution's results. -

Attributes

The first few lines in the template's source code reference the attributes and a constant related to the robotic process:

  • private static final String EXCEL_FILENAME = "robot-dataprovider-excel-inputfile.xlsx" (line 27) is a constant that contains the name of the Excel file to process. This file is included in the template. In other robotic processes, You can change this definition using a parameterized robotic process variable in subsequent executions.
  • private IJidokaServer<?> server; (line 30) declares the server variable to manage the interaction with the server. The server is what communicates information to the console.
  • The following variables will manage IJidokaExcelDataProvider and the process's iteration:
    • private IJidokaExcelDataProvider<ExcelRow> dataProvider; (line 33) indicates the data provider.
    • private ExcelRow currentItem; (line 36) stores the current item and is represented by the ExcelRow JavaBean class. This is necessary to define when iterating over many items.
    • private String excelFile; (line 39) stores the Excel file name.
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
public class RobotDataProviderExcelTemplate implements IRobot {
	
	/** The Constant EXCEL_FILENAME. */
	private static final String EXCEL_FILENAME = "robot-dataprovider-excel-inputfile.xlsx";

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

	/** The data provider. */
	private IJidokaExcelDataProvider<ExcelRow> dataProvider;

	/** The current item. */
	private ExcelRow currentItem;

	/** The excel file. */
	private String excelFile;

startUp() method

The startUp() method can be used to verify conditions on your resource before executing the actions in your workflow. For example, you can verify if an application is available, determine the screen resolution, or verify that the robotic process can be executed. This is also where you can initialize the different instances needed by the robotic process.

In this template, the startUp() method is where you will get an instance of IJidokaExcelDataProvider and initialize IJidokaServer.

  • server = (IJidokaServer<?>) JidokaFactory.getServer(); (line 49) initializes IJidokaServer.
  • dataProvider = IJidokaDataProvider.getInstance(this, Provider.EXCEL); (line 51) gets an instance of IJidokaExcelDataProvider, where this refers to the class that implements the IRobot interface.
46
47
48
49
50
51
52
53
54
@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 is used to define the initial actions of the workflow and is associated with the Start action. This is also where you can initialize objects that will be used by the robotic process if you do not want to use the startUp() method.

This is where you will initialize the instance of IJidokaExcelDataProvider obtained from the startUp() method. Since the template is designed to process many rows of data, this is also where you can indicate the total number of rows that will be processed using the count() method defined in the IJidokaExcelDataProvider interface.

  • server.info (line 66) sends a log message of type info to the server to indicate IJidokaExcelDataProvider is being initialized with the file listed in the constant EXCEL_FILENAME.
  • Paths.get(server.getCurrentDir(), EXCEL_FILENAME).toString(); (line 69) defines excelFile with the path to the Excel file that will be processed.
  • dataProvider.init(excelFile, null, 0, new ExcelRowMapper()); (line 72) initializes the instance of IJidokaExcelDataProvider obtained in the startUp() method. This is initialized using the ExcelRowMapper class since it implements IRowMapper.
  • server.setNumberOfItems(dataProvider.count()); (line 75) uses the count() method to indicate the number of rows to treat from the Excel file.
64
65
66
67
68
69
70
71
72
73
74
75
76
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 file. This method implements the workflow-guided loop and is associated with a conditional action.

Unlike generic actions, conditional actions can only return string or boolean values because the values must match the output labels on your conditional workflow action. For example, if the hasMoreItems() method returned a boolean value, there would only be two possible outputs for the condition and the output labels would be true and false.

However, the More Items? action in the template's workflow returns two outputs with the labels yes and no, so the method needs to return a string value. When the method returns a string, there can be two or more outputs, and the outputs must match the output labels.

dataProvider.nextRow() (line 87) uses the nextRow() method from the IJidokaExcelDataProvider interface to determine if the data provider has a next row. If there are more rows to process, the output is yes. If not, the output is no.

84
85
86
87
public String hasMoreItems() {	
	// To get the next row, we rely again on the Data Provider module
	return dataProvider.nextRow() ? "yes" : "no";
}

processItem() method

The processItem() method evaluates and processes each item. In this template, the method will read each row of the Excel file by mapping its content to an instance of the ExcelRow class. This method cannot be called until the nextRow() method is called since the process needs to be positioned in the row of the Excel file before processing.

  • currentItem = dataProvider.getCurrentItem(); (line 99) returns the object associated with the data provider's current position.
  • String itemKey = "row " + dataProvider.getCurrentItemNumber(); (line 102) defines the itemKey as the row and the item number. For example, "row 1", "row 2", etc.
  • server.setCurrentItem(dataProvider.getCurrentItemNumber(), itemKey); (line 103) notifies the server about the start of the item processing.
  • String result = currentItem.getCol1() + currentItem.getCol2() + currentItem.getCol3(); (line 106) connects the strings from the first three columns and assigns the concatenated value to result.
  • currentItem.setResult(result); (line 107) assigns the result variable the value of result from line 106.
  • dataProvider.updateItem(currentItem); (line 110) updates the Result column in the Excel file with the information from the currentItem.
  • server.setCurrentItemResultToOK(currentItem.getResult()); (line 113) sets the current item result to OK. This accepts a string parameter where you can add a description of the processing result.
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
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 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 used to define the final actions of the workflow and is associated with the End action. By default, the template calls server.info (line 121) to send a log message of type info to indicate the robotic process has finished.

116
117
118
119
120
121
122
	/**
	 * Last action of the robot.
	 */
	public void end() {

		server.info("End process");
	}

cleanUp() method

The cleanUp() method is invoked by the platform when all actions in the workflow have finished their execution, or if the process ended with errors or failed. The purpose of this method is to leave the resource in the same state it was before the robotic process started its execution. This is where you can add tasks to clean up the resource, like closing an application or logging out.

In this template, you will close the Excel file. Since the Excel file is used as the data provider, you can use the closeDataProvider() method to close the file.

133
134
135
136
137
138
139
140
141
142
private void closeDataProvider() throws IOException {
	
	if (dataProvider != null)  {
		
		server.info("Closing Data Provider...");
		
		dataProvider.close();
		dataProvider = null;
	}
}

When the cleanUp() method is implemented, it's possible that the process finished with an exception. To account for this, this template uses the cleanUp() method to first verify that the file was created correctly, and if it was, send the Excel file to the console.

156
157
158
159
160
161
162
163
164
165
@Override
public String[] cleanUp() throws Exception {
	
	closeDataProvider();
	if (new File(excelFile).exists()) {
		return new String[] { excelFile };
	}

	return IRobot.super.cleanUp();
}

This version of the Appian RPA documentation was written for Appian 21.4, and does not represent the interfaces or functionality of other Appian versions.
Open in Github Built: Fri, Dec 03, 2021 (02:38:50 PM)

On This Page

FEEDBACK