Scripts/export_xlsx.py

"""Export all experiment results to Excel."""
import os, json, numpy as np
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.utils import get_column_letter

PROJECT_DIR='/home/user/goat'
os.chdir(PROJECT_DIR)

wb = Workbook()
header_font = Font(bold=True, color='FFFFFF', size=11)
header_fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')
green_fill = PatternFill(start_color='C6EFCE', end_color='C6EFCE', fill_type='solid')
yellow_fill = PatternFill(start_color='FFEB9C', end_color='FFEB9C', fill_type='solid')
red_fill = PatternFill(start_color='FFC7CE', end_color='FFC7CE', fill_type='solid')

# ── Sheet 1: Training Validation ──
ws1 = wb.active
ws1.title = "Training Validation"

headers = [
    ('#', '序号', 5),
    ('Experiment', '实验名称', 35),
    ('Best Ep', '最佳Epoch', 8),
    ('Total Ep', '总Epoch', 8),
    ('mAP50 ↑', '越高越好', 10),
    ('mAP50-95 ↑', '越高越好', 12),
    ('Precision ↑', '越高越好', 10),
    ('Recall ↑', '越高越好', 10),
    ('Box Loss ↓', '越低越好', 10),
    ('Cls Loss ↓', '越低越好', 10),
    ('DFL Loss ↓', '越低越好', 10),
    ('Val BoxL ↓', '越低越好', 12),
    ('Val ClsL ↓', '越低越好', 12),
    ('Val DflL ↓', '越低越好', 12),
    ('Category', '类别', 14),
    ('Note', '备注', 25),
]

for col, (key, desc, w) in enumerate(headers, 1):
    cell = ws1.cell(row=1, column=col, value=desc)
    cell.font = header_font
    cell.fill = header_fill
    cell.alignment = Alignment(horizontal='center', wrap_text=True)
    ws1.column_dimensions[get_column_letter(col)].width = w

exp = 'runs/detect/Detection_experiments'
results = []

for ename in sorted(os.listdir(exp)):
    csv_path = f'{exp}/{ename}/results.csv'
    if not os.path.exists(csv_path): continue
    with open(csv_path) as f: lines = f.readlines()
    if len(lines) < 2: continue
    h = [s.strip() for s in lines[0].split(',')]
    try:
        idxs = {col: h.index(col) for col in h}
    except: continue

    i5095 = h.index('metrics/mAP50-95(B)')
    best_idx = 0; best_v = 0
    for i, line in enumerate(lines[1:]):
        try:
            v = float(line.strip().split(',')[i5095])
            if v > best_v: best_v = v; best_idx = i + 1
        except: pass

    cols = lines[best_idx].strip().split(',')
    def g(col):
        try:
            if col in idxs: return float(cols[idxs[col]])
            return '-'
        except: return '-'

    name = ename
    n = name.lower()
    if 'seed' in n: cat = 'seed'
    elif 'distilled' in n: cat = 'distilled'
    elif 'whatif' in n: cat = 'whatif'
    elif 'eastleft' in n: cat = 'eastleft'
    elif 'westright' in n: cat = 'westright'
    elif 'yolo11m' in n: cat = 'yolo11m'
    elif 'yolo11n' in n: cat = 'yolo11n'
    elif 'gmm' in n: cat = 'GMM'
    elif 'selfchallenge' in n: cat = 'self-challenge'
    elif 'mask_refined' in n: cat = 'mask'
    elif 'expanded' in n: cat = 'expanded'
    elif 'final_v3' in n: cat = 'label_v3'
    elif 'sc3' in n: cat = 'SC3'
    elif 'p2_s' in n: cat = 'P2_head'
    elif 'baseline' in n: cat = 'baseline'
    else: cat = 'other'

    mAP_val = g('metrics/mAP50-95(B)')
    note = ''
    if isinstance(mAP_val, float):
        if mAP_val >= 0.513: note = 'BEST'
        elif mAP_val >= 0.511: note = 'GOOD'
        elif mAP_val < 0.50: note = 'FAIL'

    results.append((
        ename, best_idx, len(lines)-1,
        g('metrics/mAP50(B)'),
        mAP_val,
        g('metrics/precision(B)'),
        g('metrics/recall(B)'),
        g('train/box_loss'),
        g('train/cls_loss'),
        g('train/dfl_loss'),
        g('val/box_loss'),
        g('val/cls_loss'),
        g('val/dfl_loss'),
        cat, note,
    ))

results.sort(key=lambda x: x[4] if isinstance(x[4], (int, float)) else 0, reverse=True)

for i, r in enumerate(results):
    row = i + 2
    ws1.cell(row=row, column=1, value=i+1)
    for j, val in enumerate(r):
        cell = ws1.cell(row=row, column=j+2, value=val if val != '-' else '-')
        if j in [3, 4, 5, 6] and isinstance(val, float):
            cell.number_format = '0.0000'
            if val >= 0.96: cell.fill = green_fill
            elif val >= 0.51 and j == 4: cell.fill = yellow_fill
        elif j in [7, 8, 9, 10, 11, 12] and isinstance(val, float):
            cell.number_format = '0.0000'

# ── Sheet 2: WBF Ensemble ──
ws2 = wb.create_sheet("WBF Ensemble")
wbf_headers = [
    ('Method', '方法', 35), ('mAP50-95 ↑', '越高越好', 14),
    ('IoU@75 ↑', '越高越好', 12), ('Delta', '相对基线提升', 16),
    ('N Models', '模型数', 10), ('N Sources', '预测源数', 10), ('Note', '备注', 30),
]
for col, (key, desc, w) in enumerate(wbf_headers, 1):
    cell = ws2.cell(row=1, column=col, value=desc)
    cell.font = header_font; cell.fill = header_fill
    cell.alignment = Alignment(horizontal='center', wrap_text=True)
    ws2.column_dimensions[get_column_letter(col)].width = w

wbf_data = [
    ('v6_1 Single (训练验证)', 0.5125, 0.4900, '-', 1, 1, '训练验证标准基准'),
    ('v6_1 Single (eval框架)', 0.5521, 0.5789, '—', 1, 1, 'Eval框架基准'),
    ('1 Model x 3 Scales WBF', 0.5626, 0.6003, '+0.010', 1, 3, '单模型多尺度'),
    ('5 Checkpoint Ensemble', 0.5660, None, '+0.014', 1, 5, '单模型5cp WBF'),
    ('1m Kitchen Sink (12x)', 0.5691, 0.6055, '+0.017', 1, 12, '单模型×12变体'),
    ('5 Models x 3 Scales WBF', 0.5776, 0.6169, '+0.025', 5, 15, ''),
    ('Kitchen Sink 60x (5m)', 0.5816, 0.6203, '+0.030', 5, 60, '5模型×12变体'),
    ('KS 7 Models', 0.5872, 0.6215, '+0.035', 7, 84, '含yolo11n'),
    ('KS 10 Models', 0.5880, 0.6266, '+0.036', 10, 120, '10模型×12变体'),
    ('KS 13 Models BEST', 0.5888, 0.6266, '+0.037', 13, 156, '当前最佳WBF'),
    ('Mask Weighted WBF', 0.5690, None, '+0.017', 1, 12, '掩码加权→微弱'),
    ('Prior WBF', 0.5689, None, '-0.0002', 1, 12, '位置先验→无效'),
]
for i, row_data in enumerate(wbf_data):
    for j, val in enumerate(row_data):
        cell = ws2.cell(row=i+2, column=j+1, value=val if val is not None else '-')
        if j == 1 and isinstance(val, float):
            cell.number_format = '0.0000'
            if val >= 0.58: cell.fill = green_fill
        elif j == 2 and isinstance(val, float):
            cell.number_format = '0.0000'

# ── Sheet 3: Per-Camera ──
ws3 = wb.create_sheet("Per-Camera")
cam_headers = [
    ('Method', '方法', 30), ('EastLeft ↑', '东左(越高越好)', 14),
    ('EastRight ↑', '东右(越高越好)', 14), ('WestLeft ↑', '西左(越高越好)', 14),
    ('WestRight ↑', '西右(越高越好)', 14), ('Overall ↑', '总体(越高越好)', 12), ('Note', '备注', 25),
]
for col, (key, desc, w) in enumerate(cam_headers, 1):
    cell = ws3.cell(row=1, column=col, value=desc)
    cell.font = header_font; cell.fill = header_fill
    cell.alignment = Alignment(horizontal='center', wrap_text=True)
    ws3.column_dimensions[get_column_letter(col)].width = w

cam_data = [
    ('v6_1 Single', 0.5240, 0.6082, 0.5465, 0.5399, 0.5521, '基准'),
    ('v19_eastleft (特化)', 0.5283, 0.6049, 0.5469, 0.5396, 0.5526, 'EastLeft +0.0043'),
    ('KS 7 Models', 0.5602, 0.6299, 0.5772, 0.5690, 0.5820, 'WBF全面提升'),
    ('KS 10 Models', None, None, None, None, 0.5880, ''),
    ('KS 13 Models BEST', None, None, None, None, 0.5888, '当前最佳'),
]
for i, row_data in enumerate(cam_data):
    for j, val in enumerate(row_data):
        cell = ws3.cell(row=i+2, column=j+1, value=val if val is not None else '-')
        if isinstance(val, float):
            cell.number_format = '0.0000'

# ── Sheet 4: Idea Progress ──
ws4 = wb.create_sheet("Idea Progress")
idea_headers = [('Idea', '思路', 35), ('Status', '状态', 12), ('Best Result', '最佳结果', 18), ('Note', '备注', 40)]
for col, (key, desc, w) in enumerate(idea_headers, 1):
    cell = ws4.cell(row=1, column=col, value=desc)
    cell.font = header_font; cell.fill = header_fill
    ws4.column_dimensions[get_column_letter(col)].width = w

ideas = [
    ('WBF Multi-Model Ensemble', 'WORKS', '+0.037 (13m)', '最有效路径'),
    ('Multi-Scale + Aug WBF', 'WORKS', '+0.010', '免费推理增强'),
    ('Per-Camera Adapt WBF', 'WORKS', 'EastLeft +0.036', '逐机位阈值+尺度'),
    ('Per-Camera Specialized', 'WORKS', 'Best single 0.5134', 'EastLeft oversampling'),
    ('Cross-Validation', 'WORKS', '0.552 +/-0.011', '确认真实mAP'),
    ('Checkpoint Ensemble', 'WORKS', '+0.014', '单模型5cp免费'),
    ('Knowledge Distillation', 'DEAD', '0.5018', 'TTA自蒸馏退化'),
    ('What-If Augmentation', 'DEAD', '0.5067', '亚像素增强'),
    ('BRN Boundary Refinement', 'DEAD', '退化', '合成噪声不匹配'),
    ('Model Soup', 'DEAD', '崩溃', 'BN不兼容'),
    ('Snake/Edge Refinement', 'DEAD', '退化', '山羊毛边界模糊'),
    ('GWD/WIoU/InnerIoU Loss', 'DEAD', '持平/退化', '改不动天花板'),
    ('Label Refine v2/v3', 'DEAD', '退化', '引入新偏差'),
    ('Auto-Labeling v13', 'DEAD', '0.5094', '伪标签上限=老师'),
    ('Mask-based Label Refine', 'DEAD', '0.4927', '掩码质量不够'),
    ('Position-Size Prior', 'DEAD', '-0.0002', '模型已校准'),
    ('Self-Challenge Training', 'RUNNING', '0.5084', '聚焦边界案例'),
    ('GMM Wasserstein Loss', 'RUNNING', '0.4925', '多高斯建模山羊'),
    ('WestRight Specialized', 'RUNNING', '0.4435', '刚启动epoch5'),
    ('Background Diff Segment', 'READY', '掩码已生成', '待训分割头'),
    ('Attention Probe', 'TODO', '-', '内部特征定位'),
    ('Contrastive Cross-Camera', 'TODO', '-', '跨机位不变特征'),
    ('Iterative Denoising Detect', 'TODO', '-', '多轮渐进精修'),
    ('Orthogonal Error Experts', 'TODO', '-', '刻意制造互补'),
    ('Stereo Geometry', 'TODO', '-', '双机位立体约束'),
    ('Iterative Consensus v4', 'TODO', '-', '打破标注天花板'),
]
for i, (idea, status, result, note) in enumerate(ideas):
    for j, val in enumerate([idea, status, result, note]):
        cell = ws4.cell(row=i+2, column=j+1, value=val)
        if status == 'WORKS': cell.fill = green_fill
        elif status == 'DEAD': cell.fill = red_fill
        elif status == 'RUNNING': cell.fill = yellow_fill

# ── Sheet 5: Legend ──
ws5 = wb.create_sheet("Legend")
ws5.column_dimensions['A'].width = 20
ws5.column_dimensions['B'].width = 50
ws5.cell(row=1, column=1, value='指标').font = Font(bold=True)
ws5.cell(row=1, column=2, value='说明').font = Font(bold=True)
legend = [
    ('mAP50', 'IoU=0.5时的平均精度,衡量检测能力. 越高越好. 当前最佳: 0.9616'),
    ('mAP50-95', 'IoU=0.5到0.95的平均精度,衡量定位精度. 越高越好. 当前单模型最佳: 0.5134, WBF最佳: 0.5888'),
    ('Precision', '预测框中真正是羊的比例. 越高越好.'),
    ('Recall', '真羊中被检出的比例. 越高越好.'),
    ('Box Loss', '训练时边界框回归损失. 越低越好. 反映模型画框的准确度.'),
    ('Cls Loss', '训练时分类损失. 越低越好. 反映模型分辨羊/背景的能力.'),
    ('DFL Loss', 'Distribution Focal Loss. 越低越好. 反映框边缘分布的精确度.'),
    ('Val Box/Cls/DFL Loss', '验证集上的对应损失. 越低越好. 训练损失高+验证损失高=欠拟合, 训练低+验证高=过拟合.'),
    ('WBF', 'Weighted Box Fusion: 多模型多尺度预测的加权融合.'),
    ('IoU@75', 'IoU阈值0.75时的召回率. 越高越好. 反映高精度定位能力.'),
    ('★ 两个mAP口径', '训练验证mAP(0.5125)≠ Eval框架mAP(0.5521). 前者是ultralytics批处理评估,后者是单图推理评估.'),
]
for i, (k, v) in enumerate(legend):
    ws5.cell(row=i+3, column=1, value=k)
    ws5.cell(row=i+3, column=2, value=v)

path = 'logs/DairyGoat_All_Results.xlsx'
wb.save(path)
print(f'Saved: {path}')
print(f'Sheets: {wb.sheetnames}')