Apply Advanced Conditional Formatting with Python.NET

Microsoft Excel 2007 and later versions (2010/2013/2016) provide advanced conditional formatting features including cell shading, borders, colored icons, arrows, flags, and font formatting.

Implement Advanced Conditional Formatting in Excel Files

Aspose.Cells for Python via .NET supports all advanced conditional formatting features including:

• Add shaded data bars to graphically enhance the underlying numbers by embedding a simple bar chart in the cells.
• Automatically shade cells with color scales based on their relation to values in other cells in the range. The default settings shades the lowest value in red moving up to the highest value in green.
• Use icon sets in a similar way to color scales, but rather than shading the cells it adds small icons, such as arrows and traffic lights to the cells.

Aspose.Cells fully supports the conditional formatting provided by Microsoft Excel 2007 and later versions in XLSX format on cells at runtime. This example demonstrates an exercise for advanced conditional formatting types including IconSets, DataBars, Color Scales, TimePeriods, Top/Bottom and other rules with different sets of attributes.

• Adding Color Scale Conditional Formattings
• Adding Above Average Conditional Formattings
• Adding DataBars Conditional Formattings
• Adding IonSets Conditional Formattings
• Adding Text Conditional Formattings
• Adding TimePeriods Conditional Formattings
• Adding Top10 Conditional Formattings

Calculate Excel’s Color Selection for Color Scale Formatting

This code shows how to determine the color chosen by Excel for ColorScale conditional formatting rules:

import os
from aspose.cells import Workbook
from aspose.pydrawing import Color

# For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Python
current_dir = os.path.dirname(os.path.abspath(__file__))
data_dir = os.path.join(current_dir, "data")

# Instantiate a workbook object and open the template file
workbook = Workbook(os.path.join(data_dir, "Book1.xlsx"))
# Get the first worksheet
worksheet = workbook.worksheets[0]
# Get the A1 cell
a1 = worksheet.cells.get("A1")

# Get the conditional formatting resultant object
cfr1 = a1.get_conditional_formatting_result()
# Get the ColorScale resultant color object
c = cfr1.color_scale_result

# Read and print the color values
print(c.to_argb())
print(c.name)