Adición de métodos de referencia a valores de celda

Agregue métodos de referencia a los valores de celda para proporcionar a los usuarios acceso a cálculos dinámicos basados en el valor de celda. Los EntityCellValue tipos y LinkedEntityCellValue admiten métodos de referencia. Por ejemplo, agregue un método a un valor de entidad de producto que convierta su peso en unidades diferentes.

En la imagen siguiente se muestra un ejemplo de adición de un ConvertWeight método a un valor de entidad de producto que representa la combinación de panqueques.

Los DoubleCellValuetipos , BooleanCellValuey StringCellValue también admiten métodos de referencia. En la imagen siguiente se muestra un ejemplo de adición de un ConvertToRomanNumeral método a un tipo de valor doble.

Los métodos de referencia no aparecen en la tarjeta de tipo de datos.

Adición de un método de referencia a un valor de entidad

Para agregar un método de referencia a un valor de entidad, defínelo en JSON mediante el Excel.JavaScriptCustomFunctionReferenceCellValue tipo . En el ejemplo de código siguiente se muestra cómo definir un método simple que devuelve el valor 27.

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

Las propiedades se describen en la tabla siguiente.

Propiedad	Descripción
type	Especifica el tipo de referencia. Esta propiedad solo admite function y debe establecerse en Excel.CellValueType.function.
functionType	Especifica el tipo de función. Esta propiedad solo admite funciones de referencia de JavaScript y debe establecerse en Excel.FunctionCellValueType.javaScriptReference.
Namespace	Espacio de nombres que contiene la función personalizada. Este valor debe coincidir con el espacio de nombres especificado por el elemento customFunctions.namespace en el manifiesto unificado o con el elemento Namespace del manifiesto de solo complemento.
id	Nombre de la función personalizada que se va a asignar a este método de referencia. El nombre es la versión en mayúsculas del nombre de la función personalizada.

Al crear el valor de entidad, agregue el método de referencia a la lista de propiedades. En el ejemplo de código siguiente se muestra cómo crear un valor de entidad simple denominado Math y agregarle un método de referencia. Get27 es el nombre del método que aparece a los usuarios (por ejemplo: 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;
}

En el ejemplo de código siguiente se muestra cómo crear una instancia de la Math entidad y agregarla a la celda seleccionada.

// 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 último, implemente el método de referencia con una función personalizada. En el ejemplo de código siguiente se muestra cómo implementar la función personalizada.

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

En el ejemplo de código anterior, la @excludeFromAutoComplete etiqueta garantiza que la función personalizada no aparece en la interfaz de usuario de Excel cuando un usuario la escribe en un cuadro de búsqueda. Sin embargo, un usuario todavía puede llamar a la función personalizada por separado de un valor de entidad si la escribe directamente en una celda.

Cuando se ejecuta el código, crea un Math valor de entidad como se muestra en la siguiente imagen. El método aparece en la fórmula Autocompletar cuando el usuario hace referencia al valor de entidad de una fórmula.

Agregar argumentos

Si el método de referencia necesita argumentos, agréguelos a la función personalizada. En el ejemplo de código siguiente se muestra cómo agregar un argumento denominado x a un método denominado addValue. El método agrega uno al x valor llamando a una función personalizada denominada 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]];
}

Hacer referencia al valor de entidad como un objeto que realiza una llamada

Un escenario común es que los métodos necesitan hacer referencia a las propiedades en el propio valor de la entidad para realizar cálculos. Por ejemplo, es más útil si el addValue método agrega el valor del argumento al propio valor de la entidad. Especifique que el valor de entidad se pasa como primer argumento aplicando la @capturesCallingObject etiqueta a la función personalizada, como se muestra en el ejemplo de código siguiente.

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

Puede usar cualquier nombre de argumento que se ajuste a las reglas de sintaxis de Excel en Nombres en fórmulas. Dado que se trata de una entidad matemática, el argumento del objeto que realiza la llamada se denomina math. El nombre del argumento se puede usar en el cálculo.

Tenga en cuenta lo siguiente sobre el ejemplo de código anterior.

• La @excludeFromAutoComplete etiqueta garantiza que la función personalizada no aparece en la interfaz de usuario de Excel cuando un usuario la escribe en un cuadro de búsqueda. Sin embargo, un usuario todavía puede llamar a la función personalizada por separado de un valor de entidad si la escribe directamente en una celda.
• El objeto que realiza la llamada siempre se pasa como primer argumento y debe ser de tipo any. En este caso, se denomina math y se usa para obtener la propiedad value del math objeto .
• Devuelve una matriz doble de números.
• Cuando el usuario interactúa con el método de referencia en Excel, no ve el objeto que realiza la llamada como un argumento.

Ejemplo: Cálculo del impuesto sobre las ventas de productos

En el código siguiente se muestra cómo implementar una función personalizada que calcula el impuesto de ventas para el precio unitario de un producto.

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

En el ejemplo de código siguiente se muestra cómo especificar el método de referencia e incluye el id de la applySalesTax función personalizada.

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

En el código siguiente se muestra cómo agregar el método de referencia al valor de la product entidad.

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

Exclusión de funciones personalizadas de la interfaz de usuario de Excel

Use la @excludeFromAutoComplete etiqueta de la etiqueta JSDoc de las funciones personalizadas usadas por los métodos de referencia para indicar que la función se excluirá de la fórmula AutoCompletar y Formula Builder. Esto ayuda a evitar que los usuarios usen accidentalmente una función personalizada por separado de su valor de entidad.

Nota:

Si la función se escribe manualmente correctamente en la cuadrícula, la función se sigue ejecutando.

Importante

Una función no puede tener las etiquetas @excludeFromAutoComplete y @linkedEntityLoadService al mismo tiempo.

La @excludeFromAutoComplete etiqueta se procesa durante la compilación para generar un archivo functions.json mediante el paquete Custom-Functions-Metadata . Este paquete se agrega automáticamente al proceso de compilación si crea el complemento con el generador de Yeoman para complementos de Office y elige una plantilla de funciones personalizadas. Si no usa el paquete Custom-Functions-Metadata , deberá agregar la excludeFromAutoComplete propiedad manualmente al archivo functions.json .

En el ejemplo de código siguiente se muestra cómo definir manualmente la APPLYSALESTAX función personalizada con JSON en el archivo functions.json . La propiedad excludeFromAutoComplete se configura 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 obtener más información, consulte Creación manual de metadatos JSON para funciones personalizadas.

Adición de una función a un tipo de valor básico

Para agregar funciones a los tipos de valor básicos de , doubley string, use el mismo proceso que para los valores de Booleanentidad. En el ejemplo de código siguiente se muestra cómo crear un valor básico doble con una función personalizada denominada addValue. La función agrega el valor x al 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]];
}

En el ejemplo de código siguiente se muestra cómo definir la addValue función personalizada del ejemplo anterior en JSON y, a continuación, hacer referencia a ella con un método denominado 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 opcionales

En el ejemplo de código siguiente se muestra cómo crear un método de referencia que acepta argumentos opcionales. El método de referencia se denomina generateRandomRange y genera un intervalo de valores aleatorios.

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

En el ejemplo de código siguiente se muestra la implementación del método de referencia como una función personalizada denominada generateRandomRange. Devuelve una matriz dinámica de valores aleatorios que coinciden con el número de rows y columns especificados. Los min argumentos y max son opcionales y, si no se especifican, tendrán como valor predeterminado 1 y 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;
}

Cuando el usuario escribe la función personalizada en Excel, Autocompletar muestra las propiedades de la función e indica argumentos opcionales si los rodea entre corchetes ([]). En la imagen siguiente se muestra un ejemplo de especificación de parámetros opcionales mediante el método de generateRandomRange referencia.

Varios parámetros

Los métodos de referencia admiten varios parámetros, de forma similar a cómo la función de Excel SUM admite varios parámetros. En el ejemplo de código siguiente se muestra cómo crear una función de referencia que concatena cero o más nombres de producto pasados en una matriz de productos. La función se muestra al usuario 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(" | "); 
}

En el ejemplo de código siguiente se muestra cómo crear una entidad con el método de concatProductNames referencia.

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

En la imagen siguiente se muestra un ejemplo de especificación de varios parámetros mediante el método de concatProductNames referencia.

Varios parámetros con intervalos

Para admitir el paso de intervalos al método de referencia, como B1:B3, use una matriz multidimensional. En el ejemplo de código siguiente se muestra cómo crear una función de referencia que suma cero o más parámetros que pueden 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; 
} 

En el ejemplo de código siguiente se muestra cómo crear una entidad con el método de sumAll referencia.

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

En la imagen siguiente se muestra un ejemplo de especificación de varios parámetros, incluido un parámetro de rango, mediante el sumAll método de referencia.

Detalles de compatibilidad

Los métodos de referencia se admiten en todos los tipos de función personalizados, como las funciones volátiles y de streaming . Se admiten todos los tipos de valor devuelto de función personalizados(matriz, escalar y error).

Importante

Una entidad vinculada no puede tener una función personalizada que combine un método de referencia y un proveedor de datos. Al desarrollar entidades vinculadas, mantenga separados estos tipos de funciones personalizadas.

Vea también

• Crear funciones personalizadas en Excel
• Generar automáticamente metadatos JSON de funciones personalizadas
• Creación manual de metadatos JSON para funciones personalizadas