How To Use Cypress To Read Excel Files

I don’t think there is an app in this world which doesn’t have some type of export of data. When the data is exported from the application it usually gets stored in Excel file. This quite often seem too cumbersome to deal with in test automation and QA engineers leave these types of tasks for manual testing. With Cypress this doesn’t have to be that difficult. Quite the opposite, Cypress has an option for doing this in an easy and straight forward way. Let’s find out how to use Cypress to read Excel file.

Cypress Setup

We can find node-xlsx library on npm which can be used for reading Excel files using Javascript. This library allows us to parse Excel file in xlsx and xls formats. We will use this library in combination with readFile built-in command in Cypress. We can use readFile for checking if the file exists before parsing the content of it. To install node-xlsx we need to run the following command:

npm install node-xlsx --save

First we will require xlsx and fs in cypress.config.js and then we will add a new node event in cypress.config.js like this:

This event will call the File System readFileSync function which will read the content of the file on specified path and then xlsx library will parse the content.

Adding Custom command

In order to use the event from the above section we need to create a custom command which will call the task from the cypress.config.js file. To create a custom command open commands.js file and add the following code:

Cypress.Commands.add('parseXlsx', (inputFile) => {
  cy.readFile(inputFile, 'binary', { timeout: 15000 });
  cy.task('parseXlsx', { filePath: inputFile })
});

In this command we will pass inputFile as an argument. The function will execute first readFile command which has timeout option set to 15 seconds. The readFile command has file existence assertion implemented by default. This means that it constantly checks if the file was downloaded until the file exists or timeout expires. Using this approach over some alternative setups is my preferred. After all, there are files which might take a longer time to download. Any try/catch block would throw exception right away, while this code waits for the download to finish. When the file exists, the task is executed and it executes the event parseXlsx created in the previous step.

Usage in a test

Now, since we have the custom command created we can start writing a test. We need to chain custom parseXlsx command of cy command and pass the file path. The Excel file content will be parsed as JSON data.

The data is represented by Arrays for each row. And the Excel file looks like this:

The test passes successfully. If you want to access a specific item in the row Array you would need to pass another index for the two dimensional Array. Something like jsonData[0].data[2][1] which would return QA Essentials. In addition to testing values in Excel files, you can use Cypress to read Excel file for Data Driven Testing as well. You can feed data in your tests from Excel files which helps with maintenance of the tests and avoids hard-coded data. And if your file names have timestamps in them, read my article about checking the files with dynamic names.