69 lines
2.9 KiB
Python
69 lines
2.9 KiB
Python
"""Analyse la structure du classeur de masse salariale sans afficher d'identité ni de montant."""
|
|
from collections import Counter
|
|
from pathlib import Path
|
|
import re
|
|
|
|
from openpyxl import load_workbook
|
|
|
|
|
|
workbook_path = Path("/home/ubuntu/upload/TableaurecapMassesalarialeSANTINOVA-28.08.26.xlsx")
|
|
workbook = load_workbook(workbook_path, data_only=False)
|
|
values_workbook = load_workbook(workbook_path, data_only=True)
|
|
|
|
for sheet in workbook.worksheets:
|
|
print(f"sheet={sheet.title}|rows={sheet.max_row}|cols={sheet.max_column}")
|
|
for row_index in range(1, min(sheet.max_row, 8) + 1):
|
|
values = []
|
|
for column_index in range(1, sheet.max_column + 1):
|
|
value = sheet.cell(row_index, column_index).value
|
|
if isinstance(value, str) and value.startswith("="):
|
|
value = f"FORMULA:{value}"
|
|
if value is not None:
|
|
values.append(f"C{column_index}={value}")
|
|
if values:
|
|
print(f"row={row_index}|{' ; '.join(values)}")
|
|
formula_count = sum(
|
|
1
|
|
for row in sheet.iter_rows()
|
|
for cell in row
|
|
if isinstance(cell.value, str) and cell.value.startswith("=")
|
|
)
|
|
print(f"formula_count={formula_count}")
|
|
|
|
salary_sheet = workbook["Masse salariale"]
|
|
data_rows = [row for row in range(6, salary_sheet.max_row + 1) if salary_sheet.cell(row, 2).value]
|
|
print(f"salaries_with_identifier={len(data_rows)}")
|
|
for column, label in {
|
|
8: "2025_brut_avec_primes",
|
|
9: "2025_astreintes",
|
|
11: "2025_taux_charges",
|
|
12: "2025_evolution",
|
|
15: "2026_astreintes",
|
|
17: "2026_taux_charges",
|
|
18: "2026_evolution",
|
|
21: "2027_astreintes",
|
|
23: "2027_evolution",
|
|
24: "2027_prime",
|
|
}.items():
|
|
populated = sum(salary_sheet.cell(row, column).value is not None for row in data_rows)
|
|
formulas = sum(
|
|
isinstance(salary_sheet.cell(row, column).value, str)
|
|
and salary_sheet.cell(row, column).value.startswith("=")
|
|
for row in data_rows
|
|
)
|
|
print(f"field={label}|populated={populated}|formulas={formulas}")
|
|
|
|
for column, label in {7: "2025_hors_astreinte", 14: "2026_avec_primes", 20: "2027_avec_primes"}.items():
|
|
formulas = Counter()
|
|
for row in data_rows:
|
|
value = salary_sheet.cell(row, column).value
|
|
if isinstance(value, str) and value.startswith("="):
|
|
formulas[re.sub(r"\d+", "#", value)] += 1
|
|
for formula, count in formulas.items():
|
|
print(f"formula_pattern={label}|count={count}|formula={formula}")
|
|
|
|
values_sheet = values_workbook["Masse salariale"]
|
|
for column, label in {7: "2025_hors_astreinte", 10: "2025_mensuel", 13: "2026_hors_astreinte", 14: "2026_avec_primes", 16: "2026_mensuel", 19: "2027_hors_astreinte", 20: "2027_avec_primes", 22: "2027_mensuel"}.items():
|
|
calculated = sum(values_sheet.cell(row, column).value is not None for row in data_rows)
|
|
print(f"cached_value={label}|populated={calculated}")
|