c3a0739a80
- gerar_plano.py: le planilha de planejamento (.xlsx) e um PPTX-modelo, gera uma quinzena por slide pintando cada quadrante (Geotecnia, Infraestrutura, Superestrutura) conforme o status na data - cores.json: cores/opacidades configuraveis sem precisar mexer no codigo - dist-linux/: executavel standalone (PyInstaller) + arquivos de exemplo - dist-windows/: pacote com Python embeddable (sem instalacao) + .bat Co-Authored-By: Claude Sonnet 5 <noreply@anthropic.com>
456 lines
17 KiB
Python
456 lines
17 KiB
Python
#!/usr/bin/env python3
|
|
# -*- coding: utf-8 -*-
|
|
"""
|
|
Gerador do "Plano de Ataque" (PPTX) por quinzena.
|
|
|
|
Uso: coloque este executavel na MESMA PASTA de:
|
|
- um arquivo .json de cores (ex.: cores.json)
|
|
- um arquivo .pptx modelo (com o slide 2 = layout de uma quinzena)
|
|
- um arquivo .xlsx (planilha de planejamento)
|
|
|
|
e execute. O programa detecta esses 3 arquivos automaticamente na pasta
|
|
e gera "<nome-do-modelo>_ATUALIZADO.pptx" na mesma pasta.
|
|
|
|
Nao precisa de Python nem de nenhuma instalacao no computador que for
|
|
rodar o executavel -- tudo ja vai embutido nele.
|
|
"""
|
|
import glob
|
|
import json
|
|
import os
|
|
import re
|
|
import sys
|
|
import copy
|
|
import traceback
|
|
from datetime import date, timedelta
|
|
|
|
|
|
def base_dir():
|
|
"""Pasta onde o executavel (ou este script) esta localizado."""
|
|
if getattr(sys, 'frozen', False):
|
|
return os.path.dirname(os.path.abspath(sys.executable))
|
|
return os.path.dirname(os.path.abspath(__file__))
|
|
|
|
|
|
def pause_on_exit(frozen_only=True):
|
|
if not frozen_only or getattr(sys, 'frozen', False):
|
|
try:
|
|
input('\nPressione ENTER para sair...')
|
|
except EOFError:
|
|
pass
|
|
|
|
|
|
def find_one(patterns, folder, label, exclude_suffix=None):
|
|
candidates = []
|
|
for pat in patterns:
|
|
for f in glob.glob(os.path.join(folder, pat)):
|
|
name = os.path.basename(f)
|
|
if name.startswith('~$'):
|
|
continue
|
|
if exclude_suffix and name.endswith(exclude_suffix):
|
|
continue
|
|
candidates.append(f)
|
|
candidates = sorted(set(candidates))
|
|
if not candidates:
|
|
raise SystemExit(
|
|
f'ERRO: nenhum arquivo {label} encontrado na pasta:\n {folder}\n'
|
|
f'Coloque um arquivo {label} nessa pasta e execute novamente.')
|
|
if len(candidates) > 1:
|
|
lista = '\n '.join(candidates)
|
|
raise SystemExit(
|
|
f'ERRO: mais de um arquivo {label} encontrado na pasta:\n {lista}\n'
|
|
f'Deixe apenas UM arquivo {label} na pasta e execute novamente.')
|
|
return candidates[0]
|
|
|
|
|
|
def main():
|
|
folder = base_dir()
|
|
print(f'Pasta de trabalho: {folder}')
|
|
|
|
json_path = find_one(['cores.json', '*.json'], folder, '.json (cores)')
|
|
xlsx_path = find_one(['*.xlsx'], folder, '.xlsx (planilha)')
|
|
pptx_path = find_one(['*.pptx'], folder, '.pptx (modelo)', exclude_suffix='_ATUALIZADO.pptx')
|
|
|
|
print(f' cores : {os.path.basename(json_path)}')
|
|
print(f' xlsx : {os.path.basename(xlsx_path)}')
|
|
print(f' modelo : {os.path.basename(pptx_path)}')
|
|
|
|
with open(json_path, 'r', encoding='utf-8') as f:
|
|
cfg = json.load(f)
|
|
|
|
import openpyxl
|
|
from pptx import Presentation
|
|
from pptx.oxml.ns import qn
|
|
from pptx.opc.constants import RELATIONSHIP_TYPE as RT
|
|
from lxml import etree
|
|
|
|
out_name = os.path.splitext(os.path.basename(pptx_path))[0] + '_ATUALIZADO.pptx'
|
|
out_path = os.path.join(folder, out_name)
|
|
|
|
# -----------------------------------------------------------
|
|
# 1) Coordenadas de pixel dos eixos, medidas nas imagens do modelo
|
|
# original (colunas 1-26 na "Imagem 6", colunas 26-48 na "Imagem 3").
|
|
# Isso e' fixo porque descreve a planta em si, nao a cor.
|
|
# -----------------------------------------------------------
|
|
im1_cols_list = [102.1, 152.3, 203.3, 253.2, 303.9, 353.7, 404.8, 455.0, 505.3, 556.4,
|
|
606.4, 657.1, 707.2, 757.9, 808.1, 858.6, 909.4, 959.4, 1010.6, 1059.7,
|
|
1110.7, 1160.8, 1211.5, 1262.3, 1312.4, 1363.6]
|
|
im2_cols_list = [83.0, 132.8, 183.7, 234.0, 284.5, 335.4, 385.2, 435.9, 486.0, 536.7,
|
|
587.0, 637.4, 688.4, 738.3, 789.0, 839.0, 889.8, 939.8, 990.6, 1041.1,
|
|
1091.4, 1142.1, 1192.1]
|
|
|
|
IM1_COLPX = {i + 1: v for i, v in enumerate(im1_cols_list)}
|
|
IM2_COLPX = {26 + i: v for i, v in enumerate(im2_cols_list)}
|
|
IM1_ROWPX = {'D': 88.8, 'C': 178.3, 'B1': 237.7, 'B': 305.3, 'A1': 359.2, 'A': 434.9}
|
|
IM2_ROWPX = {'D': 72.5, 'C': 161.5, 'B1': 221.5, 'B': 288.5, 'A1': 342.5, 'A': 418.5}
|
|
IM1_PXSIZE = (1442, 500)
|
|
IM2_PXSIZE = (1267, 485)
|
|
|
|
def which_image(c1, c2):
|
|
return 'im1' if max(c1, c2) <= 26 else 'im2'
|
|
|
|
def quad_pixel_rect(c1, c2, r1, r2):
|
|
img = which_image(c1, c2)
|
|
colpx = IM1_COLPX if img == 'im1' else IM2_COLPX
|
|
rowpx = IM1_ROWPX if img == 'im1' else IM2_ROWPX
|
|
x0, x1 = sorted([colpx[c1], colpx[c2]])
|
|
y0, y1 = sorted([rowpx[r1], rowpx[r2]])
|
|
return img, x0, y0, x1, y1
|
|
|
|
# -----------------------------------------------------------
|
|
# 2) Transform pixel -> EMU, usando a posicao/tamanho reais das
|
|
# imagens 'Imagem 6' (im1) e 'Imagem 3' (im2) no slide-modelo.
|
|
# 'Imagem 3' tem um corte (srcRect) aplicado no PowerPoint que
|
|
# precisa ser descontado, senao o desenho fica deslocado.
|
|
# -----------------------------------------------------------
|
|
IM1_OFF = (16846, 989588)
|
|
IM1_EXT = (8200608, 2841514)
|
|
IM2_OFF = (4073548, 3846576)
|
|
IM2_EXT = (8118452, 3006806)
|
|
|
|
IM1_SX = IM1_EXT[0] / IM1_PXSIZE[0]
|
|
IM1_SY = IM1_EXT[1] / IM1_PXSIZE[1]
|
|
|
|
IM2_CROP_T = 3246 / 100000
|
|
IM2_CROP_B = 1 / 100000
|
|
IM2_VIS_Y0 = IM2_CROP_T * IM2_PXSIZE[1]
|
|
IM2_VIS_Y1 = (1 - IM2_CROP_B) * IM2_PXSIZE[1]
|
|
IM2_VIS_X0 = 0.0
|
|
IM2_VIS_X1 = IM2_PXSIZE[0]
|
|
|
|
IM2_SX = IM2_EXT[0] / (IM2_VIS_X1 - IM2_VIS_X0)
|
|
IM2_SY = IM2_EXT[1] / (IM2_VIS_Y1 - IM2_VIS_Y0)
|
|
|
|
def to_emu(img, px, py):
|
|
if img == 'im1':
|
|
return IM1_OFF[0] + px * IM1_SX, IM1_OFF[1] + py * IM1_SY
|
|
return (IM2_OFF[0] + (px - IM2_VIS_X0) * IM2_SX,
|
|
IM2_OFF[1] + (py - IM2_VIS_Y0) * IM2_SY)
|
|
|
|
# -----------------------------------------------------------
|
|
# 3) Ler planilha de planejamento
|
|
# -----------------------------------------------------------
|
|
pat = re.compile(r'^(\d+)-(\d+)/([A-Z0-9]+)-([A-Z0-9]+)$')
|
|
|
|
def to_date(v):
|
|
if v is None:
|
|
return None
|
|
return v.date() if hasattr(v, 'date') else v
|
|
|
|
def parse_quads(path):
|
|
wb = openpyxl.load_workbook(path, data_only=True)
|
|
ws = wb['Planejamento']
|
|
quads = []
|
|
for row in ws.iter_rows(min_row=4, max_row=ws.max_row, values_only=True):
|
|
if row[1] is None:
|
|
continue
|
|
m = pat.match(str(row[1]).strip())
|
|
if not m:
|
|
continue
|
|
c1, c2, r1, r2 = int(m.group(1)), int(m.group(2)), m.group(3), m.group(4)
|
|
quads.append(dict(
|
|
c1=c1, c2=c2, r1=r1, r2=r2,
|
|
geo=(to_date(row[2]), to_date(row[3])),
|
|
inf=(to_date(row[4]), to_date(row[5])),
|
|
sup=(to_date(row[6]), to_date(row[7])),
|
|
))
|
|
return quads
|
|
|
|
QUADS = parse_quads(xlsx_path)
|
|
if not QUADS:
|
|
raise SystemExit('ERRO: nenhum quadrante valido foi lido da planilha (aba "Planejamento").')
|
|
|
|
def status(inicio, termino, p_end):
|
|
if inicio is None or inicio > p_end:
|
|
return None
|
|
if termino is not None and termino <= p_end:
|
|
return 'concluido'
|
|
return 'andamento'
|
|
|
|
# -----------------------------------------------------------
|
|
# 4) Quinzenas
|
|
# -----------------------------------------------------------
|
|
qz_cfg = cfg.get('quinzena', {})
|
|
PERIOD_DAYS = int(qz_cfg.get('duracao_dias', 14))
|
|
START = date.fromisoformat(qz_cfg.get('data_inicio', '2026-12-14'))
|
|
FIRST_NUM = int(qz_cfg.get('numero_inicial', 2))
|
|
|
|
all_dates = []
|
|
for q in QUADS:
|
|
for k in ('geo', 'inf', 'sup'):
|
|
i, f = q[k]
|
|
if i:
|
|
all_dates.append(i)
|
|
if f:
|
|
all_dates.append(f)
|
|
if not all_dates:
|
|
raise SystemExit('ERRO: a planilha nao tem nenhuma data preenchida.')
|
|
DATA_FIM = max(all_dates)
|
|
|
|
PERIODS = []
|
|
cur = START
|
|
num = FIRST_NUM
|
|
while cur <= DATA_FIM:
|
|
p_end = cur + timedelta(days=PERIOD_DAYS - 1)
|
|
PERIODS.append((num, cur, p_end))
|
|
cur = p_end + timedelta(days=1)
|
|
num += 1
|
|
|
|
def fmt(d):
|
|
return f'{d.day:02d}/{d.month:02d}/{d.year}'
|
|
|
|
# -----------------------------------------------------------
|
|
# 5) Cores (lidas do JSON)
|
|
# -----------------------------------------------------------
|
|
JSON_KEY = {'geo': 'geotecnia', 'sup': 'superestrutura', 'inf': 'infraestrutura'}
|
|
COLOR_HEX, BORDER_HEX, ALPHA_DRAWING_ST = {}, {}, {}
|
|
for short, jkey in JSON_KEY.items():
|
|
try:
|
|
section = cfg[jkey]
|
|
except KeyError:
|
|
raise SystemExit(f'ERRO: o JSON de cores nao tem a secao "{jkey}".')
|
|
for st in ('andamento', 'concluido'):
|
|
try:
|
|
entry = section[st]
|
|
COLOR_HEX[(short, st)] = entry['preenchimento'].lstrip('#').upper()
|
|
BORDER_HEX[(short, st)] = entry['borda'].lstrip('#').upper()
|
|
ALPHA_DRAWING_ST[(short, st)] = int(round(float(entry.get('alpha_preenchimento', 100)) * 1000))
|
|
except KeyError as e:
|
|
raise SystemExit(f'ERRO: falta o campo {e} em "{jkey}.{st}" no JSON de cores.')
|
|
|
|
ALPHA_LEGEND = 100000
|
|
MARKER_FRAC = float(cfg.get('marcador_fracao', 0.42))
|
|
|
|
def _solid_fill_xml(hexcol, alpha):
|
|
return (f'<a:solidFill xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main">'
|
|
f'<a:srgbClr val="{hexcol}"><a:alpha val="{alpha}"/></a:srgbClr></a:solidFill>')
|
|
|
|
def _fill_xml(key):
|
|
return _solid_fill_xml(COLOR_HEX[key], ALPHA_DRAWING_ST[key])
|
|
|
|
def _line_xml(key):
|
|
return (f'<a:ln xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main" w="9525">'
|
|
f'<a:solidFill><a:srgbClr val="{BORDER_HEX[key]}"/></a:solidFill></a:ln>')
|
|
|
|
FILL_XML = {k: _fill_xml(k) for k in COLOR_HEX}
|
|
LINE_XML = {k: _line_xml(k) for k in COLOR_HEX}
|
|
|
|
ROUND_ADJ_QUAD = 12000
|
|
ROUND_ADJ_MARK = 30000
|
|
STATUS_RANK = {'andamento': 1, 'concluido': 2}
|
|
|
|
_shape_id_counter = [9000]
|
|
|
|
def next_id():
|
|
_shape_id_counter[0] += 1
|
|
return _shape_id_counter[0]
|
|
|
|
def make_roundrect_xml(name, x_emu, y_emu, w_emu, h_emu, fill_xml, adj, line_xml=None):
|
|
sid = next_id()
|
|
line = line_xml if line_xml else '<a:ln><a:noFill/></a:ln>'
|
|
return f'''<p:sp xmlns:p="http://schemas.openxmlformats.org/presentationml/2006/main" xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main">
|
|
<p:nvSpPr>
|
|
<p:cNvPr id="{sid}" name="{name} {sid}"/>
|
|
<p:cNvSpPr/>
|
|
<p:nvPr/>
|
|
</p:nvSpPr>
|
|
<p:spPr>
|
|
<a:xfrm><a:off x="{int(x_emu)}" y="{int(y_emu)}"/><a:ext cx="{int(w_emu)}" cy="{int(h_emu)}"/></a:xfrm>
|
|
<a:prstGeom prst="roundRect"><a:avLst><a:gd name="adj" fmla="val {adj}"/></a:avLst></a:prstGeom>
|
|
{fill_xml}
|
|
{line}
|
|
</p:spPr>
|
|
<p:txBody>
|
|
<a:bodyPr/><a:lstStyle/>
|
|
<a:p><a:endParaRPr lang="pt-BR"/></a:p>
|
|
</p:txBody>
|
|
</p:sp>'''
|
|
|
|
IM1_COL_STEP = (max(im1_cols_list) - min(im1_cols_list)) / (len(im1_cols_list) - 1)
|
|
IM2_COL_STEP = (max(im2_cols_list) - min(im2_cols_list)) / (len(im2_cols_list) - 1)
|
|
COL_STEP_PX = {'im1': IM1_COL_STEP, 'im2': IM2_COL_STEP}
|
|
|
|
def add_quadrant_overlays(slide, p_end):
|
|
spTree = slide.shapes._spTree
|
|
|
|
for q in QUADS:
|
|
st = status(*q['geo'], p_end)
|
|
if st is None:
|
|
continue
|
|
img, x0, y0, x1, y1 = quad_pixel_rect(q['c1'], q['c2'], q['r1'], q['r2'])
|
|
ex0, ey0 = to_emu(img, x0, y0)
|
|
ex1, ey1 = to_emu(img, x1, y1)
|
|
xml = make_roundrect_xml('Estaqueamento', ex0, ey0, ex1 - ex0, ey1 - ey0,
|
|
FILL_XML[('geo', st)], ROUND_ADJ_QUAD, LINE_XML[('geo', st)])
|
|
spTree.append(etree.fromstring(xml))
|
|
|
|
for key in ('inf', 'sup'):
|
|
best = {}
|
|
for q in QUADS:
|
|
st = status(*q[key], p_end)
|
|
if st is None:
|
|
continue
|
|
img = which_image(q['c1'], q['c2'])
|
|
for col in (q['c1'], q['c2']):
|
|
for row in (q['r1'], q['r2']):
|
|
pt = (img, col, row)
|
|
cur_st = best.get(pt)
|
|
if cur_st is None or STATUS_RANK[st] > STATUS_RANK[cur_st]:
|
|
best[pt] = st
|
|
|
|
name = 'Superestrutura' if key == 'sup' else 'Infraestrutura'
|
|
for (img, col, row), st in best.items():
|
|
colpx = IM1_COLPX if img == 'im1' else IM2_COLPX
|
|
rowpx = IM1_ROWPX if img == 'im1' else IM2_ROWPX
|
|
px, py = colpx[col], rowpx[row]
|
|
ex, ey = to_emu(img, px, py)
|
|
step = COL_STEP_PX[img]
|
|
sx = IM1_SX if img == 'im1' else IM2_SX
|
|
sy = IM1_SY if img == 'im1' else IM2_SY
|
|
mw, mh = step * MARKER_FRAC * sx, step * MARKER_FRAC * sy
|
|
xml = make_roundrect_xml(name, ex - mw / 2, ey - mh / 2, mw, mh,
|
|
FILL_XML[(key, st)], ROUND_ADJ_MARK, LINE_XML[(key, st)])
|
|
spTree.append(etree.fromstring(xml))
|
|
|
|
# -----------------------------------------------------------
|
|
# 6) Duplicar slide (clona shapes + relacionamentos de imagem)
|
|
# -----------------------------------------------------------
|
|
def duplicate_slide(prs, source_index):
|
|
source = prs.slides[source_index]
|
|
dest = prs.slides.add_slide(source.slide_layout)
|
|
for shp in list(dest.shapes):
|
|
shp._element.getparent().remove(shp._element)
|
|
|
|
rid_map = {}
|
|
for rId, rel in source.part.rels.items():
|
|
if rel.reltype == RT.SLIDE_LAYOUT:
|
|
continue
|
|
if rel.is_external:
|
|
continue
|
|
new_rId = dest.part.relate_to(rel.target_part, rel.reltype)
|
|
rid_map[rId] = new_rId
|
|
|
|
r_embed = qn('r:embed')
|
|
r_link = qn('r:link')
|
|
for shp in source.shapes:
|
|
new_el = copy.deepcopy(shp._element)
|
|
for el in new_el.iter():
|
|
old = el.get(r_embed)
|
|
if old is not None and old in rid_map:
|
|
el.set(r_embed, rid_map[old])
|
|
old = el.get(r_link)
|
|
if old is not None and old in rid_map:
|
|
el.set(r_link, rid_map[old])
|
|
dest.shapes._spTree.append(new_el)
|
|
return dest
|
|
|
|
def set_quinzena_title(slide, num, p_start, p_end):
|
|
for sh in slide.shapes:
|
|
if sh.name == 'CaixaDeTexto 5':
|
|
runs = sh.text_frame.paragraphs[0].runs
|
|
runs[-1].text = f'Quinzena {num:02d} ({fmt(p_start)} a {fmt(p_end)})'
|
|
return
|
|
raise RuntimeError('titulo (CaixaDeTexto 5) nao encontrado no slide-modelo')
|
|
|
|
LEGEND_Y_TO_KEY = {
|
|
4200000: ('geo', 'andamento'),
|
|
4597600: ('geo', 'concluido'),
|
|
4993578: ('sup', 'andamento'),
|
|
5381498: ('sup', 'concluido'),
|
|
5802852: ('inf', 'andamento'),
|
|
6190772: ('inf', 'concluido'),
|
|
}
|
|
|
|
def update_legend_colors(slide):
|
|
for sh in slide.shapes:
|
|
if sh.shape_type != 6: # GROUP
|
|
continue
|
|
for sub in sh.shapes:
|
|
if not sub.name.startswith('Rounded'):
|
|
continue
|
|
key = None
|
|
for y, k in LEGEND_Y_TO_KEY.items():
|
|
if abs(sub.top - y) < 5000:
|
|
key = k
|
|
break
|
|
if key is None:
|
|
continue
|
|
sp = sub._element.spPr
|
|
for el in sp.findall(qn('a:solidFill')):
|
|
sp.remove(el)
|
|
fill_el = etree.fromstring(_solid_fill_xml(COLOR_HEX[key], ALPHA_LEGEND))
|
|
ln = sp.find(qn('a:ln'))
|
|
sp.insert(list(sp).index(ln), fill_el)
|
|
for c in ln.findall(qn('a:solidFill')):
|
|
ln.remove(c)
|
|
ln_fill = etree.fromstring(
|
|
f'<a:solidFill xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main">'
|
|
f'<a:srgbClr val="{BORDER_HEX[key]}"/></a:solidFill>')
|
|
ln.append(ln_fill)
|
|
|
|
# -----------------------------------------------------------
|
|
# 7) Montagem final
|
|
# -----------------------------------------------------------
|
|
prs = Presentation(pptx_path)
|
|
if len(prs.slides) < 3:
|
|
raise SystemExit('ERRO: o modelo .pptx precisa ter pelo menos 3 slides '
|
|
'(capa, slide de quinzena, encerramento).')
|
|
|
|
template_idx = 1
|
|
update_legend_colors(prs.slides[template_idx])
|
|
|
|
targets = [prs.slides[template_idx]]
|
|
for _ in range(len(PERIODS) - 1):
|
|
targets.append(duplicate_slide(prs, template_idx))
|
|
|
|
for slide, (num, p_start, p_end) in zip(targets, PERIODS):
|
|
set_quinzena_title(slide, num, p_start, p_end)
|
|
add_quadrant_overlays(slide, p_end)
|
|
|
|
sldIdLst = prs.slides._sldIdLst
|
|
ids = list(sldIdLst)
|
|
thankyou_el = ids[2]
|
|
sldIdLst.remove(thankyou_el)
|
|
sldIdLst.append(thankyou_el)
|
|
|
|
prs.save(out_path)
|
|
print(f'\nOK! Gerado: {out_path}')
|
|
print(f'Total de slides: {len(prs.slides._sldIdLst)} | Quinzenas: {len(PERIODS)}')
|
|
print(f'Primeira quinzena: {PERIODS[0][0]:02d} ({fmt(PERIODS[0][1])} a {fmt(PERIODS[0][2])})')
|
|
print(f'Ultima quinzena: {PERIODS[-1][0]:02d} ({fmt(PERIODS[-1][1])} a {fmt(PERIODS[-1][2])})')
|
|
|
|
|
|
if __name__ == '__main__':
|
|
try:
|
|
main()
|
|
except SystemExit as e:
|
|
print(str(e))
|
|
pause_on_exit()
|
|
sys.exit(1)
|
|
except Exception:
|
|
print('ERRO INESPERADO:')
|
|
traceback.print_exc()
|
|
pause_on_exit()
|
|
sys.exit(1)
|
|
else:
|
|
pause_on_exit()
|