How to Test Excel Data With Cypress

Note: This article was included in the 136th Coding Jag by LambdaTest and also in Issue #82 of Software Testing Notes.

Greetings to all Cypress enthusiasts!

A couple of days ago, I was approached by a member of our Cypress community on Discord with a question about how to validate data from an Excel file using Cypress. This prompted me to write this article.

Looking ahead, I’ll just note that this is a rather simple matter, and is mainly based on setting up the corresponding Node event inside the Cypress configuration file to convert the original Excel file to JSON format and further work with the resulting file. But first things first.

If you are not yet familiar with Cypress:

Cypress is an open-source JavaScript-based testing tool designed for modern web test automation. Cypress has emerged as a popular end-to-end testing tool for web applications due to a bunch of its powerful features, user-friendly interface, fast test execution time, easy installation and debugging, etc.

Cypress is a real game changer in e2e and component testing, and it grows at a rapid pace. And as you may have noted from my previous articles, I’m a big fan of that wonderful tool!

However, it is obvious that Cypress is not intended for testing Excel data, which in some test scenarios, can create some inconvenience if you need to validate data in Excel files.

Despite this, Cypress provides a way to do this, as I noted earlier, by converting the Excel file to Cypress-compatible JSON format.

This allows you to test your source data using the same test suite that you use to test your web applications.

Well, let’s imagine that in the course of executing some test scenario, we get a certain Excel file in the cypress/downloads directory of our project. In essence, the scenario can be anything, as well as the initial location of the initial file. And now, we need to test the data in this file.

For example, I have a companies.xlsx file in the downloads directory

with the data of some 10 non-existing companies:

First, let’s convert the original companies.xlsx file to JSON format. Actually, there are several suitable npm packages for this, the most popular of which today (judging by the number of weekly downloads) is SheetJS.

Let’s install it into the project by typing the following command in the terminal:

> npm i xlsx

Next, using the syntax of this package, let’s add a task event to the setupNodeEvents function in the cypress.config.ts file. This will allow us to move from the browser environment to the Node environment and execute some JavaScript functions in this environment.

For example, let’s call our function convertXlsxToJson, and with its help, we will convert the original Excel file into JSON format:

So, our convertXlsxToJson function takes as an argument the path to the original Excel file — filePath and uses XLSX.readFile() method to read the file, as a result of which we get a certain workbook object with a rather complex structure containing the data of the original Excel workbook. Let’s analyze it in more detail.

The main element of workbook object is Sheets object, whose properties are also objects containing the individual worksheets in the original Excel workbook, where the keys are the names of the worksheets and the values are the objects that represent each worksheet.

In turn, each worksheet object has its own set of properties, whose keys are references to cells in the worksheet (for example, A1, B2, etc.), and the values are objects containing information about the type of data in a particular cell, and also the value of the cell itself.

Also, workbook object contains SheetNames array with the names of the worksheets of the initial Excel book, while the order of the names in the array corresponds to the order of the worksheets in the workbook.

In addition, workbook object also includes a bunch of other properties that contain data about the original Excel workbook.

To retrieve the name of the first worksheet that includes the companies data table (obviously, it could have been any other worksheet), we access the value of the null element of SheetNames array.

Next, using the obtained sheet name as the key of the corresponding property on Sheets object, we get the worksheet object as the value of this property and put it in worksheet variable.

On the specified variable, we apply XLSX.utils.sheet_to_json() method to convert the worksheet data to JSON format. As a result, we have an array of objects jsonData, in which each object corresponds to a specific row of the initial Excel table.

The properties in each object include the column headings (keys) and the corresponding data in the cells (values). Ultimately, our convertXlsxToJson function returns jsonData array as the result of the task event.

In general, at this stage, the task can be considered complete, since the jsonData variable contains data in JSON format suitable for use in tests. However, let’s imagine that we want to write the received data as a companies.json file and place it, for example, in the cypress/fixtures directory.

To do this, we supplement our convertXlsxToJson function with the following lines of code:

Firstly, we define fileName variable, in which we place the name of the original Excel file (without extension), obtained based on the value of filePath variable using path.basename() method.

Using the specified file name, we set jsonFilePath variable to the path to the resulting JSON file in the fixtures directory.

Finally, we use writeFileSync() method from Node.js built-in fs module, which synchronously writes data to the resulting file as a JSON-formatted string with 2 spaces for each indentation level using JSON.stringify() method.

Thus, we set up our task event convertXlsxToJson in such a way that it accepts any Excel file located at the specified path, reads it and converts it to JSON format, and also writes the resulting JSON file to fixtures directory.

Now, to test the original data of ten companies, let’s create a spec file testExcel.cy.ts in the e2e directory. First, let’s add before hook to our test suite, in which we will call our task event, passing it the path to the initial Excel file as an argument:

After calling our task event, the resulting companies.json file will be written in the fixtures directory:

with the following structure:

After adding the tests, finally, our spec file will have the following content:

So first, we define two variables: xlsxPath — the path to the original Excel file and jsonName — the name of the final JSON file, obtained based on the value of the xlsxPath variable using the path.basename() and replace() methods.

Next, inside the beforeEach hook, we use the cy.fixture() command to load the contents of the resulting JSON file as a fixture, which we alias companiesData to access the specified content in each test.

As possible examples of tests, I have given three rather primitive demo tests. So, the first one checks if our test file contains data for 10 companies. The second verifies that each company’s data contains non-empty values ​​for the four keys — “Company Name”, “Product”, “City”, and “Email”.

And the last test checks if each company’s data contains a unique email address.

Let’s run our tests in the Cypress test runner with the standard command:

> npx cypress open

We click on the name of the spec file in the test runner window and voila — our tests were completed successfully in less than 0.1 seconds:

Final Thoughts

Although Cypress can be used to validate Excel data, this is obviously not the case for its core functionality. Cypress does not test Excel files “under the hood”.

Therefore, you should be aware of possible errors that may occur at the stage of converting an Excel file to JSON format, such as formatting errors, data loss, etc., which can subsequently lead to undesirable results.

Despite the relative rarity of the described case and the comparative simplicity of the approach to solving it, I really hope that this article will be useful for improving your testing skills with Cypress.

That’s about it. If you found this useful, share it with a friend or community. Maybe there’s someone who will benefit from it as well.

To continue your journey with me and get more information about testing with the awesome Cypress tool, you might be interested in subscribing to my blog “Testing with Cypress” and getting notified when there’s a new useful article.

The source code of all examples as well as the files presented in this article can be found in the repository of the blog on GitHub.

Thank you for your attention! Happy testing!

First published here