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:
And this is the Excel file that we will generate out of it:
You can download the Excel file here.
Robot script
Robot code explained
-
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
.
-
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 BodyCalling 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