Re: [openpyxl-users] 2026

[Микола Романець]

What it?

пн, 29 дек. 2025 г. в 15:50, Saurus <xdsn...@gmail.com>:

import pandas as pd
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side

# Création du classeur 2026
wb = Workbook()
months = ["Janvier", "Fevrier", "Mars", "Avril", "Mai", "Juin",
          "Juillet", "Aout", "Septembre", "Octobre", "Novembre", "Decembre"]

# Styles
header_fill = PatternFill(start_color="D9EAD3", end_color="D9EAD3", fill_type="solid")
calc_fill = PatternFill(start_color="F4F4F4", end_color="F4F4F4", fill_type="solid")
bold = Font(bold=True)

def create_month_sheet(ws, month_name):
    # En-têtes Section Gauche (Charges)
    ws["A1"] = "CATEGORIE"; ws["B1"] = "MONTANT"; ws["C1"] = "DATE"
   
    # Remplissage des catégories habituelles (basé sur vos CSV)
    cats = ["PAYE", "RAYN", "SDIS", "CREDITS", "EDF", "DARTY", "MAISON", "MOBILE", "ORANGE", "AUTO", "HAB", "FONCIER"]
    for i, cat in enumerate(cats, start=2):
        ws[f"A{i}"] = cat

    # Section Transactions CB (à droite comme dans l'original)
    ws["J1"] = "TRANSACTIONS"; ws["K1"] = "PRIX"; ws["L1"] = "ARRONDI"; ws["M1"] = "DATE"
   
    # Formule Arrondi Automatique (ex: ligne 2 à 50)
    for row in range(2, 51):
        ws[f"L{row}"] = f"=IF(K{row}>0, CEILING(K{row},1)-K{row}, 0)"
        ws[f"L{row}"].fill = calc_fill

    # Section Suivi Comptes (Milieu/Droite)
    ws["Q1"] = "COMPTE"; ws["R1"] = "MOIS DERNIER"; ws["S1"] = "ENCOURS"; ws["V1"] = "EPARGNE"
    comptes = ["COURANT", "LEP", "LPP", "PEE", "CRYPTO", "TOTAL"]
    for i, compte in enumerate(comptes, start=2):
        ws[f"Q{i}"] = compte

    # --- FORMULES DE CALCULS ---
    ws["A20"] = "TOTAL CHARGES"; ws["B20"] = "=SUM(B3:B19)"
    ws["J20"] = "TOTAL CB"; ws["K20"] = "=SUM(K2:K50)"
    ws["A22"] = "RESTE À VIVRE"; ws["B22"] = "=B2-B20-K20" # PAYE - CHARGES - CB
   
    # Mise en forme
    for cell in ["A20", "B20", "J20", "K20", "A22", "B22"]:
        ws[cell].font = bold
        ws[cell].fill = calc_fill

# Configuration de l'onglet TOTAL (Récapitulatif)
ws_total = wb.active
ws_total.title = "TOTAL 2026"
ws_total.append(["CATEGORIE"] + months + ["TOTAL ANNUEL"])

# On liste les lignes qu'on veut suivre dans le récap
rows_to_track = ["PAYE", "CREDITS", "EDF", "MAISON", "TOTAL CB"]

for r_idx, label in enumerate(rows_to_track, start=2):
    ws_total.cell(row=r_idx, column=1, value=label)
    for c_idx, month in enumerate(months, start=2):
        # Cherche la cellule correspondante dans chaque mois
        if label == "PAYE": cell_ref = "B2"
        elif label == "CREDITS": cell_ref = "B5"
        elif label == "TOTAL CB": cell_ref = "K20"
        else: cell_ref = "B6" # Défaut pour les autres
       
        ws_total.cell(row=r_idx, column=c_idx, value=f"='{month}'!{cell_ref}")
   
    # Somme de la ligne
    ws_total.cell(row=r_idx, column=14, value=f"=SUM(B{r_idx}:M{r_idx})").font = bold

# Création des 12 mois
for m in months:
    create_month_sheet(wb.create_sheet(m), m)

# Sauvegarde
output_file = "Account_2026_Optimise.xlsx"
wb.save(output_file)

--
You received this message because you are subscribed to the Google Groups "openpyxl-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to openpyxl-user...@googlegroups.com.
To view this discussion, visit https://groups.google.com/d/msgid/openpyxl-users/4fb90f9c-5793-4d4f-9bd2-f5e7385cd0ben%40googlegroups.com.