When working with usage statistics or analytics data in Excel, you often need to visually identify entries that fall below a certain threshold. Instead of manually scanning through hundreds of rows, Python can automate this process efficiently.

This guide demonstrates how to programmatical highlight cells containing values less than 100 in red using the openpyxl library.

Prerequisites

Ensure openpyxl is installed:

pip install openpyxl

Implementation

The solution involves loading an existing Excel workbook, iterating through target cells, and applying conditional formatting based on cell values.

from openpyxl import load_workbook
from openpyxl.styles import Font

# Load the workbook
data_file = load_workbook('./功能使用量统计.xlsx')
sheet = data_file.active

# Create a font style with red text
red_font = Font(color='FF0000')

# Iterate through cells starting from row 2, column 2
# This skips the header row and the first column containing labels
for row_cells in sheet.iter_rows(min_row=2, min_col=2):
    for individual_cell in row_cells:
        # Check if the cell contains a numeric value below threshold
        if isinstance(individual_cell.value, (int, float)):
            if individual_cell.value < 100:
                individual_cell.font = red_font

# Save the modified workbook
data_file.save('功能使用量统计.xlsx')
data_file.close()

How It Works

1. Import statements: load_workbook handles file I/O operations, while Font from the styles module enables text formatting.

2. File loading: The load_workbook() function opens the Excel file and returns a workbook object. The active property retrieves the currently selected worksheet.

3. Font configuration: The Font class accepts color parameters in hex format. Specifying 'FF0000' produces bright red text.

4. Cell iteration: The iter_rows() method with min_row=2 and min_col=2 parameters exculdes the header row and the first column containing category labels, processing only the numeric data cells.

5. Conditional styling: Before applying formatting, the code verifies the cell contains a numeric value using isinstance(). This prevents errors when encountering empty cells or text data.

6. File operations: After modifications, save() writes changes back to disk, and close() releases system resources.

Result

All numeric values below 100 in the spreadsheet now appear in red, allowing for immediate visual identification of underperforming metrics or usage anomalies.