1

I want to execute some code after Excel finished a calculate full which I triggered myself. So currently I'm calling the calculate full this way.

context.workbook.application.calculate('Full');

But it seems this just triggers Excel to calculate and is finished far before the actual calculation. Trying to await an afterwards called context.sync() doesn't change the outcome.

Instead of a promise is there an event indicating the finished calculation?

Developer
  • 186
  • 3
  • 19

1 Answers1

0

Yes, we have added the address information to onCalculated which is available for preview now, this event update will be GA with requirement set 1.11

Excel.run(function (context) {
    var sheet = context.workbook.worksheets.getItem("Sample");
    sheet.onCalculated.add(function (event) {
        return Excel.run(function (context) {
            console.log("The worksheet has recalculated.");
            return context.sync();
        });
    });
    return context.sync();
});

The document can be found at https://learn.microsoft.com/zh-cn/javascript/api/excel/excel.worksheet?view=excel-js-preview#oncalculated

The code sample can be found at https://github.com/OfficeDev/office-js-snippets/blob/master/samples/excel/85-preview-apis/workbook-calculation.yaml

Note: this API update is now available for the insider for Excel Mac and Excel Desktop, it's rolling out to Excel Online, will be ready in the next 2 weeks.

Raymond Lu
  • 2,178
  • 1
  • 6
  • 19
  • That's great news, but I have a question regarding the event. Currently I'm only interested to know when the full calculation I started for the all open workbooks is finished. Since the event is worksheet based, does it fire after the completion of the entire calculation, or already after the sheet is finished, while others maybe still are being calculated? Does it fire for worksheets without formulas? So basically can I just add a handler for one worksheet or do I have to handle all the worksheets and even figure out whether there are formulas on them? – Developer Mar 23 '20 at 08:15
  • There is WorksheetCollection object, you could listen to worksheetCollection.onCalculated Event, which you can listen all worksheets in this event. – Raymond Lu Mar 25 '20 at 04:30
  • Hi Raymond, the documentation says this is called when a worksheet or collection IS calculated and a quick test on Excel Online seems to confirm this.The event fires way before all the (especially nested) custom functions are resolved, so lots of #BUSY cells when the event is called. I would like to know when Excel finished the calculation. – Developer Mar 30 '20 at 07:38
  • There actually is an open GitHub issue for this behavior and it seems only to exist in ExcelOnline: https://github.com/OfficeDev/office-js/issues/587 – Developer Mar 31 '20 at 05:54
  • Yes, onCalculated event is available req set1.8, we are adding address information to this API. here is the document https://learn.microsoft.com/en-us/javascript/api/excel/excel.worksheetcalculatedeventargs?view=excel-js-preview – Raymond Lu Apr 13 '20 at 08:12
  • Yes, since there is the issue regarding Excel online I cannot use this event at all currently to handle my problem, I had to manually check whether a there are pending function calls and since nested functions trigger additional function calls when inner functions were resolved, I have to work with a timeout to give excel time to do so after the first functions are resolved. So that seems not as reliable as I would wish it would be. By the way your edits to the answer changed it quite much. When I had a second look at the answer I was a bit confused until I watched the history ;-) – Developer Apr 17 '20 at 10:23
  • Yes, onCalculated address information for excel online is now 50% rolled out. it may take a few weeks to 100% available for Excel online – Raymond Lu Apr 17 '20 at 10:39
  • But according to the mentioned GitHub issue (https://github.com/OfficeDev/office-js/issues/587) I cannot use this event, because it fires to early in Office online. Or does the update fix this as well? Because the GitHub issue stayed unchanged for a while now and even contains workaround suggestions – Developer Apr 17 '20 at 11:17