Takes excel format files as input and fetches the required data from the file.

Configuration

The ExcelReader microservice can be configured using the Custom Property Sheet (CPS).

Figure 1: Custom Property Sheet

Error Handling Configuration

The actions to be taken when a particular error occurs can be configured using this attribute.

Click the ellipsis button against this property to configure Error Handling properties for different types of Errors. By default, the actions Log to error logs, Stop service and Send to error port are enabled.

Icon

Refer to the Error Handling section in the Common Configurations page for detailed information.

Read File Details from Message Headers

This property is to read the file name and directory details from message headers.

Icon

Enabling this option hides the File Name and File Directory fields in the CPS.

Excel File Format

ExcelReader supports two formats of excel files as follows:

Excel Workbook: Excel Workbook supports .xls and .xlsx formats of excel files.
Excel Binary Workbook: Excel Binary Workbook supports binary (.xlsb) format of excel files.

File Name

Name of the excel file to be read. Suffix the extension of the file format such as .xlsx, .xls, and .xlsb with the name.

File Directory

Path of the excel file to be read.

Sheet Index

Sheet Index of the excel file to be read.

Icon

The value must be one less than the actual sheet index in the excel file.

Response Size

Input the number of records to be aggregated in the response.

Example

Icon

If the response size is set to "1", for 10 rows, it gives 10 outputs each having output data of the respective row.

Data Index

The row from which the actual data starts.

Icon

The value must be one less than the actual row number in the excel file.

Header Data Row

The row number where the Header data is present. This row will have the Column names.

Icon

The value must be one less than the actual row number in the excel file.

Functional Demonstration

Create a flow using ExcelReader connecting to a Feeder microservice and a Display microservice.

Figure 2: Sample Event Process

Scenario 1 - Disabling the “Read File Details from Message Headers” property

Configure the ExcelReader microservice as shown in Figure 1. Send input using the Feeder microservice and display the output using the Display microservice.

Sample Input

Figure 3: Sample Excel File contents

Sample file can be downloaded here:

Icon

Click this link Scenario1&2SampleFile.xls for the sample excel file used for Scenario 1 and Scenario 2.

Output

The data extracted from the excel file will be displayed in the Display window.

Figure 4: Output of the excel file displayed in the Display window

Scenario 2 - Enabling the “Read File Details from Message Headers” property

Configure the ExcelReader microservice as shown in the figure below. Select the input file and directory using the Feeder microservice as shown in the sample input below and display the output using the Display microservice.

Figure 5: Enabling “Read File Details from Message Headers” property in CPS

Sample Input

Use the same sample that is used in Scenario 1.

Feeder configuration

Go to the Message Details Configuration panel in the Feeder microservice and add the following values:

FileName: Name of the excel file along with the extension (.xlsx/.xls/.xlsb).
Directory: Path of the excel file.

Figure 6: Excel filename and directory path provided in the Message Details Configuration panel

Output

The data extracted from the excel file will be displayed in the Display window.

Figure 7: Output of the excel file displayed in the Display window

Scenario 3 - Reading Excel Binary Workbook

Configure the ExcelReader microservice as in the figure below. Select input from the Feeder microservice and display the output using the Display microservice.

Figure 8: Configuring Excel Binary Workbook in CPS

Sample Input

Figure 9: Sample Binary Excel file contents

Sample file can be downloaded here:

Icon

Click this link Scenario3SampleFile.xlsb for the sample excel file.

Output

The data extracted from the xlsb excel file will be displayed in the Display microservice.

Figure 10: Output of the xlsb excel file displayed in the Display window