CODEWITHSUNDEEP
cypress
Chaitanya K
Chaitanya K

Reputation: 71

Read excel files in Cypress

I am new to Cypress. How to read data from excel files using Cypress? Searched in google but could not find useful answers.

Upvotes: 3

Views: 22781

Answers (3)

joydeep
joydeep

Reputation: 1

For me the first answer pretty much worked. But i had to make a small fix.

Use function as Cypress task (plugin)

cypress/plugins/index.js

const readXlsx = require('./read-xlsx')

module.exports = (on, config) => {
  on('task', {
    'readXlsx': readXlsx.read
  })
}

when i used this code i got the below error in cypress.

CypressError cy.task('log') failed with the following error: The task 'log' was not handled in the plugins file. The following tasks are registered: readXlsx

and the below fix worked

const readXlsx = require('./read-xlsx')

module.exports = (on, config) => {
  on('task', {
    'readXlsx': readXlsx.read,
    log (message) {
      console.log(message)
      return null
    }
  })
}

Upvotes: 0

Nebojsa Jevdjovic
Nebojsa Jevdjovic

Reputation: 116

In cypress you can create cypress task to read xlsx file with SheetJS library.

Usage

cypress\integration\read-xlsx.spec.js

context('Xlsx file', () => {
  it('Read excel file', () => {
    cy.task('readXlsx', { file: 'my-excel.xlsx', sheet: "Sheet1" }).then((rows) => {
      expect(rows.length).to.equal(543)
      // expect(rows[0]["column name"]).to.equal(11060)
    })
  })
})

Need to install xlsx

 $ npm install xlsx

Create read excel fuction

cypress\plugins\read-xlsx.js

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

const read = ({file, sheet}) => {
   const buf = fs.readFileSync(file);
   const workbook = XLSX.read(buf, { type: 'buffer' });
   const rows = XLSX.utils.sheet_to_json(workbook.Sheets[sheet]);
   return rows
}

module.exports = {
   read,
}

Use function as Cypress task (plugin)

cypress\plugins\index.js

const readXlsx = require('./read-xlsx')

module.exports = (on, config) => {
  on('task', {
    'readXlsx': readXlsx.read
  })
}

Upvotes: 4

Peura
Peura

Reputation: 39

Here is an instruction how to use excel as source for cypress tests https://medium.com/@you54f/dynamically-generate-data-in-cypress-from-csv-xlsx-7805961eff55

First you need to conver your xlsx file to json with Xlsx

import { writeFileSync } from "fs";
import * as XLSX from "xlsx";
try {
  const workBook = XLSX.readFile("./testData/testData.xlsx");
  const jsonData = XLSX.utils.sheet_to_json(workBook.Sheets.testData);
  writeFileSync(
    "./cypress/fixtures/testData.json",
    JSON.stringify(jsonData, null, 4),
    "utf-8"
  );
} catch (e) {
  throw Error(e);
}

Then import json file and loop over each row and use the data in the way you want. In this example it tries to log in to a system.

import { login } from "../support/pageObjects/login.page";
const testData = require("../fixtures/testData.json");
describe("Dynamically Generated Tests", () => {
  testData.forEach((testDataRow: any) => {
    const data = {
      username: testDataRow.username,
      password: testDataRow.password
    };
    context(`Generating a test for ${data.username}`, () => {
      it("should fail to login for the specified details", () => {
        login.visit();
        login.username.type(data.username);
        login.password.type(`${data.password}{enter}`);
        login.errorMsg.contains("Your username is invalid!");
        login.logOutButton.should("not.exist");
      });
    });
  });
});

Upvotes: 1

Related Questions