我正在遍历 Excel 文件的 Sheet 1，该文件包含 3 列和 100 行数据(字符串)，并将一行中的每个单元格与 Sheet 2 中的组合行进行比较。
检查应该开始逐行使用 Sheet 1，逐行查看行中每个单元格的值是否与 Sheet 2 中的任何位置匹配。如果检查失败，则应停止对该行的进一步检查，并开始检查下一行。 Sheet 1 中无法匹配的单元格应标记为红色。
我下面的代码接近我的需要，但如果 Sheet 1 的一行中有 2 个或更多单元格，则会抛出错误(例如 Row 1: B2 and B3) 与 Sheet 2 的任何行中的任何内容都不匹配。
错误:
(node:9040) UnhandledPromiseRejectionWarning: Error: Invalid Address: Aundefined
at Object.validateAddress (C:\node_modules\exceljs\dist\es5\utils\col-cache.js:86:13)
at new module.exports (C:\node_modules\exceljs\dist\es5\doc\cell.js:29:12)
at module.exports.getCellEx (C:\node_modules\exceljs\dist\es5\doc\row.js:55:14)
at module.exports.getCell (C:\node_modules\exceljs\dist\es5\doc\row.js:72:41)
at C:\so.js:56:61
at C:\node_modules\exceljs\dist\es5\doc\worksheet.js:370:11
at Array.forEach (<anonymous>)
at module.exports.eachRow (C:\node_modules\exceljs\dist\es5\doc\worksheet.js:368:18)
at C:\so.js:16:19
at <anonymous>
(node:9040) UnhandledPromiseRejectionWarning: Unhandled promise rejection. This error originated either by throwing inside of an async function without a catch block, or by rejecting a promise which was not handled with .catch(). (rejection id: 1)
(node:9040) [DEP0018] DeprecationWarning: Unhandled promise rejections are deprecated. In the future, promise rejections that are not handled will terminate the Node.js process with a non-zero exit code.
示例数据:
工作表 1:
| COL A | COL B | COL C |
|-------|--------|--------|
| bob | one | silver |
| 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 |
工作表 2:
| COL A | COL B | COL C |
|-------|--------|--------|
| bob | eleven | blue |
| bob | eleven | red |
| bob | eight | red |
| terry | seven | yellow |
| terry | seven | orange |
根据示例数据，Sheet 1 中应该有三个标记为红色的单元格(。例如B1、B5 和C11) new.xlsx 的
这是一个 scenarios example PDF应该如何进行检查:
代码:
// 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');
});
最佳答案
此错误的来源是来自行的第二次调用:
if(!isdataRowOK)
在这个地方，变量 oneOfBestMasterRowNumber 应该不是 undefined 如果行中的 3 个单元格是坏的，因为在它之后如果 getRow('undefined') 然后我们有一个错误。因此，我们现在先检查它，如下所示，并为其写入值 1(1. 行号)，因为在这种情况下它并不重要。
if(oneOfBestMasterRowNumber == void 0) //void 0 - undefined
oneOfBestMasterRowNumber = 1;
还有一个地方我们应该改变:而不是代码行:
if(numberOfGoodCellsInRow == 2)
oneOfBestMasterRowNumber = masterRowNumber;
我们应该这样写:
if(numberOfGoodCellsInRow > numberOfGoodCellsInBestRow)
{
numberOfGoodCellsInBestRow = numberOfGoodCellsInRow;
oneOfBestMasterRowNumber = masterRowNumber;
}
因为一行中不止一个单元格可能是坏的。我们现在还有新变量 numberOfGoodCellsInBestRow。
我认为如果行中的两个或三个单元格是坏的，则标记它们会更好，但如果您只想要行中的一个单元格，则可以在单元格着色后取消注释 break;。
另请参阅我在以下代码中的注释:
// 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'],
numberOfGoodCellsInBestRow = 0;
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++;
//here we detect if 1 or 2 cells are bad
if(numberOfGoodCellsInRow > numberOfGoodCellsInBestRow)
{
numberOfGoodCellsInBestRow = numberOfGoodCellsInRow;
oneOfBestMasterRowNumber = masterRowNumber;
}
if(numberOfGoodCellsInRow == 3)
isdataRowOK = true
}
});
//here was error source: oneOfBestMasterRowNumber
//should be not undefined if 3 cells in row are bad
if(oneOfBestMasterRowNumber == void 0)//void 0 - undefined
oneOfBestMasterRowNumber = 1;
if(!isdataRowOK)
{
//here was error place: if getRow('undefined') then was error
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
//break; uncomment this if you want only one cell as color red marked in the row
}
}
}
});
return workbook.xlsx.writeFile('new.xlsx');
});
}
示例可视化
我已将 Node.js 代码翻译成客户端 JavaScript 代码，以通过检查以下代码段中的两个不同数据集(Excel 表格的模仿)来演示它:
function checkDataset(obj)
{
var radios = obj.parentNode.elements['dataset'],
dataSetNumber,
i = radios.length;
for(; i--;)
if(radios[i].checked)
{
dataSetNumber = i;
break
}
var dataSheet = xlsFile['data' + dataSetNumber],
masterSheet = xlsFile['master' + dataSetNumber];
dataSheet.forEach(function(dataRow, dataRowIndex)
{
var dataRowCells =
{
dataCell1: dataRow.A,
dataCell2: dataRow.B,
dataCell3: dataRow.C
},
isdataRowOK = false,
oneOfBestMasterRowIndex,
cellNames = ['A','B','C'],
numberOfGoodCellsInBestRow = 0;
masterSheet.forEach(function(masterRow, masterRowIndex)
{
if(!isdataRowOK)
{
var numberOfGoodCellsInRow = 0;
for(var i = 1; i < 4; i++)
if(dataRowCells['dataCell' + i].value === masterRow[cellNames[i-1]].value)
numberOfGoodCellsInRow++;
//here we detect if 1 or 2 cells are bad
if(numberOfGoodCellsInRow > numberOfGoodCellsInBestRow)
{
numberOfGoodCellsInBestRow = numberOfGoodCellsInRow;
oneOfBestMasterRowIndex = masterRowIndex;
}
if(numberOfGoodCellsInRow == 3)
isdataRowOK = true
}
});
//oneOfBestMasterRowIndex should be not undefined if 3 cells in row are bad
if(oneOfBestMasterRowIndex == void 0)//void 0 - undefined
oneOfBestMasterRowIndex = 0;
if(!isdataRowOK)
{
var masterRowForCheck = masterSheet[oneOfBestMasterRowIndex];
for(var i = 1; i < 4; i++)
{
var dataCell = dataRowCells['dataCell' + i];
if(dataCell.value !== masterRowForCheck[cellNames[i-1]].value)
{
// Mark this failed cell as color red
dataCell.bgColor = 'red';
//break; uncomment this if you want only one cell as color red marked in the row
}
}
}
});
var table = '<table border="1"><tr style="background:#00a;color:#fff">'
+ '<th>A</th><th>B</th><th>C</th></tr>';
dataSheet.forEach(function(dataRow, dataRowIndex)
{
table += '<tr>';
for(var i in dataRow)
table += '<td bgcolor="' + (dataRow[i].bgColor ? 'red' : '')
+ '">' + dataRow[i].value + '</td>';
table += '</tr>';
});
document.write(table + '</table>');
}
function cells(strValues)
{
var v = strValues.split('\t');
return{A: {value: v[0]}, B: {value: v[1]}, C: {value: v[2]}}
}
var xlsFile =
{
//Dataset 1:
data0:
[
cells('bob sacsac sxcsc'),
cells('sacfbrb eleven blue'),
cells('ascasc one red'),
cells('tyjytn one red'),
cells('ascsac one red'),
cells('terry elf yellow'),
cells('terry seven elf'),
cells('terry elf elf'),
cells('terry elf elf'),
cells('terry seven yellow'),
cells('terry elf elf'),
cells('terry seven orange'),
cells('terry seven yellow'),
cells('santa mary jane'),
cells('bob zero mauve'),
cells('bob one silver'),
cells('bob eleven blue'),
cells('bob eleven red'),
cells('bob eleven red'),
cells('bob one red'),
cells('bob eight red'),
cells('bob eight red'),
cells('bob eight red'),
cells('terry seven yellow'),
cells('terry seven yellow'),
cells('terry seven gold')
],
master0:
[
cells('bob eleven blue'),
cells('bob eleven red'),
cells('bob eight red'),
cells('terry seven yellow'),
cells('bob seven yellow'),
cells('terry seven orange'),
cells('tiger one red')
],
//Dataset 2:
data1:
[
cells('bob one blue'),
cells('bob eleven blue'),
cells('bob eleven red'),
cells('bob eleven red'),
cells('bob one red'),
cells('bob eight red'),
cells('bob eight red'),
cells('bob eight red'),
cells('terry seven yellow'),
cells('terry seven yellow'),
cells('terry seven gold')
],
master1:
[
cells('bob eleven blue'),
cells('bob eleven red'),
cells('bob eight red'),
cells('terry seven yellow'),
cells('terry seven orange')
]
};<form method="post" action="#">
<p><b>Which dataset do you want check?</b></p>
<label><input type="radio" name="dataset">Dataset 1</label><br>
<label><input type="radio" name="dataset" checked>Dataset 2</label><br><br>
<input type="button" value="Check it" onclick="checkDataset(this)">
</form>
关于javascript - UnhandledPromiseRejectionWarning : Error: Invalid Address: Aundefined. 如何处理？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/53742262/