Ajouter des méthodes de référence aux valeurs de cellule

Ajoutez des méthodes de référence aux valeurs de cellule pour permettre aux utilisateurs d’accéder à des calculs dynamiques basés sur la valeur de cellule. Les EntityCellValue types et LinkedEntityCellValue prennent en charge les méthodes de référence. Par exemple, ajoutez une méthode à une valeur d’entité de produit qui convertit son poids en différentes unités.

L’image suivante montre un exemple d’ajout d’une ConvertWeight méthode à une valeur d’entité de produit représentant un mélange de crêpes.

Les DoubleCellValuetypes , BooleanCellValueet StringCellValue prennent également en charge les méthodes de référence. L’image suivante montre un exemple d’ajout d’une ConvertToRomanNumeral méthode à un type double valeur.

Les méthodes de référence n’apparaissent pas sur le type de données carte.

Ajouter une méthode de référence à une valeur d’entité

Pour ajouter une méthode de référence à une valeur d’entité, définissez-la au format JSON à l’aide du Excel.JavaScriptCustomFunctionReferenceCellValue type . L’exemple de code suivant montre comment définir une méthode simple qui retourne la valeur 27.

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

Les propriétés sont décrites dans le tableau suivant.

Propriété	Description
type	Spécifie le type de référence. Cette propriété prend uniquement en charge function et doit être définie sur Excel.CellValueType.function.
functionType	Spécifie le type de fonction. Cette propriété prend uniquement en charge les fonctions de référence JavaScript et doit être définie sur Excel.FunctionCellValueType.javaScriptReference.
Namespace	Espace de noms qui contient la fonction personnalisée. Cette valeur doit correspondre à l’espace de noms spécifié par l’élément customFunctions.namespace dans le manifeste unifié, ou à l’élément Namespace dans le manifeste de complément uniquement.
id	Nom de la fonction personnalisée à mapper à cette méthode de référence. Le nom est la version majuscule du nom de la fonction personnalisée.

Lorsque vous créez la valeur d’entité, ajoutez la méthode de référence à la liste des propriétés. L’exemple de code suivant montre comment créer une valeur d’entité simple nommée Math et y ajouter une méthode de référence. Get27 est le nom de la méthode qui apparaît aux utilisateurs (par exemple : 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;
}

L’exemple de code suivant montre comment créer une instance de l’entité et l’ajouter Math à la cellule sélectionnée.

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

Enfin, implémentez la méthode de référence avec une fonction personnalisée. L’exemple de code suivant montre comment implémenter la fonction personnalisée.

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

Dans l’exemple de code précédent, la @excludeFromAutoComplete balise garantit que la fonction personnalisée n’apparaît pas dans l’interface utilisateur Excel lorsqu’un utilisateur l’entre dans une zone de recherche. Toutefois, un utilisateur peut toujours appeler la fonction personnalisée séparément d’une valeur d’entité s’il l’entre directement dans une cellule.

Lorsque le code s’exécute, il crée une valeur d’entité Math comme illustré dans l’image suivante. La méthode apparaît dans la saisie semi-automatique de formule lorsque l’utilisateur fait référence à la valeur d’entité à partir d’une formule.

Ajouter des arguments

Si votre méthode de référence a besoin d’arguments, ajoutez-les à la fonction personnalisée. L’exemple de code suivant montre comment ajouter un argument nommé x à une méthode nommée addValue. La méthode en ajoute un à la x valeur en appelant une fonction personnalisée nommée 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]];
}

Référencer la valeur d’entité en tant qu’objet appelant

Un scénario courant est que vos méthodes doivent référencer des propriétés sur la valeur d’entité elle-même pour effectuer des calculs. Par exemple, il est plus utile si la méthode ajoute la addValue valeur d’argument à la valeur d’entité elle-même. Spécifiez que la valeur d’entité est passée comme premier argument en appliquant la @capturesCallingObject balise à la fonction personnalisée, comme indiqué dans l’exemple de code suivant.

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

Vous pouvez utiliser n’importe quel nom d’argument conforme aux règles de syntaxe Excel dans Noms dans les formules. Étant donné qu’il s’agit d’une entité mathématique, l’argument d’objet appelant est nommé math. Le nom de l’argument peut être utilisé dans le calcul.

Notez ce qui suit à propos de l’exemple de code précédent.

• La @excludeFromAutoComplete balise garantit que la fonction personnalisée n’apparaît pas dans l’interface utilisateur Excel lorsqu’un utilisateur l’entre dans une zone de recherche. Toutefois, un utilisateur peut toujours appeler la fonction personnalisée séparément d’une valeur d’entité s’il l’entre directement dans une cellule.
• L’objet appelant est toujours passé comme premier argument et doit être de type any. Dans ce cas, il est nommé math et est utilisé pour obtenir la propriété value à partir de l’objet math .
• Elle retourne un tableau double de nombres.
• Lorsque l’utilisateur interagit avec la méthode de référence dans Excel, il ne voit pas l’objet appelant comme argument.

Exemple : Calculer la taxe sur les ventes de produits

Le code suivant montre comment implémenter une fonction personnalisée qui calcule la taxe de vente pour le prix unitaire d’un produit.

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

L’exemple de code suivant montre comment spécifier la méthode de référence et inclut le id de la applySalesTax fonction personnalisée.

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

Le code suivant montre comment ajouter la méthode de référence à la valeur d’entité 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;
}

Exclure des fonctions personnalisées de l’interface utilisateur Excel

Utilisez la @excludeFromAutoComplete balise dans la balise JSDoc des fonctions personnalisées utilisées par les méthodes de référence pour indiquer que la fonction doit être exclue de la saisie semi-automatique de formule et du Générateur de formules. Cela permet d’empêcher les utilisateurs d’utiliser accidentellement une fonction personnalisée séparément de sa valeur d’entité.

Remarque

Si la fonction est entrée manuellement correctement dans la grille, la fonction s’exécute toujours.

Importante

Une fonction ne peut pas contenir les deux balises @excludeFromAutoComplete et @linkedEntityLoadService.

La @excludeFromAutoComplete balise est traitée pendant la génération pour générer un fichier functions.json par le package Custom-Functions-Metadata . Ce package est automatiquement ajouté au processus de génération si vous créez votre complément avec le générateur Yeoman pour les compléments Office et que vous choisissez un modèle de fonctions personnalisé. Si vous n’utilisez pas le package Custom-Functions-Metadata , vous devez ajouter la excludeFromAutoComplete propriété manuellement au fichier functions.json .

L’exemple de code suivant montre comment définir manuellement la APPLYSALESTAX fonction personnalisée avec JSON dans le fichier functions.json . La excludeFromAutoComplete propriété est définie sur 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"
    }
},

Pour plus d’informations, consultez Créer manuellement des métadonnées JSON pour les fonctions personnalisées.

Ajouter une fonction à un type valeur de base

Pour ajouter des fonctions aux types valeur de base de Boolean, doubleet string, utilisez le même processus que pour les valeurs d’entité. L’exemple de code suivant montre comment créer une double valeur de base avec une fonction personnalisée appelée addValue. La fonction ajoute la valeur x à la valeur de base.

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

L’exemple de code suivant montre comment définir la addValue fonction personnalisée de l’exemple précédent dans JSON, puis la référencer avec une méthode appelée 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 arguments

L’exemple de code suivant montre comment créer une méthode de référence qui accepte des arguments facultatifs. La méthode de référence est nommée generateRandomRange et génère une plage de valeurs aléatoires.

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

L’exemple de code suivant montre l’implémentation de la méthode de référence en tant que fonction personnalisée nommée generateRandomRange. Elle retourne un tableau dynamique de valeurs aléatoires correspondant au nombre de rows et columns spécifiés. Les min arguments et max sont facultatifs et, s’ils ne sont pas spécifiés, la valeur par défaut 1 est et 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;
}

Lorsque l’utilisateur entre la fonction personnalisée dans Excel, la saisie semi-automatique affiche les propriétés de la fonction et indique les arguments facultatifs en les plaçant entre crochets ([]). L’image suivante montre un exemple de saisie de paramètres facultatifs à l’aide de la generateRandomRange méthode de référence.

Paramètres multiples

Les méthodes de référence prennent en charge plusieurs paramètres, comme la fonction Excel SUM prend en charge plusieurs paramètres. L’exemple de code suivant montre comment créer une fonction de référence qui concatène zéro ou plusieurs noms de produits passés dans un tableau de produits. La fonction est présentée à l’utilisateur sous la forme 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(" | "); 
}

L’exemple de code suivant montre comment créer une entité avec la méthode de concatProductNames référence.

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

L’image suivante montre un exemple de saisie de plusieurs paramètres à l’aide de la méthode de concatProductNames référence.

Plusieurs paramètres avec des plages

Pour prendre en charge le passage de plages à votre méthode de référence telle que B1 :B3, utilisez un tableau multidimensionnel. L’exemple de code suivant montre comment créer une fonction de référence qui additionne zéro ou plusieurs paramètres pouvant inclure des plages.

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

L’exemple de code suivant montre comment créer une entité avec la méthode de sumAll référence.

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

L’image suivante montre un exemple d’entrée de plusieurs paramètres, y compris un paramètre de plage, à l’aide de la sumAll méthode de référence.

Informations de prise en charge

Les méthodes de référence sont prises en charge dans tous les types de fonctions personnalisées, tels que les fonctions volatiles et de diffusion en continu . Tous les types de retour de fonction personnalisée (matrice, scalaire et erreur) sont pris en charge.

Importante

Une entité liée ne peut pas avoir de fonction personnalisée qui combine à la fois une méthode de référence et un fournisseur de données. Lorsque vous développez des entités liées, conservez ces types de fonctions personnalisées séparément.

Voir aussi

• Créer des fonctions personnalisées dans Excel
• Générer automatiquement des métadonnées JSON pour des fonctions personnalisées
• Créer manuellement des métadonnées JSON pour les fonctions personnalisées