Come aggiungere la formattazione condizionale con set di icone

Possibili Scenari di Utilizzo

Usare la formattazione condizionale con set di icone in Excel è un ottimo modo per visualizzare le tendenze o le categorie dei dati a colpo d’occhio usando simboli come frecce, semafori, stelle, bandiere e altro. Aggiunge un livello di chiarezza al tuo foglio di calcolo senza richiedere grafici o analisi approfondite.

1. Intuizioni visive istantanee: le icone rendono molto facile vedere quali valori sono alti, medi o bassi senza leggere ogni numero. Ottimo per dashboard, KPI e monitoraggio delle prestazioni.
2. Facile individuazione delle tendenze: le frecce mostrano se i valori aumentano, diminuiscono o rimangono neutrali. Semafori o forme aiutano a mostrare lo stato o l’urgenza.
3. Aspetto professionale: rende i report più curati e pronti per la presentazione. Aiuta gli spettatori non tecnici a capire i dati rapidamente.
4. Dinamico & automatico: si aggiorna automaticamente quando i valori cambiano — niente bisogno di riformattare manualmente.

Come aggiungere la formattazione condizionale con set di icone usando Excel

Per aggiungere la formattazione condizionale con set di icone in Excel, ecco come puoi farlo passo passo:

1. Seleziona l’intervallo di dati numerici. Esempio: B2:B20 (potrebbero essere numeri di vendita, punteggi di performance, ecc.).
2. Vai alla scheda Home.
3. Clicca su Formattazione condizionale nel gruppo Stili.
4. Passa sopra Icon Sets.
5. Scegli uno stile di icona: Frecce, Semafori, Stelle, ecc.
6. Le icone appariranno in base alla distribuzione dei valori di default: Icona verde = top 67%, Icona gialla = medio 33–67%, Icona rossa = bottom 33%.

Come aggiungere set di icone con formattazione condizionale usando Aspose.Cells for .NET

Aspose.Cells supporta pienamente la formattazione condizionale fornita da Microsoft Excel 2007 e versioni successive in formato XLSX sulle celle durante l’esecuzione. Questo esempio dimostra un esercizio di formattazione condizionale con set di attributi diversi.

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