Writing Excel files

Get the code and run this example in your favorite editor on our Portal!

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": "Eliseo@gardner.biz", "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": "Jayne_Kuhic@sydney.com", "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:

Generated Excel file

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

  1. 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: Headers

    • ${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
Last edit: May 5, 2022