Excel Add-in动态注册自定义函数：无法向多单元格写入string[][]类型数据

我使用Office JavaScript API开发Excel加载项时遇到问题：运行时动态添加的自定义函数无法向多个单元格写入数据，Excel似乎无法识别string[][]类型。返回单个值时完全正常，但返回数组就会返回#VALUE!。

动态注册的问题代码 #

const section = "MakeTable";
const description = "Make a table";
const excelParams = [];

const configFunctionProperties = [
  {
    id: section,
    name: section,
    description: description,
    parameters: excelParams,
    result: {
      type: "string[][]", // 改为string时单个单元格正常
    },
  },
];

const functionString = "async () => {
  return [['first', 'second', 'third']]; // 返回单个字符串时正常
}";

Excel.run(async (context) => {
  await (Excel as any).CustomFunctionManager.register(
    JSON.stringify({
      functions: configFunctionProperties,
    }),
    ""
  );

  CustomFunctions.associate(section, eval(functionString));

  await context.sync();
  console.log("Custom function registered successfully!");
}).catch((error) => {
  console.error("Error registering custom function:", error);
});

正常工作的非运行时注册代码 #

/**
 * Get text values that spill to the right.
 * @customfunction
 * @returns {string[][]} A dynamic array with multiple results.
 */
function spillRight() {
  let returnVal = [["first", "second", "third"]];
  console.log(typeof returnVal);
  return returnVal;
}

问题根源 #

静态注册时@customfunction注解会自动处理数组溢出逻辑，但动态注册必须显式配置溢出行为和数组维度，否则Excel无法正确识别返回的二维数组，从而抛出#VALUE!错误。

修复步骤 #

1. 添加数组维度声明：在result对象中加入dimension: "matrix"，明确告知Excel返回的是二维矩阵。
2. 启用动态数组溢出：在函数配置的options中设置allowDynamicArray: true，开启多单元格溢出支持。

修复后的完整代码 #

const section = "MakeTable";
const description = "Make a table";
const excelParams = [];

const configFunctionProperties = [
  {
    id: section,
    name: section,
    description: description,
    parameters: excelParams,
    result: {
      type: "string[][]",
      dimension: "matrix", // 声明为二维矩阵
    },
    options: {
      allowDynamicArray: true, // 关键：启用动态数组溢出
    },
  },
];

const functionString = "async () => {
  return [['first', 'second', 'third']];
}";

Excel.run(async (context) => {
  await (Excel as any).CustomFunctionManager.register(
    JSON.stringify({
      functions: configFunctionProperties,
    }),
    ""
  );

  CustomFunctions.associate(section, eval(functionString));

  await context.sync();
  console.log("Custom function registered successfully!");
}).catch((error) => {
  console.error("Error registering custom function:", error);
});

补充说明 #

• dimension属性的可选值："scalar"（单个值）、"vector"（一维数组）、"matrix"（二维数组），需与返回值类型匹配。
• 异步函数返回数组时，确保没有未处理的异步延迟，所有异步操作需通过await完成后再返回结果。
• 动态注册自定义函数时，所有与数组、溢出相关的行为都需要显式配置，无法像静态注册那样自动推断。

内容的提问来源于stack exchange，提问作者dakapitan