How to Read and Write from Excel Files in Cypress

Introduction

When dealing with test automation, it's common to work with external data sources like Excel files to manage test data efficiently. While Cypress does not natively support Excel file operations, we can leverage popular Node.js libraries to read from and write to Excel files as part of our Cypress testing framework.

In this post, we'll explore how to handle Excel files in Cypress, complete with code examples for reading and writing Excel data, and discuss best practices to efficiently manage test data using Excel files.

Why Use Excel Files in Cypress?

Excel files are widely used for:

• Managing large test datasets.
• Performing data-driven testing.
• Logging test results for post-execution analysis.

Since Cypress doesn’t have built-in support for Excel files, we’ll use Node.js packages to read and write Excel data. One popular library that helps with this is xlsx.

Setting Up Cypress to Work with Excel Files

Before we dive into the examples, we need to install the necessary dependencies and configure our Cypress environment to handle Excel files.

Step 1: Install the Required Packages
We’ll use the xlsx library to read and write Excel files. To install it, run the following command in your Cypress project directory:

npm install xlsx --save

Step 2: Folder Structure
It’s important to organize your project files for better maintainability. Here's a sample folder structure for working with Excel files in Cypress:

cypress/
    fixtures/
        testData.xlsx  // Excel file with test data
    e2e/
        excelTests.cy.js  // Cypress test for Excel handling
    support/
        commands.js    // Custom commands to read/write Excel

Reading Data from Excel Files in Cypress

Now, let’s see how to read data from an Excel file and use it in our Cypress tests.

Real Example: Reading Data from Excel File
We have a test data file testData.xlsx located in the fixtures folder. It contains login test credentials:

testData.xlsx:

We’ll create a Cypress test that reads this Excel data and automates the login test scenarios based on the provided inputs.

Step 1: Create a Custom Command to Read Excel File
Let’s add a custom command to read Excel data in the support/commands.js file:

const XLSX = require('xlsx');

Cypress.Commands.add('readExcelFile', (filePath) => {
  return cy.task('readExcelFile', filePath);
});

Step 2: Add the Task in cypress.config.js
Define a Cypress task in the cypress.config.js file that reads the Excel file and converts it into JSON format:

const path = require('path');
const XLSX = require('xlsx');

module.exports = {
  e2e: {
    setupNodeEvents(on, config) {
      on('task', {
        readExcelFile(filePath) {
          const absolutePath = path.resolve(__dirname, 'cypress/fixtures', filePath);
          const workbook = XLSX.readFile(absolutePath);
          const sheetName = workbook.SheetNames[0];
          const worksheet = workbook.Sheets[sheetName];
          const data = XLSX.utils.sheet_to_json(worksheet);
          return data;
        }
      });
    }
  }
};

In this code:

• We use the xlsx.readFile() function to read the Excel file.
• We specify the first sheet in the workbook (workbook.SheetNames[0]).
• We convert the sheet data into JSON format using XLSX.utils.sheet_to_json().

Step 3: Cypress Test to Read Data
In excelTests.cy.js, let’s create a test that reads the Excel data and performs login tests based on the data:

describe("Read data from Excel file in Cypress", () => {
  it("should use Excel data to perform login tests", () => {
    cy.readExcelFile("testData.xlsx").then((data) => {
      data.forEach((row) => {
        cy.visit("https://freelance-learn-automation.vercel.app/login");
        cy.get('input[name="email1"]').type(row.email);
        cy.get('input[name="password1"]').type(row.password);
        cy.get('button[type="submit"]').click();

        if (row.expected === "success") {
          cy.contains("Manage").should("be.visible");
        } else {
          cy.contains("USER Email Doesn't Exist").should("be.visible");
        }
      });
    });
  });
});

In this test:

• We read the Excel file using cy.readExcelFile().
• We iterate over the rows of data, extracting email, password, and expected results for each test case.
• The data is then used to test a login form.

Writing Data to Excel Files in Cypress

Writing data to Excel files can be useful when you want to log test results or capture dynamic data generated during tests. Let’s see how to write data back into an Excel file.

Example: Writing Data to Excel
Step 1: Create a Cypress Custom Command for Writing to Excel
In commands.js, we’ll create a custom command that writes test data to an Excel file:

Cypress.Commands.add('writeToExcel', (data, filePath) => {
  return cy.task('writeToExcel', { data, filePath });
});

Step 2: Define Write Task in cypress.config.js
Now, define a task in cypress.config.js that writes data to an Excel file:

const fs = require('fs');
const XLSX = require('xlsx');

module.exports = {
  e2e: {
    setupNodeEvents(on, config) {
      on('task', {
        writeToExcel({ data, filePath }) {
          const workbook = XLSX.utils.book_new();
          const worksheet = XLSX.utils.json_to_sheet(data);
          XLSX.utils.book_append_sheet(workbook, worksheet, 'Sheet1');
          XLSX.writeFile(workbook, path.resolve(__dirname, 'cypress/fixtures', filePath));
          return null;
        }
      });
    }
  }
};

In this code:

• We use XLSX.utils.book_new() to create a new workbook.
• The XLSX.utils.json_to_sheet(data) method converts JSON data to an Excel sheet.
• The XLSX.writeFile() method writes the workbook to the file system.

Step 3: Cypress Test to Log Results
In excelTests.cy.js, let’s create a test that writes results into an Excel file:

describe('Write test results to Excel', () => {
  it('should log test results to Excel file', () => {
    const testResults = [
      { testName: 'Login Test 1', status: 'Passed' },
      { testName: 'Login Test 2', status: 'Failed' },
      { testName: 'Signup Test', status: 'Passed' }
    ];

    cy.writeToExcel(testResults, 'testResults.xlsx');
  });
});

In this test:

• We define an array of test results in JSON format.
• The cy.writeToExcel() command writes the results into an Excel file (testResults.xlsx).

After running this test, an Excel file testResults.xlsx will be generated in the fixtures folder with the following content:

Best Practices for Excel Handling in Cypress

1. Modularize Commands: Create custom commands for handling Excel operations, making your code reusable.
2. Organize Test Data: Store test data separately from test code to avoid hard-coding and enable better maintainability.
3. Validate Data: Ensure that Excel files are correctly formatted before using them in tests to prevent data errors.

Conclusion

Working with Excel files in Cypress opens up powerful possibilities for managing test data and results. By integrating the xlsx Node.js library, you can easily read from and write to Excel files, enabling data-driven testing and dynamic test logging. Using the techniques outlined in this post, you can improve the flexibility of your Cypress tests and efficiently handle external test data sources.

Start integrating Excel files into your Cypress tests today to enhance your automation suite’s capabilities!