Appliquer une mise en forme conditionnelle à des plages Excel
La bibliothèque JavaScript Excel fournit des API pour appliquer une mise en forme conditionnelle aux plages de données dans vos feuilles de calcul. Cette fonctionnalité simplifie l’analyse visuelle de grands ensembles de données. La mise en forme effectue également des mises à jour dynamiques en fonction des changements dans la plage.
Remarque
Cet article décrit la mise en forme conditionnelle dans le contexte de compléments Excel JavaScript. Les articles suivants offrent des informations détaillées sur les fonctionnalités de mise en forme conditionnelles complètes dans Excel.
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’ConditionalFormat objet contient plusieurs propriétés qui définissent le format à appliquer en fonction du ConditionalFormatType.
cellValuecolorScalecustomdataBariconSetpresettextComparisontopBottom
Remarque
Chacune de ces propriétés de mise en forme a une variante correspondante*OrNullObject. En savoir plus sur ce modèle dans la section* OrNullObject méthodes.
Un seul type de format peut être défini pour l’objet ConditionalFormat. Cela est déterminé par la type propriété, c'est-à-dire une ConditionalFormatType valeur enum. type est défini lorsque vous ajoutez une mise en forme conditionnelle à une plage.
Créer des règles de mise en forme conditionnelle
Les mises en forme conditionnelles sont ajoutées à une plage à l’aide de conditionalFormats.add. Une fois ajoutées, vous pouvez définir les propriétés spécifiques à la mise en forme conditionnelle. Les exemples ci-dessous montrent la création de différents types de mise en forme.
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. Laoperator propriété est un ConditionalCellValueOperator définissant comment les expressions qui en résultent sont liées à 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. Les critères des points d’échelle ont 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. Il s’agit denullsitypeestlowestValueouhighestValue.type-Comment la formule doit être évaluée.highestValueetlowestValuefont 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" donc la cellule jaune est 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 colore les polices de cellules avec des valeurs supérieures à la cellule située à leur gauche en vert.
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 minimales et maximales dans 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 une matrice de ConditionalIconCriterion, qui définit le symbole à insérer et la condition sous laquelle celui-ci est inséré. Ce tableau est automatiquement pré-rempli avec éléments critères avec les propriétés par défaut. Les propriétés individuelles ne peut pas être remplacées. Au lieu de cela, l’ensemble de l’objet de critères doit être remplacé.
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. Ces règles sont définies par le ConditionalFormatPresetCriterion dans le ConditionalPresetCriteriaRule.
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 définissant une chaîne à comparer avec 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 qu’elles ont été 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.
Remarque
La plage spécifiée doit avoir une règle de format conditionnel existante pour utiliser les méthodes de modification. Si la plage spécifiée n’a pas de règle de format conditionnel, les méthodes de modification n’appliquent pas de nouvelle règle.
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 priorité est définie par laConditionalFormat.priority propriété. La priorité est un nombre (égal à l’index dans le ConditionalFormatCollection) et peut être définie lorsque vous créez le format. Plus la valeur est priority faible, plus la priorité du format est élevée.
L’exemple suivant montre un choix de couleur de police en conflit entre les deux formats. Les nombres négatifs recevront une police en gras, mais pas une police rouge, car la priorité se porte sur le format leur donnant 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. Lorsqu’une plage correspondant à la mise en forme conditionnelle avec stopIfTrue === true est appliquée, aucune mise en forme conditionnelle suivante n’est appliquée, même si ses 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 aura une police bleue avec un arrière-plan vert léger, quelle que soit la condition de l’autre format.
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 avec 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();
});