Anwenden von bedingter Formatierung auf Excel-Bereiche mithilfe der Excel-JavaScript-API

Verwenden Sie bedingte Formatierung, wenn Ihr Excel-Add-In verzögerte Bestellungen kennzeichnen, negative Werte hervorheben oder Trends visualisieren muss, ohne Zellwerte zu ändern. In diesem Artikel wird gezeigt, wie Sie allgemeine Regeltypen erstellen, vorhandene Regeln aktualisieren, die Regelpriorität steuern und Regeln löschen. Hintergrundinformationen zur integrierten bedingten Formatierung in Excel finden Sie unter Verwenden der bedingten Formatierung zum Hervorheben von Informationen in Excel und Verwenden einer Formel zum Anwenden von bedingter Formatierung in Excel.

Wichtige Punkte

• Verwenden Sie Range.conditionalFormats , um Regeln für die bedingte Formatierung für einen Bereich zu erstellen und zu verwalten.
• Jedes ConditionalFormat Objekt kann nur einen Regeltyp verwenden, z cellValue. B. , colorScaleoder iconSet.
• Verwenden Sie priority und stopIfTrue , um zu steuern, wie mehrere Regeln im gleichen Bereich interagieren.
• Verwenden Sie clearFormat oder clearAll , um Formatierungsdetails oder ganze Regeln zu entfernen.

Programmgesteuerte Kontrolle von bedingter Formatierung

Die Range.conditionalFormats-Eigenschaft stellt eine Sammlung von ConditionalFormat-Objekten dar, die für den Bereich gelten. Das ConditionalFormat -Objekt enthält Eigenschaften, die das anzuwendende Format basierend auf ConditionalFormatType definieren.

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

Jeder dieser Formatierungseigenschaften ist eine entsprechende *OrNullObject-Variante zugeordnet. Weitere Informationen zu diesem Muster finden Sie unter *OrNullObject-Methoden.

Sie können nur einen Formattyp für ein ConditionalFormat Objekt festlegen. Die type -Eigenschaft, bei der es sich um einen ConditionalFormatType-Enumerationswert handelt, bestimmt den Formattyp. Wird festgelegt type , wenn Sie einem Bereich ein bedingtes Format hinzufügen.

Erstellen allgemeiner Regeln für die bedingte Formatierung

Fügen Sie einem Bereich bedingte Formate hinzu, indem Sie verwenden conditionalFormats.add. Nachdem Sie ein bedingtes Format hinzugefügt haben, legen Sie die spezifischen Eigenschaften für dieses Format fest. Die folgenden Szenarien zeigen allgemeine Regeltypen, die Sie an Ihr Arbeitsblatt anpassen können.

Zellwert

Bedingte Zellwertformatierung wendet ein benutzerdefiniertes Format auf der Grundlage der Ergebnisse einer oder zweier Formeln in der ConditionalCellValueRule an. Die operator -Eigenschaft ist ein ConditionalCellValueOperator , der definiert, wie die resultierenden Ausdrücke mit der Formatierung zusammenhängen.

Das folgende Beispiel zeigt die Anwendung von roter Schriftfarbe auf jeden Wert im Bereich, der kleiner als null ist.

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

Farbskala

Bedingte Farbskalenformatierung wendet einen Farbverlauf auf einen Datenbereich an. Die criteria-Eigenschaft von ColorScaleConditionalFormat definiert drei ConditionalColorScaleCriterion: minimum, maximum und optional midpoint. Jeder der Kriterienskalierungspunkte verfügt über drei Eigenschaften:

• color: Der HTML-Farbcode für den Endpunkt.
• formula: Eine Zahl oder Formel, die den Endpunkt darstellt. Dieser Wert ist null , wenn type oder highestValueistlowestValue.
• type: Weise, in der die Formel ausgewertet werden soll. highestValue und lowestValue verweisen auf Werte im zu formatierenden Bereich.

Das folgende Beispiel zeigt einen Bereich, der von Blau über Gelb bis hin zu Rot eingefärbt ist. Beachten Sie, dass minimum und maximum die niedrigsten bzw. höchsten Werte sind und null-Formeln verwenden. midpoint verwendet den percentage -Typ mit einer Formel von "=50" , sodass die gelbste Zelle der Mittelwert ist.

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

Custom

Benutzerdefinierte bedingte Formatierung wendet auf der Grundlage einer Formel beliebiger Komplexität ein benutzerdefiniertes Format auf Zellen an. Mithilfe des ConditionalFormatRule-Objekts können Sie die Formel in verschiedenen Notationen definieren:

• formula: Standardnotation.
• formulaLocal – Lokalisiert basierend auf der Sprache des Benutzers.
• formulaR1C1: Notation im R1C1-Format.

Im folgenden Beispiel werden die Schriftarten für Zellen mit höheren Werten als in der linken Zelle grün farbig farbig.

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

Datenbalken

Bedingte Formatierung mit Datenbalken fügt den Zellen Datenbalken hinzu. Standardmäßig bilden die minimalen und maximalen Werte im Bereich die Begrenzungen und proportionalen Größen der Datenbalken. Das DataBarConditionalFormat -Objekt verfügt über mehrere Eigenschaften, um die Darstellung der Leiste zu steuern.

Im folgenden Beispiel wird der Bereich mit Datenbalken formatiert, die von links nach rechts aufgefüllt werden.

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

Symbolsatz

Bedingte Formatierung mit einem Symbolsatz verwendet Excel-Symbole zum Hervorheben von Zellen. Die criteria -Eigenschaft ist ein Array von ConditionalIconCriterion, das das einzufügende Symbol und die Bedingung für das Einfügen definiert. Dieses Array wird automatisch mit Kriterienelementen mit Standardeigenschaften im Voraus aufgefüllt. Einzelne Eigenschaften können nicht überschrieben werden. Ersetzen Sie stattdessen das gesamte Criteria-Objekt.

Das folgende Beispiel zeigt einen Symbolsatz aus drei Dreiecken, die über einen Bereich angewendet werden.

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

Vordefinierte Kriterien

Vordefinierte bedingte Formatierung wendet ein benutzerdefiniertes Format auf der Grundlage einer ausgewählten Standardregel auf den Bereich an. Die ConditionalFormatPresetCriterion in ConditionalPresetCriteriaRule definiert diese Regeln.

Im folgenden Beispiel wird die Schriftart weiß farbig, wenn der Wert einer Zelle mindestens eine Standardabweichung über dem Mittelwert des Bereichs liegt.

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

Textvergleich

Bedingte Textvergleichsformatierung verwendet Vergleiche von Zeichenfolgen als Bedingung. Die rule -Eigenschaft ist eine ConditionalTextComparisonRule , die eine Zeichenfolge definiert, die mit der Zelle verglichen werden soll, und einen Operator zum Angeben des Vergleichstyps.

Im folgenden Beispiel wird die Schriftfarbe Rot formatiert, wenn der Text einer Zelle "Delayed" enthält.

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

Oberster/unterster

Die bedingte Formatierung für den obersten/untersten Wert wendet ein Format auf die höchsten oder niedrigsten Werte in einem Bereich an. Die rule-Eigenschaft, die den Typ ConditionalTopBottomRule aufweist, legt fest, ob die Bedingungen auf dem höchsten oder dem niedrigsten Wert basiert, und außerdem, ob die Auswertung nach Rangstufe oder Prozentsatz erfolgt.

Das folgende Beispiel wendet eine grüne Hervorhebung auf die Zelle mit dem höchsten Wert im Bereich an.

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

Ändern von Regeln für die bedingte Formatierung

Das ConditionalFormat -Objekt bietet mehrere Methoden, um Regeln für die bedingte Formatierung zu ändern, nachdem der Code sie festgelegt hat.

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

Das folgende Beispiel zeigt, wie Sie die changeRuleToPresetCriteria -Methode aus der vorherigen Liste verwenden, um eine vorhandene Regel für das bedingte Format in den voreingestellten Regeltyp kriterien zu ändern. Der angegebene Bereich muss bereits über eine bedingte Formatregel verfügen. Wenn der Bereich keine Regel enthält, wenden die Änderungsmethoden keine neue an.

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

Mehrere Formate und Priorität

Sie können auf einen Bereich mehrere bedingte Formate anwenden. Wenn die Formate miteinander im Konflikt stehende Elemente aufweisen, wie etwa verschiedene Schriftfarben, wird auf das betreffende Element nur ein Format angewendet. Die ConditionalFormat.priority -Eigenschaft definiert, welches Format Vorrang hat. Priorität ist eine Zahl, die dem Index in entspricht ConditionalFormatCollection, und Sie legen sie beim Erstellen des Formats fest. Ein niedrigerer priority Wert bedeutet eine höhere Priorität.

Das folgende Beispiel zeigt die Wahl von im Konflikt stehenden Schriftfarben zwischen zwei Formaten. Negative Zahlen erhalten eine fett formatierte Schriftart, aber keine rote Schriftart, da die Priorität auf das Format geht, das ihnen eine blaue Schriftart verleiht.

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

Sich gegenseitig ausschließende bedingte Formate

Die stopIfTrue-Eigenschaft von ConditionalFormat verhindert die Anwendung von bedingten Formaten mit geringerer Priorität auf den Bereich. Wenn Ihr Code ein bedingtes Format mit stopIfTrue === true auf einen Bereich anwendet, gelten keine nachfolgenden bedingten Formate, auch wenn ihre Formatierungsdetails nicht widersprüchlich sind.

Das folgende Beispiel zeigt zwei bedingte Formate, die einem Bereich hinzugefügt werden. Negative Zahlen weisen eine blaue Schriftart mit hellgrünem Hintergrund auf, unabhängig davon, ob die andere Formatbedingung zutrifft.

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

Regeln für die bedingte Formatierung löschen

Um Formateigenschaften aus einer bestimmten Bedingungsformatregel zu entfernen, verwenden Sie die clearFormat-Methode des ConditionalRangeFormat -Objekts. Die clearFormat -Methode erstellt eine Formatierungsregel ohne Formateinstellungen.

Um alle Regeln für die bedingte Formatierung aus einem bestimmten Bereich oder einem gesamten Arbeitsblatt zu entfernen, verwenden Sie die clearAll-Methode des ConditionalFormatCollection -Objekts.

Im folgenden Beispiel wird gezeigt, wie Sie die gesamte bedingte Formatierung aus einem Arbeitsblatt mithilfe der clearAll -Methode entfernen.

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

    await context.sync();
});

Siehe auch

• Festlegen des Bereichsformats mithilfe der Excel-JavaScript-API
• Hinzufügen einer Datenüberprüfung zu Excel-Bereichen
• Grundlegende Excel-Objektmodellkonzepte für Office-Add-Ins
• Referenz zum ConditionalFormat-Objekt
• Hinzufügen, Ändern, Suchen oder Löschen bedingter Formate
• Verwenden einer Formel zum Anwenden von bedingter Formatierung in Excel