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

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

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

Os DoubleCellValuetipos , BooleanCellValuee StringCellValue também dão suporte a métodos de referência. A imagem a seguir mostra um exemplo de como adicionar um ConvertToRomanNumeral método a um tipo de valor duplo.

Os métodos de referência não são mostrados 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 usando o Excel.JavaScriptCustomFunctionReferenceCellValue tipo. O exemplo de código a seguir mostra como definir um método simples que retorna o valor 27.

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

As propriedades são descritas na tabela a seguir.

Ao criar o valor da entidade, adicione o método de referência à lista de propriedades. O exemplo de código a seguir mostra como criar um valor de entidade simples chamado Math e adicionar um método de referência a ele. Get27 é o nome do método que aparece para os usuários (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 exemplo de código a seguir 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 exemplo de código a seguir mostra como implementar a função personalizada.

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

Na amostra de código anterior, a @excludeFromAutoComplete marca garante que a função personalizada não apareça na interface do usuário do Excel quando um usuário a insere em uma caixa de pesquisa. No entanto, um usuário ainda poderá chamar a função personalizada separadamente de um valor de entidade se a inserir diretamente em uma célula.

Quando o código é executado, ele cria um valor de Math entidade, conforme mostrado na imagem a seguir. O método aparece no Preenchimento Automático da fórmula quando o usuário faz referência ao valor da entidade em 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 a seguir mostra como adicionar um argumento nomeado x a um método chamado addValue. O método adiciona um ao x valor chamando 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]];
}

Faça referência ao valor da entidade como um objeto de chamada

Um cenário comum é que seus métodos precisam fazer referência a propriedades no próprio valor da entidade para executar 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 é passado como o primeiro argumento aplicando a @capturesCallingObject marca à função personalizada, conforme mostrado no exemplo de código a seguir.

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

Você pode usar qualquer nome de argumento que esteja em conformidade com as regras de sintaxe do Excel em Nomes em fórmulas. Como essa é uma entidade matemática, o argumento do objeto de chamada é chamado math. O nome do argumento pode ser usado no cálculo.

Observe o seguinte sobre a amostra de código anterior.

• A @excludeFromAutoComplete marca garante que a função personalizada não apareça na interface do usuário do Excel quando um usuário a inserir em uma caixa de pesquisa. No entanto, um usuário ainda poderá chamar a função personalizada separadamente de um valor de entidade se a inserir diretamente em uma célula.
• O objeto de chamada é sempre passado como o primeiro argumento e deve ser do tipo any. Nesse caso, ele é nomeado math e é usado para obter a propriedade value do math objeto.
• Ele retorna uma matriz dupla de números.
• Quando o usuário interage com o método de referência no Excel, ele não vê o objeto de chamada como um argumento.

Exemplo: Calcular imposto sobre vendas de produtos

O código a seguir 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 exemplo de código a seguir mostra como especificar o método de referência e inclui o idapplySalesTax da função personalizada.

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

O código a seguir 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 interface do usuário do Excel

Use a @excludeFromAutoComplete marca na marca JSDoc de funções personalizadas usadas por métodos de referência para indicar que a função deve ser excluída do Preenchimento Automático de fórmulas e do Construtor de Fórmulas. Isso ajuda a impedir que os usuários usem acidentalmente uma função personalizada separadamente de seu valor de entidade.

A @excludeFromAutoComplete marca é processada durante o build para gerar um arquivo de functions.json pelo pacote Custom-Functions-Metadata . Esse pacote será adicionado automaticamente ao processo de build se você criar seu suplemento com o gerador Yeoman para Suplementos do Office e escolher um modelo de funções personalizado. Se você não estiver usando o pacote Custom-Functions-Metadata , precisará adicionar a excludeFromAutoComplete propriedade manualmente ao arquivo functions.json .

O exemplo de código a seguir mostra como definir manualmente a APPLYSALESTAX função personalizada com JSON no arquivo 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, consulte Criar manualmente metadados JSON para funções personalizadas.

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

Para adicionar funções aos tipos de valor básicos de , doublee , stringuse o mesmo processo que você usaria para valores de Booleanentidade. O exemplo de código a seguir 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 exemplo de código a seguir 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 exemplo de código a seguir mostra como criar um método de referência que aceita argumentos opcionais. O método de referência é nomeado 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 exemplo de código a seguir mostra a implementação do método de referência como uma função personalizada chamada generateRandomRange. Ele retorna uma matriz dinâmica de valores aleatórios correspondentes ao número de rows e columns especificado. Os min argumentos and max são opcionais e, se não forem especificados, o padrão será e 110.

/**
 * 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 usuário insere a função personalizada no Excel, o Preenchimento Automático mostra as propriedades da função e indica os argumentos opcionais colocando-as entre colchetes ([]). A imagem a seguir mostra um exemplo de inserção de parâmetros opcionais usando o generateRandomRange método de referência.

Vários parâmetros

Os métodos de referência dão suporte a vários parâmetros, de forma semelhante à forma como a função do Excel SUM dá suporte a vários parâmetros. O exemplo de código a seguir mostra como criar uma função de referência que concatena zero ou mais nomes de produto passados em uma matriz de produtos. A função é mostrada para o usuário 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 exemplo de código a seguir mostra como criar uma entidade com o método de concatProductNames 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 a seguir mostra um exemplo de inserção de vários parâmetros usando o concatProductNames método de referência.

Vários parâmetros com intervalos

Para dar suporte à passagem de intervalos para seu método de referência, como B1:B3, use uma matriz multidimensional. O exemplo de código a seguir 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 exemplo de código a seguir mostra como criar uma entidade com o método de sumAll 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 a seguir mostra um exemplo de como inserir vários parâmetros, incluindo um parâmetro de intervalo, usando o sumAll método de referência.

Detalhes do suporte

Há suporte para métodos de referência em todos os tipos de função personalizada, como funções voláteis e de streaming . Todos os tipos de retorno de função personalizada — matriz, escalar e erro — têm suporte.

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