Read excel files in Cypress

Solution 1:

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
  })
}