Automatically Highlight Data Validation Errors Inside Excel Files in Python

import openpyxl
from openpyxl.styles import PatternFill
from pathlib import Path

def highlight_validation_errors(filepath: str, output_path: str = None):
    wb = openpyxl.load_workbook(filepath)
    red_fill = PatternFill(start_color="FF0000", end_color="FF0000", fill_type="solid")
    
    for sheet in wb.worksheets:
        for row in sheet.iter_rows(min_row=2):  # Skip header row
            for cell in row:
                value = cell.value
                error = False
                
                # Example validation: non-null, non-negative numbers
                if value is None or value == "":
                    error = True
                elif isinstance(value, (int, float)) and value < 0:
                    error = True
                elif isinstance(value, str) and len(value.strip()) == 0:
                    error = True
                    
                if error:
                    cell.fill = red_fill
                    cell.value = f"ERROR: {value}" if value is not None else "ERROR: empty"
    
    output = output_path or filepath.replace(".xlsx", "_validated.xlsx")
    wb.save(output)
    print(f"Errors highlighted and saved to: {output}")
    return output

if __name__ == "__main__":
    import tempfile
    from openpyxl import Workbook
    
    # Create a sample workbook with errors
    test_wb = Workbook()
    ws = test_wb.active
    ws.append(["Name", "Age", "Score"])
    ws.append(["Alice", 30, 95])
    ws.append(["Bob", -5, 0])  # Negative age error
    ws.append(["", 25, 100])   # Empty name error
    ws.append(["Dave", None, 88])  # None age error
    
    sample_path = Path(tempfile.gettempdir()) / "test_errors.xlsx"
    test_wb.save(sample_path)
    
    highlight_validation_errors(str(sample_path))