Note: This article was included in the by LambdaTest and also in of Software Testing Notes. 136th Coding Jag Issue #82 Greetings to all Cypress enthusiasts! A couple of days ago, I was approached by a member of our on Discord with a question about how to validate data from an file using Cypress. This prompted me to write this article. Cypress community Excel Looking ahead, I’ll just note that this is a rather simple matter, and is mainly based on setting up the corresponding event inside the Cypress configuration file to convert the original file to format and further work with the resulting file. But first things first. Node Excel JSON 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 data, which in some test scenarios, can create some inconvenience if you need to validate data in files. Excel Excel Despite this, Cypress provides a way to do this, as I noted earlier, by converting the file to Cypress-compatible format. Excel JSON 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 file in the 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. Excel cypress/downloads For example, I have a file in the directory companies.xlsx downloads with the data of some 10 non-existing companies: First, let’s convert the original file to format. Actually, there are several suitable for this, the most popular of which today (judging by the number of weekly downloads) is . companies.xlsx JSON npm packages 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 event to the function in the file. This will allow us to move from the browser environment to the environment and execute some functions in this environment. task setupNodeEvents cypress.config.ts Node JavaScript For example, let’s call our function , and with its help, we will convert the original file into format: convertXlsxToJson Excel JSON So, our function takes as an argument the to the original file — and uses method to read the file, as a result of which we get a certain object with a rather complex structure containing the data of the original workbook. Let’s analyze it in more detail. convertXlsxToJson path Excel filePath XLSX.readFile() workbook Excel The main element of object is object, whose properties are also objects containing the individual worksheets in the original workbook, where the are the names of the worksheets and the are the objects that represent each worksheet. workbook Sheets Excel keys values In turn, each worksheet object has its own set of properties, whose are references to cells in the worksheet (for example, , etc.), and the are objects containing information about the type of data in a particular cell, and also the value of the cell itself. keys A1, B2 values Also, object contains array with the of the worksheets of the initial book, while the order of the names in the array corresponds to the order of the worksheets in the workbook. workbook SheetNames names Excel In addition, object also includes a bunch of other properties that contain data about the original workbook. workbook Excel To retrieve the of the worksheet that includes the companies data table (obviously, it could have been any other worksheet), we access the value of the element of array. name first null SheetNames Next, using the obtained sheet name as the of the corresponding property on object, we get the worksheet object as the value of this property and put it in variable. key Sheets worksheet On the specified variable, we apply method to convert the worksheet data to format. As a result, we have an array of objects , in which each object corresponds to a specific row of the initial table. XLSX.utils.sheet_to_json() JSON jsonData Excel The properties in each object include the column headings and the corresponding data in the cells . Ultimately, our function returns array as the result of the event. (keys) (values) convertXlsxToJson jsonData task In general, at this stage, the task can be considered complete, since the variable contains data in format suitable for use in tests. However, let’s imagine that we want to write the received data as a file and place it, for example, in the directory. jsonData JSON companies.json cypress/fixtures To do this, we supplement our function with the following lines of code: convertXlsxToJson Firstly, we define variable, in which we place the name of the original file (without extension), obtained based on the value of variable using method. fileName Excel filePath path.basename() Using the specified file name, we set variable to the path to the resulting file in the directory. jsonFilePath JSON fixtures Finally, we use method from Node.js built-in module, which synchronously writes data to the resulting file as a -formatted string with 2 spaces for each indentation level using method. writeFileSync() fs JSON JSON.stringify() Thus, we set up our event in such a way that it accepts any file located at the specified , reads it and converts it to format, and also writes the resulting file to directory. task convertXlsxToJson Excel path JSON JSON fixtures Now, to test the original data of ten companies, let’s create a file in the directory. First, let’s add hook to our test suite, in which we will call our event, passing it the to the initial file as an argument: spec testExcel.cy.ts e2e before task path Excel After calling our event, the resulting file will be written in the directory: task companies.json fixtures with the following structure: After adding the tests, finally, our file will have the following content: spec So first, we define two variables: — the path to the original file and — the name of the final file, obtained based on the value of the variable using the and methods. xlsxPath Excel jsonName JSON xlsxPath path.basename() replace() Next, inside the hook, we use the command to load the contents of the resulting file as a , which we to access the specified content in each test. beforeEach cy.fixture() JSON fixture alias companiesData As possible examples of tests, I have given rather primitive demo tests. So, the one checks if our test file contains data for companies. The verifies that each company’s data contains values ​​for the four keys — “Company Name”, “Product”, “City”, and “Email”. three first 10 second non-empty And the test checks if each company’s data contains a email address. last unique Let’s run our tests in the Cypress test runner with the standard command: > npx cypress open We click on the name of the file in the test runner window and voila — our tests were completed successfully in less than seconds: spec 0.1 Final Thoughts Although Cypress can be used to validate data, this is obviously not the case for its core functionality. Cypress does not test files “under the hood”. Excel Excel Therefore, you should be aware of possible errors that may occur at the stage of converting an file to format, such as formatting errors, data loss, etc., which can subsequently lead to undesirable results. Excel JSON 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 tool, you might be interested in subscribing to my blog and getting notified when there’s a new useful article. Cypress “Testing with Cypress” The source code of all examples as well as the files presented in this article can be found in the of the blog on GitHub. repository Thank you for your attention! Happy testing! First published here

How to Test Excel Data With Cypress

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

Conducting Cross Browser Testing With Cypress in CI/CD using Docker

11 Best Automation Testing Tools to Try in 2021

145 Stories To Learn About Test Automation

Python-Powered Performance Testing for QA Testers: A Beginner's Guide to Cloud API Load Testing

57 Stories To Learn About Automated Testing

7 Steps to Effectively Implement Test Automation for Video Games

Conducting Cross Browser Testing With Cypress in CI/CD using Docker

11 Best Automation Testing Tools to Try in 2021

145 Stories To Learn About Test Automation

Python-Powered Performance Testing for QA Testers: A Beginner's Guide to Cloud API Load Testing

57 Stories To Learn About Automated Testing

7 Steps to Effectively Implement Test Automation for Video Games

Light-Mode

Classic

Newspaper

Minty

Dark-Mode

Neon Noir

Minty

HN StartUps