Excel
Click to watch a video explaining how to read data from Excel documents.
As of Kapow 9.2, robots have a whole new way of handling Excel documents. Instead of being converted into html-pages, Excel is now shown as a spreadsheet directly in the Page View in Design Studio. In this video, I will give an overview of the features of the Excel View and take you through the process of building a robot which extracts from an Excel document. Feel free to follow along on your own machine.
An example of a robot which loads an Excel document can be found in the examples folder of the default project, and has the name excel.robot.
Go ahead and open the robot. Excel documents can be loaded in the same way as regular pages, either from a URL or from a file on your machine or server. In this particular robot, it is loaded by clicking a link, which is performed by the step named Click PersonData.xlsx.
Click the Loop Rows step to view the document. The spreadsheet now appears in the page view, with rows and columns named and arranged like you are used to it from Excel. This particular document contains tables with person data.
There are two different sheets with 100 entries in each. It is possible to switch between the two sheets in the lower left corner of the Page View. It is also possible to see the document information by clicking the leftmost tab. In this video however, we will focus on Sheet1.
From the drop down menu in the lower right corner of the page view it is possible to look at the unformatted values of the document or the raw formulas. Changing this view will also affect the extracted values when inserting a new step, so I am going to change it back to showing formatted values.
A cell is selected by clicking on it and it is even possible to select a range of cells by clicking and dragging, or clicking on a row or column name to select the entire row or column. The upper left corner selects the entire spreadsheet.
I will now delete the loop step and all the extract steps from the robot and demonstrate how easy it is to extract from an Excel document. I drag to select the steps, then hit the delete button on my keyboard.
To the new Spreadsheet View belongs a whole family of new step actions, which enable you to loop, extract, and test cells. Just like in the browser, view these functions are available from the right click menu as I will show you in a moment.
First, we want to insert a loop step which loops through all the rows of the table. First, I click the upper left corner of the Spreadsheet View to select the entire spreadsheet. Then, I right click inside the selected area - meaning anywhere inside the Spreadsheet View- and select Loop>>Loop Table Row>>Exclude First Row. I am excluding the first row since we are not interested in the header values.
The Loop in Excel step now sets the first row to loop over as the Named Range. Clicking the arrows on the loop step will show how the other rows are selected. It is now possible to extract from the Named Range and, because of the loop, corresponding values will be extracted from all the other rows.
To extract first the ID, I right click the ID value and select . I click OK in the wizard that appears, which is already properly configured.
Likewise, I can now extract the first name as a text into the name variable, the age as a number into the age variable and the gender as a Boolean into the isMale variable. The gender value is either true for male or false for female.
To show that the entire table can now be extracted, I switch to debug mode by clicking the icon in the upper left corner and clicking run in the toolbar. All 100 values from the Excel document now appear in the list of results.