Writing Excel files
This software robot shows an example of downloading JSON data from a remote API and saving the contents into an Excel file. We will be using a test API endpoint that returns a list of comments in JSON format, and our robot will save that data in an Excel file, with the appropriate headers.
Here's an example of the JSON response we get from the API:
[
{
"postId": 1,
"id": 1,
"name": "id labore ex et quam laborum",
"email": "[email protected]",
"body": "laudantium enim quasi est quidem magnam voluptate ipsam eos\ntempora quo necessitatibus\ndolor quam autem quasi\nreiciendis et nam sapiente accusantium"
},
{
"postId": 1,
"id": 2,
"name": "quo vero reiciendis velit similique earum",
"email": "[email protected]",
"body": "est natus enim nihil est dolore omnis voluptatem numquam\net omnis occaecati quod ullam at\nvoluptatem error expedita pariatur\nnihil sint nostrum voluptatem reiciendis et"
},
...
]
And this is the Excel file that we will generate out of it:
You can download the Excel file here.
Robot script
*** Settings ***
Documentation Downloads JSON data from a remote API and writes it
... into a local Excel file.
Library RPA.Excel.Files
Library RPA.HTTP
*** Tasks ***
Create an Excel file with data from a remote API
Create Workbook comments.xlsx
Set Worksheet Value 1 1 Post ID
Set Worksheet Value 1 2 ID
Set Worksheet Value 1 3 Name
Set Worksheet Value 1 4 Email address
Set Worksheet Value 1 5 Body
${response}= Http Get https://jsonplaceholder.typicode.com/comments
Append Rows To Worksheet ${response.json()}
Save Workbook
Robot code explained
*** Settings ***
Documentation Downloads JSON data from a remote API and writes it
... into a local Excel file.
Library RPA.Excel.Files
Library RPA.HTTP
-
In the
*** Settings ***
section, we add a description of our robot, and the libraries that we are going to use. We will need theRPA.HTTP
library to call the external API and theRPA.Excel.Files
library to create our Excel file and write data into it. These are part of the RPA Framework. -
Next, we create a
*** Tasks ***
section, where we add our only task for this robot, that we are callingCreate an Excel file with data from a remote API
.
*** Tasks ***
Create an Excel file with data from a remote API
Create Workbook comments.xlsx
Set Worksheet Value 1 1 Post ID
Set Worksheet Value 1 2 ID
Set Worksheet Value 1 3 Name
Set Worksheet Value 1 4 Email address
Set Worksheet Value 1 5 Body
${response}= Http Get https://jsonplaceholder.typicode.com/comments
Append Rows To Worksheet ${response.json()}
Save Workbook
-
Inside our task is where we add all the keywords that will make things happen! Let's go over them one by one:
Create Workbook comments.xlsx
: with this keyword we create a new Excel file in the folder where our robot is executed, and we decide to call itcomments.xlsx
- Using the
Set Worksheet Value
keyword, we can write into cells in our Excel file. We are passing three arguments to the keyword: the row, the column, and the value for the cell.
Set Worksheet Value 1 1 Post ID Set Worksheet Value 1 2 ID Set Worksheet Value 1 3 Name Set Worksheet Value 1 4 Email address Set Worksheet Value 1 5 Body
Calling the keyword multiple times incrementing the column value, we can create the headers of our table:
${response}= Http Get https://jsonplaceholder.typicode.com/comments
: Here we are calling theHttp Get
keyword from theRPA.HTTP
library, giving the URL of the API endpoint as an argument. The keyword returns a response object with the API contents that we assign to the${response}
variable.Append Rows To Worksheet ${response.json()}
using theAppend Rows To Worksheet
keyword we are populating the Excel file with the contents that we get from the API. We pass to the keyword the JSON contents of the response, that we get to by calling thejson()
method on it:${response.json()}
.Save Workbook
saves the changes we made to the Excel file.
Summary
You learned:
- how to use the
RPA.HTTP
library to call an API endpoint - how to get JSON data out of a response object
- how to create and save a new Excel file
- how to write into cells in an Excel file
- how to add multiple rows to an Excel file