Office JS:在多个Excel实例中执行外接程序时出现问题 [英] Office JS: Problems when Addin is executed in multiple Excel instances

问题描述

我在多个Excel实例中执行Office加载项时遇到问题.当两个都同时执行时，一个就会停止运行.

I have problems executing an office addin in multiple Excel instances. One stops running when both are executed hat the same time.

我做了两个快速的ScriptLab示例，您可以在其中重现一些问题(我将它们粘贴了).其中包含一个UDF函数，只需在ScriptLab中注册即可.另一个是一个样本，它产生了我的问题之一.

I did 2 quick ScriptLab samples, where you can reproduce some issues (I pasted them). One contains an UDF-Function, just register it in ScriptLab. The other on is a sample which produces one of my problems.

首先注册UDF，然后在使用第二部分之前创建2个工作簿，每个工作簿包含100个工作表，这些工作表包含以下功能(取决于代码段名称，在我的情况下为空白代码段(1)")，如果您名称不同，请在此处以及"findAllOrNullObject"函数的插件代码中调整公式名称.

First register the UDF, than before using the second part, create 2 workbooks with each having 100 worksheets that contain the following function (depending on the Snippent name, which is in my case "Blank snippet (1)", if your name is different, please adjust the formula name here and also in the addin-code in the function "findAllOrNullObject").

= SCRIPTLAB.BLANKSNIPPET1.ADD(1; 2)

=SCRIPTLAB.BLANKSNIPPET1.ADD(1;2)

最快的方法是: 使用该功能创建十个工作表，并将这十个工作表复制十次到工作簿的末尾.完成此操作后，用另一个名称再次保存工作簿.然后，打开两个工作簿并单击运行"(在两个表中).而不是在运行或打开一个应用程序时都单击它.在控制台上，您将看到一个计数器，该计数器指示Addin实际在哪张纸上工作.您应该在两种情况下都期望"INDEX:100"，但是当您单击另一个应用程序或启动一个应用程序但不会达到100时，一个实例将停止.如果您没有直接遇到问题，请重试，它肯定会出现.

The quickest way to do this is: Create ten sheets with that function and copy this ten worksheets ten times to the end of the workbook. After this is done, save the workbook a second time with a different name. Afterwards, open both workbooks and click "Run" (in both sheets). Than click into another application while both are running or open one. On the console you will see a counter that indicates, on which sheet the addin is actually working. You should expect "INDEX: 100" in both instances but one instance will stop, when you click into another application or start one and will not reach 100. If you will not have the problem directly, just try again, it will sure appear.

UDF的代码:

/**
 * Adds two numbers.
 * @customfunction
 * @param first First number
 * @param second Second number
 * @returns The sum of the two numbers.
 */
/* global clearInterval, console, setInterval */

function add(first: number, second: number): number {
  return first + second;
}

插件代码:

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

async function run() {
  this.refreshWorkbook();
}

async function refreshWorkbook() {
  let sheets: Excel.WorksheetCollection;

  Excel.run(async (context) => {
    sheets = context.workbook.worksheets;
    sheets.load("items/name");
    await sheets.context.sync();
    if (sheets.items.length >= 1) {
      for (let sheetIndex = 0; sheetIndex < sheets.items.length; sheetIndex++) {
        console.log("INDEX : " + sheetIndex);
        const sheet = sheets.items[sheetIndex];
        await this.getInfo(sheet.name).then((information) => {
          // some stuff
        });
      }
    }
  });
}

async function getInfo(worksheetName: string): Promise<string> {
  return new Promise<string>((resolve, reject) => {
    Excel.run(async (context) => {
      const sheet: Excel.Worksheet = context.workbook.worksheets.getItem(worksheetName);
      sheet.load("name");
      await context.sync();
      const usedRange = sheet.getUsedRangeOrNullObject();
      if (usedRange) {
        const functionCells = sheet.findAllOrNullObject("=SCRIPTLAB.SCRIPTLAB.BLANKSNIPPET1.ADD(", {
          matchCase: false,
          completeMatch: false
        });
        functionCells.load("areaCount");
        await context.sync();
        if (functionCells) {
          resolve("A");
        } else {
          reject("X");
        }
      }
    });
  });
}

/** Default helper for invoking an action and handling errors. */
async function tryCatch(callback) {
  try {
    await callback();
  } catch (error) {
    // Note: In a production add-in, you'd want to notify the user through your add-in's UI.
    console.error(error);
  }
}

如果我删除搜索部分，它将起作用.

If I delete the search part it works.

推荐答案

我们可以复制此问题，也可以在没有CF的情况下复制.所以这是更多与API相关的问题.创建了一个错误4124929来跟踪此问题.更可能是findAllOrNullObject API中的问题，我们正在对此问题进行一些调查.

We can repro this issue, it can be repro without CF. so this is more API related issues. a bug 4124929 was created to track this issue. more likely an issue in findAllOrNullObject API, we are doing some investigation on this issue.

这篇关于Office JS:在多个Excel实例中执行外接程序时出现问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！