excel_report.py

#!/usr/bin/env python3
"""
Export author publication data to Excel.
- One "summary" sheet per author: profile metrics, date accessed, citation-by-year
- One "papers"  sheet per author: title, venue, year, total cites, status, per-year citations

Post-cutoff papers (published after the cutoff year and not fetched in phase 2)
are explicitly marked "not fetched (post-cutoff)" with only the total citation
count shown — no per-year breakdown.

Usage (standalone — normally run automatically by historical_hindex.py):
    python excel_report.py
    python excel_report.py --output-dir reports --out publications.xlsx
"""

import argparse
import json
import os
import re

from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, PatternFill
from openpyxl.utils import get_column_letter


HEADER_FONT = Font(bold=True)
HEADER_FILL = PatternFill(start_color="D9E1F2", end_color="D9E1F2", fill_type="solid")
MUTED_FONT = Font(italic=True, color="808080")
BOLD_FONT = Font(bold=True)


def make_sheet_name(name, suffix="", max_len=31):
    clean = re.sub(r'[^\w\s\-]', '', name).strip()
    if suffix:
        clean = clean[:max_len - len(suffix) - 1] + " " + suffix
    return clean[:max_len]


def get_all_years(pubs):
    years = set()
    for pub in pubs:
        cpy = pub.get("cites_per_year")
        if cpy and isinstance(cpy, dict):
            for y in cpy.keys():
                try:
                    years.add(int(y))
                except ValueError:
                    pass
    return sorted(years)


def _paper_status(pub, cutoff_year):
    """Return (status_label, show_per_year)."""
    cpy = pub.get("cites_per_year")
    year = pub.get("year")
    if cpy and isinstance(cpy, dict) and cpy:
        return ("", True)
    if pub.get("total_cites_now", 0) == 0 and cpy == {}:
        return ("", True)  # no citations — empty dict is correct
    if year is not None and year > cutoff_year and cpy is None:
        return ("not fetched (post-cutoff)", False)
    return ("pending", False)


def add_papers_sheet(wb, report):
    name = report["name"]
    pubs = report["publications"]
    cutoff = report.get("cutoff_year", 0)

    ws = wb.create_sheet(title=make_sheet_name(name, "papers"))

    all_years = get_all_years(pubs)
    headers = ["Title", "Venue", "Pub Year", "Total Cites",
               f"Cites@{cutoff}", "Status"] + [str(y) for y in all_years]

    for col, header in enumerate(headers, 1):
        cell = ws.cell(row=1, column=col, value=header)
        cell.font = HEADER_FONT
        cell.fill = HEADER_FILL

    pubs_sorted = sorted(pubs, key=lambda p: (p.get("year") or cutoff), reverse=True)

    for row_idx, pub in enumerate(pubs_sorted, 2):
        status, show_per_year = _paper_status(pub, cutoff)

        ws.cell(row=row_idx, column=1, value=pub.get("title", ""))
        ws.cell(row=row_idx, column=2, value=pub.get("venue", ""))
        ws.cell(row=row_idx, column=3, value=pub.get("year"))
        ws.cell(row=row_idx, column=4, value=pub.get("total_cites_now", 0))

        if show_per_year:
            ws.cell(row=row_idx, column=5, value=pub.get("cites_at_cutoff"))
        else:
            cell = ws.cell(row=row_idx, column=5, value="n/a")
            cell.font = MUTED_FONT

        status_cell = ws.cell(row=row_idx, column=6, value=status)
        if status:
            status_cell.font = MUTED_FONT

        if show_per_year:
            cpy = pub.get("cites_per_year") or {}
            for i, y in enumerate(all_years):
                val = cpy.get(str(y), cpy.get(y, None))
                if val is not None:
                    ws.cell(row=row_idx, column=7 + i, value=val)

    ws.column_dimensions["A"].width = 70
    ws.column_dimensions["B"].width = 45
    ws.column_dimensions["C"].width = 9
    ws.column_dimensions["D"].width = 11
    ws.column_dimensions["E"].width = 12
    ws.column_dimensions["F"].width = 24
    for i in range(len(all_years)):
        ws.column_dimensions[get_column_letter(7 + i)].width = 7

    for row in ws.iter_rows(min_row=2, max_col=2):
        for cell in row:
            cell.alignment = Alignment(wrap_text=True, vertical="top")

    ws.freeze_panes = "A2"
    return ws


def add_summary_sheet(wb, report):
    name = report["name"]
    cutoff = report.get("cutoff_year", 0)

    ws = wb.create_sheet(title=make_sheet_name(name, "summary"))

    def kv(row, label, value):
        ws.cell(row=row, column=1, value=label).font = BOLD_FONT
        ws.cell(row=row, column=2, value=value)

    kv(1, "Author", name)
    kv(2, "Scholar ID", report.get("scholar_id", ""))
    kv(3, "Cutoff Year", cutoff)
    kv(4, "Date Accessed", report.get("date_accessed", ""))

    current = report.get("current", {})
    at_cutoff = report.get("at_cutoff", {})

    row = 6
    ws.cell(row=row, column=1, value="Metric").font = HEADER_FONT
    ws.cell(row=row, column=2, value="Current").font = HEADER_FONT
    ws.cell(row=row, column=3, value=f"At {cutoff}").font = HEADER_FONT
    for c in range(1, 4):
        ws.cell(row=row, column=c).fill = HEADER_FILL

    ws.cell(row=7, column=1, value="H-index")
    ws.cell(row=7, column=2, value=report.get("h_index_from_profile") or current.get("h_index"))
    ws.cell(row=7, column=3, value=at_cutoff.get("h_index"))

    ws.cell(row=8, column=1, value="Total Citations")
    ws.cell(row=8, column=2, value=current.get("total_citations"))
    ws.cell(row=8, column=3, value=at_cutoff.get("total_citations"))

    ws.cell(row=9, column=1, value="Publications")
    ws.cell(row=9, column=2, value=current.get("total_publications"))
    ws.cell(row=9, column=3, value=at_cutoff.get("publications_by_cutoff"))

    cites_by_year = report.get("total_cites_by_year", {})
    if cites_by_year:
        ws.cell(row=11, column=1, value="Total Citations by Year").font = BOLD_FONT
        ws.cell(row=12, column=1, value="Year").font = HEADER_FONT
        ws.cell(row=12, column=2, value="Citations").font = HEADER_FONT
        ws.cell(row=12, column=1).fill = HEADER_FILL
        ws.cell(row=12, column=2).fill = HEADER_FILL
        for i, (y, c) in enumerate(sorted(cites_by_year.items(), key=lambda x: int(x[0]))):
            ws.cell(row=13 + i, column=1, value=int(y))
            ws.cell(row=13 + i, column=2, value=c)

    ws.column_dimensions["A"].width = 25
    ws.column_dimensions["B"].width = 18
    ws.column_dimensions["C"].width = 18
    return ws


def export(output_dir="reports", out="publications.xlsx"):
    reports = []
    for fname in sorted(os.listdir(output_dir)):
        if fname.endswith(".json") and fname != "summary.json":
            with open(os.path.join(output_dir, fname)) as f:
                reports.append(json.load(f))

    reports = [r for r in reports if r.get("publications")]
    if not reports:
        print(f"No author reports with publications in {output_dir}/", flush=True)
        return None

    wb = Workbook()
    wb.remove(wb.active)

    for report in reports:
        add_summary_sheet(wb, report)
        add_papers_sheet(wb, report)

    wb.save(out)
    print(f"Saved {out} ({len(wb.sheetnames)} sheets, {len(reports)} authors)",
          flush=True)
    return out


def main():
    parser = argparse.ArgumentParser(description="Export publications to Excel")
    parser.add_argument("--output-dir", default="reports")
    parser.add_argument("--out", default="publications.xlsx")
    args = parser.parse_args()
    export(args.output_dir, args.out)


if __name__ == "__main__":
    main()