work-helpers/work_helpers/excel2changelog.py

import click
import re
import pyperclip

a2z = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
EXCEL_COLUMNS = list(a2z)
for prefix in a2z:
    further = [prefix + c for c in a2z]
    EXCEL_COLUMNS.extend(further)


sheet = "Input_Data_*"
col_start = "A"
row_start = 8

def xls_formula_spacing(formula):
    for c in "();=":
        formula = formula.replace(c, f"{c} ")
    return formula



def text_to_changelog(
    raw_text, sheet, start_column, start_row, compact=False, keep=False
):
    cells = [line.split("\t") for line in raw_text.splitlines()]

    xls_sep = "!" if compact else "\t"

    output = []
    current_row = 1
    columns_start_index = EXCEL_COLUMNS.index(start_column)
    columns_used = EXCEL_COLUMNS[columns_start_index:]

    for row, line in enumerate(cells, start=start_row):
        if row != current_row:
            if not compact:
                output.append("")
            current_row = row
        for column, cell in zip(columns_used, line):
            cell = cell.strip()
            if not (cell or keep):
                continue
            if compact:
                cell = xls_formula_spacing(cell)
            output.append(f"{sheet}{xls_sep}{column}{row}\t{cell}")

    iterator = iter(output)
    prev = next(iterator)
    yield prev

    for current in iterator:
        if current != prev:
            yield current
        prev = current


def clipboard_to_changelog(sheet, start_column, start_row, compact=False, keep=False):
    xls = pyperclip.paste()
    result = list(text_to_changelog(xls, sheet, start_column, start_row, compact, keep))
    line_count = len(result)
    if line_count == 1:
        print(f"Copied one line to the clipboard")
        print(result[0])
    else:
        print(f"Copied {line_count} lines to the clipboard")
        first_line = next(l for l in result if l.strip())
        print(first_line)
        print("...")
        last_line = next(l for l in result[::-1] if l.strip())
        print(last_line)

    pyperclip.copy("\r\n".join(result))


@click.command()
@click.option("-s", "--sheet", prompt=True, required=True, default="Input_Data_*")
@click.option("-w", "--well", prompt=True, required=True, default="A1")
@click.option("-c", "--compact", is_flag=True)
@click.option("-k", "--keep", is_flag=True)
def cli(sheet, well, compact, keep):
    regex_well = r"(?P<column>[A-Z]{1,2})(?P<row>\d+)"
    match = re.match(regex_well, well.upper())
    if match is None:
        raise ValueError(f"No Excel Well: {well}")
    regex_result = match.groupdict()
    start_column = regex_result["column"]
    start_row = int(regex_result["row"])
    clipboard_to_changelog(sheet, start_column, start_row, compact, keep)