Cómo agregar formato condicional de Conjuntos de Iconos

Escenarios de uso posibles

Usar el formato condicional de Conjuntos de Iconos en Excel es una excelente forma de visualizar tendencias o categorías de datos de un vistazo usando símbolos como flechas, semáforos, estrellas, banderas y más. Añade una capa extra de claridad a tu hoja de cálculo sin necesidad de gráficos o análisis profundo.

1. Información Visual Instantánea: Los iconos facilitan mucho ver qué valores son altos, medios o bajos sin leer cada número. Ideal para paneles, KPIs y seguimiento de rendimiento.
2. Detección de Tendencias Fácil: Las flechas muestran si los valores están aumentando, disminuyendo o manteniéndose neutrales. Los semáforos o formas ayudan a mostrar estado o urgencia.
3. Aspecto Profesional: Hace que los informes sean más pulidos y listos para la presentación. Ayuda a que los usuarios no técnicos comprendan los datos rápidamente.
4. Dinámico y Automático: Se actualiza automáticamente cuando cambian los valores — sin necesidad de reformatear manualmente.

Cómo agregar formato condicional de Conjuntos de Iconos usando Excel

Para agregar formato condicional de Conjuntos de Iconos en Excel, así es como puedes hacerlo paso a paso:

1. Selecciona tu rango de datos numéricos. Ejemplo: B2:B20 (podrían ser números de ventas, puntajes de rendimiento, etc.).
2. Ve a la pestaña Inicio.
3. Haz clic en Formato condicional en el grupo Estilos.
4. Pasa el cursor sobre Conjuntos de iconos.
5. Elige un estilo de icono: Flechas, Semáforos, Estrellas, etc.
6. Los iconos aparecerán en función de la distribución de valores por defecto: Icono verde = 67% superior, Icono amarillo = 33-67%, Icono rojo = 33% inferior.

Cómo agregar conjuntos de iconos en Formato condicional usando Aspose.Cells for .NET

Aspose.Cells admite completamente el formato condicional proporcionado por Microsoft Excel 2007 y versiones posteriores en formato XLSX en las celdas en tiempo de ejecución. Este ejemplo demuestra un ejercicio para formato condicional de conjuntos de iconos con diferentes conjuntos de atributos.

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