设置Excel和ODS文件的条件格式。

介绍

条件格式是Microsoft Excel的高级功能，允许您对单元格或一系列单元格应用格式，并根据单元格的数值或公式的值更改格式。例如，只有当单元格的值大于500时，该单元格才会显示为粗体。当单元格的值满足条件时，会将指定的格式应用到单元格。如果单元格的值不符合格式条件，则使用单元格的默认格式。在Microsoft Excel中，选择格式，然后选择条件格式以打开条件格式对话框。

Aspose.Cells for Python via .NET支持在运行时向单元格应用条件格式。本文将介绍如何操作，也会说明Excel的颜色尺度条件格式使用的颜色是如何计算的。

应用条件格式

Aspose.Cells for Python via .NET支持多种条件格式：

• 使用设计师电子表格
• 使用复制方法。
• 在运行时创建条件格式。

使用设计者电子表格

开发者可以创建一个包含条件格式的设计者电子表格，然后用Aspose.Cells for Python via .NET打开该电子表格。Aspose.Cells for Python via .NET会加载和保存设计者电子表格，并保留任何条件格式设置。

使用复制方法

Aspose.Cells for Python via .NET 允许开发者通过调用 Range.copy() 方法将条件格式设置从一个单元格复制到工作表中的另一个单元格。

from aspose.cells import Workbook

#  For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
#  The path to the documents directory.
dataDir = RunExamples.GetDataDir(".")
#  Creating a file stream containing the Excel file to be opened
fstream = open(dataDir + "Book1.xlsx", "rb")
#  Opening the Excel file through the file stream
workbook = Workbook(fstream)
#  Accessing the first worksheet in the Excel file
worksheet = workbook.worksheets[0]
#  Copying conditional format settings from cell "A1" to cell "B1"
# worksheet.CopyConditionalFormatting(0, 0, 0, 1);
TotalRowCount = 0
for i in range(len(workbook.worksheets)):
    sourceSheet = workbook.worksheets[i]
    sourceRange = sourceSheet.cells.max_display_range
    destRange = worksheet.cells.create_range(sourceRange.first_row + TotalRowCount, sourceRange.first_column, sourceRange.row_count, sourceRange.column_count)
    destRange.copy(sourceRange)
    TotalRowCount = sourceRange.row_count + TotalRowCount
#  Saving the modified Excel file
workbook.save(dataDir + "output.xls")
#  Closing the file stream to free all resources
fstream.close()

在运行时应用条件格式

Aspose.Cells for Python via .NET 让您在运行时添加和删除条件格式。下面的示例展示了如何设置条件格式：

1. 实例化一个工作簿。
2. 添加一个空的条件格式。
3. 设置应用格式的范围。
4. 定义格式条件。
5. 保存文件。

在此示例之后还有许多其他小示例，演示如何应用字体设置、边框设置和图案。

Microsoft Excel 2007 添加了更高级的条件格式，Aspose.Cells for Python via .NET 也支持。这里列出的示例说明了如何使用简单格式，Microsoft Excel 2007 示例显示了如何应用更复杂的条件格式。

from aspose.cells import CellArea, FormatConditionType, OperatorType, Workbook
from aspose.pydrawing import Color

#  For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
#  The path to the documents directory.
dataDir = RunExamples.GetDataDir(".")
filePath = dataDir + "Book1.xlsx"
#  Instantiating a Workbook object
workbook = Workbook()
sheet = workbook.worksheets[0]
#  Adds an empty conditional formatting
index = sheet.conditional_formattings.add()
fcs = sheet.conditional_formattings[index]
#  Sets the conditional format range.
ca = CellArea()
ca.start_row = 0
ca.end_row = 0
ca.start_column = 0
ca.end_column = 0
fcs.add_area(ca)
ca = CellArea()
ca.start_row = 1
ca.end_row = 1
ca.start_column = 1
ca.end_column = 1
fcs.add_area(ca)
#  Adds condition.
conditionIndex = fcs.add_condition(FormatConditionType.CELL_VALUE, OperatorType.BETWEEN, "=A2", "100")
#  Adds condition.
conditionIndex2 = fcs.add_condition(FormatConditionType.CELL_VALUE, OperatorType.BETWEEN, "50", "100")
#  Sets the background color.
fc = fcs[conditionIndex]
fc.style.background_color = Color.red
#  Saving the Excel file
workbook.save(dataDir + "output.xls")

设置字体

from aspose.cells import SaveFormat, Workbook
from os import os, path

#  For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
#  The path to the documents directory.
dataDir = RunExamples.GetDataDir(".")
#  Create directory if it is not already present.
IsExists = path.isdir(dataDir)
if notIsExists:
    os.makedirs(dataDir)
#  Instantiating a Workbook object
workbook = Workbook()
#  Adding a new worksheet to the Excel object
i = workbook.worksheets.add()
#  Obtaining the reference of the newly added worksheet by passing its sheet index
worksheet = workbook.worksheets[i]
#  Accessing the "A1" cell from the worksheet
cell = worksheet.cells.get("A1")
#  Adding some value to the "A1" cell
cell.put_value("Hello Aspose!")
#  Obtaining the style of the cell
style = cell.get_style()
#  Setting the font weight to bold
style.font.is_bold = True
#  Applying the style to the cell
cell.set_style(style)
#  Saving the Excel file
workbook.save(dataDir + "book1.out.xls", SaveFormat.EXCEL_97_TO_2003)

您只能更改字体样式、文字颜色、下划线样式和删除线样式。

设置边框

from aspose.cells import BorderType, CellArea, CellBorderType, FormatConditionType, OperatorType, Workbook
from aspose.pydrawing import Color

#  For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
#  The path to the documents directory.
dataDir = RunExamples.GetDataDir(".")
#  Instantiating a Workbook object
workbook = Workbook()
sheet = workbook.worksheets[0]
#  Adds an empty conditional formatting
index = sheet.conditional_formattings.add()
fcs = sheet.conditional_formattings[index]
#  Sets the conditional format range.
ca = CellArea()
ca.start_row = 0
ca.end_row = 5
ca.start_column = 0
ca.end_column = 3
fcs.add_area(ca)
#  Adds condition.
conditionIndex = fcs.add_condition(FormatConditionType.CELL_VALUE, OperatorType.BETWEEN, "50", "100")
#  Sets the background color.
fc = fcs[conditionIndex]
fc.style.borders.get(BorderType.LEFT_BORDER).line_style = CellBorderType.DASHED
fc.style.borders.get(BorderType.RIGHT_BORDER).line_style = CellBorderType.DASHED
fc.style.borders.get(BorderType.TOP_BORDER).line_style = CellBorderType.DASHED
fc.style.borders.get(BorderType.BOTTOM_BORDER).line_style = CellBorderType.DASHED
fc.style.borders.get(BorderType.LEFT_BORDER).color = Color.from_argb(0, 255, 255)
fc.style.borders.get(BorderType.RIGHT_BORDER).color = Color.from_argb(0, 255, 255)
fc.style.borders.get(BorderType.TOP_BORDER).color = Color.from_argb(0, 255, 255)
fc.style.borders.get(BorderType.BOTTOM_BORDER).color = Color.from_argb(255, 255, 0)
workbook.save(dataDir + "output.xlsx")

您只能使用细线样式来设置轮廓边框，不允许使用对角线。

设置填充

from aspose.cells import BackgroundType, CellArea, FormatConditionType, OperatorType, Workbook
from aspose.pydrawing import Color

#  For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
#  The path to the documents directory.
dataDir = RunExamples.GetDataDir(".")
#  Instantiating a Workbook object
workbook = Workbook()
sheet = workbook.worksheets[0]
#  Adds an empty conditional formatting
index = sheet.conditional_formattings.add()
fcs = sheet.conditional_formattings[index]
#  Sets the conditional format range.
ca = CellArea()
ca.start_row = 0
ca.end_row = 5
ca.start_column = 0
ca.end_column = 3
fcs.add_area(ca)
#  Adds condition.
conditionIndex = fcs.add_condition(FormatConditionType.CELL_VALUE, OperatorType.BETWEEN, "50", "100")
fc = fcs[conditionIndex]
fc.style.pattern = BackgroundType.REVERSE_DIAGONAL_STRIPE
fc.style.foreground_color = Color.from_argb(255, 255, 0)
fc.style.background_color = Color.from_argb(0, 255, 255)
workbook.save(dataDir + "output.xlsx")

高级主题

• 添加2色阶和3色阶条件格式
• 应用工作表中的条件格式
• 使用条件格式为交替的行和列添加阴影
• 生成条件格式设置数据条图像
• 获取在条件格式设置中使用的图标集、数据条或颜色刻度对象