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 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 currently isn't looping right and makes way more checks returning true than I think I need/want even just for one check of sheet:1/cell:A against sheet:2/cell:A.
// 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.
from How to check cell values in different sheets using Javascript and ExcelJS
No comments:
Post a Comment