如何添加图标集条件格式

可能的使用场景

在Excel中使用图标集条件格式是一种一目了然地可视化数据趋势或类别的好方法，使用箭头、交通灯、星星、旗帜等符号。它为您的电子表格添加了额外的清晰度，无需图表或深入分析。

1. 即时视觉洞察：图标使得轻松识别高值、中值或低值变得非常容易，无需逐个阅读数字。非常适合仪表盘、关键绩效指标和绩效跟踪。
2. 轻松发现趋势：箭头显示数值是上升、下降还是保持中性，交通灯或形状帮助显示状态或紧急程度。
3. 专业外观：让报告更具品质，便于展示。帮助非技术人员快速理解数据。
4. 动态和自动：值变化时自动更新，无需手动重新格式化。

如何使用Excel添加图标集条件格式

在Excel中添加图标集条件格式，以下是逐步操作的方法：

1. 选择您的数值范围。例如：B2:B20（可以是销售数字、绩效分数等）。
2. 转到“开始”选项卡。
3. 在样式组中点击“条件格式”。
4. 悬停在“图标集”上。
5. 选择图标样式：箭头、交通灯、星星等。
6. 默认情况下，图标将根据值的分布显示：绿色图标=前67%，黄色图标=中间33-67%，红色图标=后33%。

如何使用Aspose.Cells for .NET添加图标集条件格式

Aspose.Cells完全支持在运行时将微软Excel 2007及更高版本的条件格式应用于XLSX格式的单元格。此示例演示不同属性集的图标集条件格式。

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