Excel Automation

from openpyxl.styles import Font, PatternFill, Border, Side, Alignment
# FontsBLUE_FONT = Font(color="0000FF", size=10, name="Calibri")BLACK_FONT_BOLD = Font(color="000000", size=10, name="Calibri", bold=True)GREEN_FONT = Font(color="008000", size=10, name="Calibri")HEADER_FONT = Font(color="FFFFFF", size=12, name="Calibri", bold=True)
# FillsDARK_BLUE_FILL = PatternFill("solid", fgColor="4472C4")LIGHT_BLUE_FILL = PatternFill("solid", fgColor="D9E1F2")INPUT_GREEN_FILL = PatternFill("solid", fgColor="E2EFDA")LIGHT_GRAY_FILL = PatternFill("solid", fgColor="F2F2F2")
# BordersTHIN_BORDER = Border(bottom=Side(style="thin", color="B2B2B2"))BOTTOM_DOUBLE = Border(bottom=Side(style="double", color="000000"))

from openpyxl.formatting.rule import ColorScaleRule
rule = ColorScaleRule(    start_type="min", start_color="F8696B",   # Red (low)    mid_type="percentile", mid_value=50, mid_color="FFEB84",  # Yellow (mid)    end_type="max", end_color="63BE7B"         # Green (high))ws.conditional_formatting.add(f"B2:F6", rule)

import zipfileimport xml.etree.ElementTree as ET
MAIN_NS = 'http://schemas.openxmlformats.org/spreadsheetml/2006/main'REL_NS = 'http://schemas.openxmlformats.org/officeDocument/2006/relationships'RELS_NS = 'http://schemas.openxmlformats.org/package/2006/relationships'
def get_sheet_path(zf, sheet_name):    """Resolve sheet name to physical XML file path inside ZIP."""    # Step 1: workbook.xml → find rId for the sheet name    wb_xml = ET.fromstring(zf.read('xl/workbook.xml'))    sheets = wb_xml.findall(f'.//{{{MAIN_NS}}}sheet')    rid = None    for s in sheets:        if s.get('name') == sheet_name:            rid = s.get(f'{{{REL_NS}}}id')            break    if not rid:        raise ValueError(f"Sheet '{sheet_name}' not found")
    # Step 2: workbook.xml.rels → map rId to file path    rels_xml = ET.fromstring(zf.read('xl/_rels/workbook.xml.rels'))    for rel in rels_xml.findall(f'{{{RELS_NS}}}Relationship'):        if rel.get('Id') == rid:            return 'xl/' + rel.get('Target')
    raise ValueError(f"No file mapping for {rid}")

def extract_cells(zf, sheet_path):    """Extract all cell values from a sheet XML."""    # Build shared strings lookup    shared = []    try:        ss_xml = ET.fromstring(zf.read('xl/sharedStrings.xml'))        for si in ss_xml.findall(f'{{{MAIN_NS}}}si'):            texts = si.itertext()            shared.append(''.join(texts))    except KeyError:        pass  # No shared strings
    # Parse sheet cells    sheet_xml = ET.fromstring(zf.read(sheet_path))    rows = sheet_xml.findall(f'.//{{{MAIN_NS}}}row')
    data = {}    for row in rows:        for cell in row.findall(f'{{{MAIN_NS}}}c'):            ref = cell.get('r')         # e.g., "A1"            cell_type = cell.get('t')   # "s" = shared string, None = number            val_el = cell.find(f'{{{MAIN_NS}}}v')
            if val_el is not None and val_el.text:                if cell_type == 's':                    data[ref] = shared[int(val_el.text)]                else:                    try:                        data[ref] = float(val_el.text)                    except ValueError:                        data[ref] = val_el.text    return data

def fix_defined_names(zf_in_path, zf_out_path):    """Remove corrupted DefinedNames and repackage."""    import shutil, tempfile    with tempfile.TemporaryDirectory() as tmp:        tmp = Path(tmp)        with zipfile.ZipFile(zf_in_path, 'r') as zf:            zf.extractall(tmp)
        wb_xml_path = tmp / 'xl' / 'workbook.xml'        tree = ET.parse(wb_xml_path)        root = tree.getroot()
        ns = {'main': MAIN_NS}        defined_names = root.find('.//main:definedNames', ns)        if defined_names is not None:            for name in list(defined_names):                if name.text and "Formula removed" in name.text:                    defined_names.remove(name)
        tree.write(wb_xml_path, encoding='utf-8', xml_declaration=True)
        with zipfile.ZipFile(zf_out_path, 'w', zipfile.ZIP_DEFLATED) as zf:            for fp in tmp.rglob('*'):                if fp.is_file():                    zf.write(fp, fp.relative_to(tmp))