import json import sys from pathlib import Path from openpyxl import Workbook from openpyxl.styles import Alignment, Border, Font, PatternFill, Side from openpyxl.utils import get_column_letter if hasattr(sys.stdout, "reconfigure"): sys.stdout.reconfigure(encoding="utf-8") def clean(value): if value is None: return "" return str(value).strip() def counts(values): if not isinstance(values, dict): values = {} return [int(values.get(key) or 0) for key in ("C", "B", "A", "AD")] def main(json_file, xlsx_file): report = json.loads(Path(json_file).read_text(encoding="utf-8")) filters = report.get("filters") if isinstance(report.get("filters"), dict) else {} rows = report.get("rows") if isinstance(report.get("rows"), list) else [] periods = report.get("periods") if isinstance(report.get("periods"), list) else [] periods = periods[:4] romans = ["I", "II", "III", "IV"] while len(periods) < 4: periods.append({"id_periodo_academico": 0, "short": romans[len(periods)]}) level_code = clean(filters.get("id_nivel")).upper() level_label = clean(filters.get("level_label") or level_code).upper() grade_label = clean(filters.get("grade_short") or filters.get("grade_label") or filters.get("id_grado")) year = clean(filters.get("year")) sheet_name = "SECUNDARIA" if level_code == "SEC" else "PRIMARIA" workbook = Workbook() worksheet = workbook.active worksheet.title = sheet_name worksheet.sheet_view.showGridLines = False worksheet.freeze_panes = "F5" worksheet.page_setup.orientation = "landscape" worksheet.page_setup.paperSize = worksheet.PAPERSIZE_A4 worksheet.page_setup.fitToWidth = 1 worksheet.page_setup.fitToHeight = 0 worksheet.sheet_properties.pageSetUpPr.fitToPage = True worksheet.page_margins.left = 0.25 worksheet.page_margins.right = 0.25 worksheet.page_margins.top = 0.35 worksheet.page_margins.bottom = 0.35 widths = { "A": 11, "B": 14, "C": 10, "D": 25, "E": 52, } for column, width in widths.items(): worksheet.column_dimensions[column].width = width for column in range(6, 26): worksheet.column_dimensions[get_column_letter(column)].width = 7 thin_orange = Side(style="thin", color="F2B200") border = Border(left=thin_orange, right=thin_orange, top=thin_orange, bottom=thin_orange) title_fill = PatternFill("solid", fgColor="FFF2CC") head_fill = PatternFill("solid", fgColor="FFC000") subhead_fill = PatternFill("solid", fgColor="FFE699") body_fill = PatternFill("solid", fgColor="FFF8E1") final_fill = PatternFill("solid", fgColor="E2F0D9") def style_range(cell_range, fill, bold=True, font_color="000000"): for row_cells in worksheet[cell_range]: for cell in row_cells: cell.fill = fill cell.border = border cell.font = Font(name="Arial", size=10, bold=bold, color=font_color) cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True) worksheet.merge_cells("A1:Y1") worksheet["A1"] = f"LOGROS DE APRENDIZAJES POR PERIODOS - NIVEL {level_label}" worksheet["A1"].fill = title_fill worksheet["A1"].font = Font(name="Arial", size=16, bold=True) worksheet["A1"].alignment = Alignment(horizontal="center", vertical="center") worksheet["A1"].border = border worksheet.row_dimensions[1].height = 25 headers = [ ("A2:A4", "A\u00d1O"), ("B2:B4", "NIVEL"), ("C2:C4", "GRADO"), ("D2:D4", "AREA"), ("E2:E4", "COMPETENCIA"), ] for cell_range, label in headers: worksheet.merge_cells(cell_range) cell = worksheet[cell_range.split(":")[0]] cell.value = label worksheet.merge_cells("F2:U2") worksheet["F2"] = "BIMESTRES" worksheet.merge_cells("V2:Y3") worksheet["V2"] = "SITUACION FINAL" for start, end, index in (("F", "I", 0), ("J", "M", 1), ("N", "Q", 2), ("R", "U", 3)): worksheet.merge_cells(f"{start}3:{end}3") worksheet[f"{start}3"] = clean(periods[index].get("short") or romans[index]) literals = ["C", "B", "A", "AD"] column = 6 for _ in range(5): for literal in literals: worksheet.cell(4, column).value = literal column += 1 style_range("A2:Y2", head_fill) style_range("A3:Y3", subhead_fill) style_range("A4:Y4", subhead_fill) row_number = 5 for item in rows: worksheet.cell(row_number, 1).value = year worksheet.cell(row_number, 2).value = level_label worksheet.cell(row_number, 3).value = grade_label worksheet.cell(row_number, 4).value = clean(item.get("area")) worksheet.cell(row_number, 5).value = clean(item.get("competence")) period_values = item.get("periods") if isinstance(item.get("periods"), dict) else {} column = 6 for period in periods: period_id = str(int(period.get("id_periodo_academico") or 0)) for value in counts(period_values.get(period_id) or period_values.get(int(period_id) if period_id.isdigit() else period_id)): worksheet.cell(row_number, column).value = value column += 1 for value in counts(item.get("final")): worksheet.cell(row_number, column).value = value column += 1 for cell in worksheet[row_number]: cell.border = border cell.font = Font(name="Arial", size=10) cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True) if cell.column == 4: cell.fill = body_fill cell.font = Font(name="Arial", size=10, italic=True, color="9C6500") cell.alignment = Alignment(horizontal="left", vertical="center", wrap_text=True) elif cell.column == 5: cell.alignment = Alignment(horizontal="left", vertical="center", wrap_text=True) elif cell.column >= 22: cell.fill = final_fill row_number += 1 if not rows: worksheet.merge_cells("A5:Y5") worksheet["A5"] = "No se encontraron registros para los filtros seleccionados." worksheet["A5"].border = border last_row = max(5, row_number - 1) worksheet.auto_filter.ref = f"A4:Y{last_row}" workbook.save(xlsx_file) print(json.dumps({"ok": True}, ensure_ascii=False)) if __name__ == "__main__": try: main(sys.argv[1], sys.argv[2]) except Exception as exc: print(json.dumps({"ok": False, "error": str(exc)}, ensure_ascii=False)) sys.exit(1)