How to Read & Write Excel Data in Cypress? – Guide By Experts

Back to Blog
image of Write excel in cypress

How to Read & Write Excel Data in Cypress? – Guide By Experts

File conversion is one method we could use to read data from excel files. Due to Cypress’s lack of support.xlsx file. The only format supported by Cypress is JSON, thus we would need to convert it. In other words, we will convert the Excel.xlsx file to a JSON file. And using that JSON file, we’ll be able to extract the info we require.

To fulfill our use case, we will require an additional plugin. Run the following command at the project root level to accomplish this.

npm install node--xlsx --save-dev

We would then need to add a task to our e2e.js file located in the plugin folder. e2e.js files are mostly used to manage plugins and are found in plugin directories.

In the e2e.js file, we would need to define a job for reading the excel file.

Image of e2e.js file

In order to parse the xlsx file, a job has now been started.

The only thing left to do is convert the data to JSON and save it in a fresh JSON file below is the code to convert it.

Image of converted Code

In this case, we used the parseXlsx function, in which the e2e.js file already

Image of parseXlsx function

contained an event. Then, after iterating each element on the Excel sheet, we assigned each one to the template we created in the writeFile () method.

To create a new JSON file and parse the extracted data into it, use the WriteFile () method. That’s all there is to it; the xlsx file has been successfully converted to a JSON file. We can now use the data in our test due to fixtures.

The command.js file in the support folder and the following code must now use, as shown so that it won’t make any errors.

Now we can run the script can see the JSON data on the console.

This is the whole script where we are driving data from excel for login.

Image of Json Conversion

Frequently Asked Questions

To convert an Excel file to JSON in Cypress, you can use an external package like ‘xlsx’. You can read the Excel file using this package and convert it to JSON using the ‘utils’ functions in Cypress.

Cypress provides an external package called ‘xlsx’ that can be used to read Excel files in Cypress. You can use this package to read the file and extract the data in the desired format.

Yes, Cypress can read xlsx files using the ‘xlsx’ package. This package can be installed and used in Cypress to read the data from an xlsx file and use it in your tests.

Yes, Cypress can read CSV files using the different-2 plugins. Cypress-file-upload is one of the most popular plugins that allow cypress to read CSV files. Apart from that we can use the following plugins “cypress-fixture-data, cypress-csv-upload, cypress-file-commands, cypress-parallel-csvm, etc.

Cypress does not provide a built-in method to write to a file. However, you can use the ‘fs’ module to write data to a file in your test code. This module provides methods to write data to a file on the disk.

To parse JSON in Cypress, you can use the built-in ‘JSON.parse()’ method. This method can use to parse a JSON string into a JavaScript object, which can use in your tests.

To cypress write to file use ‘Cy.writeFile()’ command & follow the below steps:-

  • Install “cypress-file-commands”
  • Import plugin in “cypress/support/index.js” file
  • Use ‘cy.writeFile()’ command

Related Blog

Share this post

Back to Blog