向单元格值添加引用方法

将引用方法添加到单元格值，使用户能够基于单元格值访问动态计算。 EntityCellValue和 LinkedEntityCellValue 类型支持引用方法。 例如，向将权重转换为不同单位的产品实体值添加方法。

下图显示了向表示煎饼混合的产品实体值添加 ConvertWeight 方法的示例。

DoubleCellValue、 BooleanCellValue和 StringCellValue 类型还支持引用方法。 下图显示了向双精度值类型添加 ConvertToRomanNumeral 方法的示例。

引用方法不会出现在数据类型卡上。

向实体值添加引用方法

若要向实体值添加引用方法，请使用 Excel.JavaScriptCustomFunctionReferenceCellValue 类型在 JSON 中定义它。 以下代码示例演示如何定义返回值 27 的简单方法。

const referenceCustomFunctionGet27: Excel.JavaScriptCustomFunctionReferenceCellValue = { 
  type: Excel.CellValueType.function,
  functionType: Excel.FunctionCellValueType.javaScriptReference,
  namespace: "CONTOSO", 
  id: "GET27" 
} 

下表介绍了这些属性。

创建实体值时，请将 reference 方法添加到属性列表。 以下代码示例演示如何创建名为 Math 的简单实体值，并向其添加引用方法。 Get27 是向用户显示的方法名称， (例如： A1.Get27()) 。

function makeMathEntity(value: number){
  const entity: Excel.EntityCellValue = {
    type: Excel.CellValueType.entity,
    text: "Math value",
    properties: {
      "value": {
        type: Excel.CellValueType.double,
        basicValue: value,
        numberFormat: "#"
      },
      Get27: referenceCustomFunctionGet27
    }
  };
  return entity;
}

下面的代码示例演示如何创建实体的 Math 实例并将其添加到所选单元格。

// Add entity to selected cell.
async function addEntityToCell(){
  const entity: Excel.EntityCellValue = makeMathEntity(10);
  await Excel.run( async (context) => {
    const cell = context.workbook.getActiveCell();
    cell.valuesAsJson = [[entity]];
    await context.sync();
  });
}

最后，使用自定义函数实现 reference 方法。 以下代码示例演示如何实现自定义函数。

/**
 * Returns the value 27.
 * @customfunction
 * @excludeFromAutoComplete
 * @returns {number} 27
 */
function get27() {
  return 27;
}

在前面的代码示例中 @excludeFromAutoComplete ，标记可确保当用户在搜索框中输入自定义函数时，该函数不会显示在 Excel UI 中。 但是，如果用户直接在单元格中输入自定义函数，则仍可以独立于实体值调用该函数。

当代码运行时，它会创建一个 Math 实体值，如下图所示。 当用户从公式中引用实体值时，方法将显示在公式“自动完成”中。

添加参数

如果引用方法需要参数，请将它们添加到自定义函数。 下面的代码示例演示如何将名为 的参数添加到名为 xaddValue的方法。 方法通过调用名为 的addValue自定义函数将x一个添加到值。

/**
 * Adds a value to 1.
 * @customfunction
 * @excludeFromAutoComplete
 * @param {number} x The value to add to 1.
 * @return {number[][]}  Sum of x and 1.
 */
function addValue(x): number[][] {  
  return [[x+1]];
}

引用实体值作为调用对象

一种常见方案是，方法需要引用实体值本身的属性来执行计算。 例如，如果 addValue 方法将参数值添加到实体值本身，则它更有用。 通过将 标记应用于 @capturesCallingObject 自定义函数，指定实体值作为第一个参数传递，如以下代码示例所示。

/**
 * Adds x to the calling object.
 * @customfunction
 * @excludeFromAutoComplete
 * @capturesCallingObject
 * @param {any} math The math object (calling object).
 * @param {number} x The value to add.
 * @return {number[][]}  Sum.
 */
function addValue(math, x): number[][] {  
  const result: number = math.properties["value"].basicValue + x;
  return [[result]];
}

可以使用符合 公式中“名称”中的 Excel 语法规则的任何参数名称。 由于这是一个数学实体，因此调用对象参数名为 math。 参数名称可用于计算。 在前面的代码示例中，它将检索 math.[value] 属性以执行计算。

以下代码示例演示函数的 Contoso.addValue 实现。

/**
 * Adds x to the calling object.
 * @customfunction
 * @excludeFromAutoComplete
 * @param {any} math The math object (calling object).
 * @param {number} x The value to add.
 * @return {number[][]}  Sum.
 */
function addValue(math, x): number[][] {  
  const result: number = math.properties["value"].basicValue + x;
  return [[result]];
}

请注意以下有关上一个代码示例。

• 标记 @excludeFromAutoComplete 可确保当用户在搜索框中输入自定义函数时，该函数不会显示在 Excel UI 中。 但是，如果用户直接在单元格中输入自定义函数，则仍可以独立于实体值调用该函数。
• 调用对象始终作为第一个参数传递，并且类型必须为 any。 在本例中，它命名 math 为 ，用于从 math 对象获取值属性。
• 它返回数字的双精度数组。
• 当用户与 Excel 中的引用方法交互时，他们不会将调用对象视为参数。

示例：计算产品销售税

以下代码演示如何实现计算产品单价销售税的自定义函数。

/**
 * Calculates the price when a sales tax rate is applied.
 * @customfunction
 * @excludeFromAutoComplete
 * @capturesCallingObject
 * @param {any} product The product entity value (calling object).
 * @param {number} taxRate The tax rate (0.11 = 11%).
 * @return {number[][]}  Product unit price with tax rate applied.
 */
function applySalesTax(product, taxRate): number[][] {
  const unitPrice: number = product.properties["Unit Price"].basicValue;
  const result: number = unitPrice * taxRate + unitPrice;
  return [[result]];
}

下面的代码示例演示如何指定引用方法，并包括 id 自定义函数的 applySalesTax 。

const referenceCustomFunctionCalculateSalesTax: Excel.JavaScriptCustomFunctionReferenceCellValue = { 
  type: Excel.CellValueType.function,
  functionType: Excel.FunctionCellValueType.javaScriptReference,
  namespace: "CONTOSO", 
  id: "APPLYSALESTAX" 
} 

以下代码演示如何将引用方法添加到 product 实体值。

function makeProductEntity(productID: number, productName: string, price: number) {
  const entity: Excel.EntityCellValue = {
    type: Excel.CellValueType.entity,
    text: productName,
    properties: {
      "Product ID": {
        type: Excel.CellValueType.string,
        basicValue: productID.toString() || ""
      },
      "Product Name": {
        type: Excel.CellValueType.string,
        basicValue: productName || ""
      },
      "Unit Price": {
        type: Excel.CellValueType.formattedNumber,
        basicValue: price,
        numberFormat: "$* #,##0.00"
      },
      applySalesTax: referenceCustomFunctionCalculateSalesTax
    },
  };
  return entity;
}

从 Excel UI 中排除自定义函数

@excludeFromAutoComplete在引用方法使用的自定义函数的 JSDoc 标记中使用 标记，以指示从公式 AutoComplete 和 Formula Builder 中排除该函数。 这有助于防止用户意外地将自定义函数与其实体值分开使用。

标记@excludeFromAutoComplete在生成过程中进行处理，以便通过 Custom-Functions-Metadata 包生成functions.json文件。 如果使用 Office 外接程序的 Yeoman 生成器创建加载项并选择自定义函数模板，则此包会自动添加到生成过程。 如果不使用 Custom-Functions-Metadata 包，则需要将 属性手动添加到 excludeFromAutoCompletefunctions.json 文件。

以下代码示例演示如何在 functions.json 文件中使用 JSON 手动定义APPLYSALESTAX自定义函数。 excludeFromAutoComplete 属性设置为 true。

{
    "description": "Calculates the price when a sales tax rate is applied.",
    "id": "APPLYSALESTAX",
    "name": "APPLYSALESTAX",
    "options": {
        "excludeFromAutoComplete": true,
        "capturesCallingObject": true
    },
    "parameters": [
        {
            "description": "The product entity value (calling object).",
            "name": "product",
            "type": "any"
        },
        {
            "description": "The tax rate (0.11 = 11%).",
            "name": "taxRate",
            "type": "number"
        }
    ],
    "result": {
        "dimensionality": "matrix",
        "type": "number"
    }
},

有关详细信息，请参阅 手动为自定义函数创建 JSON 元数据。

将函数添加到基本值类型

若要将函数添加到 、 double和 string的基本值类型Boolean，请使用与实体值相同的过程。 下面的代码示例演示如何使用名为 addValue的自定义函数创建双重基本值。 函数将值 x 添加到基本值。

/**
 * Adds the value x to the number value.
 * @customfunction
 * @capturesCallingObject
 * @param {any} numberValue The number value (calling object).
 * @param {number} x The value to add.
 * @return {number[][]}  Sum of the number value and x.
 */
export function addValue(numberValue: any, x: number): number[][] {
  return [[x+numberValue.basicValue]];
}

下面的代码示例演示如何在 JSON 中定义 addValue 上述示例中的自定义函数，然后使用名为 createSimpleNumber的方法引用它。

const referenceCustomFunctionAddValue: Excel.JavaScriptCustomFunctionReferenceCellValue = { 
  type: Excel.CellValueType.function,
  functionType: Excel.FunctionCellValueType.javaScriptReference,
  namespace: "CONTOSO", 
  id: "ADDVALUE" 
} 

async function createSimpleNumber() {
  await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getActiveWorksheet();
    const range = sheet.getRange("A1");
    range.valuesAsJson = [
      [
        {
          type: Excel.CellValueType.double,
          basicType: Excel.RangeValueType.double,
          basicValue: 6.0,
          properties: {
            addValue: referenceCustomFunctionAddValue
          }
        }
      ]
    ];
    await context.sync();
  });
}

Optional 参数

以下代码示例演示如何创建接受可选参数的引用方法。 引用方法命名 generateRandomRange 为 ，它生成一系列随机值。

const referenceCustomFunctionOptional: Excel.JavaScriptCustomFunctionReferenceCellValue = { 
  type: Excel.CellValueType.function,
  functionType: Excel.FunctionCellValueType.javaScriptReference,
  namespace: "CONTOSO", 
  id: "GENERATERANDOMRANGE" 
}

function makeProductEntity(productID: number, productName: string, price: number) {
  const entity: Excel.EntityCellValue = {
    type: Excel.CellValueType.entity,
    text: productName,
    properties: {
      "Product ID": {...},
      "Product Name": {...},
      "Unit Price": {...},
      generateRandomRange: referenceCustomFunctionOptional
    },
  };
  return entity;
}

下面的代码示例将 reference 方法的实现显示为名为 的 generateRandomRange自定义函数。 它返回与 和 指定的数目rowscolumns匹配的随机值的动态数组。 min和 max 参数是可选的，如果未指定，则默认为 1 和 10。

/**
 * Generates a dynamic array of random numbers.
 * @customfunction
 * @excludeFromAutoComplete
 * @param {number} rows Number of rows to generate.
 * @param {number} columns Number of columns to generate.
 * @param {number} [min] Lowest number that can be generated. Default is 1.
 * @param {number} [max] Highest number that can be generated. Default is 10.
 * @returns {number[][]} A dynamic array of random numbers.
 */
function generateRandomRange(rows, columns, min, max) {
  // Set defaults for any missing optional arguments.
  if (min === undefined) min = 1;
  if (max === undefined) max = 10;

  let numbers = new Array(rows);
  for (let r = 0; r < rows; r++) {
    numbers[r] = new Array(columns);
    for (let c = 0; c < columns; c++) {
      numbers[r][c] = Math.round(Math.random() * (max - min) ) + min;
    }
  }
  return numbers;
}

当用户在 Excel 中输入自定义函数时，AutoComplete 会显示函数的属性，并通过将可选参数括在括号 ([]) 来指示可选参数。 下图显示了使用 generateRandomRange reference 方法输入可选参数的示例。

多个参数

引用方法支持多个参数，类似于 Excel SUM 函数支持多个参数的方式。 下面的代码示例演示如何创建一个引用函数，该函数连接在 product 数组中传递的零个或多个产品名称。 函数以 的形式 concatProductNames([products], ...)向用户显示。

/** 
 * @customfunction 
 * @excludeFromAutoComplete 
 * @description Concatenate the names of given products, joined by " | " 
 * @param {any[]} products - The products to concatenate.
 * @returns A string of concatenated product names. 
 */ 
function concatProductNames(products: any[]): string { 
  return products.map((product) => product.properties["Product Name"].basicValue).join(" | "); 
}

下面的代码示例演示如何使用 concatProductNames reference 方法创建实体。

const referenceCustomFunctionMultiple: Excel.JavaScriptCustomFunctionReferenceCellValue = { 
  type: Excel.CellValueType.function,
  functionType: Excel.FunctionCellValueType.javaScriptReference,
  namespace: "CONTOSO", 
  id: "CONCATPRODUCTNAMES" 
} 

function makeProductEntity(productID: number, productName: string, price: number) {
  const entity: Excel.EntityCellValue = {
    type: Excel.CellValueType.entity,
    text: productName,
    properties: {
      "Product ID": {...},
      "Product Name": {...},
      "Unit Price": {...},
      concatProductNames: referenceCustomFunctionMultiple,
    },
  };
  return entity;
}

下图显示了使用 concatProductNames reference 方法输入多个参数的示例。

具有范围的多个参数

若要支持将范围传递给引用方法（如 B1：B3），请使用多维数组。 下面的代码示例演示如何创建一个引用函数，该函数对可以包含范围的零个或多个参数求和。

/** 
 * @customfunction 
 * @excludeFromAutoComplete 
 * @description Calculate the sum of arbitrary parameters. 
 * @param {number[][][]} operands - The operands to sum. 
 * @returns The sum of all operands. 
 */ 
function sumAll(operands: number[][][]): number { 
  let total: number = 0; 
 
  operands.forEach(range => { 
    range.forEach(row => { 
      row.forEach(num => { 
        total += num; 
      }); 
    }); 
  }); 
 
  return total; 
} 

下面的代码示例演示如何使用 sumAll reference 方法创建实体。

const referenceCustomFunctionRange: Excel.JavaScriptCustomFunctionReferenceCellValue = { 
  type: Excel.CellValueType.function,
  functionType: Excel.FunctionCellValueType.javaScriptReference,
  namespace: "CONTOSO", 
  id: "SUMALL" 
} 

function makeProductEntity(productID: number, productName: string, price: number) {
  const entity: Excel.EntityCellValue = {
    type: Excel.CellValueType.entity,
    text: productName,
    properties: {
      "Product ID": {...},
      "Product Name": {...},
      "Unit Price": {...},
      sumAll: referenceCustomFunctionRange
    },
  };
  return entity;
}

下图显示了使用 reference 方法输入多个参数（包括范围参数） sumAll 的示例。

支持详细信息

所有自定义函数类型（如 易失函数 和 流式处理 函数）都支持引用方法。 支持所有自定义函数返回类型（矩阵、标量和错误）。

另请参阅

• 在 Excel 中创建自定义函数
• 为自定义函数自动生成 JSON 元数据
• 手动为自定义函数创建 JSON 元数据