In Cypress, things don't always read correctly for XLSX files

Question

I have a front-end Angular app in which I'm implementing Cypress tests.

Things don't always read correctly for XLSX files though.

If I do this:

cy.readFile('cypress/downloads/myfile.XLSX');

it does in fact read the file as expected.

However if I do this:

cy.readFile('cypress/downloads/myfile.XLSX').should('contain', 'mytext');

It doesn't read 'mytext' even though I know it is in the file.

I know I'm syntactically correct because it does fine reading text from .xls files.

Is there any way to get cypress to look for text with .XLSX files?

Thanks much.

Answer 1

The SheetJS package can be used to parse the XLSX file after reading with base64 encoding

In the following example I used a small worksheet which contains just text

After reading the file, I convert to HTML so that it can be mounted in the Cypress test page.

The test runner shows the data and it can be queried just like a web page.

const XLSX = require('xlsx')

it('reads and tests an XL file', () => {

  cy.readFile('cypress/downloads/file_example_XLS_10.xlsx', 'base64')
    .then(file => {

      // parse to HTML
      const data = XLSX.read(file)
      const worksheet = data.Sheets['Sheet1']
      const html = XLSX.utils.sheet_to_html(worksheet) 

      // mount to test page
      const worksheetAsTable = Cypress.$(html)
      Cypress.$('body').empty()
      Cypress.$('body').append(worksheetAsTable)
    })

  // Now test the HTML table with Cypress commands
  cy.get('table tr:nth-child(3) td:nth-child(3)')
    .invoke('text')
    .should('eq', 'Hashimoto')

  // Or simply assert the text is present
  cy.get('body').should('contain', 'Hashimoto')

})