Partager via

Didacticiel : créer des fonctions personnalisées dans Excel

  • Article

Les fonctions personnalisées vous permettent d’ajouter de nouvelles fonctions dans Excel en définissant ces fonctions dans JavaScript dans le cadre d’un complément. Les utilisateurs d’Excel peuvent accéder aux fonctions personnalisées comme ils le feraient pour n’importe quelle fonction native d’Excel, telle que SUM(). Vous pouvez créer des fonctions personnalisées qui effectuent des tâches simples comme des calculs personnalisés ou des tâches plus complexes telles que la diffusion en continu des données en temps réel à partir du web dans une feuille de calcul.

Dans ce didacticiel, vous allez :

  • Créer un complément de fonction personnalisée à l’aide la Générateur Yeoman de compléments Office.
  • Utiliser une fonction personnalisée prédéfinie pour effectuer un calcul simple.
  • Créer une fonction personnalisée qui demande les données à partir du web.
  • Créer une fonction personnalisée qui diffuse les données en temps réel à partir du web.

Conditions préalables

  • Node.js (la dernière version LTS) Visitez le siteNode.js pour télécharger et installer la version appropriée pour votre système d’exploitation.

  • La dernière version deYeoman et du Générateur Yeoman Générateur de compléments Office. Pour installer ces outils globalement, exécutez la commande suivante via l’invite de commande.

    npm install -g yo generator-office

    Remarque

    Même si vous avez précédemment installé le générateur Yeoman, nous vous recommandons de mettre à jour votre package vers la dernière version de npm.

  • Office connecté à un abonnement Microsoft 365 (y compris Office on the web).

    Remarque

    Si vous n’avez pas encore Office, vous pouvez bénéficier d’un abonnement Microsoft 365 E5 développeur par le biais du Programme pour les développeurs Microsoft 365. Pour plus d’informations, consultez le FAQ. Vous pouvez également vous inscrire à un essai gratuit de 1 mois ou acheter un plan Microsoft 365.

Créer un projet de fonctions personnalisées

Pour commencer, créez le projet de code pour générer votre complément de fonction personnalisée. Le générateur Yeoman pour les compléments Office configurera votre projet avec des fonctions personnalisées prédéfinies que vous pouvez essayer. Si vous avez déjà exécuté les fonctions personnalisées de démarrage rapide et généré un projet, continuez à utiliser ce projet et passez à cette étape à la place.

Remarque

Si vous recréez le projet Yo Office, vous risquez d’obtenir une erreur, car le cache Office a déjà une instance d’une fonction portant le même nom. Vous pouvez éviter cela en vidant le cache Office avant d’exécuter npm run start.

  1. Exécutez la commande suivante pour créer un projet de complément à l’aide du générateur Yeoman. Un dossier qui contient le projet est ajouté au répertoire actif.

    yo office

    Remarque

    Lorsque vous exécutez la commande yo office, il est possible que vous receviez des messages d’invite sur les règles de collecte de données de Yeoman et les outils CLI de complément Office. Utilisez les informations fournies pour répondre aux invites comme vous l’entendez.

    Lorsque vous y êtes invité, fournissez les informations suivantes pour créer votre projet de complément.

    • Choisissez un type de projet :Excel Custom Functions using a Shared Runtime
    • Choisissez un type de script :JavaScript
    • Que voulez-vous nommer votre complément ?My custom functions add-in

    L’interface de ligne de commande du générateur de compléments Office Yeoman invite à entrer des projets de fonctions personnalisées.

    Le générateur crée le projet et installe les composants Node.js de la prise en charge.

  2. Accédez au dossier racine du projet.

    cd "My custom functions add-in"

  3. Créez le projet.

    npm run build

    Remarque

    Les compléments Office doivent utiliser le protocole HTTPS, et non HTTP, même lorsque vous développez. Si vous êtes invité à installer un certificat après avoir exécuté npm run build, acceptez d’installer le certificat fourni par le générateur Yeoman.

  4. Démarrez le serveur web local qui est exécuté dans Node.js. Vous pouvez essayer le complément de fonction personnalisée dans Excel.

Pour tester votre complément dans Excel sur Windows ou Mac, exécutez la commande suivante. Lorsque vous exécutez cette commande, le serveur web local et Excel s’ouvrent avec votre complément chargé.

npm run start:desktop

Remarque

  • Les compléments Office doivent utiliser HTTPS, et non HTTP, même lorsque vous développez. Si vous êtes invité à installer un certificat après avoir exécuté l’une des commandes suivantes, acceptez l’invite pour installer le certificat fourni par le générateur Yeoman. Il se peut également que vous deviez exécuter votre invite de commande ou votre terminal en tant qu'administrateur pour que les modifications soient effectuées.

  • Si c’est la première fois que vous développez un complément Office sur votre ordinateur, vous pouvez être invité dans la ligne de commande à accorder à Microsoft Edge WebView une exemption de bouclage (« Autoriser le bouclage localhost pour Microsoft Edge WebView ? »). Lorsque vous y êtes invité, entrez Y pour autoriser l’exemption. Notez que vous aurez besoin de privilèges d’administrateur pour autoriser l’exemption. Une fois autorisé, vous ne devez pas être invité à bénéficier d’une exemption lorsque vous chargez une version test des compléments Office à l’avenir (sauf si vous supprimez l’exemption de votre ordinateur). Pour plus d’informations, consultez « Nous ne pouvons pas ouvrir ce complément à partir de localhost » lors du chargement d’un complément Office ou de l’utilisation de Fiddler.

    Invite dans la ligne de commande pour autoriser Microsoft Edge WebView à bénéficier d’une exemption de bouclage.

Essayer une fonction personnalisée prédéfinie

Le projet de fonctions personnalisées que vous avez créé contient certaines fonctions personnalisées prédéfinies, définies dans le fichier ./src/functions/functions.js. Le fichiermanifest.xmlindique que toutes les fonctions personnalisées appartiennent à l’CONTOSOespace de noms. L’espace de noms CONTOSO permet d’accéder aux fonctions personnalisées dans Excel.

Ensuite, essayez la ADD fonction personnalisée en effectuant les étapes suivantes.

  1. Dans Excel, accédez à n’importe quelle cellule et entrez =CONTOSO. Notez que le menu de saisie semi-automatique affiche la liste de toutes les fonctions dans l’espace de noms CONTOSO.

  2. Exécutez laCONTOSO.ADD fonction, avec les nombres 10 et 200 comme paramètres d’entrée, en spécifiant la valeur=CONTOSO.ADD(10,200)suivante dans la cellule et appuyez sur entrée.

Le ADD fonction personnalisée calcule la somme des deux nombres que vous avez spécifiés et renvoie le résultat210 .

Si l’espace de noms CONTOSO n’est pas disponible dans le menu de saisie semi-automatique, procédez comme suit pour inscrire le complément dans Excel.

  1. Dans Excel, sélectionnez Compléments d’accueil>, puis Autres compléments.

  2. Sous l’onglet MES COMPLÉMENTS , sélectionnez Gérer mes compléments et choisissez Charger mon complément.

  3. Sélectionnez Parcourir... et accédez au répertoire racine du projet créé par le Générateur de Yo Office.

  4. Sélectionnez le fichiermanifest.xml puis sélectionnezOuvrir, puis sélectionnez Télécharger.

  5. Essayez la nouvelle fonction. Dans la cellule B1, tapez le texte =CONTOSO. GETSTARCOUNT(« OfficeDev », « Excel-Custom-Functions »), puis appuyez sur Entrée. Le résultat dans la cellule B1 doit correspondre au nombre d’étoiles actuellement attribuées au référentiel GitHub Excel-Custom-Functions.

Remarque

Consultez la section Résolution des problèmes de cet article si vous rencontrez des erreurs lors du chargement indépendant du complément.

Créer une fonction personnalisée qui demande les données à partir du web

Intégration de données à partir du Web est un excellent moyen pour étendre Excel via les fonctions personnalisées. Vous allez ensuite créer une fonction personnalisée nommée getStarCount qui affiche le nombre d’étoiles attribuées à un référentiel GitHub donné.

  1. Dans le projet de complément Mes fonctions personnalisées , recherchez le fichier ./src/functions/functions.js et ouvrez-le dans votre éditeur de code.

  2. Dans le fichier function.js, ajoutez le code suivant.

    /**
  * Gets the star count for a given Github repository.
  * @customfunction 
  * @param {string} userName string name of Github user or organization.
  * @param {string} repoName string name of the Github repository.
  * @return {number} number of stars given to a Github repository.
  */
  async function getStarCount(userName, repoName) {
    try {
      //You can change this URL to any web request you want to work with.
      const url = "https://api.github.com/repos/" + userName + "/" + repoName;
      const response = await fetch(url);
      //Expect that status code is in 200-299 range
      if (!response.ok) {
        throw new Error(response.statusText)
      }
        const jsonResponse = await response.json();
        return jsonResponse.watchers_count;
    }
    catch (error) {
      return error;
    }
  }

  3. Exécutez la commande suivante pour régénérer le projet.

    npm run build

  4. Enregistrez de nouveau le complément dans Excel en procédant comme suit (pour Excel sur le web, Windows ou Mac). Vous devez suivre ces étapes pour que la nouvelle fonction devienne disponible.

  1. Fermez Excel, puis rouvrez-le.

  2. Dans le ruban Excel, sélectionnezCompléments d’accueil>.

  3. Dans la section Compléments développeur , sélectionnez Le complément Mes fonctions personnalisées pour l’inscrire .

    Boîte de dialogue Mes compléments qui affiche les compléments actifs, avec le bouton Mon complément de fonction personnalisée mis en évidence.

  4. Essayez la nouvelle fonction. Dans la cellule B1, tapez le texte =CONTOSO. GETSTARCOUNT(« OfficeDev », « Office-Add-in-Samples ») et appuyez sur Entrée. Vous devriez voir que le résultat dans la cellule B1 correspond au nombre actuel d’étoiles données au référentiel Office-Add-in-Samples.

Remarque

Consultez la section Résolution des problèmes de cet article si vous rencontrez des erreurs lors du chargement indépendant du complément.

Créer une fonction personnalisée asynchrone de diffusion en continu

La fonction getStarCount renvoie le nombre d’étoiles attribuées à un référentiel à un moment donné. Les fonctions personnalisées peuvent également renvoyer des données qui changent continuellement. Ces fonctions sont appelées fonctions de diffusion en continu. Elles doivent inclure un invocation paramètre qui fait référence à la cellule à partir de laquelle la fonction a été appelée. Le invocation paramètre permet de mettre à jour le contenu de la cellule à tout moment.

Vous remarquerez que l’exemple de code suivant inclut deux fonctions (currentTime et clock). currentTime est une fonction statique qui n’utilise pas la diffusion en continu. Elle renvoie la date sous la forme d’une chaîne. La fonction clock utilise la fonction currentTime pour fournir la nouvelle heure toutes les secondes à une cellule dans Excel. Elle utilise invocation.setResult pour communiquer l’heure à la cellule Excel et invocation.onCanceled pour gérer le résultat de l’annulation de la fonction.

Le projet de complément Mes fonctions personnalisées contient déjà les deux fonctions suivantes dans le fichier ./src/functions/functions.js .

/**
 * Returns the current time
 * @returns {string} String with the current time formatted for the current locale.
 */
function currentTime() {
  return new Date().toLocaleTimeString();
}
    
/**
 * Displays the current time once a second
 * @customfunction
 * @param {CustomFunctions.StreamingInvocation<string>} invocation Custom function invocation
 */
function clock(invocation) {
  const timer = setInterval(() => {
    const time = currentTime();
    invocation.setResult(time);
  }, 1000);
    
  invocation.onCanceled = () => {
    clearInterval(timer);
  };
}

Pour tester les fonctions, tapez le texte =CONTOSO. CLOCK() dans la cellule C1, ensuite appuyez sur Entrée. La date du jour doit apparaître. Elle est mise à jour toutes les secondes. Cette horloge n’est qu’une minuterie incluse dans une boucle, mais vous pouvez vous inspirer de cette idée pour créer des fonctions plus complexes qui récupèrent des données en temps réel en exécutant des requêtes web.

Résolution des problèmes

Vous pouvez rencontrer des problèmes si vous exécutez le didacticiel plusieurs fois. Votre complément retourne une erreur lors de son chargement si le cache d'Office contient déjà une instance d'une fonction qui porte le même nom.

Vous pouvez éviter ce conflit en effaçant le cache Office avant d’exécuter npm run start. Si votre processus npm est déjà en cours d’exécution, entrez npm stop, effacez le cache Office, puis redémarrez npm.

Message d’erreur Excel intitulé « Erreur lors de l’installation des fonctions ». Il contient le texte « Ce complément n’a pas été installé car une fonction personnalisée du même nom existe déjà ».

Étapes suivantes

Félicitations ! Vous avez créé un nouveau projet de fonctions personnalisées, essayé une fonction prédéfinie, créé une fonction personnalisée qui récupère des données à partir du web et créé une fonction personnalisée qui diffuse des données. Découvrez ensuite comment partager des données de fonction personnalisées avec le volet Office.