import json import sys from pathlib import Path from openpyxl import Workbook from openpyxl.comments import Comment from openpyxl.styles import Alignment, Border, Font, PatternFill, Side from openpyxl.worksheet.datavalidation import DataValidation def clean(value): if value is None: return "" return str(value).replace("\r", "\n").strip() def build_template(rows, output_path): workbook = Workbook() sheet = workbook.active sheet.title = "Instrumento 1" sheet.sheet_view.showGridLines = False sheet.freeze_panes = "A12" green = "00703B" light_green = "E2F0D9" pale_green = "F5FBF7" dark = "0F2237" gray = "546577" border_color = "9FB8A9" thin = Side(style="thin", color=border_color) border = Border(left=thin, right=thin, top=thin, bottom=thin) sheet.merge_cells("A1:G1") sheet["A1"] = "INSTRUMENTO 1: Ficha de monitoreo de la planificacion curricular de docentes" sheet["A1"].font = Font(bold=True, size=15, color="FFFFFF") sheet["A1"].alignment = Alignment(horizontal="center", vertical="center") sheet["A1"].fill = PatternFill("solid", fgColor=green) sheet.row_dimensions[1].height = 28 sheet.merge_cells("A2:G2") sheet["A2"] = "Plantilla estandar para importar al sistema COMANO 0003" sheet["A2"].font = Font(bold=True, italic=True, color=gray) sheet["A2"].alignment = Alignment(horizontal="center") general = [ ("A4", "DRE", "B4", "SAN MARTIN", "D4", "UGEL", "E4", "HUALLAGA", "F4", "FECHA", "G4", ""), ("A5", "INSTITUCION EDUCATIVA", "B5", "0003", "F5", "NIVEL", "G5", ""), ("A6", "DOCENTE MONITOREADO", "B6", "", "F6", "DNI", "G6", ""), ("A7", "GRADO", "B7", "", "D7", "SECCION", "E7", "", "F7", "CELULAR", "G7", ""), ("A8", "MONITOR(A)", "B8", "Prof. Alvert Kennedy Rodriguez Cardenas", "F8", "DNI", "G8", "45228933"), ] for row in range(4, 9): for col in range(1, 8): cell = sheet.cell(row=row, column=col) cell.border = border cell.alignment = Alignment(vertical="center", wrap_text=True) for item in general: for index in range(0, len(item), 2): cell = sheet[item[index]] value = item[index + 1] cell.value = value if item[index] in {"A4", "D4", "F4", "A5", "F5", "A6", "F6", "A7", "D7", "F7", "A8", "F8"}: cell.fill = PatternFill("solid", fgColor=light_green) cell.font = Font(bold=True, color=dark) cell.alignment = Alignment(horizontal="right", vertical="center", wrap_text=True) else: cell.font = Font(bold=True, color=dark) sheet.merge_cells("B5:E5") sheet.merge_cells("B6:E6") sheet.merge_cells("B8:E8") sheet.merge_cells("A10:G10") sheet["A10"] = "Indicaciones: no cambie los codigos de ITEM. En RESPUESTA escriba SI o NO. Complete SUSTENTO REVISADO y COMENTARIO TECNICO para cada item observado." sheet["A10"].font = Font(bold=True, color=gray) sheet["A10"].alignment = Alignment(wrap_text=True) sheet["A10"].fill = PatternFill("solid", fgColor=pale_green) sheet["A10"].border = border sheet.row_dimensions[10].height = 34 headers = [ "DIMENSION", "ITEM", "ASPECTO VERIFICADO", "RESPUESTA (SI/NO)", "SUSTENTO REVISADO", "COMENTARIO TECNICO", "SUSTENTO ESPERADO", ] for column, header in enumerate(headers, start=1): cell = sheet.cell(row=11, column=column, value=header) cell.fill = PatternFill("solid", fgColor=green) cell.font = Font(bold=True, color="FFFFFF") cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True) cell.border = border row_number = 12 current_dimension = "" for row in rows: dimension_code = clean(row.get("dimension_codigo")) dimension_name = clean(row.get("dimension_nombre")) dimension = f"{dimension_code} - {dimension_name}".strip(" -") item_code = clean(row.get("item_codigo")).upper() values = [ dimension if dimension != current_dimension else "", item_code, clean(row.get("aspecto")), "", "", "", "", ] current_dimension = dimension for column, value in enumerate(values, start=1): cell = sheet.cell(row=row_number, column=column, value=value) cell.border = border cell.alignment = Alignment(vertical="top", wrap_text=True) if column in (1, 2): cell.font = Font(bold=True, color=dark) elif column == 4: cell.font = Font(bold=True, color=green) cell.alignment = Alignment(horizontal="center", vertical="center") elif column == 7: cell.font = Font(bold=True, color=dark) else: cell.font = Font(color=dark) expected_support = clean(row.get("sustento_esperado")) if expected_support: sheet.cell(row=row_number, column=7).comment = Comment(expected_support, "COMANO 0003") sheet.cell(row=row_number, column=7).font = Font(italic=True, color=gray) sheet.row_dimensions[row_number].height = 62 row_number += 1 last_row = max(12, row_number - 1) validation = DataValidation( type="list", formula1='"SI,NO"', allow_blank=True, showErrorMessage=True, errorTitle="Respuesta no valida", error="Escriba o seleccione SI o NO.", ) sheet.add_data_validation(validation) validation.add(f"D12:D{last_row}") sheet.auto_filter.ref = f"A11:G{last_row}" reflection_row = last_row + 2 sheet.merge_cells(start_row=reflection_row, start_column=1, end_row=reflection_row, end_column=7) title_cell = sheet.cell(row=reflection_row, column=1, value="INFORMACION RELEVANTE A PARTIR DEL DIALOGO REFLEXIVO") title_cell.fill = PatternFill("solid", fgColor=green) title_cell.font = Font(bold=True, color="FFFFFF") title_cell.alignment = Alignment(horizontal="center", vertical="center") title_cell.border = border sheet.row_dimensions[reflection_row].height = 24 reflection_items = [ ("Aspectos a Mejorar", "Aca agregar Aspectos a Mejorar", 62), ("Acuerdos y compromisos del/de la docente", "Aca agregar los Acuerdos y compromisos del/de la docente", 62), ("Fecha tentativa del proximo monitoreo", "", 24), ] for offset, (label, placeholder, height) in enumerate(reflection_items, start=1): target_row = reflection_row + offset sheet.merge_cells(start_row=target_row, start_column=2, end_row=target_row, end_column=7) label_cell = sheet.cell(row=target_row, column=1, value=label) value_cell = sheet.cell(row=target_row, column=2, value=placeholder) for column in range(1, 8): cell = sheet.cell(row=target_row, column=column) cell.border = border cell.alignment = Alignment(vertical="top", wrap_text=True) label_cell.fill = PatternFill("solid", fgColor=light_green) label_cell.font = Font(bold=True, color=dark) value_cell.font = Font(color=gray if placeholder else dark) if label.startswith("Fecha"): value_cell.number_format = "dd/mm/yyyy" value_cell.comment = Comment("Ingrese la fecha tentativa del proximo monitoreo.", "COMANO 0003") sheet.row_dimensions[target_row].height = height widths = { "A": 24, "B": 10, "C": 58, "D": 18, "E": 36, "F": 36, "G": 44, } for column, width in widths.items(): sheet.column_dimensions[column].width = width sheet.page_setup.orientation = "landscape" sheet.page_setup.fitToWidth = 1 sheet.page_setup.fitToHeight = 0 sheet.sheet_properties.pageSetUpPr.fitToPage = True sheet.print_title_rows = "1:11" output = Path(output_path) output.parent.mkdir(parents=True, exist_ok=True) workbook.save(output) def main(): if len(sys.argv) != 3: print(json.dumps({"ok": False, "error": "Uso: generator.py datos.json salida.xlsx"})) return 1 try: payload = json.loads(Path(sys.argv[1]).read_text(encoding="utf-8")) rows = payload.get("rows", []) if isinstance(payload, dict) else [] if not rows: raise RuntimeError("No se recibieron items para generar la plantilla.") build_template(rows, sys.argv[2]) print(json.dumps({"ok": True, "count": len(rows)})) return 0 except Exception as exc: print(json.dumps({"ok": False, "error": str(exc)}, ensure_ascii=False)) return 1 if __name__ == "__main__": raise SystemExit(main())