Cypress is returning an empty array when trying to log sheetnames of an excel file

user_19546216 0
Viewed 54

I am currently trying to get the sheetnames of an excel file but Cypress is returning an empty array. Is there something I missed? I'll be using it to verify data on later steps.

I'm using Cypress 9.6.0 with Cucumber. Below are my scripts and screenshots:

index.js for task

module.exports = (on, config) => {
on('file:preprocessor', cucumber());
on('task', { 
  checkExcelSheetContents(args){
    if (fs.existsSync(args.filePath)) {
      const workbook = xlsx.readFile(args.filePath);
      return xlsx.utils.sheet_to_json(workbook.SheetNames)
      } else {
        throw new Error ("File not found")
      }
  }
  })
return Object.assign({}, config, {
  fixturesFolder: 'cypress/fixtures',
  integrationFolder: 'cypress/integration',
  screenshotsFolder: 'cypress/screenshots',
  videosFolder: 'cypress/videos',
  supportFile: 'cypress/support/index.js'
  });

}

.js file

And ('try', () => {
        var excelFilePath = "../CreateAutomatedTests/cypress/downloads/courses20220714_09_51_27.xlsx"
        cy.wrap(excelFilePath).as('filePath')
    
    cy.get('@filePath').then((filePath) => {
        cy.task('checkExcelSheetContents', { filePath }).then((contents) => {
            cy.log(contents)
            })
        })
})

Please see these screenshots as well

Cypress UI result

Sheet names

cypress excel
1 Answers

I've always used the buffer version of xlsx.read().

From xlsx package

For Node ESM, the readFile helper is not enabled. Instead, fs.readFileSync should be used to read the file data as a Buffer for use with XLSX.read:

import { readFileSync } from "fs";
import { read } from "xlsx/xlsx.mjs";

const buf = readFileSync("test.xlsx");
/* buf is a Buffer */
const workbook = read(buf);

Your task:

on('task', { 
  checkExcelSheetContents(args){
    if (fs.existsSync(args.filePath)) {
      const buf = fs.readFileSync(file);
      const workbook = xlsx.read(buf, { type: 'buffer' });
      return workbook.SheetNames
    } else {
      throw new Error ("File not found")
    }
  }
})
user_169977070
Related