To be fair, I’ve never been a fan of using Excel files in Automation. I think it is a little bit complicated to handle, compared with other sources where you can store or retrieve data from and it is also heavier.
But it can also be useful, especially when you find a package that helps you to handle it.
Let’s talk about exceljs.
A few weeks back, during some leisurely API testing to explore routing and mocking functionalities, I found myself grappling with the task of handling and storing the collected data. Initially, I resorted to printing data in the VS Code Console, but a growing need to manipulate and save it prompted me to seek out a more efficient solution.
So I found this package and decided to give it a try.
It might have been the first time (as even if I never liked using Excel, I had done it before), that it was so easy to create a new spreadsheet, with headers, map the data and save it.
Should we do a quick walk-through ?
As a frequent user of Playwright for data extraction from APIs, I’ll guide you through incorporating exceljs into your automation workflow.
Installation
As with any package, open your terminal and run the following command:
npm install exceljs
Assuming you already have Playwright installed (if not, check out my previous Medium articles for a handy guide), we’re all set to embark on a journey of seamless data handling.
Done, installed, we can start working on it!
Getting Started with exceljs in Your Playwright Project
Assuming your Playwright project is already up and running, let’s dive into the practical implementation. In this example, I’ll guide you through using JavaScript to seamlessly integrate exceljs into your testing arsenal.
Let’s create our test class (.spec.js) to import the packages.
import { test, expect } from '@playwright/test';
import ExcelJS from 'exceljs';
Perfect, now we can create a test:
test('Get Cat Breeds', async ({request}) =>{});
As you can see, we are using the built-in request fixture.
We can also setup a project within our .config file to specify the baseURL, like this:
projects: [
{
name: 'api',
use: {
baseURL: 'https://catfact.ninja,
}
}]
With this configuration, all requests we send are going to this API endpoint.
Exploring the API
Before we dive headfirst into building requests, let’s gather the essential information by exploring the API. This step is crucial for understanding the data structure and laying the foundation for our subsequent actions.
Begin by visiting the API site. Once there, locate the “Breeds” section. You’ll find a button labeled “Try it out.” Click on it to unlock the gateway to data exploration. You should now be able to set the “limit”.
Ooops! I forgot, before trying it, open DevTools and navigate to the “Network” tab.
Cool, we sent the request. Now if you explore the “Network”, you will see the different events happening behind the scenes. The one that we are interested in in this case, is the first one:
/breeds?limit=5
Clicking on this event opens up a window providing detailed insights into the request, and this is where we’ll extract valuable clues to construct our own.
Analysing the Request Event
Within this event tab, take note of the following details:
- Method: It’s a GET request, indicating the type of interaction.
- URL: This is the endpoint we’ll be targeting in our Playwright script.
Exploring the Payload Tab
Now, let’s navigate to the “payload” tab. This section exposes the data being sent to the API endpoint. In our case, with a simple API that only requires one parameter, the payload might seem minimal. However, in more complex scenarios, you’ll encounter JSON payloads, forms, queries, and other essential elements.
Cool! We are now ready to build the request.
let response = await request.get('/breeds', {
params: {
'limit': '5'
}
});
Two things to notice here:
- The test will automatically pick the baseURL we have configured before, and we only need to append the route.
- This API call request receives a parameter to limit the amount of records you would like to retrieve.
Once we have the response, as it is a Promise, we need to use await (to resolve it), then we are going to parse it, and print it in the console, let’s see how it looks like:
Great! We are already retrieving the data, now we need to work on it!
As you can see, it returned 5 results, set by the “limit”.
Storing API Response Data
What are we doing next ?
Ok, we need to save our formatted data in a new Excel file.
Before starting with it, I am going to store the response data (with the cat breeds) in a new variable, so it is easier to handle.
let cats = await parsedResponse.data;
By “.data”, I mean this part of the response:
Crafting the Excel File
Alright, let’s move forward. In order to start , we need to have a new Workbook and a Worksheet:
const workbook = new ExcelJS.Workbook();
const worksheet = workbook.addWorksheet('Cats');
Then we are going to set our “Headers”, basically, the “Title” for each column of our Worksheet.
In this case, I am going to use the most important information in the response for me, which is: Breed, Country and Origin.
const desiredHeaders = ['breed', 'country', 'origin'];
Now we need to get those headers from the “data” we saved before:
const headers = Object.keys(cats[0]);
Oh wait.. what is that “Object.keys()”?
This method is used to extract the “keys” of an object and return them as an Array. So in our case, the “keys” are: breed, country, origin, coat and pattern.
{breed: 'Abyssinian', country: 'Ethiopia', origin: 'Natural/Standard', coat: 'Short', pattern: 'Ticked'}
Perfect, but… we do not need all of them, cause we are only saving the first three, right ?
Exactly, so I am going to quickly filter them and add a row in our sheet.
const filteredKeys = headers.filter(key => desiredHeaders.includes(key));
worksheet.addRow(filteredKeys);
What I am doing there is telling the program to give me “keys” that match with the name of the headers I declared before.
And finally, we need to loop through our data, start saving each value under the corresponding header and create the file!
cats.forEach(row => {
const values = filteredKeys.map(header => row[header]);
worksheet.addRow(values);
});
await workbook.xlsx.writeFile('catsexcel.xlsx');
})
We are all set! Let’s kick off the test and see it in action!
As you can see, once the test is run, the file gets generated!
This is how it looks like, check the name, headers and data:
And this is it!
This may be very helpful if you need to store large sets of data or do some comparisons too!
Full Test File
import { test, expect } from '@playwright/test';
import ExcelJS from 'exceljs';
test('Cats', async ({request}) =>{
let response = await request.get('/breeds', {
params: {
'limit': '5'
}
});
let parsedResponse = await JSON.parse(await response.text());
console.log(parsedResponse);
let cats = await parsedResponse.data;
const workbook = new ExcelJS.Workbook();
const worksheet = workbook.addWorksheet('Cats');
const desiredHeaders = ['breed', 'country', 'origin'];
const headers = Object.keys(cats[0]);
const filteredKeys = headers.filter(key => desiredHeaders.includes(key));
worksheet.addRow(filteredKeys);
cats.forEach(row => {
const values = filteredKeys.map(header => row[header]);
worksheet.addRow(values);
});
await workbook.xlsx.writeFile('catsexcel.xlsx');
})
That's it for this guide. Thank you for reading!
