Utiliser les classeurs utilisant l’API JavaScript Excel

  • Article

Cet article fournit des exemples de code qui montrent comment effectuer des tâches courantes à l’aide de classeurs utilisant l’API JavaScript pour Excel. Pour obtenir la liste complète des propriétés et méthodes prises en charge par l’objetWorkbook, consultez Objet Workbook (API JavaScript pour Excel). Cet article décrit également les actions de niveau classeur effectuées via l’objetApplication.

L’objet classeur est le point d’entrée pour votre complément pour interagir avec Excel. Il gère les collections de feuilles de calcul, des tableaux, des tableaux croisés dynamiques et plus, via lesquels les données Excel sont consultées et modifiées. L’objetWorksheetCollection donne accès à votre complément aux données de tous les classeurs via les feuilles de calcul individuelles. Plus précisément, il permet à votre complément d’ajouter des feuilles de calcul et naviguer parmi celles-ci, et assigner des gestionnaires d’événements de feuille de calcul. L’article Manipuler des feuilles de calcul à l’aide de l’API JavaScript Excel décrit comment accéder et modifier des feuilles de calcul.

Obtenir la cellule active ou la plage sélectionnée

L’objet de classeur contient deux méthodes qui obtiennent une plage de cellules que l’utilisateur ou complément a sélectionnée : getActiveCell() et getSelectedRange(). getActiveCell() obtient la cellule active du classeur en tant qu’un objet plage. L’exemple suivant montre un appel à getActiveCell(), suivi par adresse de la cellule imprimée sur la console.

await Excel.run(async (context) => {
    let activeCell = context.workbook.getActiveCell();
    activeCell.load("address");
    await context.sync();

    console.log("The active cell is " + activeCell.address);
});

Le getSelectedRange() méthode retourne la plage unique actuellement sélectionnée. Si plusieurs plages sont sélectionnées, une erreur InvalidSelection est envoyée. L’exemple suivant montre un appel à getSelectedRange() qui définit ensuite la couleur de remplissage de la plage en jaune.

await Excel.run(async (context) => {
    let range = context.workbook.getSelectedRange();
    range.format.fill.color = "yellow";
    await context.sync();
});

Créer un classeur

Votre complément peut créer un nouveau classeur, distinct de l’instance d’Excel dans laquelle le complément est en cours d’exécution. L’objet d’Excel a la méthodecreateWorkbook prévue à cet effet. Lorsque cette méthode est appelée, le nouveau classeur est immédiatement ouvert et affiché dans une nouvelle instance d’Excel. Votre complément reste ouvert et en cours d’exécution avec le classeur précédent.

Excel.createWorkbook();

La createWorkbook méthode peut également créer une copie d’un classeur existant. La méthode accepte comme un paramètre facultatif une représentation de chaîne codée en base 64 d’un fichier .xlsx. Le classeur résultant sera une copie de ce fichier, en supposant que l’argument de chaîne est un fichier .xlsx valide.

Vous pouvez obtenir le classeur actuel de votre complément sous la forme d’une chaîne encodée en base64 à l’aide du découpage de fichier. La classe FileReader peut être utilisée pour convertir un fichier dans la chaîne codée en base 64 requise, comme indiqué dans l’exemple suivant.

// Retrieve the external workbook file and set up a `FileReader` object. 
let myFile = document.getElementById("file");
let reader = new FileReader();

reader.onload = (function (event) {
    Excel.run(function (context) {
        // Remove the metadata before the base64-encoded string.
        let startIndex = reader.result.toString().indexOf("base64,");
        let externalWorkbook = reader.result.toString().substr(startIndex + 7);

        Excel.createWorkbook(externalWorkbook);
        return context.sync();
    });
});

// Read the file as a data URL so we can parse the base64-encoded string.
reader.readAsDataURL(myFile.files[0]);

Insérer une copie d’un classeur existant dans l’offre actuelle

L’exemple précédent montre un nouveau classeur créé à partir d’un classeur existant. Vous pouvez également copier la totalité ou une partie d’un classeur existant dans le tableau actuellement associé à votre complément. Un classeur a la insertWorksheetsFromBase64 méthode permettant d’insérer des copies des feuilles de calcul du classeur cible dans lui-même. Le fichier de l’autre classeur est passé en tant que chaîne encodée en base64, tout comme l’appel Excel.createWorkbook .

insertWorksheetsFromBase64(base64File: string, options?: Excel.InsertWorksheetOptions): OfficeExtension.ClientResult<string[]>;

Importante

La insertWorksheetsFromBase64 méthode est prise en charge pour Excel sur Windows, Mac et le web. Il n’est pas pris en charge pour iOS. En outre, dans Excel sur le Web cette méthode ne prend pas en charge les feuilles de calcul sources avec des éléments de tableau croisé dynamique, de graphique, de commentaire ou de segment. Si ces objets sont présents, la insertWorksheetsFromBase64 méthode retourne l’erreur UnsupportedFeature dans Excel sur le Web.

L’exemple de code suivant montre comment insérer des feuilles de calcul d’un autre classeur dans le classeur actif. Cet exemple de code traite d’abord un fichier de classeur avec un FileReader objet et extrait une chaîne encodée en base64, puis insère cette chaîne encodée en base64 dans le classeur actif. Les nouvelles feuilles de calcul sont insérées après la feuille de calcul nommée Sheet1. Notez que [] est passé en tant que paramètre pour la propriété InsertWorksheetOptions.sheetNamesToInsert . Cela signifie que toutes les feuilles de calcul du classeur cible sont insérées dans le classeur actif.

// Retrieve the external workbook file and set up a `FileReader` object. 
let myFile = document.getElementById("file");
let reader = new FileReader();

reader.onload = (event) => {
    Excel.run((context) => {
        // Remove the metadata before the base64-encoded string.
        let startIndex = reader.result.toString().indexOf("base64,");
        let externalWorkbook = reader.result.toString().substr(startIndex + 7);
            
        // Retrieve the current workbook.
        let workbook = context.workbook;
            
        // Set up the insert options. 
        let options = { 
            sheetNamesToInsert: [], // Insert all the worksheets from the source workbook.
            positionType: Excel.WorksheetPositionType.after, // Insert after the `relativeTo` sheet.
            relativeTo: "Sheet1" // The sheet relative to which the other worksheets will be inserted. Used with `positionType`.
        }; 
            
         // Insert the new worksheets into the current workbook.
         workbook.insertWorksheetsFromBase64(externalWorkbook, options);
         return context.sync();
    });
};

// Read the file as a data URL so we can parse the base64-encoded string.
reader.readAsDataURL(myFile.files[0]);

Protéger la structure du classeur

Votre complément permet de contrôler la possibilité d’un utilisateur de modifier la structure du classeur. La propriété de l’objet classeur protection est un objetWorkbookProtection avec une méthodeprotect(). L’exemple suivant illustre un scénario de base activer/désactiver la protection de la structure du classeur.

await Excel.run(async (context) => {
    let workbook = context.workbook;
    workbook.load("protection/protected");
    await context.sync();

    if (!workbook.protection.protected) {
        workbook.protection.protect();
    }
});

La méthodeprotect accepte un paramètre de chaîne facultatif. Cette chaîne représente le mot de passe nécessaire pour un utilisateur pour ignorer la protection et modifier la structure du classeur.

La protection peut également être définie au niveau de la feuille de calcul pour empêcher la modification de données non souhaitée. Pour plus d’informations, voir la sectionprotection des données de l’articlemanipuler des feuilles de calcul à l’aide de l’API JavaScript Excel.

Remarque

Pour plus d’informations sur la protection du classeur dans Excel, voir l’article protéger un classeur.

Accès aux propriétés du document

Les objets classeur ont accès aux métadonnées de fichier Office, qui sont connues comme propriétés du document. La propriété de l’objet properties Workbook est un objet DocumentProperties qui contient certaines de ces valeurs de métadonnées. L’exemple suivant montre comment définir la author propriété .

await Excel.run(async (context) => {
    let docProperties = context.workbook.properties;
    docProperties.author = "Alex";
    await context.sync();
});

Vous pouvez également définir des propriétés personnalisées. L’objet DocumentProperties contient une propriété custom qui représente une collection de paires de valeur clés pour les propriétés définies par l’utilisateur. Pour obtenir un exemple de définition de propriétés personnalisées, consultez la section Données XML personnalisées dans Excel et Word de l’article Conserver l’état et les paramètres du complément.

Accès aux paramètres de document

Les paramètres d’un classeur sont similaires à la collection de propriétés personnalisées. La différence est que les paramètres sont spécifiques à un seul fichier Excel et au jumelage complément, tandis que les propriétés sont uniquement connectées à celui-ci. L’exemple suivant montre comment créer et accéder à un paramètre.

await Excel.run(async (context) => {
    let settings = context.workbook.settings;
    settings.add("NeedsReview", true);
    let needsReview = settings.getItem("NeedsReview");
    needsReview.load("value");

    await context.sync();
    console.log("Workbook needs review : " + needsReview.value);
});

Accéder aux paramètres de culture d’application

Un classeur a des paramètres de langue et de culture qui affectent la façon dont certaines données sont affichées. Ces paramètres peuvent vous aider à localiser les données lorsque les utilisateurs de votre complément partagent des classeurs dans différentes langues et cultures. Votre complément peut utiliser l’analyse de chaînes pour localiser le format des nombres, des dates et des heures en fonction des paramètres de culture système afin que chaque utilisateur voit les données dans le format de sa propre culture.

Application.cultureInfo définit les paramètres de culture système en tant qu’objet CultureInfo . Il contient des paramètres tels que le séparateur décimal numérique ou le format de date.

Certains paramètres de culture peuvent être modifiés via l’interface utilisateur Excel. Les paramètres système sont conservés dans l’objet CultureInfo . Toutes les modifications locales sont conservées en tant que propriétés au niveau de l’application, telles que Application.decimalSeparator.

L’exemple suivant remplace le caractère séparateur décimal d’une chaîne numérique par le caractère utilisé par les paramètres système.

// This will convert a number like "14,37" to "14.37"
// (assuming the system decimal separator is ".").
await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getItem("Sample");
    let decimalSource = sheet.getRange("B2");

    decimalSource.load("values");
    context.application.cultureInfo.numberFormat.load("numberDecimalSeparator");
    await context.sync();

    let systemDecimalSeparator =
        context.application.cultureInfo.numberFormat.numberDecimalSeparator;
    let oldDecimalString = decimalSource.values[0][0];

    // This assumes the input column is standardized to use "," as the decimal separator.
    let newDecimalString = oldDecimalString.replace(",", systemDecimalSeparator);

    let resultRange = sheet.getRange("C2");
    resultRange.values = [[newDecimalString]];
    resultRange.format.autofitColumns();
    await context.sync();
});

Contrôler le comportement de calcul

Définir le mode de calcul

Par défaut, Excel recalcule les résultats d’une formule chaque fois qu’une cellule référencée est modifiée. Le performances de votre complément peuvent profiter de l’ajustement de ce comportement de calcul. L’objet Application a une calculationMode propriété de type CalculationMode. Il peut être défini sur les valeurs suivantes.

  • automatic: Le comportement de recalcul par défaut dans lequel Excel calcule les résultats d’une nouvelle formule chaque fois que les données pertinentes sont modifiées.
  • automaticExceptTables: Identique automatic, sauf que les modifications apportées à des valeurs dans les tableaux sont ignorées.
  • manual: Calculs sont uniquement effectués lorsque l’utilisateur ou un complément les demande.

Définir le type de calcul

L’objet Application fournit une méthode pour forcer un nouveau calcul immédiat. Application.calculate(calculationType) démarre un recalcul manuel basé sur la valeur calculationType. Les valeurs suivantes peuvent être spécifiées.

  • full: Recalculer toutes les formules dans tous les classeurs ouverts, qu’elles aient changé depuis le dernier recalcul ou non.
  • fullRebuild: Revérifier les formules dépendantes, puis recalculer toutes les formules de tous les classeurs ouverts, qu’elles aient changé depuis le dernier recalcul ou non.
  • recalculate: Recalculer des formules qui ont changé (ou marqués par programme pour le recalcul) depuis le dernier calcul et les formules dépendantes, dans tous les classeurs actifs.

Remarque

Pour plus d’informations sur le recalcul, voir l’article recalcul de modification, l’itération ou la précision.

Suspendre temporairement les calculs

L’API Excel vous permet également de désactiver les compléments calculs jusqu'à ce que RequestContext.sync() ne soit appelé. Cette opération est effectuée avec suspendApiCalculationUntilNextSync(). Utilisez cette méthode lorsque votre complément modifie de grandes plages sans avoir à accéder aux données entre les modifications.

context.application.suspendApiCalculationUntilNextSync();

Détecter l’activation du classeur

Votre complément peut détecter quand un classeur est activé. Un classeur devient inactif lorsque l’utilisateur bascule le focus vers un autre classeur, vers une autre application ou (dans Excel sur le Web) vers un autre onglet du navigateur web. Un classeur est activé lorsque l’utilisateur retourne le focus sur le classeur. L’activation du classeur peut déclencher des fonctions de rappel dans votre complément, telles que l’actualisation des données du classeur.

Pour détecter quand un classeur est activé, inscrivez un gestionnaire d’événements pour l’événement onActivated d’un classeur. Les gestionnaires d’événements pour l’événement onActivated reçoivent un objet WorkbookActivatedEventArgs lorsque l’événement se déclenche.

Importante

L’événement onActivated ne détecte pas quand un classeur est ouvert. Cet événement détecte uniquement lorsqu’un utilisateur revient au focus sur un classeur déjà ouvert.

L’exemple de code suivant montre comment inscrire le gestionnaire d’événements onActivated et configurer une fonction de rappel.

async function run() {
    await Excel.run(async (context) => {
        // Retrieve the workbook.
        let workbook = context.workbook;
    
        // Register the workbook activated event handler.
        workbook.onActivated.add(workbookActivated);
        await context.sync();
    });
}

async function workbookActivated(event) {
    await Excel.run(async (context) => {
        // Retrieve the workbook and load the name.
        let workbook = context.workbook;
        workbook.load("name");        
        await context.sync();

        // Callback function for when the workbook is activated.
        console.log(`The workbook ${workbook.name} was activated.`);
    });
}

Enregistrer le classeur

Workbook.save enregistre le classeur dans un espace de stockage permanent. La save méthode prend un seul paramètre facultatif saveBehavior qui peut être l’une des valeurs suivantes.

  • Excel.SaveBehavior.save (par défaut) : le fichier est enregistré sans inviter l’utilisateur à spécifier le nom de fichier et l’emplacement d’enregistrement. Si le fichier n’a pas été enregistré précédemment, il est enregistré dans l’emplacement par défaut. Si le fichier a été enregistré précédemment, il est enregistré au même emplacement.
  • Excel.SaveBehavior.prompt : si le fichier n’a pas été enregistré précédemment, l’utilisateur sera invité à spécifier le nom de fichier et l’emplacement d’enregistrement. Si le fichier a été enregistré précédemment, il est enregistré dans le même emplacement et l’utilisateur ne reçoit pas d’invite.

Attention

Si l’utilisateur est invité à enregistrer mais annule alors l’opération, save renvoie une erreur.

context.workbook.save(Excel.SaveBehavior.prompt);

Fermer le classeur

Workbook.close ferme le classeur, ainsi que des compléments qui sont associées au classeur (l’application Excel reste ouverte). La close méthode prend un seul paramètre facultatif closeBehavior qui peut être l’une des valeurs suivantes.

  • Excel.CloseBehavior.save (par défaut) : le fichier est enregistré avant d’être fermé. Si le fichier n’a pas été enregistré précédemment, l’utilisateur sera invité à spécifier le nom de fichier et l’emplacement d’enregistrement.
  • Excel.CloseBehavior.skipSave : le fichier est fermé immédiatement, sans enregistrer. Les modifications non enregistrées sont perdues.
context.workbook.close(Excel.CloseBehavior.save);

Voir aussi