Writing Excel files tutorial

Tutorial showing how to create and write into Excel files with the RPA.Excel.Files library.

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.

Prerequisites

Set up your development environment.

Initialize the software robot directory

Using Robocode Lab

In the Robocode Lab welcome screen, click on "Create new activity", and select the directory on you system where you want to store the code.

Creating activity in Robocode Lab

Using Robocode CLI

Navigate to your projects directory in the terminal or the command prompt. Initialize the software robot directory:

robo init excel-file-writer

Navigate to the directory:

cd excel-file-writer

Robot task file

Paste the following Robot Framework code in the tasks/robot.robot file:

*** Settings ***
Documentation   An example robot that 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

Wrap the robot

robo wrap

Run the robot

Using Robocode Lab

If you are using Robocode Lab, you can run the robot by clicking on the >> button in the task bar: Running robot

Using Robocode CLI

Windows:

robo run entrypoint.cmd

macOS / Linux:

robo run entrypoint.sh

Robot code explained

*** Settings ***
Documentation   An example robot that 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

In this short tutorial, 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