时间戳

首页 > 解决方案 > Excel JS API：使用自定义函数从单元格中写入、计算和加载值

javascript - 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在ctx.sync(). 使用自定义函数（向外部服务器发送请求以计算结果）的值为'#GETTING_DATA'. 我之前尝试过以下所有方法rng.load("values");来触发计算，但到目前为止没有任何效果：

有没有办法触发自定义函数的计算并确保值在ctx.sync()?

标签： javascriptoffice365office-jsexcel-addinscustom-functions-excel

解决方案

有趣的场景！

今天，利用 onCalculate 事件这可能是可行的，但需要注意的是，当您正在计算自定义函数时，它会触发 2 次。

  • 这是因为您的自定义函数首先会显示 #GETTING_DATA，而它会在后台进行计算。
    • 这让用户在您的函数仍在评估时获得控制权，从而使应用程序更具响应性。此行为不同于可能挂起 Excel 的 VBA 或 XLL UDF。
  • 当 Excel 完成计算后，它将再次触发计算事件。这是通过解决承诺返回结果的时候。

这个Script lab gist应该告诉你它是如何工作的：

/*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);
  }
}

推荐阅读