Reading data from excel file – Cypress

Reading Time: 4 minutes

Hi folks,

In this blog, we will try to learn how to use excel file in cypress, so that we can have a data-driven framework with it. So, without much ado, let’s get started.

Approach

In cypress, we already have a defined folder structure for placing data files in the project. We mainly have

• fixtures
• integration
• plugins
• support
• node_module

in our project. Out of these folders, the integration folder is where we keep our test scripts. On the other hand, we use fixtures to keep the data files in it.

The approach that we may take in order to read data from excel files is file conversion. Since cypress doesn’t support .xlsx file we would have to convert it, cypress only supports JSON files. What it means is that we will convert the .xlsx(excel file) to a JSON file. And through that JSON file we will extract the data as per our need.

Implementation

Since we are clear with our approach now, we can move ahead with the implementation part. First of all, we will need an extra plugin to achieve our use case. We can do so by running the following command at the project root level.

npm install node-xlsx --save-dev

This will help to install another npm package that can parse the excel and convert it to a JSON format. After this, we would have to create a task in our index.js file inside the plugin folder. The main reason for having an index.js file in plugins folders is to manage the plugins only. Now, one more question arises.

What are tasks in cypress?

If we were to define tasks simply, it can be described as task() provides an escape hatch for running arbitrary Node code, so you can take actions necessary for your tests outside of the scope of Cypress.

For better understanding, please refer to the documentation, https://docs.cypress.io/api/commands/task#Usage.

Now, coming back to the implementation part. We would have to define a task for reading the excel file in the index.js file. It will look something like this.

const xlsx = require('node-xlsx').default; 
const fs = require('fs'); // for file
const path = require('path'); // for file path
module.exports = (on, config) => {
   on('task', { parseXlsx({ filePath }) 
   { return new Promise((resolve, reject) =>
     { try 
      {
         const jsonData = xlsx.parse(fs.readFileSync(filePath)); 
         resolve(jsonData);
         } catch (e) 
         {
            reject(e);
         } });
       }}); } 

Here, we are explicitly telling cypress to use the xlsx plugin that we have installed. “on” is used to trigger an event in a task. We are using xlsx.parse to convert the xlsx file to JSON. On top of that, we have put on try and catch method as well to throw an exception if no file of xlsx format is found on the file path.

So far so good, we have now trigged a task for parsing xlsx file. Now, we are halfway through in achieving our use case.

Converting the xlsx file to JSON

We have already managed the plugin to parse xlsx to JSON. Now, all we have to do is to convert that data to JSON and save it on a new JSON file. We don’t have to make a new .script.js file to do so, as some may suggest. We can do so in our test script only. Please have a look at the code snippet below for a better understanding.

describe('convert data to Json', () => 
{ it('read data from xcel', () =>
 { 
   cy.parseXlsx('cypress/fixtures/excelData.xlsx').then( (jsonData) =>
    { const rowLength = Cypress.$(jsonData[0].data).length
       for (let index = 0; index < rowLength; index++)
        { 
          var jsonData = jsonData[index].data 
          console.log(jsonData[index].data)
          cy.writeFile("cypress/fixtures/xlsxData.json", {username:jsonData[0][0], password:jsonData[0][1]})
        }
    })
 })
}) 

Here, we have used the parseXlsx function for which we have already created an event in the index.js file. After this, we have iterated every element on the excel sheet and then we have assigned it to the schema that we have defined in the writeFile() method.

WriteFile() method is used to create a new JSON file and parsing the extracted data to the new JSON file. That’s it, nothing more, we have successfully converted the xlsx file to json file. And now, with the help of fixtures, we can use the data in our test. But still, this won’t work. Cypress will throw an error as mentioned in the screenshot below.

Cypress refused to recognise the parseXlsx method. But there is nothing to worry about, we do have a workaround for this. If you remember, we explicitly told cypress to use the xlsx plugin. Hence, we have to explicitly define commands for this, so that cypress can recognise it.

Resolving “cy.parseXlsx is not a function”

For this, we have to use the command.js file in the support folder. Just add this code snippet to it and we are good to go.

Cypress.Commands.add("parseXlsx", (inputFile) => {
    return cy.task('parseXlsx', { filePath: inputFile })
    });

Now, we are all set. Now the script will work. If you run the test script on the cypress runner, we can see the JSON data on the developer’s console on the browser.

Now we can use this data in our test. Please have a look at the snippet below. This is the whole test script, I hope it may be useful if someone is facing any issues.

/// <reference types="Cypress" />

describe('convert data to Json', () => 
{ it('read data from xcel', () =>
 { 
   cy.parseXlsx('cypress/fixtures/excelData.xlsx').then( (jsonData) =>
    { const rowLength = Cypress.$(jsonData[0].data).length
       for (let index = 0; index < rowLength; index++)
        { 
          var jsonData = jsonData[index].data 
          console.log(jsonData[index].data)
          cy.writeFile("cypress/fixtures/xlsxData.json", {username:jsonData[0][0], password:jsonData[0][1]})
        }
    })
 })
}) 
        
describe("Reading Data from newly created json file",function()
{
    it("Sample test case of login", function()
    {
        cy.visit("http://www.testyou.in/Login.aspx");
        cy.fixture('xlsxData').then((user) =>
        {
            cy.get("input[name='ctl00$CPHContainer$txtUserLogin']").type(user.username)
            cy.get("input[name='ctl00$CPHContainer$txtPassword']").type(user.password)
        })
        cy.wait(2000)      
        cy.get("input[name='ctl00$CPHContainer$btnLoginn']").click()
    })
})

Here we used fixtures to read the newly created xlsxData.json file to read data. If we running the test script for the first time, then a new xlsxData.json file will be created. On the other hand, if we running the test more than one time, then the same file will get overwritten with each iteration.

That’s all folks. I hope you may have found this useful.

Reference

https://docs.cypress.io/api/commands/readfile