#!/usr/bin/env python3 """Convert Migration XLSX (rows 2-118) to JSON for CF import. report_year=2026.""" import json import re import zipfile import xml.etree.ElementTree as ET from pathlib import Path XLSX = Path(__file__).resolve().parent.parent / "MigrationDef_ZugestelltAnOnlineumfragenAm_20260723.xlsx" OUT = Path(__file__).resolve().parent / "migration_2026_rows.json" NS = {"m": "http://schemas.openxmlformats.org/spreadsheetml/2006/main"} def col_key(col: str) -> int: n = 0 for ch in col: n = n * 26 + (ord(ch) - 64) return n def col_letter(n: int) -> str: s = "" while n: n, r = divmod(n - 1, 26) s = chr(65 + r) + s return s def read_sheet(path: Path): with zipfile.ZipFile(path) as z: shared = [] if "xl/sharedStrings.xml" in z.namelist(): root = ET.fromstring(z.read("xl/sharedStrings.xml")) for si in root.findall("m:si", NS): shared.append("".join((t.text or "") for t in si.findall(".//m:t", NS))) sheet = ET.fromstring(z.read("xl/worksheets/sheet1.xml")) rows = {} for row in sheet.findall("m:sheetData/m:row", NS): rnum = int(row.get("r", "0")) cells = {} for c in row.findall("m:c", NS): ref = c.get("r", "") m = re.match(r"([A-Z]+)", ref) if not m: continue col = m.group(1) v = c.find("m:v", NS) if v is None: continue val = v.text or "" if c.get("t") == "s" and val.isdigit(): val = shared[int(val)] cells[col] = val rows[rnum] = cells return rows def cell(cells, col, default=""): return str(cells.get(col, default) or "").strip() def num(cells, col): raw = cell(cells, col) if not raw: return 0 raw = raw.replace("'", "").replace(" ", "").replace(",", ".") raw = re.sub(r"[^0-9.\-]", "", raw) if not raw or raw in ("-", "."): return 0 try: return int(round(float(raw))) except ValueError: return 0 def main(): rows = read_sheet(XLSX) out = [] kio_cols = [ ("2stoff_waerme", "AS", "DM", "EK", "EQ", "EW"), ("2stoff_prozess", "BE", "DQ", "EL", "ER", "EX"), ("1stoff_nichtgeschuetzt_waerme", "BQ", "DU", "EM", "ES", "EY"), ("1stoff_nichtgeschuetzt_prozess", "CC", "DY", "EN", "ET", "EZ"), ("1stoff_geschuetzt_waerme", "CO", "EC", "EO", "EU", "FA"), ("1stoff_geschuetzt_prozess", "DA", "EG", "EP", "EV", "FB"), ] # month cols: start letter for month 1 (Apr), 12 months consecutive month_starts = { "2stoff_waerme": "AS", "2stoff_prozess": "BE", "1stoff_nichtgeschuetzt_waerme": "BQ", "1stoff_nichtgeschuetzt_prozess": "CC", "1stoff_geschuetzt_waerme": "CO", "1stoff_geschuetzt_prozess": "DA", } quarter_starts = { "2stoff_waerme": "DM", "2stoff_prozess": "DQ", "1stoff_nichtgeschuetzt_waerme": "DU", "1stoff_nichtgeschuetzt_prozess": "DY", "1stoff_geschuetzt_waerme": "EC", "1stoff_geschuetzt_prozess": "EG", } def next_cols(start: str, count: int): base = col_key(start) return [col_letter(base + i) for i in range(count)] for rnum in range(2, 119): cells = rows.get(rnum, {}) name = cell(cells, "A") if not name: continue phone = cell(cells, "I") if cell(cells, "I") and cell(cells, "I") != "0" else cell(cells, "H") if phone == "0": phone = "" ak = cell(cells, "AK") period = "hydrological" if ak == "1" else "calendar" kio = {} for key, mstart in month_starts.items(): months = next_cols(mstart, 12) for mi, col in enumerate(months, start=1): kio[f"kio_table_{key}_{mi:02d}"] = num(cells, col) qcols = next_cols(quarter_starts[key], 4) # Q2, Q3, Q4, Q1 kio[f"kio_extra_quarter_q2_{key}"] = num(cells, qcols[0]) kio[f"kio_extra_quarter_q3_{key}"] = num(cells, qcols[1]) kio[f"kio_extra_quarter_q4_{key}"] = num(cells, qcols[2]) kio[f"kio_extra_quarter_q1_{key}"] = num(cells, qcols[3]) for key, _m, _q, annual, calendar, hydro in kio_cols: kio[f"kio_extra_annual_apr_mrz_{key}"] = num(cells, annual) kio[f"kio_extra_calendar_year_{key}"] = num(cells, calendar) kio[f"kio_extra_hydro_year_{key}"] = num(cells, hydro) end_kwh = num(cells, "M") sectors = { "private": num(cells, "N"), "agriculture": num(cells, "O"), "industry": num(cells, "P"), "services": num(cells, "Q"), "district": num(cells, "R"), "other": num(cells, "S"), "fuel": num(cells, "T"), "wkk": num(cells, "U"), } sector_sum_ohne_wkk = sum(sectors[k] for k in ("private", "agriculture", "industry", "services", "district", "other", "fuel")) # WKK is often a subset, not an extra "davon"-slot; only include if needed for sum. wkk_for_sum = 0 if sector_sum_ohne_wkk == end_kwh else sectors["wkk"] out.append( { "row": rnum, "companyName": name, "companySuffix": cell(cells, "B"), "upstreamOperator": cell(cells, "C"), "firstName": cell(cells, "D"), "lastName": cell(cells, "E"), "firstName2": cell(cells, "F"), "lastName2": cell(cells, "G"), "phone": phone, "email": cell(cells, "J"), "periodType": period, "totalSaleKwh": num(cells, "K"), "resellersKwh": num(cells, "L"), "endconsumersKwh": end_kwh, "sectorPrivate": sectors["private"], "sectorAgriculture": sectors["agriculture"], "sectorIndustry": sectors["industry"], "sectorServices": sectors["services"], "sectorDistrict": sectors["district"], "sectorOther": sectors["other"], "sectorFuel": sectors["fuel"], "sectorWkk": wkk_for_sum, "sectorWkkRaw": sectors["wkk"], "biogasTotal": num(cells, "V"), "biogasLbg": num(cells, "W"), "biogasNetwork": num(cells, "X"), "biogasCertificate": num(cells, "Y"), "heatingTotal": num(cells, "Z"), "heatingPrivate": num(cells, "AA"), "heatingAgriculture": num(cells, "AB"), "heatingIndustry": num(cells, "AC"), "heatingServices": num(cells, "AD"), "wkkTotal": num(cells, "AE"), "wkkPrivate": num(cells, "AF"), "wkkAgriculture": num(cells, "AG"), "wkkIndustry": num(cells, "AH"), "wkkServices": num(cells, "AI"), "gasMeters": num(cells, "AJ"), "employees": num(cells, "AL"), "investmentTotal": num(cells, "AM"), "investmentHigh": num(cells, "AN"), "investmentMidLow": num(cells, "AO"), "investmentStorage": num(cells, "AP"), "investmentOther": num(cells, "AQ"), "annualReportPrintCount": num(cells, "AR"), "kio": kio, } ) OUT.write_text(json.dumps(out, ensure_ascii=False, indent=0), encoding="utf-8") print(f"Wrote {len(out)} rows to {OUT}") if __name__ == "__main__": main()