Appliquer une mise en forme conditionnelle aux plages Excel à l’aide de l’API JavaScript Excel

Utilisez la mise en forme conditionnelle lorsque votre complément Excel doit marquer des commandes retardées, mettre en surbrillance des valeurs négatives ou visualiser des tendances sans modifier les valeurs des cellules. Cet article explique comment créer des types de règles courants, mettre à jour des règles existantes, contrôler la priorité des règles et effacer des règles. Pour plus d’informations sur l’expérience de mise en forme conditionnelle intégrée d’Excel, voir Utiliser la mise en forme conditionnelle pour mettre en surbrillance les informations dans Excel et Utiliser une formule pour appliquer la mise en forme conditionnelle dans Excel.

Points essentiels

• Utilisez Range.conditionalFormats pour créer et gérer des règles de mise en forme conditionnelle pour une plage.
• Chaque ConditionalFormat objet ne peut utiliser qu’un seul type de règle, tel que cellValue, colorScaleou iconSet.
• Utilisez priority et stopIfTrue pour contrôler la façon dont plusieurs règles interagissent sur la même plage.
• Utilisez clearFormat ou clearAll pour supprimer des détails de mise en forme ou des règles entières.

Contrôle par programme de mise en forme conditionnelle

La Range.conditionalFormats propriété est un ensemble d’objets ConditionalFormatqui s’appliquent à la plage. L’objet ConditionalFormat contient des propriétés qui définissent le format à appliquer en fonction du ConditionalFormatType.

• cellValue
• colorScale
• custom
• dataBar
• iconSet
• preset
• textComparison
• topBottom

Chacune de ces propriétés de mise en forme a une variante correspondante*OrNullObject. Pour plus d’informations sur ce modèle, consultez *Méthodes OrNullObject.

Vous ne pouvez définir qu’un seul type de format pour un ConditionalFormat objet. La type propriété, qui est une valeur d’énumération ConditionalFormatType , détermine le type de format. Défini type lorsque vous ajoutez un format conditionnel à une plage.

Créer des règles de mise en forme conditionnelle courantes

Ajoutez des formats conditionnels à une plage à l’aide de conditionalFormats.add. Après avoir ajouté un format conditionnel, définissez les propriétés spécifiques à ce format. Les scénarios suivants présentent des types de règles courants que vous pouvez adapter à votre feuille de calcul.

Valeur de la cellule

La mise en forme conditionnelle de valeur de la cellule applique un format défini par l’utilisateur en fonction des résultats d’une ou deux formules dans la ConditionalCellValueRule. La operator propriété est un ConditionalCellValueOperator qui définit la relation entre les expressions résultantes et la mise en forme.

L’exemple suivant montre une coloration de la police en rouge appliquée à une valeur dans la plage inférieure à zéro.

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    const range = sheet.getRange("B21:E23");
    const conditionalFormat = range.conditionalFormats.add(
        Excel.ConditionalFormatType.cellValue
    );
    
    // Set the font of negative numbers to red.
    conditionalFormat.cellValue.format.font.color = "red";
    conditionalFormat.cellValue.rule = { formula1: "=0", operator: "LessThan" };
    
    await context.sync();
});

Échelle de couleur

La mise en forme conditionnelle de l’échelle de couleur applique un dégradé de couleur au sein de la plage de données. Lacriteria propriété sur le ColorScaleConditionalFormat définit troisConditionalColorScaleCriterion: minimum, maximumet éventuellement, midpoint. Chacun des points d’échelle de critère a trois propriétés :

• color -Le code de couleur HTML pour le point de terminaison.
• formula -Un nombre ou une formule représentant le point de terminaison. Cette valeur est null si type est lowestValue ou highestValue.
• type -Comment la formule doit être évaluée. highestValue et lowestValue font référence à des valeurs dans la plage en cours de mise en forme.

L’exemple suivant montre une plage colorée de bleue à jaune à rouge. Notez que minimum et maximum sont les valeurs inférieures et supérieures respectivement et utilisent les null formules. midpoint utilise le percentage type avec une formule de "=50" afin que la cellule la plus jaune soit la valeur moyenne.

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    const range = sheet.getRange("B2:M5");
    const conditionalFormat = range.conditionalFormats.add(
          Excel.ConditionalFormatType.colorScale
    );
    
    // Color the backgrounds of the cells from blue to yellow to red based on value.
    const criteria = {
          minimum: {
               formula: null,
               type: Excel.ConditionalFormatColorCriterionType.lowestValue,
               color: "blue"
          },
          midpoint: {
               formula: "50",
               type: Excel.ConditionalFormatColorCriterionType.percent,
               color: "yellow"
          },
          maximum: {
               formula: null,
               type: Excel.ConditionalFormatColorCriterionType.highestValue,
               color: "red"
          }
    };
    conditionalFormat.colorScale.criteria = criteria;
    
    await context.sync();
});

Personnalisé

La mise en forme conditionnelle personnalisée applique un format défini par l’utilisateur aux cellules en fonction d’une formule de complexité arbitraire. L’objet ConditionalFormatRule vous permet de définir la formule dans des notations différentes :

• formula -Notation standard.
• formulaLocal - Localisé en fonction de la langue de l’utilisateur.
• formulaR1C1 -Notation type L1C1.

L’exemple suivant colorie les polices en vert pour les cellules dont les valeurs sont supérieures à celles de la cellule à gauche.

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    const range = sheet.getRange("B8:E13");
    const conditionalFormat = range.conditionalFormats.add(
         Excel.ConditionalFormatType.custom
    );
    
    // If a cell has a higher value than the one to its left, set that cell's font to green.
    conditionalFormat.custom.rule.formula = '=IF(B8>INDIRECT("RC[-1]",0),TRUE)';
    conditionalFormat.custom.format.font.color = "green";
    
    await context.sync();
});

Barre de données

La mise en forme conditionnelle de la barre de données ajoute des barres de données aux cellules. Par défaut, les valeurs minimale et maximale de la plage forment les limites et les tailles proportionnelles des barres de données. L’objet DataBarConditionalFormat a plusieurs propriétés pour contrôler l’apparence de la barre.

L’exemple suivant met en forme la plage contenant des barres de données remplissant de gauche à droite.

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    const range = sheet.getRange("B8:E13");
    const conditionalFormat = range.conditionalFormats.add(
         Excel.ConditionalFormatType.dataBar
    );
    
    // Give left-to-right, default-appearance data bars to all the cells.
    conditionalFormat.dataBar.barDirection = Excel.ConditionalDataBarDirection.leftToRight;
    await context.sync();
});

Jeu d’icônes

La mise en forme conditionnelle du jeu d’icônes utilise Excel icônes pour mettre en surbrillance les cellules. La criteria propriété est un tableau de ConditionalIconCriterion, qui définit le symbole à insérer et la condition d’insertion. Ce tableau préremple automatiquement les éléments critères qui ont des propriétés par défaut. Vous ne pouvez pas remplacer des propriétés individuelles. Remplacez plutôt l’ensemble de l’objet critère.

L’exemple suivant montre un jeu d’icônes trois triangles utilisé dans la plage.

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    const range = sheet.getRange("B8:E13");
    const conditionalFormat = range.conditionalFormats.add(
         Excel.ConditionalFormatType.iconSet
    );
    
    const iconSetCF = conditionalFormat.iconSet;
    iconSetCF.style = Excel.IconSet.threeTriangles;
    
    /*
       With a "three*" icon set style, such as "threeTriangles", the third
        element in the criteria array (criteria[2]) defines the "top" icon;
        e.g., a green triangle. The second (criteria[1]) defines the "middle"
        icon, The first (criteria[0]) defines the "low" icon, but it can often 
        be left empty as this method does below, because every cell that
       does not match the other two criteria always gets the low icon.
    */
    iconSetCF.criteria = [
        {},
          {
            type: Excel.ConditionalFormatIconRuleType.number,
            operator: Excel.ConditionalIconCriterionOperator.greaterThanOrEqual,
            formula: "=700"
          },
          {
            type: Excel.ConditionalFormatIconRuleType.number,
            operator: Excel.ConditionalIconCriterionOperator.greaterThanOrEqual,
            formula: "=1000"
          }
    ];

    await context.sync();
});

Critères prédéfinis

La mise en forme conditionnelle prédéfinie applique un format défini par l’utilisateur pour la plage basée sur une règle standard sélectionnée. La propriété ConditionalFormatPresetCriterion dans ConditionalPresetCriteriaRule définit ces règles.

L’exemple suivant montre comment colorer la police en blanc lorsque la valeur d’une cellule est au moins un écart type au-dessus de la moyenne de la plage.

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    const range = sheet.getRange("B2:M5");
    const conditionalFormat = range.conditionalFormats.add(
         Excel.ConditionalFormatType.presetCriteria
    );
    
    // Color every cell's font white that is one standard deviation above average relative to the range.
    conditionalFormat.preset.format.font.color = "white";
    conditionalFormat.preset.rule = {
         criterion: Excel.ConditionalFormatPresetCriterion.oneStdDevAboveAverage
    };
    
    await context.sync();
});

Comparaison de texte

La mise en forme conditionnelle de comparaison de texte utilise des comparaisons de chaînes comme condition. La rule propriété est un ConditionalTextComparisonRule qui définit une chaîne à comparer à la cellule et un opérateur pour spécifier le type de comparaison.

L’exemple suivant met en forme la couleur de police rouge lorsque le texte d’une cellule contient « Delayed ».

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    const range = sheet.getRange("B16:D18");
    const conditionalFormat = range.conditionalFormats.add(
         Excel.ConditionalFormatType.containsText
    );
    
    // Color the font of every cell containing "Delayed".
    conditionalFormat.textComparison.format.font.color = "red";
    conditionalFormat.textComparison.rule = {
         operator: Excel.ConditionalTextOperator.contains,
         text: "Delayed"
    };
    
    await context.sync();
});

Supérieure/inférieure

La mise en forme conditionnelle supérieure/inférieure applique un format aux valeurs les plus élevées ou plus faibles d’une plage. La rule propriété, de type ConditionalTopBottomRule, définit si la condition est basée sur le plus élevé ou le plus bas, ainsi que si l’évaluation est en classement ou pourcentage.

L’exemple suivant applique un surlignage vert à la cellule de valeur plus élevée dans la plage.

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    const range = sheet.getRange("B21:E23");
    const conditionalFormat = range.conditionalFormats.add(
         Excel.ConditionalFormatType.topBottom
    );
    
    // For the highest valued cell in the range, make the background green.
    conditionalFormat.topBottom.format.fill.color = "green"
    conditionalFormat.topBottom.rule = { rank: 1, type: "TopItems"}
    
    await context.sync();
});

Modifier les règles de mise en forme conditionnelle

L’objet ConditionalFormat propose plusieurs méthodes pour modifier les règles de mise en forme conditionnelle une fois que votre code les a définies.

• changeRuleToCellValue
• changeRuleToColorScale
• changeRuleToContainsText
• changeRuleToCustom
• changeRuleToDataBar
• changeRuleToIconSet
• changeRuleToPresetCriteria
• changeRuleToTopBottom

L’exemple suivant montre comment utiliser la changeRuleToPresetCriteria méthode de la liste précédente pour remplacer une règle de format conditionnel existante par le type de règle de critères prédéfinis. La plage spécifiée doit déjà avoir une règle de format conditionnel. Si la plage n’a pas de règle, les méthodes de modification n’en appliquent pas une nouvelle.

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    const range = sheet.getRange("B2:M5");
    
    // Retrieve the first existing `ConditionalFormat` rule on this range. 
    // Note: The specified range must have an existing conditional format rule.
    const conditionalFormat = range.conditionalFormats.getItemOrNullObject("0");
    
    // Change the conditional format rule to preset criteria.
    conditionalFormat.changeRuleToPresetCriteria({
        criterion: Excel.ConditionalFormatPresetCriterion.oneStdDevAboveAverage, 
    });
    conditionalFormat.preset.format.font.color = "red";
    
    await context.sync();
});

Formats multiples et priorité

Vous pouvez appliquer plusieurs mises en forme conditionnelles à une plage. Si les formats ont des éléments en conflit, tels que les couleurs de police différentes, la mise en forme s’applique uniquement à un élément particulier. La ConditionalFormat.priority propriété définit le format prioritaire. La priorité est un nombre égal à l’index dans , ConditionalFormatCollectionque vous définissez lors de la création du format. Une valeur inférieure priority signifie une priorité plus élevée.

L’exemple suivant montre un choix de couleur de police en conflit entre les deux formats. Les nombres négatifs obtiennent une police en gras, mais pas une police rouge, car la priorité va au format qui leur donne une police bleue.

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    const temperatureDataRange = sheet.tables.getItem("TemperatureTable").getDataBodyRange();
    
    
    // Set low numbers to bold, dark red font and assign priority 1.
    const presetFormat = temperatureDataRange.conditionalFormats
        .add(Excel.ConditionalFormatType.presetCriteria);
    presetFormat.preset.format.font.color = "red";
    presetFormat.preset.format.font.bold = true;
    presetFormat.preset.rule = { criterion: Excel.ConditionalFormatPresetCriterion.oneStdDevBelowAverage };
    presetFormat.priority = 1;
    
    // Set negative numbers to blue font with green background and set priority 0.
    const cellValueFormat = temperatureDataRange.conditionalFormats
        .add(Excel.ConditionalFormatType.cellValue);
    cellValueFormat.cellValue.format.font.color = "blue";
    cellValueFormat.cellValue.format.fill.color = "lightgreen";
    cellValueFormat.cellValue.rule = { formula1: "=0", operator: "LessThan" };
    cellValueFormat.priority = 0;
    
    await context.sync();
});

Formats exclusifs de mise en forme conditionnelle

La stopIfTrue propriété de ConditionalFormat empêche les mises en forme conditionnelles de priorité inférieure de s’appliquer à la plage. Lorsque votre code applique un format conditionnel avec stopIfTrue === true à une plage, aucun format conditionnel suivant ne s’applique, même si leurs détails de mise en forme ne sont pas contradictoires.

L’exemple suivant montre deux mises en forme conditionnelles ajoutées à une plage. Les nombres négatifs ont une police bleue avec un arrière-plan vert clair, que l’autre condition de format soit vraie ou non.

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    const temperatureDataRange = sheet.tables.getItem("TemperatureTable").getDataBodyRange();
    
    // Set low numbers to bold, dark red font and assign priority 1.
    const presetFormat = temperatureDataRange.conditionalFormats
        .add(Excel.ConditionalFormatType.presetCriteria);
    presetFormat.preset.format.font.color = "red";
    presetFormat.preset.format.font.bold = true;
    presetFormat.preset.rule = { criterion: Excel.ConditionalFormatPresetCriterion.oneStdDevBelowAverage };
    presetFormat.priority = 1;
    
    // Set negative numbers to blue font with green background and 
    // set priority 0, but set stopIfTrue to true, so none of the 
    // formatting of the conditional format with the higher priority
    // value will apply, not even the bolding of the font.
    const cellValueFormat = temperatureDataRange.conditionalFormats
        .add(Excel.ConditionalFormatType.cellValue);
    cellValueFormat.cellValue.format.font.color = "blue";
    cellValueFormat.cellValue.format.fill.color = "lightgreen";
    cellValueFormat.cellValue.rule = { formula1: "=0", operator: "LessThan" };
    cellValueFormat.priority = 0;
    cellValueFormat.stopIfTrue = true;
    
    await context.sync();
});

Effacer les règles de mise en forme conditionnelle

Pour supprimer les propriétés de format d’une règle de format conditionnel spécifique, utilisez la méthode clearFormat de l’objet ConditionalRangeFormat . La clearFormat méthode crée une règle de mise en forme sans paramètres de format.

Pour supprimer toutes les règles de mise en forme conditionnelle d’une plage spécifique ou d’une feuille de calcul entière, utilisez la méthode clearAll de l’objet ConditionalFormatCollection .

L’exemple suivant montre comment supprimer toutes les mises en forme conditionnelles d’une feuille de calcul à l’aide de la clearAll méthode .

await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getItem("Sample");
    const range = sheet.getRange();
    range.conditionalFormats.clearAll();

    await context.sync();
});

Voir aussi

• Définir le format de plage à l’aide de l’API JavaScript Excel
• Ajout de validation des données à des plages Excel
• Concepts principaux du modèle objet Excel pour les compléments Office
• Informations de référence sur l’objet ConditionalFormat
• Ajouter, modifier ou effacer des formats conditionnels
• Utiliser une formule pour appliquer une mise en forme conditionnelle dans Excel