The Data Provider module allows your robotic task to access data from Excel or ZIP files. This module also allows your robotic task to create and write to Excel files.

To use the Data Provider module, you must first include the module dependency in the pom.xml.

Using the Data Provider module

The Data Provider module allows your robotic task to access data from two different sources: Excel and ZIP files. Once the robotic task has access to either of these sources, each Excel row or compressed file can be used as an item in your workflow. An item represents an element to be treated by a robotic task.

Your robotic task also has the ability to create Excel files and add data to them.

Adding data to Excel

The IExcel interface allows your robotic task to handle Excel files.

The IExcel interface uses Apache POI. In Apache POI, the interface Workbook is the highest-level representation of an Excel workbook. The two following classes implement the Workbook interface in Apache POI:

• HSSFWorkbook (HSSF): Implements Excel file handling using 2007 and earlier Excel file formatting, such as \*.xls and \*.doc.
• XSSFWorkbook (XSSF): Implements Excel file handling in OOXML file format, such as \*.xlsx and \*.docx.

Because IExcel uses Apache POI, it has direct access to work with its classes and simplifies its implementation.

Use the following code snippet to obtain an instance of IExcel:

1
IExcel excel = IExcel.getExcelInstance(this);

Where this refers to the class that implements the IRobot interface.

Accessing existing Excel files

To initialize the instance with an existing Excel file, use the init(String url) method, where url is the path to the file you want to open.

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

Creating Excel files

To create a new Excel file, use the create(String url, EExcelType type) method.

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

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

• url: The path where the Excel file will be created.
• type: The type of Excel file format. This parameter accepts one of the values of the enum type EExcelType, which are:
  • XLS for files \*.xls.
  • XLSX for files \*.xlsx.

The following example shows how to create a new Excel file and initialize the instance of IExcel.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
// Get the robotic task's 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 tasks with Excel here
} catch(Exception e) {
    // Manage exceptions here
}

Adding a sheet to a workbook

To add a sheet to a workbook, use the createSheet(String sheetName) method, where sheetName is the specified name of the new sheet.

The method returns true if the sheet was successfully created. It's recommended to check the returned value before entering data into the sheet.

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

The createSheet(String sheetName, int index) method allows you to create a new sheet in a specific position by specifying the index.

1
boolean createSheet(String sheetName, int index)

In Apache POI, the Sheet interface represents a sheet in an Excel workbook. It provides a wide range of methods that allow you to create rows, delete rows, get a specific row, or set measurements, dimensions, and margins. For more information on the Apache POI Sheet interface, please see the Apache POI documentation.

Adding a header to a sheet

Before entering data, it's recommended to first create a row for the header where you can define the content of each column. To create a new row, you must create an object Row.

The Apache POI interface Row represents an Excel row. Row inherits from Iterable<Cell>, which allows you to iterate on the row's cells. To create a header row, use the following code snippet:

1
2
// Create a header row in a specified sheet
Row row = excel.getWorkbook().getSheet("Sheet 1").createRow(0);

To enter values in a new row, create a cell for each value using the createCell (int column, int type) method of the class Row. This method returns the object Cell.

The createCell (int column, int type) method receives the following parameters:

• int column: Indicates which column to add the cell to (zero-based).
• int type: Indicates the type of content in the cell. The types available are:
  • CELL_TYPE_NUMERIC
  • CELL_TYPE_STRING
  • CELL_TYPE_FORMULA
  • CELL_TYPE_BLANK
  • CELL_TYPE_BOOLEAN
  • CELL_TYPE_ERROR

To add values to your cell, use the setCellValue method. For example, in this header, there are four cells with different values: 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");

Another option is to create each cell independently and set its content type last. For example:

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

Formatting data in a sheet

To apply a style to a row, use the class XSSFCellStyle or HSSFCellStyle, depending on which Excel file format you've implemented. Both classes implement the interface CellStyle.

For example, to create and apply a style to a header row using XSSFCellStyle:

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);

The last line of this example sets the sheet's default column width. Another option is to set each cell width based on its content using the following code snippet:

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

Apache POI recommends setting width at the end after all data has been entered to avoid slowing down the task.

Entering data in a sheet

After creating and formatting the header, the next step would be to write the data processed by your robotic task to the rows in the sheet. To add data that is stored in an array to your sheet, you will follow the same pattern used to create a row for the header.

Before writing the data, first retrieve the data and assign each value an index. For example:

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];

Once you have the data, create a new row in your workbook using createRow. Use itemIndex to refer to the current item. For an array of data, you should start from 0. Since the first row of a sheet is typically the header row, add + 1 to create the row after the header row.

1
2
// Create a row in the only available sheet
Row row = excel.getSheet().createRow(currentItem + 1);

To enter values in a new row, first create a cell for each value using the createCell (int column, int type) method of the class Row. Then add values to your cell using the setCellValue method.

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);

To insert all of the data stored in the array, use a for loop. To increase the value of itemIndex each time the code in the loop is executed, add ++.

1
2
3
for (int itemIndex = 0 ; itemIndex < books.length ; itemIndex++) {
  /* Code */
} 

Closing the workbook

When you've finished entering data and your robotic task ends, all resources used by IExcel should be closed and released using the close method. If the file has already been closed, this method will be ignored. This method should be called in the cleanUp method.

If your robotic task is compatible with initializing IExcel with a try-with-resources, then you don't need to use the close method because the JVM will call it for you.

Obtaining cells and rows

IExcel allows you to obtain cells and rows from a cell reference. This reference is the class CellReference in Apache POI.

Cells

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

• Cell getCell(CellReference reference): Get this cell.
• Cell getCellWithCreation(CellReference reference): Get this cell. If the cell doesn't exist, create it.

To initialize CellReference, use either of the two methods and include the row and column in which the cell is located. For example, the following code snippet obtains the cell from row 2 and column 3 and will create the cell if it does not exist.

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

Alternatively, you can obtain the reference from the cell representation in Excel. For example, using the format "B3" instead of (2,3).

If you already have a cell and want to obtain its CellReference, use the following code snippet:

1
CellReference cReference = new CellReference(cell);

To indicate the sheet where the cells are located, use the following code snippet:

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

To set the value of a cell, use the setCellValueByName method. For example, the code snippet below sets the value "This is the cell B3" to the cell B3:

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

To obtain a cell value, use the cell's location in Excel or through a CellReference. For example:

1
excel.getCellValueByName("B3");

Rows

To obtain a row, you can reference one of its cells using the following code snippet:

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

To create a row in the event that it does not exist, use the following code snippet:

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

To return a list of cells in a row, use the getEntireRow method. This method returns a list of cells (List<Cell>) from a row according to the row number or directly from an object Row:

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

For example:

1
excel.setCellValueByRef(new CellReference("B3"), "This is the cell B3");

For more information about this class, see Apache POI's CellReference documentation.

Inserting a link in a cell

To insert a link into a cell, use the setCellLink method.

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

Inserting images

To insert images into a cell, use the insertImage method. You can also specify an image's height and width, or its vertical and horizontal alignment.

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

Obtaining a list with all the cells of a row

To obtain the cells of a row, use the getEntireRow method. This method 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

IExcel provides access to FormulaEvaluator through the getFormulaEvaluator() method.

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, obtain an instance of FormulaEvaluator:

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

To learn more about FormulaEvaluator, see the Apache POI documentation on FormulaEvaluator.

Accessing data from Excel using the ExcelDataProvider

The IJidokaExcelDataProvider interface allows you to use an Excel file as a data source for your items. This interface contains methods that allow you to access items and obtain information about them.

To get an instance of the object, use the following code snippet:

1
IJidokaDataProvider.getInstance(this, Provider.EXCEL)

Where this refers to the class that implements the IRobot interface.

Once you have an instance of IJidokaExcelDataProvider, you must initialize the instance. If you do not initialize the instance, exceptions may occur.

To initialize the instance, call the method:

1
init (String url, String sheetName, Integer firstRow, IRowMapper\<IExcel, T\>mapper)

This method accepts the following parameters:

• url: Path to the Excel file.
• sheetName: Excel file sheet name.
• firstRow: Indicates the last row used as the header in Excel. For example, if you are using row 1 to display header content, enter the number 1 so the robotic task knows to start after the first row.
• mapper: An instance of IRowMapper.

Once the instance has been initialized, you can use any of the following methods provided by the IJidokaExcelDataProvider interface:

Transforming Excel rows to Java objects

The IRowMapper interface determines how to transform an Excel row (retrieved as an input) into a Java object that represents the information in that row. This interface provides methods to update a specific row and to indicate the last Excel row to be read. To use these methods, you must create a class that implements the IRowMapper interface.

The methods to implement the IRowMapper interface are:

The IRowMapper interface uses two generic method declarations:

• The object that operates directly with Excel data. For ExcelDataProvider, the object D is always IExcel.
• The object built from each Excel row (Instance T). This object must be created by the developer.

To learn more about this interface, open the Appian RPA Console and click Help > Javadoc in the left menu.

Accessing data from ZIP files

The IJidokaZipDataProvider interface allows you to use a ZIP file as a data source for your items. Using this data provider, you delegate the control of the items to Appian RPA.

You can select specific files from the ZIP file to process in the data provider. You can filter by one or more extensions. If no filter is specified, all files contained in the ZIP file will be processed.

To obtain an instance of IJidokaZipDataProvider, use the following code snippet:

1
IJidokaDataProvider.getInstance(this, Provider.ZIP)

Where this refers to the class that implements the interface IRobot.

To initialize the instance, call the method:

1
init(String url, String\... extensionsToExtract)

This method accepts the following parameters:

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

Once we have initialized the instance, you can use any of the following methods provided by the IJidokaZipDataProvider interface: