如何添加文本条件格式

可能的使用场景

在电子表格中使用基于文本的条件格式有助于突出显示符合特定文本条件的单元格。这可以改善数据分析并使在大量数据中查找关键信息变得更容易。以下是使用文本条件格式的一些原因:

  1. 高亮特定文本:可以根据特定词语、短语或字符应用格式。例如,您可能希望突出显示所有包含“紧急”或“已完成”的单元格,以便轻松区分任务。
  2. 识别模式或趋势:如果您处理类别或状态(如“高”、“中”、“低”),文本条件格式可以直观地区分它们,使跟踪进度或优先任务变得更容易。
  3. 错误或数据录入提醒:文本格式可以标记不一致或错误的输入,如拼写错误、不完整的文本或错误的值。这在大量文本输入的数据集中尤其有用。
  4. 提升可读性:使用颜色编码或改变文本样式(加粗、斜体等)有助于突出重要信息,提升整体可读性。
  5. 动态反馈:可以设置规则,当文本符合某些条件时自动调整格式。这意味着随着数据变化,无需手动更新格式。

本质上,文本条件格式帮助您快速发现相关信息、错误和趋势,是管理和解释文本数据的强大工具。

如何使用Excel添加文本条件格式

在Excel中添加基于文本的条件格式,请按照以下步骤操作:

  1. 选择单元格范围:突出显示您希望应用条件格式的单元格。
  2. 打开条件格式菜单:在Excel功能区的“开始”标签中,点击“条件格式”。
  3. 选择“新建规则”:在下拉菜单中选择“新建规则”。
  4. 选择“只格式化包含特定内容的单元格”:在新格式规则对话框中,选择“只格式化满足以下条件的单元格”,在“选择规则类型”部分。
  5. 设置规则条件:在“格式单元格值”部分,从下拉菜单选择“特定文本”。根据你要应用的条件,选择“包含”、“以…开始”或“以…结束”。输入你要格式化的文本(例如,“紧急”或“已完成”)。
  6. 选择格式:点击“格式”按钮。在“设置单元格格式”对话框中,可以选择字体颜色、填充颜色或其他格式设置。
  7. 应用规则:设置好所需格式后,点击“确定”以应用规则。在新格式规则对话框中再次点击“确定”以关闭。
  8. 查看结果:包含指定文本的单元格现在会应用对应的格式,方便快速识别相关信息。

如何使用编号Aspose.Cells for .NET添加文本条件格式

Aspose.Cells完全支持在运行时对XLSX格式的Excel中提供的条件格式,包括高级条件格式类型如BeginsWith、ContainsBlank、ContainsText等。

当单元格值以特定文本开头时进行格式设置

// This method implements the BeginsWith conditional formatting type.
private void AddBeginWith()
{
// Instantiate a workbook object
Workbook book = new Workbook();
// Create a worksheet object and get the first worksheet
Worksheet _sheet = book.Worksheets[0];
FormatConditionCollection conds = GetFormatCondition("E15:G16", Color.LightGoldenrodYellow, _sheet);
int idx = conds.AddCondition(FormatConditionType.BeginsWith);
FormatCondition cond = conds[idx];
cond.Style.BackgroundColor = Color.Pink;
cond.Style.Pattern = BackgroundType.Solid;
cond.Text = "ab";
Cell c = _sheet.Cells["E15"];
c.PutValue("abc");
c = _sheet.Cells["G16"];
c.PutValue("babx");
book.Save("BeginsWith.xlsx");
}
// 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;
}

当单元格值包含空白时进行格式设置

// This method implements the ContainsBlank conditional formatting type.
private void AddContainsBlank()
{
// Instantiate a workbook object
Workbook book = new Workbook();
// Create a worksheet object and get the first worksheet
Worksheet _sheet = book.Worksheets[0];
FormatConditionCollection conds = GetFormatCondition("E9:G10", Color.LightBlue, _sheet);
int idx = conds.AddCondition(FormatConditionType.ContainsBlanks);
FormatCondition cond = conds[idx];
cond.Style.BackgroundColor = Color.Yellow;
cond.Style.Pattern = BackgroundType.Solid;
Cell c = _sheet.Cells["E9"];
c.PutValue(" ");
c = _sheet.Cells["G10"];
c.PutValue(" ");
book.Save("ContainsBlank.xlsx");
}

当单元格值包含错误时进行格式设置

// This method implements the ContainsErrors conditional formatting type.
private void AddContainsErrors()
{
// Instantiate a workbook object
Workbook book = new Workbook();
// Create a worksheet object and get the first worksheet
Worksheet _sheet = book.Worksheets[0];
FormatConditionCollection conds = GetFormatCondition("E17:G18", Color.LightSkyBlue, _sheet);
int idx = conds.AddCondition(FormatConditionType.ContainsErrors);
FormatCondition cond = conds[idx];
cond.Style.BackgroundColor = Color.Yellow;
cond.Style.Pattern = BackgroundType.Solid;
Cell c = _sheet.Cells["E17"];
c.PutValue(" ");
c = _sheet.Cells["G18"];
c.PutValue(" ");
book.Save("ContainsErrors.xlsx");
}

当单元格值包含特定文本时进行格式设置

// This method implements the ContainsText conditional formatting type.
private void AddContainsText()
{
// Instantiate a workbook object
Workbook book = new Workbook();
// Create a worksheet object and get the first worksheet
Worksheet _sheet = book.Worksheets[0];
FormatConditionCollection conds = GetFormatCondition("E5:G6", Color.LightBlue, _sheet);
int idx = conds.AddCondition(FormatConditionType.ContainsText);
FormatCondition cond = conds[idx];
cond.Style.BackgroundColor = Color.Yellow;
cond.Style.Pattern = BackgroundType.Solid;
cond.Text = "1";
book.Save("ContainsText.xlsx");
}

当单元格值包含重复值时进行格式设置

// This method implements the DuplicateValues conditional formatting type.
private void AddDuplicateValues()
{
// Instantiate a workbook object
Workbook book = new Workbook();
// Create a worksheet object and get the first worksheet
Worksheet _sheet = book.Worksheets[0];
FormatConditionCollection conds = GetFormatCondition("E23:G24", Color.LightSlateGray, _sheet);
int idx = conds.AddCondition(FormatConditionType.DuplicateValues);
FormatCondition cond = conds[idx];
cond.Style.BackgroundColor = Color.Pink;
cond.Style.Pattern = BackgroundType.Solid;
Cell c = _sheet.Cells["E23"];
c.PutValue("bb");
c = _sheet.Cells["G24"];
c.PutValue("bb");
book.Save("DuplicateValues.xlsx");
}

当单元格值以特定文本结尾时进行格式设置

// This method implements the EndsWith conditional formatting type.
private void AddEndWith()
{
// Instantiate a workbook object
Workbook book = new Workbook();
// Create a worksheet object and get the first worksheet
Worksheet _sheet = book.Worksheets[0];
FormatConditionCollection conds = GetFormatCondition("E13:G14", Color.LightGray, _sheet);
int idx = conds.AddCondition(FormatConditionType.EndsWith);
FormatCondition cond = conds[idx];
cond.Style.BackgroundColor = Color.Yellow;
cond.Style.Pattern = BackgroundType.Solid;
cond.Text = "ab";
Cell c = _sheet.Cells["E13"];
c.PutValue("nnnab");
c = _sheet.Cells["G14"];
c.PutValue("mmmabc");
book.Save("EndsWith.xlsx");
}

当单元格值不为空白时进行格式设置

// This method implements the NotContainsBlank conditional formatting type.
private void AddNotContainsBlank()
{
// Instantiate a workbook object
Workbook book = new Workbook();
// Create a worksheet object and get the first worksheet
Worksheet _sheet = book.Worksheets[0];
FormatConditionCollection conds = GetFormatCondition("E11:G12", Color.LightCoral, _sheet);
int idx = conds.AddCondition(FormatConditionType.NotContainsBlanks);
FormatCondition cond = conds[idx];
cond.Style.BackgroundColor = Color.Pink;
cond.Style.Pattern = BackgroundType.Solid;
Cell c = _sheet.Cells["E11"];
c.PutValue("abc");
c = _sheet.Cells["G12"];
c.PutValue(" ");
book.Save("NotContainsBlank.xlsx");
}

当单元格值不包含错误时进行格式设置

// This method implements the NotContainsErrors conditional formatting type.
private void AddNotContainsErrors()
{
// Instantiate a workbook object
Workbook book = new Workbook();
// Create a worksheet object and get the first worksheet
Worksheet _sheet = book.Worksheets[0];
FormatConditionCollection conds = GetFormatCondition("E19:G20", Color.LightSeaGreen, _sheet);
int idx = conds.AddCondition(FormatConditionType.NotContainsErrors);
FormatCondition cond = conds[idx];
cond.Style.BackgroundColor = Color.Pink;
cond.Style.Pattern = BackgroundType.Solid;
Cell c = _sheet.Cells["E19"];
c.PutValue(" ");
c = _sheet.Cells["G20"];
c.PutValue(" ");
book.Save("NotContainsErrors.xlsx");
}

当单元格值不包含特定文本时进行格式设置

// This method implements the NotContainsText conditional formatting type.
private void AddNotContainsText()
{
// Instantiate a workbook object
Workbook book = new Workbook();
// Create a worksheet object and get the first worksheet
Worksheet _sheet = book.Worksheets[0];
FormatConditionCollection conds = GetFormatCondition("E7:G8", Color.LightCoral, _sheet);
int idx = conds.AddCondition(FormatConditionType.NotContainsText);
FormatCondition cond = conds[idx];
cond.Style.BackgroundColor = Color.Pink;
cond.Style.Pattern = BackgroundType.Solid;
cond.Text = "3";
book.Save("NotContainsText.xlsx");
}

当单元格值包含唯一值时进行格式设置

// This method implements the UniqueValues conditional formatting type.
private void AddUniqueValues()
{
// Instantiate a workbook object
Workbook book = new Workbook();
// Create a worksheet object and get the first worksheet
Worksheet _sheet = book.Worksheets[0];
FormatConditionCollection conds = GetFormatCondition("E21:G22", Color.LightSalmon, _sheet);
int idx = conds.AddCondition(FormatConditionType.UniqueValues);
FormatCondition cond = conds[idx];
cond.Style.BackgroundColor = Color.Yellow;
cond.Style.Pattern = BackgroundType.Solid;
Cell c = _sheet.Cells["E21"];
c.PutValue("aa");
c = _sheet.Cells["G22"];
c.PutValue("aa");
book.Save("UniqueValues.xlsx");
}