按命名范围和JavaScript API计算公式 [英] Evaluate a formula by named ranges and JavaScript API

问题描述

我正在寻找一种方法来evaluate a formula by JavaScript API。我在想，命名区域是否能有所帮助。

我在脚本实验室制作了this gist，部分内容如下。在此示例中，我们看到=100+200、=A2、=name2+100等公式确实可以通过命名范围进行求值；求值可以通过属性value进行访问。但是，无法很好地评估=Sample!A2+100；这里似乎认为A2的值为0。

有人知道如何修复代码以使=Sample!A2+100这样的公式正确求值吗？

$("#test").click(() => tryCatch(test));

async function test() {
  await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getActiveWorksheet();

    // setup cell values
    sheet.getRange("A2").values = [[3000]];
    await context.sync();

    // clean names:
    const name1 = sheet.names.getItemOrNullObject("name1");
    const name2 = sheet.names.getItemOrNullObject("name2");
    const name3 = sheet.names.getItemOrNullObject("name3");
    const name4 = sheet.names.getItemOrNullObject("name4");
    name1.load();
    name2.load();
    name3.load();
    name4.load();
    await context.sync();
    if (name1.value) name1.delete();
    if (name2.value) name2.delete();
    if (name3.value) name3.delete();
    if (name4.value) name4.delete();
    await context.sync();

    // add names:
    sheet.names.add("name1", "=100+200"); // "value" evaluates well to 300
    sheet.names.add("name2", "=A2"); // "value" evaluates to "Sample!$A$2"
    sheet.names.add("name3", "=name2+100"); // "value" evaluates well to 3100
    sheet.names.add("name4", "=Sample!A2+100"); // "value" evaluates to 100, which is wrong. A2 is considered here as 0.
    await context.sync();

    // show names:
    const namedItems = context.workbook.worksheets.getActiveWorksheet().names.load();
    await context.sync();
    console.log("This worksheet contains " + namedItems.items.length + " named items.");
    for (let i = 0; i < namedItems.items.length; i++) {
      console.log(JSON.stringify(namedItems.items[i])) + "
";
    }
    await context.sync();
  });
}

结果：

推荐答案

如果您更新这两行：

sheet.names.add("name2", "=A2"); // "value" evaluates to "Sample!$A$2"
sheet.names.add("name4", "=Sample!A2+100"); // "value" evaluates to 100, which is wrong. A2 is considered here as 0.

至此：

sheet.names.add("name2", "=$A$2"); // "value" evaluates to "Sample!$A$2"
sheet.names.add("name4", "=Sample!$A$2+100"); // "value" evaluates to 100, which is wrong. A2 is considered here as 0.

我显示该问题已修复。

这篇关于按命名范围和JavaScript API计算公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！