So fügen Sie Icon Sets Bedingte Formatierung hinzu

Mögliche Verwendungsszenarien

Die Verwendung von Icon Sets in der bedingten Formatierung in Excel ist eine großartige Möglichkeit, Daten-Trends oder Kategorien auf einen Blick mithilfe von Symbolen wie Pfeilen, Ampeln, Sternen, Flaggen und mehr zu visualisieren. Es verleiht Ihrer Tabelle eine zusätzliche Klarheit, ohne Diagramme oder tiefgehende Analysen zu erfordern.

1. Sofortige visuelle Einblicke: Symbole machen es superleicht, zu sehen, welche Werte hoch, mittel oder niedrig sind, ohne jede Zahl lesen zu müssen. Ideal für Dashboards, KPIs und Leistungsüberwachung.
2. Einfaches Trend-Spotting: Pfeile zeigen, ob Werte steigen, fallen oder neutral bleiben. Ampeln oder Formen helfen, Status oder Dringlichkeit anzuzeigen.
3. Professionelles Aussehen: Macht Berichte polierter und präsentationsbereit. Hilft nicht-technischen Betrachtern, Daten schnell zu verstehen.
4. Dynamisch & Automatisch: Aktualisiert sich automatisch, wenn sich Werte ändern — kein manuelles Neufomattieren notwendig.

So fügen Sie Icon Sets-Bedingte Formatierung mit Excel hinzu

Um Icon Sets-Bedingte Formatierung in Excel hinzuzufügen, gehen Sie Schritt für Schritt wie folgt vor:

1. Wählen Sie Ihren Bereich mit numerischen Daten aus. Beispiel: B2:B20 (könnten Verkaufszahlen, Leistungswerte usw. sein).
2. Gehen Sie zum Start-Tab.
3. Klicken Sie auf Bedingte Formatierung im Bereich styles.
4. Bewegen Sie die Maus über Icon-Sätze.
5. Wählen Sie einen Symbolstil: Pfeile, Ampeln, Sterne usw.
6. Die Symbole erscheinen standardmäßig basierend auf der Wertverteilung: Grünes Icon = oberste 67 %, Gelbes Icon = mittlere 33–67 %, Rotes Icon = unterste 33 %.

Wie man Icon-Sets bedingte Formatierung mit Aspose.Cells for .NET hinzufügt

Aspose.Cells unterstützt vollständig die bedingte Formatierung, die von Microsoft Excel 2007 und späteren Versionen im XLSX-Format bei der Laufzeit bereitgestellt wird. Dieses Beispiel demonstriert eine Übung zur bedingten Formatierung mit Icon-Sets und verschiedenen Attributen.

private void TestIconSets()
{
    // Instantiate a workbook object
    Workbook book = new Workbook();
    // Create a worksheet object and get the first worksheet
    Worksheet _sheet = book.Worksheets[0];

    AddDefaultIconSet(_sheet);
    AddIconSet2(_sheet);
    AddIconSet3(_sheet);
    AddIconSet4(_sheet);
    AddIconSet5(_sheet);
    AddIconSet6(_sheet);
    AddIconSet7(_sheet);
    AddIconSet8(_sheet);
    AddIconSet9(_sheet);
    AddIconSet10(_sheet);
    AddIconSet11(_sheet);
    AddIconSet12(_sheet);
    AddIconSet13(_sheet);
    AddIconSet14(_sheet);
    AddIconSet15(_sheet);
    AddIconSet16(_sheet);
    AddIconSet17(_sheet);
    AddIconSet18(_sheet);

    book.Save(filePath + "IconSets.xlsx");
}

// This method implements the IconSet conditional formatting type.
private void AddDefaultIconSet(Worksheet _sheet)
{            
    FormatConditionCollection conds = GetFormatCondition("A1:C2", Color.Yellow, _sheet);
    int idx = conds.AddCondition(FormatConditionType.IconSet);
}

// This method implements the IconSet conditional formatting type with 3 Arrows Colored attribute.
private void AddIconSet2(Worksheet _sheet)
{
    FormatConditionCollection conds = GetFormatCondition("M1:O2", Color.AliceBlue, _sheet);
    int idx = conds.AddCondition(FormatConditionType.IconSet);
    FormatCondition cond = conds[idx];
    cond.IconSet.Type = IconSetType.Arrows3;
    Cell c = _sheet.Cells["M1"];
    c.PutValue("Arrows3");
}
// This method implements the IconSet conditional formatting type with 4 Arrows Colored attribute.
private void AddIconSet3(Worksheet _sheet)
{
    FormatConditionCollection conds = GetFormatCondition("M3:O4", Color.AntiqueWhite, _sheet);
    int idx = conds.AddCondition(FormatConditionType.IconSet);
    FormatCondition cond = conds[idx];
    cond.IconSet.Type = IconSetType.Arrows4;
    Cell c = _sheet.Cells["M3"];
    c.PutValue("Arrows4");

}
// This method implements the IconSet conditional formatting type with 5 Arrows Colored attribute.
private void AddIconSet4(Worksheet _sheet)
{
    FormatConditionCollection conds = GetFormatCondition("M5:O6", Color.Aqua, _sheet);
    int idx = conds.AddCondition(FormatConditionType.IconSet);
    FormatCondition cond = conds[idx];
    cond.IconSet.Type = IconSetType.Arrows5;
    Cell c = _sheet.Cells["M5"];
    c.PutValue("Arrows5");
}
// This method implements the IconSet conditional formatting type with 3 Arrows Gray attribute.
private void AddIconSet5(Worksheet _sheet)
{
    FormatConditionCollection conds = GetFormatCondition("M7:O8", Color.Aquamarine, _sheet);
    int idx = conds.AddCondition(FormatConditionType.IconSet);
    FormatCondition cond = conds[idx];
    cond.IconSet.Type = IconSetType.ArrowsGray3;
    Cell c = _sheet.Cells["M7"];
    c.PutValue("ArrowsGray3");
}
// This method implements the IconSet conditional formatting type with 4 Arrows Gray attribute.
private void AddIconSet6(Worksheet _sheet)
{
    FormatConditionCollection conds = GetFormatCondition("M9:O10", Color.Azure, _sheet);
    int idx = conds.AddCondition(FormatConditionType.IconSet);
    FormatCondition cond = conds[idx];
    cond.IconSet.Type = IconSetType.ArrowsGray4;
    Cell c = _sheet.Cells["M9"];
    c.PutValue("ArrowsGray4");
}
// This method implements the IconSet conditional formatting type with 5 Arrows Gray attribute.
private void AddIconSet7(Worksheet _sheet)
{
    FormatConditionCollection conds = GetFormatCondition("M11:O12", Color.Beige, _sheet);
    int idx = conds.AddCondition(FormatConditionType.IconSet);
    FormatCondition cond = conds[idx];
    cond.IconSet.Type = IconSetType.ArrowsGray5;
    Cell c = _sheet.Cells["M11"];
    c.PutValue("ArrowsGray5");
}
// This method implements the IconSet conditional formatting type with 3 Flags attribute.
private void AddIconSet8(Worksheet _sheet)
{
    FormatConditionCollection conds = GetFormatCondition("M13:O14", Color.Bisque, _sheet);
    int idx = conds.AddCondition(FormatConditionType.IconSet);
    FormatCondition cond = conds[idx];
    cond.IconSet.Type = IconSetType.Flags3;
    Cell c = _sheet.Cells["M13"];
    c.PutValue("Flags3");
}
// This method implements the IconSet conditional formatting type with 5 Quarters attribute.
private void AddIconSet9(Worksheet _sheet)
{
    FormatConditionCollection conds = GetFormatCondition("M15:O16", Color.BlanchedAlmond, _sheet);
    int idx = conds.AddCondition(FormatConditionType.IconSet);
    FormatCondition cond = conds[idx];
    cond.IconSet.Type = IconSetType.Quarters5;
    Cell c = _sheet.Cells["M15"];
    c.PutValue("Quarters5");
}
// This method implements the IconSet conditional formatting type with 4 Ratings attribute.
private void AddIconSet10(Worksheet _sheet)
{
    FormatConditionCollection conds = GetFormatCondition("M17:O18", Color.Blue, _sheet);
    int idx = conds.AddCondition(FormatConditionType.IconSet);
    FormatCondition cond = conds[idx];
    cond.IconSet.Type = IconSetType.Rating4;
    Cell c = _sheet.Cells["M17"];
    c.PutValue("Rating4");
}
// This method implements the IconSet conditional formatting type with 5 Ratings attribute.
private void AddIconSet11(Worksheet _sheet)
{
    FormatConditionCollection conds = GetFormatCondition("M19:O20", Color.BlueViolet, _sheet);
    int idx = conds.AddCondition(FormatConditionType.IconSet);
    FormatCondition cond = conds[idx];
    cond.IconSet.Type = IconSetType.Rating5;
    Cell c = _sheet.Cells["M19"];
    c.PutValue("Rating5");
}
// This method implements the IconSet conditional formatting type with 4 Red To Black attribute.
private void AddIconSet12(Worksheet _sheet)
{
    FormatConditionCollection conds = GetFormatCondition("M21:O22", Color.Brown, _sheet);
    int idx = conds.AddCondition(FormatConditionType.IconSet);
    FormatCondition cond = conds[idx];
    cond.IconSet.Type = IconSetType.RedToBlack4;
    Cell c = _sheet.Cells["M21"];
    c.PutValue("RedToBlack4");

}
// This method implements the IconSet conditional formatting type with 3 Signs attribute.
private void AddIconSet13(Worksheet _sheet)
{
    FormatConditionCollection conds = GetFormatCondition("M23:O24", Color.BurlyWood, _sheet);
    int idx = conds.AddCondition(FormatConditionType.IconSet);
    FormatCondition cond = conds[idx];
    cond.IconSet.Type = IconSetType.Signs3;
    Cell c = _sheet.Cells["M23"];
    c.PutValue("Signs3");
}
// This method implements the IconSet conditional formatting type with 3 Symbols attribute.
private void AddIconSet14(Worksheet _sheet)
{
    FormatConditionCollection conds = GetFormatCondition("M25:O26", Color.CadetBlue, _sheet);
    int idx = conds.AddCondition(FormatConditionType.IconSet);
    FormatCondition cond = conds[idx];
    cond.IconSet.Type = IconSetType.Symbols3;
    Cell c = _sheet.Cells["M25"];
    c.PutValue("Symbols3");
}
// This method implements the IconSet conditional formatting type with another 3 Symbols attribute.
private void AddIconSet15(Worksheet _sheet)
{
    FormatConditionCollection conds = GetFormatCondition("M27:O28", Color.Chartreuse, _sheet);
    int idx = conds.AddCondition(FormatConditionType.IconSet);
    FormatCondition cond = conds[idx];
    cond.IconSet.Type = IconSetType.Symbols32;
    Cell c = _sheet.Cells["M27"];
    c.PutValue("Symbols32");
}
// This method implements the IconSet conditional formatting type with 3 Traffic Lights attribute.
private void AddIconSet16(Worksheet _sheet)
{
    FormatConditionCollection conds = GetFormatCondition("M29:O30", Color.Chocolate, _sheet);
    int idx = conds.AddCondition(FormatConditionType.IconSet);
    FormatCondition cond = conds[idx];
    cond.IconSet.Type = IconSetType.TrafficLights31;
    Cell c = _sheet.Cells["M29"];
    c.PutValue("TrafficLights31");
}
// This method implements the IconSet conditional formatting type with another 3 Traffic Lights attribute.
private void AddIconSet17(Worksheet _sheet)
{
    FormatConditionCollection conds = GetFormatCondition("M31:O32", Color.Coral, _sheet);
    int idx = conds.AddCondition(FormatConditionType.IconSet);
    FormatCondition cond = conds[idx];
    cond.IconSet.Type = IconSetType.TrafficLights32;
    Cell c = _sheet.Cells["M31"];
    c.PutValue("TrafficLights32");
}
// This method implements the IconSet conditional formatting type with 4 Traffic Lights attribute.
private void AddIconSet18(Worksheet _sheet)
{
    FormatConditionCollection conds = GetFormatCondition("M33:O35", Color.CornflowerBlue, _sheet);
    int idx = conds.AddCondition(FormatConditionType.IconSet);
    FormatCondition cond = conds[idx];
    cond.IconSet.Type = IconSetType.TrafficLights4;
    Cell c = _sheet.Cells["M33"];
    c.PutValue("TrafficLights4");
}

// This method adds formatted conditions.
private FormatConditionCollection GetFormatCondition(string cellAreaName, Color color, Worksheet _sheet)
{
    // Adds an empty conditional formattings
    int index = _sheet.ConditionalFormattings.Add();
    // Get the formatted conditions
    FormatConditionCollection formatConditions = _sheet.ConditionalFormattings[index];
    // Get the cell area calling the custom GetCellAreaByName method
    CellArea area = GetCellAreaByName(cellAreaName);
    // Add the formatted conditions cell area.
    formatConditions.AddArea(area);
    // Call the custom FillCell method
    FillCell(cellAreaName, color, _sheet);
    // Return the formatted conditions
    return formatConditions;
}

// This method specifies the cell shading color for the conditional formattings cellarea range.
private void FillCell(string cellAreaName, Color color, Worksheet _sheet)
{
    CellArea area = GetCellAreaByName(cellAreaName);
    int k = 0;
    for (int i = area.StartColumn; i <= area.EndColumn; i++)
    {
        for (int j = area.StartRow; j <= area.EndRow; j++)
        {
            Cell c = _sheet.Cells[j, i];
            if (!color.IsEmpty)
            {
                Style s = c.GetStyle();
                s.ForegroundColor = color;
                s.Pattern = BackgroundType.Solid;
                c.SetStyle(s);
            }
            // Set some random values to the cells in the cellarea range
            int value = j + i + k;
            c.PutValue(value);
            k++;
        }
    }

}
// This method specifies the CellArea range (start row, start col, end row, end col etc.)
// For the conditional formatting
internal static CellArea GetCellAreaByName(string s)
{
    CellArea area = new CellArea();
    string[] strCellRange = s.Replace("$", "").Split(':');
    int column;
    CellsHelper.CellNameToIndex(strCellRange[0], out area.StartRow, out column);
    area.StartColumn = column;
    if (strCellRange.Length == 1)
    {
        area.EndRow = area.StartRow;
        area.EndColumn = area.StartColumn;
    }
    else
    {
        CellsHelper.CellNameToIndex(strCellRange[1], out area.EndRow, out column);
        area.EndColumn = column;
    }
    return area;
}