How to validate tables, rows or any content of an Excel file using Cypress

At the company I work for, we already have many test cases to validate a key behavior of our SaaS, which through the user downloads a table as an Excel file of the information needed. But there was a need to validate some edge cases, in which we also needed to validate that the content corresponds to what the table showed.

This would mean that Cypress needs to deterministically validate rows, numbers, names and even colors inside the Excel file set by our user flows. After some research, we basically came upon two Node.js libs: @e965/xlsx and exceljs.

While @e965/xlsx is mostly used for data content validation, as in validating a JSON rows straight from the sheet - exceljs is more focused for style assertion, meaning assertions like “is A1 light-green?”. All right, so now we could split keeps tests readable and fast.

Configuring @e965/xlsx library

First, wire up the configuration in Cypress. Head off to Node with cy.task(). It’s the official way to run filesystem code from Cypress tests: register tasks in setupNodeEvents and they’ll return values back to your spec.

Remember to also import the package on the config file:

//cypress.config.ts

const xlsx = require('@e965/xlsx');
...
...
...
      on('task', {
        async readExcelByPattern(pattern: string, timeoutMs = 15000) {
          const re = new RegExp(pattern);
          const end = Date.now() + timeoutMs;

          while (Date.now() < end) {
            const files = fs.readdirSync(downloadsDir).filter(f => re.test(f) && !f.endsWith('.crdownload') && !f.endsWith('.tmp'));

            if (files.length) {
              const { fullPath } = files
                .map(f => {
                  const fullPath = path.join(downloadsDir, f);
                  return { fullPath, mtime: fs.statSync(fullPath).mtimeMs };
                })
                .sort((a, b) => b.mtime - a.mtime)[0];

              await sleep(200);

              const wb = xlsx.readFile(fullPath);
              const sheet = wb.Sheets[wb.SheetNames[0]];
              const data = xlsx.utils.sheet_to_json(sheet);
              return { fileName: path.basename(fullPath), data };
            }

            await sleep(300);
          }

          throw new Error(`File .xlsx taht matches /${pattern}/ not found on "${downloadsDir}" inside ${timeoutMs}ms`);
        },

You can see that readExcelByPattern is the task we should call to validate the content like rows, tables and any information inside the Excel file. You can then define it inside your test context and methods (or define it globally over commands.ts if you plan to use it in many tests), but for a single test it should look something like this:

//my-testing-context.ts

  @step('Read downloaded excel values')
  readExcelDownloadedFile(
    pathToFile: string = 'excel_export/bugs/',
    fixture: string,
    fileName: string
  ): ExportPrintableReportContext<TParent> {
    cy.fixture(pathToFile + fixture).then((expected: any[]) => {
      cy.task('readExcelByPattern', fileName).then(({ data }: { data: any[] }) => {
        expect(data.length, 'Table length').to.equal(expected.length);
        expected.forEach((expectedRow, i) => {
          const actualRow = data[i];
          Object.entries(expectedRow).forEach(([key, expectedValue]) => {
            const actualValue = actualRow[key] === undefined ? null : actualRow[key];
            expect(actualValue, `Row ${i} - Column "${key}"`).to.equal(expectedValue);
          });
        });
      });
    });
    return this;
  }

As you can see it's pretty straight forward, it calls for a JSON file inside 'fixtures/excel_export/bugs/' that already has the values you want to validate and should be equal to the Excel file and executes a forEach of the Table length, and each row, which already awaits for a value.

And this is how it would look inside a test:

import { testContext } from '@/my-testing-context'

const testingContext = new testContext();

describe('example of reading excel files', => ()
  it('case 1', () => {
    testingContext.readExcelDownloadedFile('excel_export/bugs/', tk, 'Excel.xlsx');
  });
});

Basically it checked 171 rows of the file content and succeeded in 40 seconds.

For the second part of this tutorial, I'll expand on how to validate Excel colors as well. Happy testing!