Excel JS API: запись, вычисление и загрузка значений из ячеек с помощью настраиваемых функций

Намерение состоит в том, чтобы записать формулу (пользовательскую функцию) в ячейку, вычислить ее, загрузить значения и получить их в одной функции.

function myFunc() {
    Excel.run(function (ctx) {
        var fExcel = '=SUM(1,2)';
        var fCustom = '=custFunc()';

        var rng = ctx.workbook.worksheets.getActiveWorksheet().getRange('A1');

        //rng.formulas = [[fExcel]]; // works OK          
        rng.formulas = [[fCustom]]; // values are #GETTING_DATA

        // try different calc calls

        rng.load("values");

        return ctx.sync().then(function () {
            console.log(rng.values);
        });
    });
}

Для встроенных функций Excel все работает должным образом, и console записывает значение 3 после ctx.sync(). С пользовательскими функциями (которые отправляют запрос на внешний сервер для вычисления результата) значения равны '#GETTING_DATA'. Я пробовал все следующие вещи до rng.load("values");, чтобы запустить вычисление, но пока ничего не помогло:

  • rng.calculate();

  • var s = ctx.workbook.worksheets.getActiveWorksheet(); s.calculate(true);

  • ctx.workbook.application.calculate('Full');

Есть ли способ запустить вычисление пользовательских функций и убедиться, что значения доступны после ctx.sync()?


javascript office365 office-js excel-addins custom-functions-excel
person Oleg Shirokikh    schedule 17.01.2019    source источник

Ответы (1)


arrow_upward
1
arrow_downward

Интересный сценарий!

Сегодня это может быть выполнимо с использованием события onCalculate, но предостережение в том, что оно будет срабатывать вдвое, когда ваша настраиваемая функция вычисляет.

  • This is because your custom function first will show a #GETTING_DATA, while it calculates in the background.
    • This gives the user back control while your functions are still evaluating, allowing the application to be more responsive. This behavior differs from VBA or XLL UDFs that could hang Excel.
  • Когда Excel завершит вычисление, он снова запустит событие вычисления. Это когда результаты возвращаются после выполнения обещания.

Эта Суть лабораторной работы по скриптам должна дать вам представление о том, как это работает:

/*This gist works in combination with any registered Excel JS Custom function*/

$("#set-formulas").click(() => tryCatch(setFormulas));

var rangeToCheck;

async function setFormulas() {
  await Excel.run(async (context) => {
    //register for event
    context.workbook.worksheets.getActiveWorksheet().onCalculated.add(handleCalculate);

    //write to grid
    const sheet = context.workbook.worksheets.getItem("Sheet1");

    rangeToCheck = "A1";
    const range = sheet.getRange(rangeToCheck);
    range.formulas = [['=CONTOSO.CONTAINS(A1, Days)']];
    range.format.autofitColumns();

    await context.sync();
  });
}

async function handleCalculate(event) {
  //read cell
  console.log("calc ended - begin");

  console.log("Change type of event: " + event.changeType);
  console.log("Address of event: " + event.address);
  console.log("Source of event: " + event.source);

  //Read A1 and log it back to the console
  await Excel.run(async (context) => {
    //write to grid
    const sheet = context.workbook.worksheets.getItem("Sheet1");

    const range = sheet.getRange(rangeToCheck);
    range.load("values");

    await context.sync();

    if (range.values.toString() != "GETTING_DATA") {
      console.log("Success: " + range.values);
    }
  });
}

/** Default helper for invoking an action and handling errors. */
async function tryCatch(callback) {
  try {
    await callback();
  } catch (error) {
    OfficeHelpers.UI.notify(error);
    OfficeHelpers.Utilities.log(error);
  }
}
person Keyur Patel - MSFT    schedule 12.02.2019