javascript - UnhandledPromiseRejectionWarning：错误：无效地址：未定义。如何处理？
问题描述
我正在遍历Sheet 1一个 Excel 文件，该文件包含 3 列和 100 行数据（字符串），并将行中的每个单元格与Sheet 2.
检查应该Sheet 1逐行开始，查看行中每个单元格的值是否与任何地方匹配Sheet 2，逐行。如果检查失败，则应停止对该行的进一步检查并开始要检查的下一行。Sheet 1无法匹配的单元格应标记为红色。
我下面的代码接近我需要的代码，但是如果Sheet 1(Eg Row 1: B2 and B3)的一行中有 2 个或多个单元格与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 |
根据示例数据，应该有三个单元格（B1和B5）标记C11为红色Sheet 1。new.xlsx例如
这是一个关于如何进行检查的场景示例 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)
在这个地方，如果行中的 3 个单元格不好，则变量oneOfBestMasterRowNumber不应该是undefined，因为在它之后，如果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 代码翻译成客户端 JavaScrpt 代码，以通过检查以下片段中的两个不同数据集（模仿 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>圣诞快乐和新年快乐！
推荐阅读
- reactjs - 无法使用 laravel 控制器保存反应原生 stringfy 数据
- java - StackOverflowError：执行获取请求时为空
- windows - 为什么不能在本机反应中创建新应用程序
- c++ - 如何导入使用自制软件安装的 GMP？
- ios - 三个实体分层，但在第三个中第一个不被考虑
- php - 如何使用 CURL_SETOPT 通过另一个 IP 地址路由 CURL？
- c++ - 复制省略并在返回值中移动语义
- javascript - 我可以使用 JS 获取客户端 DNS IP 地址吗？
- html - 如何使 Web 输入字段在某个窗口宽度后开始缩小？
- android - JaCoCo 如何/何时创建 .exec 文件？