Cypress: Data Driven testing with Excel File

With the busy schedule, I got free time for few days. So I thought doing some learning on https://www.cypress.io/. So in this blog, I will be showing how to use data driven testing in cypress using excel file. Cypress supports json file but managing json file with test data will be tedious. Therefore, using excel sheet will be quite handy.

Cypress doesn’t supports .xlsx (excel file), so we need to convert data from excel file into JSON file, then fetch that data from JSON file in our test script.

For this blog, I am using https://github.com/cornflourblue/angular-7-registration-login-example ( I don’t own this project and this may be removed in future). Therefore, if it is removed in future, you can still have knowledge about data driven using excel by going through the details below.

Go to https://github.com/cornflourblue/angular-7-registration-login-example and download the file or clone it in your local machine.

Once the app is downloaded, enter the following in terminal which will start the applicaiton.

npm i
npm start

On the next terminal, enter the following command to download and install the cypress in your project path:

cd /your/project/path

npm install cypress --save-dev

cypress will be installed in the project and you can see cypress added in package.json too.

Open cypress using following command in the terminal:

npx cypress open

OR

node_modules/.bin/cypress open

Now we have successfully installed the test project and installed the cypress in the project.

We need to install plugin using following command:

npm install node-xlsx --save-dev

Create task in index.js (under plugins) file as shown in screenshot below.

Create file under plugins to read xlsx file shown in the screenshot below:

Add following code under command.js file:

Create .js file. This file contains the test script which uses data from excel file. Here, in this test, we go to Login Page, then click on Register, then user will be redirected to register page. User need to fill in the details. Once the user click the “Register” button then user will see successful message.

For this, we need to write a script which performs above steps. But, we need to perform data driven testing. Therefore, we need to convert the excel file into json data and feed test data from json in the script.

Install jsonpath (https://www.npmjs.com/package/jsonpath)as it is used in the test script to extract data from json file.

npm install jsonpath

In Cypress, we will see data.js file. Clicking on data.js under TEST will open the test in the browser.

Once we run the script, the excel file will be converted into json file.

Final test file, cypress folder can be found https://github.com/untoldstory69/dataDrivenCypressExcel

Thanks for reading this blog. I am aware this may not be professional blog, as I am not a blogger. I have written this just for my future reference and may help some one who need it.