How to check cell values in different sheets using JavaScript and ExcelJS?

I have a spreadsheet with two sheets. Sheet 1 contains 3 columns with 100s of rows of data (strings). Sheet 2 is to be used as a mastersheet of correct combinations for Sheet 1 to be checked against to make sure the values in in cell are correct. Sheet 2 has 3 columns also.

What I would like to do is loop through Sheet 1 row by row, and check each cell's value in the row matches anywhere what is in Sheet 2, row by row.

If a check fails, then further checks on that row should cease and the next row to be checked commences. Cells in Sheet 1 that couldn't be matched should be marked red.

My code below is close to what I need, but is marking more cells incorrect than what is required.

// Import the library
var Excel = require('exceljs');
var moment = require('moment');
// Define Excel filename
var ExcelFile = 'so.xlsx';

// Read from the file
var workbook = new Excel.Workbook();
workbook.xlsx.readFile(ExcelFile)
    .then(function() {
        // Use workbook
        var dataSheet = workbook.getWorksheet('Sheet 1');
        var masterSheet = workbook.getWorksheet('Sheet 2');

        dataSheet.eachRow({ includeEmpty: false }, function(row, rowNumber) {

            var dataSheetCell1 = row.getCell('A').value;
            var dataSheetCell2 = row.getCell('B').value;
            var dataSheetCell3 = row.getCell('C').value;

            masterSheet.eachRow({ includeEmpty: false }, function(row, rowNumber) {

                var masterSheetCell1 = row.getCell('A').value;
                var masterSheetCell2 = row.getCell('B').value;
                var masterSheetCell3 = row.getCell('C').value;

                // Iterate over all cells in a row (including empty cells)
                row.eachCell({ includeEmpty: false }, function(cell, colNumber) {
                    if(dataSheetCell1 == masterSheetCell1) {
                        console.log(true)
                    } else {
                        // Stop all further checks for this sheet(n) row and move onto next row
                        // Mark this failed cell as color red
                    }
                });
            });
        });

        return workbook.xlsx.writeFile('new.xlsx');
    });

Example data would look like this:

Sheet 1:

| COL A | COL B  | COL C  |
|-------|--------|--------|
| bob   | eleven | blue   |
| bob   | eleven | blue   |
| bob   | eleven | red    |
| bob   | eleven | red    |
| bob   | one    | red    |
| bob   | eight  | red    |
| bob   | eight  | red    |
| bob   | eight  | red    |
| terry | seven  | yellow |
| terry | seven  | yellow |
| terry | seven  | gold   |

Sheet 2:

| COL A | COL B  | COL C  |
|-------|--------|--------|
| bob   | eleven | blue   |
| bob   | eleven | red    |
| bob   | eight  | red    |
| terry | seven  | yellow |
| terry | seven  | orange |

According to the sample data, there should be two errors (B5 and C11) that are marked red in Sheet 1 of new.xlsx. E.g.

This is an example PDF of how the checking should take place:

Solution

You can mark each failed cell as color red like follows:

// Import the library
var Excel = require('exceljs'),
    moment = require('moment'),
    // Define Excel filename
    ExcelFile = 'so.xlsx',
    // Read from the file
    workbook = new Excel.Workbook();

workbook.xlsx.readFile(ExcelFile)
    .then(function()
    {
        // Use workbook
        var dataSheet = workbook.getWorksheet('Sheet 1'),
            masterSheet = workbook.getWorksheet('Sheet 2');

        dataSheet.eachRow({ includeEmpty: false }, function(dataRow, dataRowNumber)
        {
            var dataRowCells =
                {
                    dataCell1: dataRow.getCell('A'),
                    dataCell2: dataRow.getCell('B'),
                    dataCell3: dataRow.getCell('C')
                },
                isdataRowOK = false,
                oneOfBestMasterRowNumber,
                cellNames = ['A','B','C'];

            masterSheet.eachRow({ includeEmpty: false }, function(masterRow, masterRowNumber)
            {
                if(!isdataRowOK)
                {
                    var numberOfGoodCellsInRow = 0;

                    for(var i = 1; i < 4; i++)
                        if(dataRowCells['dataCell' + i].value === masterRow.getCell(cellNames[i-1]).value)
                            numberOfGoodCellsInRow++;

                    if(numberOfGoodCellsInRow == 2)
                        oneOfBestMasterRowNumber = masterRowNumber;

                    if(numberOfGoodCellsInRow == 3)
                        isdataRowOK = true
                }
            });


            if(!isdataRowOK)
            {
                var masterRowForCheck = masterSheet.getRow(oneOfBestMasterRowNumber);

                for(var i = 1; i < 4; i++)
                {
                    var dataCell = dataRowCells['dataCell' + i];
                    if(dataCell.value !== masterRowForCheck.getCell(cellNames[i-1]).value)
                    {
                        // Mark this failed cell as color red
                        dataCell.style = Object.create(dataCell.style); // Shallow-clone the style, break references
                        dataCell.fill = {type: 'pattern', pattern:'solid', fgColor:{argb:'FA8072'}}; // Set background
                    }
                }

            }
        });

        return workbook.xlsx.writeFile('new.xlsx');
    });