Parsing XLSX files using Cypress

Since Cypress now overwrites the xlsx files that are downloaded as part of my tests, I needed to figure out how to get Cypress to check those files before the next scenario is executed and the file over-written. Here’s how I did it.

FROM WITHIN VSC AND IN YOUR CYPRESS DIRECTORY:
1. Execute the following command

npm install node-xlsx --save-dev

2. Add to plugins/index.js:

const xlsx = require("node-xlsx").default;
const fs = require("fs");
const path = require("path");

module.exports = (on, config) => {
  // `on` is used to hook into various events Cypress emits
  on("task", {
    parseXlsx({ filePath }) {
      return new Promise((resolve, reject) => {
        try {
          const jsonData = xlsx.parse(fs.readFileSync(filePath));
          resolve(jsonData);
        } catch (e) {
          reject(e);
        }
      });
    }
  });
};

3. Add to commands.js

Cypress.Commands.add('parseXlsx', (inputFile) => {
  return cy.task('parseXlsx', { filePath: inputFile })
  })

4. Examine the file to determine what data is needed and add that to the script

const data = 
    ( 
     "Test Barn 2",
      "Test_250"
      //,"clean"
    )

5. Change the path where the file is downloaded to match your directory structure. Pay attention to the note in this section of code

// call the parseXlsx task we created above to parse the excel and return data as json
    cy.parseXlsx('/users/mfettes/gits/open-stalls-app/cypress/downloads/StallsReport.xlsx')
.then(
      jsonData => {
        // finally we write the assertion rule to check 
        // if that data matches the data we expected the excel file to have
        // ******************* NOTE: *********************
        // data[1] is the ROW that contains the data to be matched using the 
        // const above 
        expect(jsonData[0].data[1]).to.contain(data) 
      }
    )

Pay attention to the comment from above:

// data[1] is the ROW that contains the data to be matched using the 
// const above 

The easiest way to find out where the data you needs resides is to use an online .xlsx to .json converter and then looking in the resulting data for the appropriate array that contains the information you’re attempting to find via parseXLSX.