设置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")

高级主题