86 lines
2.9 KiB
Python
86 lines
2.9 KiB
Python
import pandas as pd
|
|
from openpyxl import load_workbook
|
|
from openpyxl.styles import PatternFill, Font
|
|
import sys
|
|
import os
|
|
import io
|
|
from pathlib import Path
|
|
|
|
# Fix Windows console encoding
|
|
if sys.platform == 'win32':
|
|
sys.stdout = io.TextIOWrapper(sys.stdout.buffer, encoding='utf-8')
|
|
|
|
def post_process_file(target_file, mapping_file):
|
|
print(f"🎨 Starting Post-Processing for: {target_file}")
|
|
|
|
# 1. Load Mappings
|
|
if not os.path.exists(mapping_file):
|
|
print(f"❌ Mapping file not found: {mapping_file}")
|
|
return
|
|
|
|
map_df = pd.read_excel(mapping_file)
|
|
# columns: code, Type, tag
|
|
|
|
omission_codes = set(map_df[map_df['Type'].str.lower() == 'omission']['code'].astype(str).tolist())
|
|
reverse_codes = set(map_df[map_df['tag'].str.lower() == 'reverse-scoring item']['code'].astype(str).tolist())
|
|
|
|
print(f"📊 Mapping loaded: {len(omission_codes)} Omission items, {len(reverse_codes)} Reverse items")
|
|
|
|
# 2. Load Target Workbook
|
|
if not os.path.exists(target_file):
|
|
print(f"❌ Target file not found: {target_file}")
|
|
return
|
|
|
|
wb = load_workbook(target_file)
|
|
ws = wb.active
|
|
|
|
# Define Styles (Text Color)
|
|
green_font = Font(color="008000") # Dark Green text
|
|
red_font = Font(color="FF0000") # Bright Red text
|
|
|
|
# 3. Process Columns
|
|
# header row is 1
|
|
headers = [cell.value for cell in ws[1]]
|
|
|
|
modified_cols = 0
|
|
for col_idx, header in enumerate(headers, start=1):
|
|
if not header:
|
|
continue
|
|
|
|
header_str = str(header).strip()
|
|
|
|
target_font = None
|
|
|
|
# Priority: Red (Reverse) > Green (Omission)
|
|
if header_str in reverse_codes:
|
|
target_font = red_font
|
|
print(f" 🚩 Marking header {header_str} text as RED (Reverse)")
|
|
elif header_str in omission_codes:
|
|
target_font = green_font
|
|
print(f" 🟢 Marking header {header_str} text as GREEN (Omission)")
|
|
|
|
if target_font:
|
|
# Apply ONLY to the header cell (row 1)
|
|
ws.cell(row=1, column=col_idx).font = target_font
|
|
modified_cols += 1
|
|
|
|
# Clear any existing column fills from previous runs (Clean up)
|
|
for col in range(1, ws.max_column + 1):
|
|
for row in range(2, ws.max_row + 1):
|
|
ws.cell(row=row, column=col).fill = PatternFill(fill_type=None)
|
|
|
|
# 4. Save
|
|
wb.save(target_file)
|
|
print(f"✅ Success: {modified_cols} columns formatted and file saved.")
|
|
|
|
if __name__ == "__main__":
|
|
# Default paths for the current task
|
|
DEFAULT_TARGET = r"C:\work\CP_Automation\Personality_14-17.xlsx"
|
|
DEFAULT_MAPPING = r"C:\work\CP_Automation\Simulated_Assessment_Engine\data\AllQuestions.xlsx"
|
|
|
|
# Allow command line overrides
|
|
target = sys.argv[1] if len(sys.argv) > 1 else DEFAULT_TARGET
|
|
mapping = sys.argv[2] if len(sys.argv) > 2 else DEFAULT_MAPPING
|
|
|
|
post_process_file(target, mapping)
|