Adicionar métodos de referência a valores de células

Adicione métodos de referência a valores de células para dar aos utilizadores acesso a cálculos dinâmicos com base no valor da célula. Os EntityCellValue tipos e LinkedEntityCellValue suportam métodos de referência. Por exemplo, adicione um método a um valor de entidade de produto que converte o seu peso em unidades diferentes.

A imagem seguinte mostra um exemplo de como adicionar um ConvertWeight método a um valor de entidade de produto que representa a mistura de panquecas.

Os DoubleCellValuetipos , BooleanCellValuee StringCellValue também suportam métodos de referência. A imagem seguinte mostra um exemplo de adição de um ConvertToRomanNumeral método a um tipo de valor duplo.

Os métodos de referência não aparecem no tipo de dados card.

Adicionar um método de referência a um valor de entidade

Para adicionar um método de referência a um valor de entidade, defina-o em JSON com o Excel.JavaScriptCustomFunctionReferenceCellValue tipo . O seguinte exemplo de código mostra como definir um método simples que devolve o valor 27.

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

As propriedades estão descritas na tabela seguinte.

Propriedade	Descrição
type	Especifica o tipo de referência. Esta propriedade só suporta function e tem de ser definida como Excel.CellValueType.function.
functionType	Especifica o tipo de função. Esta propriedade só suporta funções de referência javaScript e tem de ser definida como Excel.FunctionCellValueType.javaScriptReference.
espaço de nomes	O espaço de nomes que contém a função personalizada. Este valor tem de corresponder ao espaço de nomes especificado pelo elemento customFunctions.namespace no manifesto unificado ou ao elemento Espaço de Nomes no manifesto apenas do suplemento.
id	O nome da função personalizada a mapear para este método de referência. O nome é a versão em maiúscula do nome da função personalizada.

Quando criar o valor da entidade, adicione o método de referência à lista de propriedades. O seguinte exemplo de código mostra como criar um valor de entidade simples com o nome Math e adicionar um método de referência ao mesmo. Get27 é o nome do método que aparece aos utilizadores (por exemplo: 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;
}

O seguinte exemplo de código mostra como criar uma instância da Math entidade e adicioná-la à célula selecionada.

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

Por fim, implemente o método de referência com uma função personalizada. O seguinte exemplo de código mostra como implementar a função personalizada.

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

No exemplo de código anterior, a @excludeFromAutoComplete etiqueta garante que a função personalizada não aparece na IU do Excel quando um utilizador a introduz numa caixa de pesquisa. No entanto, um utilizador ainda pode chamar a função personalizada separadamente de um valor de entidade se a introduzir diretamente numa célula.

Quando o código é executado, cria um Math valor de entidade, conforme mostrado na imagem seguinte. O método aparece na Conclusão Automática da fórmula quando o utilizador referencia o valor da entidade a partir de uma fórmula.

Adicionar argumentos

Se o método de referência precisar de argumentos, adicione-os à função personalizada. O exemplo de código seguinte mostra como adicionar um argumento com o nome x a um método com o nome addValue. O método adiciona um ao x valor ao chamar uma função personalizada chamada addValue.

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

Referenciar o valor da entidade como um objeto de chamada

Um cenário comum é que os seus métodos precisam de referenciar propriedades no próprio valor da entidade para efetuar cálculos. Por exemplo, é mais útil se o addValue método adicionar o valor do argumento ao próprio valor da entidade. Especifique que o valor da entidade é transmitido como o primeiro argumento ao aplicar a @capturesCallingObject etiqueta à função personalizada, conforme mostrado no exemplo de código seguinte.

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

Pode utilizar qualquer nome de argumento que esteja em conformidade com as regras de sintaxe do Excel em Nomes em fórmulas. Uma vez que se trata de uma entidade matemática, o argumento de objeto de chamada chama-se math. O nome do argumento pode ser utilizado no cálculo.

Tenha em atenção o seguinte sobre o exemplo de código anterior.

• A @excludeFromAutoComplete etiqueta garante que a função personalizada não aparece na IU do Excel quando um utilizador a introduz numa caixa de pesquisa. No entanto, um utilizador ainda pode chamar a função personalizada separadamente de um valor de entidade se a introduzir diretamente numa célula.
• O objeto de chamada é sempre transmitido como o primeiro argumento e tem de ser do tipo any. Neste caso, tem o nome math e é utilizado para obter a propriedade de valor do math objeto.
• Devolve uma matriz dupla de números.
• Quando o utilizador interage com o método de referência no Excel, não vê o objeto de chamada como um argumento.

Exemplo: Calcular o imposto sobre vendas de produtos

O código seguinte mostra como implementar uma função personalizada que calcula o imposto sobre vendas para o preço unitário de um produto.

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

O seguinte exemplo de código mostra como especificar o método de referência e inclui o id da applySalesTax função personalizada.

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

O código seguinte mostra como adicionar o método de referência ao valor da product entidade.

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;
}

Excluir funções personalizadas da IU do Excel

Utilize a @excludeFromAutoComplete etiqueta na etiqueta JSDoc das funções personalizadas utilizadas pelos métodos de referência para indicar que a função está excluída da Conclusão Automática de fórmulas e do Construtor de Fórmulas. Isto ajuda a impedir que os utilizadores utilizem acidentalmente uma função personalizada separadamente do respetivo valor de entidade.

Observação

Se a função for introduzida manualmente corretamente na grelha, a função continuará a ser executada.

Importante

Uma função não pode ter as tags @excludeFromAutoComplete e @linkedEntityLoadService ao mesmo tempo.

A @excludeFromAutoComplete etiqueta é processada durante a compilação para gerar um ficheiro functions.json pelo pacote Custom-Functions-Metadata . Este pacote é adicionado automaticamente ao processo de compilação se criar o seu suplemento com o gerador Yeoman para Suplementos do Office e escolher um modelo de funções personalizadas. Se não estiver a utilizar o pacote Custom-Functions-Metadata , terá de adicionar a excludeFromAutoComplete propriedade manualmente ao ficheiro functions.json .

O seguinte exemplo de código mostra como definir manualmente a APPLYSALESTAX função personalizada com JSON no ficheiro functions.json . A propriedade excludeFromAutoComplete está definida como 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"
    }
},

Para obter mais informações, veja Criar metadados JSON manualmente para funções personalizadas.

Adicionar uma função a um tipo de valor básico

Para adicionar funções aos tipos de valor básico de , doublee string, utilize o mesmo processo que faria para os valores de Booleanentidade. O seguinte exemplo de código mostra como criar um valor básico duplo com uma função personalizada chamada addValue. A função adiciona o valor x ao valor básico.

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

O seguinte exemplo de código mostra como definir a addValue função personalizada do exemplo anterior em JSON e, em seguida, referenciá-la com um método chamado 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();
  });
}

argumentos Optional

O seguinte exemplo de código mostra como criar um método de referência que aceita argumentos opcionais. O método de referência tem o nome generateRandomRange e gera um intervalo de valores aleatórios.

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;
}

O seguinte exemplo de código mostra a implementação do método de referência como uma função personalizada chamada generateRandomRange. Devolve uma matriz dinâmica de valores aleatórios que correspondem ao número de rows e columns especificado. Os min argumentos e max são opcionais e, se não forem especificados, serão predefinidos como 1 e 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;
}

Quando o utilizador introduz a função personalizada no Excel, a Conclusão Automática mostra as propriedades da função e indica argumentos opcionais ao rodeá-los entre parênteses retos ([]). A imagem seguinte mostra um exemplo de introdução de parâmetros opcionais com o generateRandomRange método de referência.

Vários parâmetros

Os métodos de referência suportam vários parâmetros, semelhantes à forma como a função do Excel SUM suporta vários parâmetros. O seguinte exemplo de código mostra como criar uma função de referência que concatena zero ou mais nomes de produtos transmitidos numa matriz de produtos. A função é apresentada ao utilizador como 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(" | "); 
}

O seguinte exemplo de código mostra como criar uma entidade com o concatProductNames método de referência.

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;
}

A imagem seguinte mostra um exemplo de introdução de vários parâmetros com o concatProductNames método de referência.

Vários parâmetros com intervalos

Para suportar a transmissão de intervalos para o método de referência, como B1:B3, utilize uma matriz multidimensional. O seguinte exemplo de código mostra como criar uma função de referência que soma zero ou mais parâmetros que podem incluir intervalos.

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

O seguinte exemplo de código mostra como criar uma entidade com o sumAll método de referência.

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;
}

A imagem seguinte mostra um exemplo de introdução de vários parâmetros, incluindo um parâmetro de intervalo, através do sumAll método de referência.

Detalhes do suporte

Os métodos de referência são suportados em todos os tipos de funções personalizadas, como funções voláteis e de transmissão em fluxo . Todos os tipos de retorno de funções personalizadas (matriz, escalar e erro) são suportados.

Importante

Uma entidade ligada não pode ter uma função personalizada que combine um método de referência e um fornecedor de dados. Quando desenvolver entidades ligadas, mantenha estes tipos de funções personalizadas separadas.

Confira também

• Criar funções personalizadas no Excel
• Gerar metadados JSON automaticamente para funções personalizadas
• Criar manualmente metadados JSON para funções personalizadas