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

Issue

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.

Solution

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

})

Answered By - TesterDick