如何添加Top10条件格式

可能的使用场景

在Excel中使用Top 10条件格式有助于快速突出显示数据集中表现最佳的值——不仅仅是字面意义上的前10名，而是通常包括前N个值或前N%的值（可选择！）。

1. 识别趋势和离群值：即刻识别表现最优的数据（例如：前10名销售代表、最佳成绩、最高收入月份）。无须排序数据，便于分析。
2. 数据可视化：添加颜色提示，使重要数据点直观突出。帮助表格查看者一目了然关键数值。
3. 快速对比：在仪表盘和报告中十分实用，可以突出表现优秀或达到顶峰的值。
4. 动态更新：如果数据发生变化，条件格式会自动更新以反映新的前N值。

** 如何使用Excel添加Top10条件格式**

以下是逐步在Excel中添加Top10条件格式的方法：

1. 选择你想分析的单元格范围。例如：选择B2:B100，若你在处理得分或销售额。
2. 转到Excel功能区的“开始”标签。
3. 在样式组中点击“条件格式”。
4. 将鼠标悬停在“前/后规则”中。
5. 点击“前10项…”
6. 会弹出一个对话框：内容为“设置前10名的单元格”。你可以更改数字（例如前5、前3等）。选择一种格式（如浅红色填充、加粗文本，或点击“自定义格式”获取更多选项）。
7. 点击“确定”

使用Aspose.Cells for .NET添加Top10条件格式的方法

Aspose.Cells 完全支持在XLSX格式中运行时由Microsoft Excel 2007及以后版本提供的条件格式功能。本示例演示了带有不同属性集的Top 10条件格式练习。

private void TestTop10()
{
    // Instantiate a workbook object
    Workbook book = new Workbook();
    // Create a worksheet object and get the first worksheet
    Worksheet _sheet = book.Worksheets[0];
    AddTop10_1(_sheet);
    AddTop10_2(_sheet);
    AddTop10_3(_sheet);
    AddTop10_4(_sheet);
    book.Save(filePath + "Top10.xlsx");
}
// This method implements a simple Top10 conditional formatting type.
private void AddTop10_1(Worksheet _sheet)
{
    FormatConditionCollection conds = GetFormatCondition("A17:C20", Color.Gray, _sheet);
    int idx = conds.AddCondition(FormatConditionType.Top10);
    FormatCondition cond = conds[idx];
    cond.Style.BackgroundColor = Color.Yellow;
    cond.Style.Pattern = BackgroundType.Solid;
}

// This method implements another Top10 conditional formatting type.
private void AddTop10_2(Worksheet _sheet)
{
    FormatConditionCollection conds = GetFormatCondition("A21:C24", Color.Green, _sheet);
    int idx = conds.AddCondition(FormatConditionType.Top10);
    FormatCondition cond = conds[idx];
    cond.Style.BackgroundColor = Color.Pink;
    cond.Style.Pattern = BackgroundType.Solid;

    cond.Top10.IsBottom = true;
}

// This method implements another Top10 conditional formatting type with some custom attributes.
private void AddTop10_3(Worksheet _sheet)
{
    FormatConditionCollection conds = GetFormatCondition("A25:C28", Color.Orange, _sheet);
    int idx = conds.AddCondition(FormatConditionType.Top10);
    FormatCondition cond = conds[idx];
    cond.Style.BackgroundColor = Color.Blue;
    cond.Style.Pattern = BackgroundType.Solid;

    cond.Top10.IsPercent = true;
}

// This method implements another Top10 conditional formatting type with some custom attributes.
private void AddTop10_4(Worksheet _sheet)
{
    FormatConditionCollection conds = GetFormatCondition("A29:C32", Color.Gold, _sheet);
    int idx = conds.AddCondition(FormatConditionType.Top10);
    FormatCondition cond = conds[idx];
    cond.Style.BackgroundColor = Color.Green;
    cond.Style.Pattern = BackgroundType.Solid;

    cond.Top10.Rank = 3;
}

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