Excel任务窗格绑定数据更改事件永远不会结束

Excel Task Pane - bindingDataChanged event never ends

本文关键字：永远 事件 结束 任务 绑定 数据 Excel      更新时间：2023-09-26

我正在Excel任务窗格应用程序上工作。基本上它所做的是在用户键入时更改具有无效格式(邮政编码r1243)的单元格的背景颜色。我创建了一个绑定，并添加了一个事件处理程序bindingdatachchanged，用于侦听数据更改。如果有任何更改，请验证格式并更改背景颜色。代码如下:

代码是一种工作，但问题是，onbindingdatachchanges似乎永远不会结束，即使没有数据上的任何变化。会出什么问题呢?

function listenChanges() {
    Office.select("bindings#customerTableBinding").addHandlerAsync(Office.EventType.BindingDataChanged, onBindingDataChanged, function (asyncResult) {
        if (asyncResult.status === "failed") {
            app.showNotification('Error: ' + asyncResult.error.message);
        } else {
            app.showNotification('New event handler added for binding.');
        }
    });
}
function onBindingDataChanged(eventArgs) {
    eventArgs.binding.getDataAsync({coercionType: 'table'},
     function (asyncResult) {
         if (asyncResult.status == 'succeeded') {
             var table = new Office.TableData();
             table.headers = asyncResult.value.headers;
             table.rows = asyncResult.value.rows;
             var column = getColumns(table);
             var rows = getRows(table, column);
             eventArgs.binding.setFormatsAsync([{ cells: Office.Table.Data, format: { backgroundColor: "white" } }], function (asyncResult) { });
             for (var i = 0; i < rows.length; i++) {
                 eventArgs.binding.setFormatsAsync([{ cells: { row: rows[i], column: column }, format: { backgroundColor: "red", fontStyle: "bold" } }], function (asyncResult) { });
             }
         }
         else
             app.showNotification("The error message is " + asyncResult.error.message);
     });
}
function getColumns(table) {
    var column;
    for (var i = 0; i < table.headers.length; i++) {
        for (var j = 0; j < table.headers[i].length; j++) {
            if (table.headers[i][j] == "PostalCode") {
                column = j;
            }
        }
    }
    return column;
}
function getRows(table, column) {
    var rows = new Array();
    for (var i = 0; i < table.rows.length; i++) {
        if (!isValidUSZip(table.rows[i][column])) {
            rows.push(i);
        }
    }
    return rows;
}
function isValidUSZip(zip) {
    return /^'d{5}(-'d{4})?$/.test(zip);
}

eventArgs.binding。setFormatsAsync([{cells: Office.Table.]数据，格式:{backgroundColor: "white"}}]，函数(asyncResult) {});

每次更改事件触发时，将背景设置为白色。这将再次触发change事件(导致无限循环)。要解决这个问题，您可以在触发数据事件操作时删除处理程序，然后在进行更新后将其添加回来。

function addHandlerChanged() {
    if (selfBinding) {
        selfBinding.addHandlerAsync(
           Office.EventType.BindingDataChanged, dataChangeEvent);
    }
}
function removeHandlerChanged() {
    if (selfBinding) {
        selfBinding.removeHandlerAsync(
           Office.EventType.BindingDataChanged, { handler: dataChangeEvent }, function (result) {
           });
    }
};
function dataChangeEvent(eventArgs) {    
  //remove handler
  removeHandlerChanged();
   // do you're table format update here
   //then add the handler back 
   addHandlerChanged();
}
相关文章：