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

1. In the *** Settings *** section, we add a description of our robot, and the libraries that we are going to use. We will need the RPA.HTTP library to call the external API and the RPA.Excel.Files library to create our Excel file and write data into it. These are part of the RPA Framework.

2. Next, we create a *** Tasks *** section, where we add our only task for this robot, that we are calling Create 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

3. 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 it comments.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 the Http Get keyword from the RPA.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 the Append 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 the json() 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