[SAP PROCESS AUTOMATION] – WORKING WITH EXCEL SDK

Hi guys, in this article I want to share one scenario in SAP Process Automation about working with Excel SDK. In this we will learn

How to capture an application with different screens
How to declare elements of a captured application
How to build an automation using a captured application and export data into excel template with Excel SDK

PREREQUISITES

SAP BTP trial account
Excel template
Desktop agent 2.0.31
SAP Intelligent RPA extension for Chrome

SETUP ENVIRONMENT

CREATE TRIAL ACCOUNT SAP BTP AND SUBSCRIPTION SAP PROCESS AUTOMATION

CREATE EXCEL TEMPLATE

DOWNLOAD AND INSTALL DESKTOP AGENT 2.0.31

NOTE

When install desktop agent, intaller require choose extension for SAP Inntelligent RPA. We have to choose version 3.0

REQUIREMENT

We will UI5 HTML at Orders List

When we select one order, we will go to order details and our mission is get some data on this we page fill into excel template. In last, we will save excel with data of Order in it.

To do this we will some task

Create application in SAP Process Automation and capture data of web page
Create elements of Webpage and extract them into variable
Using Excel SDK to fill data in variable above into cells of excel template.

HOW TO ?

CREATE NEW BUSINESS PROCESS

CREATE APPLICATION

We have to open web page of Order Detail in another window browser – Not open in new tab. After create application, one window will open as below.

Capture and Go To Application

CAPTURE DATA AND DECLARE ELEMENT IN APPLICATION

Remove criteria MAINFRAME and add URL criteria as below

Select HTML tag of Order Number. Remove Text criteria and add ID criteria

Do the same task for Customer Name, Order Amount, ShippingAddress, Shipping Street, Shipping Zip Code/City, Shipping Region, Shipping Country

Done, we will have element as below

Next, we will declare element for Line items. Because of this is a table with tag emelement

<TR> : Table Row
<TH>: Table column with format header
<TD>: Table column with format data

Decalre element for TH with name Table Header and criteria is Collection

Declare element for TR with name Table Row and criteria is collection

Declare element for TD with name Table Data and criteria is collection

Add Table Row as criteria of Table Data. When select Table Data, click three dot beside Table Row and select Add Criteria

IMPORT EXCEL TEMPLATE

CREATE AUTOMATION AND USING EXCEL SDK TO EXTRACT DATA FROM SCREEN APPLICATION INTO EXCEL TEMPLTE

Create Automation

Add Screen into automation

Get element in screen. Select screen and choose Define Screen Activitives

Get Element Order Number, Customer Name, Order Amount

Set Output for each element.

Do the same task with shipping

Add Open Excel Instance

Add Open Workbook

Set file path excel template in project

Set value for Order Number Cell

Set value for Customer Name Cell (E6)

Set value cell for Amount (E7)

Set value cell for Shipping (D12 – D16)

Next, we will set value for line items. Because of this is collection fo we will have two loop for each. One for Row and another one for Columns.

Select Screen and search for each. Drag Foreach into Table data of screen which declared above

In this step, one dialog will appear, we have to select one element which declared above (Table Row or Table Data). Because of this is array 2 dimensions so we will have 2 foreach. To do this, we will select table data

Every Row, we will loop every Column and get data in this by variable currentMember of step 19 and output with variable tableData. We will use this output to set value into Excel cell.

Next step, we will use condition to set value into excel

Set condition expression for every case base on index of step 19

0 : Product Name
1 : Unit Price
2 : Quantity
3 : Total

Base on template excel, we see that data for line items begin at row 23. So every index of Row (Step 18) we will plus for number 23.

Set value for Price (Column C in excel template)

Set value for quantity (Column D in excel template)

Set value for Total (Column E in excel template)

Add Save As Workbook

TEST AUTOMATION

SUMMARY

In this article I shared step by step How to working with Excel SDK in SAP Process Automation to extract data from web page into Excel with Excel template. Next article, I will share How to do this automation by BOT by using SAP Conversational AI. Thanks for your reading and any advise, kindly leave your comment on this.

Thanks.

Josheph.