The ExcelWriter microservice is used to write data into a new excel file and update the data in the same excel file.

Configuration and Testing

Component Configuration

The figure below illustrates the Component Properties Sheet (CPS) properties.


Figure 1: Component Configuration properties

Attributes

Pre Processing XSL Configuration

Pre Processing XSL configuration can be used to transform request message before processing it. Click the small button against the property to configure the properties.

Icon

Refer to the Pre/Post Processing XSL Configuration section under Common Configurations topic for details regarding XSL configuration. The properties comprising this XSL configuration are:

  • Provide XSL
  • XSLT Engine
  • Transformation source data
  • Set transformation result as

Post Processing XSL Configuration 

Post Processing XSL configuration can be used to transform the response message before sending it to the output port.

Icon

As mentioned in the above section, refer to the Pre/Post Processing XSL Configuration section under the Common Configurations topic for details regarding XSL configuration.

Process Message Based on Property 

The property helps components to skip certain messages from processing.

Icon

Refer the Process Message Based On a Property section under the Common Configurations topic.

Error handling configuration

The remedial 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 options Log to error logs, Stop service and Send to error port are enabled.

Icon

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

Sheet Name

The name of the excel sheet which needs to be created and into which the data needs to be updated or appended. The default name is Sheet1.

Field Delimiter

Field Delimiter to separate the fields in a record. The tab can be given as \t. The default value is , (comma)

Record Delimiter

The delimiter used to separate the Records. The default value is \n.

Output Mode

  • Append: The data will be updated in the existing excel file.
  • New file for each message: A new excel file will be created for each input message.

Excel File Path

Path of the excel file to be updated including the file name. It is enabled when Output Mode is selected as "Append".

Icon
  • The microservice runs on the peer server and therefore the file paths and directories mentioned in the CPS should be valid on the machine where the peer server is running.
  • If the microservice fails over to another peer server, ensure that the machine on which the secondary peer server is running carries the same path.

Example

Icon

E:\outputExcel\sample1.xlsx

Output Excel File Directory

Provide the path where the excel file has to be created. It is enabled when Output Mode is selected as "New file for each message".

Example

Icon

E:\outputExcel

Output Excel File Name

Provide the name of the new excel file along with the extension name. It is enabled when Output Mode is selected as "New file for each message".

Example

Icon

sample1.xlsx

Append TimeStamp

Appends timestamp to the filename. It is enabled when Output Mode is selected as "New file for each message".

Example

Icon

sample1_20200902151815

Threadpool Configuration

This property is used when there is a need to process messages in parallel within the component, still maintaining the sequence from the external perspective. 

Icon
  • Limit the usage of the Threadpool Configuration property only in circumstances as mentioned above.
  • If sequential processing is not required, please use sessions on the input port.

Click the Threadpool Configuration ellipsis button to configure the Threadpool Configuration properties.


Figure 2: Threadpool Configuration

Enable Thread Pool

Enable this option to configure the properties that appear as below.

Pool Size

The number of requests to be processed in parallel within the component. Default value is '1'.

Batch Eviction Interval (in ms)

Time in milliseconds after which the threads are evicted in case of inactivity. New threads are created in place of evicted threads when new requests are received. Default value is '1000'.

Functional Demonstration

Configure ExcelWriter to take an input message based on the configured field, record delimiters and update or create an excel file based on the selected output mode.

Scenario 1

Configure ExcelReader with the "New file for each message" Output Mode following Figure1 to add data to the sample1.xlsx file.


Figure 3: Scenario1 Sample Flow for adding a message in a new Excel sheet and file

Sample Input

Use the following message in the Feeder:

Input text

Icon

01-03-2020,4,GET /accounts,v3.1,OIDC,Y,6,1,0,Tae
01-04-2020,4,GET /accounts,v3.1,OIDC,N,6,1,0,Ta


Figure 4: Input text for Scenario1 in the Feeder window

Output


Figure 5: Confirmation message of file creation with the data input


Figure 6: Data inserted in Sheet1 in the Sample1 excel file

Scenario 2

Configure ExcelReader in the same manner as in Scenario1 but selectng the Output Mode as "Append" to append data to the sample1.xlsx fle.. 


Figure 7: Scenario2 Sample Flow for appending/updating message in the existng Excel file


Figure 8: CPS edited wth Append output mode and excel file path

Sample Input

Use the following message in the Feeder:

Input text

Icon

01-05-2020,4,POST /accounts,v3.1,OIDC,Y,5,1,0,Te
01-06-2020,4,GET /accounts,v3.1,OIDC,Y,8,1,0,Td


Figure 9: Input text for Scenario2 in the Feeder window

Output


Figure 10: Confirmation message of file updation in the Display window


Figure 11: Data appended to Sheet1 in the Sample1 excel file

Adaptavist ThemeBuilder EngineAtlassian Confluence