Cypress & Cucumber: Commands.js function does not work in step definition

A function I created in commands.js does not work in cucumber step definitions. Yet, another function with a simple return works in the same step definition.

Commands.js

Cypress.Commands.add('excelData', async () => {

    const ExcelJs = require('exceljs')

    var columnArray = [];

    var workbook = new ExcelJs.Workbook()
    await workbook.xlsx.readFile('E:/Automations/Cypress/FrameworkToCucumberSpecFlow/cypress/downloads/exceldata.xlsx')
    var worksheet = workbook.getWorksheet('sampledatab')

    worksheet.eachRow((row) => {
        row.eachCell((cell) => {
            columnArray.push(cell.value)
        })
    })

    columnArray.splice(0, 1) 

    return cy.wrap(columnArray)

})

Cypress.Commands.add('TestCommandsJS', () => {
    return 'TestCommandsJS works'
})

step definition

When('I compare that list with the Excel specification sheet list', () => {

    cy.excelData().then(data => { //this does not work
        cy.log(data)
    })

    cy.TestCommandsJS().then(data => { //this work fine
        cy.log(data)
    })

})

Any help appreciated.

The exceljs library you are using is a Nodejs library.

You will have to move the custom command code to a task - see Cypress task docs.

on('task', {
  async excelData({path, sheet}) {
    const ExcelJs = require('exceljs')
    const columnArray = []
    const workbook = new ExcelJs.Workbook()
    await workbook.xlsx.readFile(path)
    const worksheet = workbook.getWorksheet(sheet)
    worksheet.eachRow(row => {
      row.eachCell(cell => {
        columnArray.push(cell.value)
      })
    })
    return columnArray
  }
})

Then your test will use it:

cy.task('excelData', {
    path: 'SampleData.xlsx',   // path relative to project root
    sheet: 'SalesOrders'       // specify the sheet in test not in task 
  })
  .then(data => data.slice(0,7))  // headers
  .should('deep.eq', 
    ['OrderDate', 'Region', 'Rep', 'Item', 'Units', 'Unit Cost', 'Total'])