#!/usr/bin/env python3
"""Find the text in an .xlsx workbook that a translator may miss or break.

Usage:
  python3 check_xlsx_text.py workbook.xlsx
      List where the workbook keeps its text, grouped by what usually
      happens to it in a cell-by-cell translation.

  python3 check_xlsx_text.py --compare source.xlsx translated.xlsx
      Compare a translated copy with its source: formulas, numbers, codes,
      dropdown values, and sheet names.

Needs only Python 3. Reads the files and never changes them.
"""
import re
import sys
import zipfile
import posixpath
import xml.etree.ElementTree as ET
from collections import Counter

NS = {
    "m": "http://schemas.openxmlformats.org/spreadsheetml/2006/main",
    "r": "http://schemas.openxmlformats.org/officeDocument/2006/relationships",
    "rel": "http://schemas.openxmlformats.org/package/2006/relationships",
    "a": "http://schemas.openxmlformats.org/drawingml/2006/main",
}
R_ID = "{%s}id" % NS["r"]
FORMULA_STRING = re.compile(r'"((?:[^"]|"")+)"')
LETTER = re.compile(r"[^\W\d_]")
DIGIT = re.compile(r"\d")


def is_code_like(text):
    """Short tokens with letters and digits and no spaces, such as A-77 or SKU001."""
    t = text.strip()
    return (" " not in t and len(t) <= 24 and LETTER.search(t)
            and DIGIT.search(t)) or "@" in t or "://" in t


def has_letters(text):
    return bool(LETTER.search(text or ""))


def read_xml(z, name):
    try:
        return ET.fromstring(z.read(name))
    except KeyError:
        return None


def rels_for(z, part):
    folder, base = posixpath.split(part)
    root = read_xml(z, posixpath.join(folder, "_rels", base + ".rels"))
    out = {}
    if root is not None:
        for rel in root.findall("rel:Relationship", NS):
            target = rel.get("Target")
            if rel.get("TargetMode") != "External":
                target = posixpath.normpath(posixpath.join(folder, target)).lstrip("/")
            out[rel.get("Id")] = (rel.get("Type", "").rsplit("/", 1)[-1], target)
    return out


def shared_strings(z):
    root = read_xml(z, "xl/sharedStrings.xml")
    items = []
    if root is not None:
        for si in root.findall("m:si", NS):
            text = "".join(t.text or "" for t in si.iter("{%s}t" % NS["m"])
                           if not _in_phonetic(si, t))
            items.append((text, si.find("m:r", NS) is not None))
    return items


def _in_phonetic(si, t):
    return any(t in list(rph.iter()) for rph in si.findall("m:rPh", NS))


def load(path):
    z = zipfile.ZipFile(path)
    wb = read_xml(z, "xl/workbook.xml")
    wb_rels = rels_for(z, "xl/workbook.xml")
    sst = shared_strings(z)
    sheets = []
    for s in wb.findall("m:sheets/m:sheet", NS):
        kind, target = wb_rels[s.get(R_ID)]
        if kind != "worksheet":
            continue
        root = read_xml(z, target)
        cells = {}
        for c in root.iter("{%s}c" % NS["m"]):
            ref, t = c.get("r"), c.get("t", "n")
            f, v, inline = c.find("m:f", NS), c.find("m:v", NS), c.find("m:is", NS)
            cell = {"formula": None, "text": None, "rich": False, "number": None}
            if f is not None:
                cell["formula"] = f.text or "(shared)"
            if t == "s" and v is not None:
                cell["text"], cell["rich"] = sst[int(v.text)]
            elif t == "inlineStr" and inline is not None:
                cell["text"] = "".join(x.text or "" for x in inline.iter("{%s}t" % NS["m"]))
                cell["rich"] = inline.find("m:r", NS) is not None
            elif t == "str" and v is not None:
                cell["cached"] = v.text
            elif t == "n" and v is not None:
                cell["number"] = v.text
            cells[ref] = cell
        links = {h.get("ref") for h in root.iter("{%s}hyperlink" % NS["m"])}
        lists = []
        for dv in root.iter("{%s}dataValidation" % NS["m"]):
            f1 = dv.find("m:formula1", NS)
            if dv.get("type") == "list" and f1 is not None and f1.text:
                lists.append((dv.get("sqref", ""), f1.text))
        hf = root.find("m:headerFooter", NS)
        hf_text = [x.text for x in (hf if hf is not None else []) if x.text]
        parts = rels_for(z, target)
        sheets.append({"name": s.get("name"), "cells": cells, "links": links,
                       "lists": lists, "headerFooter": hf_text, "parts": parts})
    return z, wb, sheets


def expand(sqref):
    for rng in sqref.split():
        a, _, b = rng.partition(":")
        b = b or a
        ca, ra = re.match(r"([A-Z]+)(\d+)", a).groups()
        cb, rb = re.match(r"([A-Z]+)(\d+)", b).groups()
        for col in range(col_num(ca), col_num(cb) + 1):
            for row in range(int(ra), int(rb) + 1):
                yield col_name(col) + str(row)


def col_num(s):
    n = 0
    for ch in s:
        n = n * 26 + ord(ch) - 64
    return n


def col_name(n):
    s = ""
    while n:
        n, r = divmod(n - 1, 26)
        s = chr(65 + r) + s
    return s


def drawing_text(z, sheets):
    found = Counter()
    seen = set()
    for sh in sheets:
        for kind, target in sh["parts"].values():
            if kind == "drawing" and target not in seen:
                seen.add(target)
                root = read_xml(z, target)
                if root is not None:
                    found["text boxes and shapes"] += sum(
                        1 for t in root.iter("{%s}t" % NS["a"]) if has_letters(t.text))
                for k2, t2 in rels_for(z, target).values():
                    if k2 == "chart":
                        c = read_xml(z, t2)
                        if c is not None:
                            found["chart titles and labels"] += sum(
                                1 for el in c.iter() if el.tag.endswith("}v") or el.tag.endswith("}t")
                                if has_letters(el.text))
    return found


def inventory(path):
    z, wb, sheets = load(path)
    rows = Counter()
    details = []
    for sh in sheets:
        name = sh["name"]
        for ref, c in sh["cells"].items():
            where = f"{name}!{ref}"
            if c["formula"]:
                for lit in FORMULA_STRING.findall(c["formula"]):
                    if has_letters(lit):
                        rows[("formula text", "text inside a formula")] += 1
                        details.append((where, "formula text", lit.replace('""', '"')))
                continue
            text = c["text"]
            if text is None or not has_letters(text):
                continue
            if ref in sh["links"]:
                rows[("cell", "link text")] += 1
                details.append((where, "link text", text))
            elif c["rich"]:
                rows[("cell", "mixed formatting")] += 1
                details.append((where, "mixed formatting", text))
            elif is_code_like(text):
                rows[("cell", "code, ID or address")] += 1
                details.append((where, "code, ID or address", text))
            else:
                rows[("cell", "plain text")] += 1
        for sqref, f1 in sh["lists"]:
            if f1.startswith('"'):
                rows[("dropdown", "list of allowed values")] += 1
                details.append((f"{name}!{sqref}", "dropdown values", f1.strip('"')))
            else:
                rows[("dropdown", "list from a cell range")] += 1
                details.append((f"{name}!{sqref}", "dropdown source", f1))
        for h in sh["headerFooter"]:
            rows[("page setup", "header or footer")] += 1
            details.append((name, "header or footer", h))
        if has_letters(name):
            rows[("sheet", "sheet name")] += 1
    for kind_name in z.namelist():
        if re.match(r"xl/(comments\d*|threadedComments/.+)\.xml$", kind_name):
            root = read_xml(z, kind_name)
            n = sum(1 for el in root.iter() if el.tag.endswith("}t") and has_letters(el.text))
            if n:
                rows[("comments", "comment or note text")] += n
        if kind_name.startswith("xl/pivotTables/") and kind_name.endswith(".xml"):
            rows[("pivot", "pivot table")] += 1
        if kind_name == "xl/vbaProject.bin":
            rows[("workbook", "macros (VBA project)")] += 1
    for k, n in drawing_text(z, sheets).items():
        if n:
            rows[("drawings", k)] += n
    dn = wb.find("m:definedNames", NS)
    if dn is not None and len(dn):
        rows[("workbook", "defined name")] += len(dn)

    print(f"Text in {path}\n")
    print("Counts by location:")
    for (loc, kind), n in sorted(rows.items()):
        print(f"  {loc:<12} {kind:<26} {n}")
    plain = rows[("cell", "plain text")]
    at_risk = sum(n for (loc, kind), n in rows.items()
                  if (loc, kind) not in {("cell", "plain text"), ("sheet", "sheet name"),
                                         ("workbook", "defined name")})
    print(f"\nPlain text cells (translated by a cell-by-cell translator): {plain}")
    print(f"Items that need a decision before you upload: {at_risk}")
    if details:
        print("\nDetails:")
        for where, kind, text in details:
            print(f"  {where:<20} {kind:<20} {text[:60]!r}")


def compare(src_path, out_path):
    _, _, src = load(src_path)
    _, _, out = load(out_path)
    problems = []
    names_src, names_out = [s["name"] for s in src], [s["name"] for s in out]
    if names_src != names_out:
        problems.append(f"sheet names changed: {names_src} -> {names_out}")
    out_by_name = {s["name"]: s for s in out}
    checked = Counter()
    for sh in src:
        o = out_by_name.get(sh["name"])
        if o is None:
            continue
        for ref, c in sh["cells"].items():
            d = o["cells"].get(ref, {})
            where = f"{sh['name']}!{ref}"
            if c["formula"]:
                checked["formulas"] += 1
                if d.get("formula") != c["formula"]:
                    problems.append(f"{where}: formula changed: {c['formula']!r} -> {d.get('formula')!r}")
            elif c["number"] is not None:
                checked["numbers and dates"] += 1
                if d.get("number") != c["number"]:
                    problems.append(f"{where}: number changed: {c['number']} -> {d.get('number') or d.get('text')!r}")
            elif c["text"] and is_code_like(c["text"]):
                checked["codes"] += 1
                if d.get("text") != c["text"]:
                    problems.append(f"{where}: code changed: {c['text']!r} -> {d.get('text')!r}")
        for sqref, f1 in o["lists"]:
            if not f1.startswith('"'):
                continue
            allowed = set(f1.strip('"').split(","))
            for ref in expand(sqref):
                val = o["cells"].get(ref, {}).get("text")
                checked["dropdown cells"] += 1
                if val and val not in allowed:
                    problems.append(f"{sh['name']}!{ref}: {val!r} is not in its dropdown list {sorted(allowed)}")
    print("Checked: " + ", ".join(f"{n} {k}" for k, n in checked.items()))
    print(f"Problems: {len(problems)}")
    for p in problems:
        print("  " + p)
    return 1 if problems else 0


if __name__ == "__main__":
    if len(sys.argv) == 4 and sys.argv[1] == "--compare":
        sys.exit(compare(sys.argv[2], sys.argv[3]))
    if len(sys.argv) == 2:
        inventory(sys.argv[1])
    else:
        print(__doc__)
        sys.exit(2)