未处理的 Promise 拒绝警告：错误：无效地址：Aundefined。如何处理？

Question

我正在遍历一个包含三列数据（字符串）和数百行数据的Excel文件的Sheet 1，并将每个单元格与Sheet 2中的组合行进行比较。

应该从Sheet 1逐行开始检查，看看行中每个单元格的值是否与Sheet 2中的任何行匹配。如果检查失败，则应停止对该行进行进一步检查，并开始下一行的检查。在Sheet 1中无法匹配的单元格应标记为红色。

我的代码接近所需，但如果在Sheet 1的一行中有两个或更多个单元格（例如Row 1: B2 和 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 |

根据示例数据，在 new.xlsx 的 Sheet 1 中应该有三个单元格（B1、B5 和 C11）标记为红色。例如：B1、B5 和 C11。

这是一个场景示例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');
    });

- dom_ahdigital

---

为什么你不在 Promise 中处理拒绝呢？就像这样：workbook.xlsx.readFile(ExcelFile) .then(function(){....}) .catch(function(e){console.log(e.stack);}) - Vasyl Moskalov

Answer 1

这个错误的源头是第二次调用位于以下行的代码：

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代码翻译为客户端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>

- Bharata

---

@dom_ahdigital，你能否帮我上传“so.xlsx”文件并包含所有新的案例吗？但请不要上传你的产品版本。 - Bharata

这是我一直在使用来测试不同场景的链接：https://www.dropbox.com/s/3hbewhmca33gaqj/so.xlsx?dl=0 - dom_ahdigital