如何在Office JS API BindingDataChanged事件中识别更改的单元格? [英] How to identify the changed cell in office js api BindingDataChanged event?

问题描述

在excel中的绑定中更改数据时，如果触发BindingDataChanged事件.

When the data is changed in a binding in excel, the BindingDataChanged event if fired.

function addHandler() {
    Office.select("bindings#MyBinding").addHandlerAsync(
    Office.EventType.BindingDataChanged, dataChanged);
}
function dataChanged(eventArgs) {
    write('Bound data changed in binding: ' + eventArgs.binding.id);
}

但是eventArgs没有有关数据的哪一部分被更改的信息.有什么办法，我们可以追踪这些信息?我们具有大量单元格的绑定，例如5000行* 15列或90行* 350列.我们正在使用Office js api 1.2.

But the eventArgs do not have information about what part of the data is changed. Is there any way, we can track this information? We have bindings with large number of cells like 5000 rows * 15 columns or 90 rows * 350 columns. We are using office js api 1.2.

更新1 使用了下面的Michael Saunders提供的代码.看到一些奇怪的行为.我选择了G9:H9并按Delete键.但是标题始终返回为Column1和Column2(请查看右上方的Toastr通知).我期待的是Column7和Column8.这是预期的行为吗? (测试是在具有表绑定的Excel 2016上完成的，而不是在Office 365上完成的.下一步将尝试).

Update 1 Used the code provided by Michael Saunders below. Seeing some strange behavior.I have selected G9:H9 and pressed delete. But the header is always returning as Column1 and Column2 (Look at the toastr notification on top right). I was expecting Column7 and Column8. Is this the expected behavior? (The testing was done on Excel 2016 with a table binding and not on office 365. Will try it next)

推荐答案

我认为上述跟踪selectionchanged的解决方案很聪明，但是我相信它会使您在事件触发之间处于竞争状态.我测试了上述解决方案，看来SelectionChange在DataChanged事件之前触发，这意味着您将获取当前选定的单元格，而不是前一个.我认为您不能避免这种竞争状况，因为事件是异步的，但是您可以像这样跟踪先前和当前的选择:

I think the above solution of tracking selectionchanged is clever but I believe it gets you in a race condition between events firing. I tested the above solution and it appears that the SelectionChange triggers before the DataChanged Event which means you would be grabbing the current selected cell rather than the previous. I do not think you can avoid this race condition because events are async but potentially you could track previous and current selection like this:

myBinding.addHandlerAsync(Office.EventType.BindingSelectionChanged, onSelectionChange);

var startRow, rowCount, startColumn, columnCount;
var previousStartRow, previousRowCount, previousStartColumn, previousColumnCount;



function onSelectionChange(eventArgs){
    // save "previous" selected cell into previous variables 
    previousStartRow = startRow;
    previousRowCount = rowCount;
    previousStartColumn = startColumn;
    previousColumnCount = columnCount;

    // re-assign the current selected to the eventArgs
    startRow = eventArgs.startRow;
    rowCount = eventArgs.rowCount;
    startColumn = eventArgs.startColumn;
    columnCount = eventArgs.columnCount;
}

function onBindingDataChange(eventArgs){
    eventArgs.binding.getDataAsync({
        startRow: previousStartRow,
        rowCount: previousRowCount,
        startCol: previousStartColumn,
        columnCount: previousColumnCount  
    }, function(result){
        // Do whatever you need with result.value.
        // You might want to compare and update your in-memory representation of the data. 
    });
}

我已经对其进行了测试，但效果不错……可能存在更好的跟踪多个级别的方法，但它似乎可以正常工作.理想情况下，DataChanged事件将在VSTO之类的EventArgs中具有此功能.手指交叉它即将来临！:)

I tested it and it works...not nice and potentially there is a better way to track multiple levels but it seems to work. Ideally the DataChanged Event would have this in the EventArgs like VSTO. Fingers crossed it's coming soon!:)

注意-似乎与复制&粘贴，但是需要增强此解决方案以处理以下情况:

Note - It does seem to work with copy & paste but this solution would need to be enhanced to handle the following situations:

• 带有"+"号的单元格拖动n'Drop.
• 选中多个单元格，然后使用键盘更新单个单元格并输入

更新1-其他情况

• 在更新绑定的边缘单元并单击Enter时，它将导致选择超出绑定范围.选择变更事件尚未开始，上述解决方案将失败

这篇关于如何在Office JS API BindingDataChanged事件中识别更改的单元格?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！