Testing Excel Data with Cypress
Tags: cypress, testing, javascript, test
Published: 2023-04-21 03:02:20

> _**Note:** this article was included in the [136th Coding Jag](https://www.lambdatest.com/newsletter/editions/issue136) by LambdaTest and also in [Issue #82](https://softwaretestingnotes.substack.com/p/issue-82-software-testing-notes) of Software Testing Notes._


_Greetings to all Cypress enthusiasts!_

A couple of days ago I was approached by a member of our **[Cypress-community](https://discord.gg/cypress)** 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](https://www.cypress.io/)**:

> _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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/f617nnqs8iubcf140ko4.png)

with the data of some _10_ non-existing companies:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ytgeb57xkbssdohjmg8n.png)

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](https://github.com/SheetJS/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_ function 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:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/byvfyh8khkra95s65fyj.png)

So, our `convertXlsxToJson` function takes as an argument the _path_ to the initial _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 completed, 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:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ps9jl45oehhpidhozkjn.png)

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 _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 _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:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xx0v5a37fql9a522osam.png)

After calling our `task` event, the resulting _companies.json_ file will be written in the _fixtures_ directory:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8logrk3oo0b8e92z5w1r.png)

with the following structure:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fh94lnqq2yj33v9ipvxh.png)

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/t1vw6bgicq2vdxdagz82.png)

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 completed successfully in less than _0.1_ seconds:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/slvmp0bwyk8p08cxioz2.gif)

## 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](https://www.cypress.io/)** tool, you might be interested in subscribing to my blog **[“Testing with Cypress”](https://medium.com/testing-with-cypress)** and get 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](https://github.com/Sanzhanov/Blog-Testing-with-Cypress) of the blog on GitHub.

_Thank you for your attention! Happy testing!_