Testing Excel File Data with Cypress

Cypress community on Discord, a question was briefly touched on how to check data from a file excel using Cypress. This prompted me to make some useful notes that became the basis for this article.

Looking ahead, I’ll just note that this is a rather simple matter, and basically comes down to setting up the appropriate node events inside the Cypress config file to convert the original excel file to format JSON and further work with the resulting file. But first things first.

If you are not yet familiar with Cypressthen this tool can be briefly described as follows:

Cypress is a JavaScript-based end-to-end testing tool designed for modern web test automation. Cypress has become a popular end-to-end web application testing tool due to its relatively powerful features, user-friendly interface, and fast test execution.

However, it is clear that Cypress is not designed for data testing. excelwhich in some test scenarios can create some inconvenience if you need to check data in files excel. Regardless, Cypress provides a way to do this, as I noted earlier, by converting excel file to format JSON, compatible with Cypress. 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 some companies.xlsx file in directory cypress/downloads our project. In essence, the script can be anything, as well as the initial location of the source file.

Let’s also assume that we are faced with the task of testing the data in this file, which contains information about the following ten companies:

First, let’s transform the original companies.xlsx file to format JSON. Actually, there are several suitable npm packages, 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, given the syntax of this package, add task event to block setupNodeEvents V cypress.config.ts file. This will allow us to move from the browser environment to the Node environment and execute some given JavaScript function in this environment. For example, let’s call our function convertXlsxToJsonwith its help we will convert the original Excel file into JSON format:

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

The key element of the object workbook is an object SheetsAn whose properties are also objects containing the individual worksheets in the source workbook excel, 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 cell references in the worksheet (for example A1, B2 etc.), and the values ​​are objects that contain information about the type of data in a particular cell, as well as the value of the cell itself. Also object workbook contains an array SheetNames with the worksheet names of the book, with the order of the names in the array corresponding to the order of the worksheets in the book. In addition, the object workbook includes a number of other properties containing data about the source book excel.

To extract the name of the first worksheet that includes the company data table (obviously, it could have been any other worksheet), we refer to the value of the zero element of the array SheetNames. Next, using the resulting sheet name as the key of the corresponding property of the object Sheetswe get the worksheet object as the value of this property and put it into a variable worksheet.

We apply the method to the specified variable XLSX.utils.sheet_to_json() to convert worksheet data to format JSON. As a result, we have an array of objects jsonData, where each object corresponds to a specific row in the source table. The properties in each object include the column headings (keys) and the corresponding data in the cells (values). Finally, our function convertXlsxToJson returns an array jsonData as a result of execution task events.

In general, at this stage, the task can be considered completed, since the variable jsonData contains data in the format JSONsuitable for use in tests. However, let’s imagine that we want to write the received data in the form companies.json file and place it, for example, in the directory cypress/fixtures (obviously we can choose any other file location). To do this, we add our function convertXlsxToJson with the following lines of code:

So, first we define a variable fileNamein which we place the name of the original Excel file (without extension), obtained based on the value of the variable filePath using the method path.basename(). Using the specified file name, set in the variable jsonFilePath path to the result JSON file in directory fixtures. And finally we use the method writeFileSync() from built-in fs a Node.js module that synchronously writes data to the resulting file as a JSON-formatted string with two spaces for each indentation level using the method JSON.stringify().

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

Now, in order to test the initial data of ten companies, we will create spec-file testExcel.cy.ts V e2e directories. First, add to the block describe hook beforein which we will call our task event, passing it as an argument the path to the source excel file:

After the call taskevents to directory fixtures the resulting file will be written companies.json:

having the following structure:

After adding tests, our spec-file will finally look like this:

So, first we define two variables: xlsxPath – path to source excel file and jsonName – the name of the final JSON file, obtained based on the value of the variable xlsxPath using methods path.basename() And replace(). Coming inside beforeEach hook we use the command cy.fixture() to download the contents of the final JSON a file in the form of a fixture, to which we assign an 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 test checks if our test file contains data 10 companies. The second allows you to make sure that each company’s data contains non-empty values ​​for 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 using 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:

Instead of a conclusion

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 during the conversion stage. excel file in JSON format, such as formatting errors, data loss, etc., which may 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.

For more information about testing with Cypressyou can subscribe to my blog “Testing with Cypress” (in English) on Medium.com to be notified when new articles are posted.

The source code, as well as the files presented in this article, you can find in the corresponding repositories my blog on GitHub.

Thank you for your attention and happy testing!