
# Imports standards Python
import sys
import os
import re
import xlsxwriter
from datetime import datetime

# Imports QGIS
from qgis.core import (
    QgsVectorLayer,
    QgsFeature,
    QgsSpatialIndex,
    QgsFeatureRequest,
)


# Imports Qt (passer par qgis.PyQt pour compatibilité Qt5/Qt6)
from qgis.PyQt.QtWidgets import QFileDialog, QMessageBox
from qgis.PyQt.QtCore import QDate
from qgis.core import QgsFeature, QgsSpatialIndex, QgsGeometry

# Imports externes
from xlsxwriter.utility import xl_rowcol_to_cell

# Imports internes du plugin
from .constantes import TYPE_PARC_LIBELLES
from .utils import tr

# Statistiques et analyses des éléments forestiers

def calcul_surface_forestiere(features_data):
    total_surface_m2 = 0
    for f in features_data:
        possession = f["Possession"]
        type_parc = f["typeParc"]
        surface = f["SURFACE"]
        indice_parc = f["indice_parc"]

        if indice_parc not in (None, "", 0):
            continue

        try:
            surface_val = float(surface)
        except (TypeError, ValueError):
            surface_val = 0

        if possession and type_parc not in (1 ,12, 13):
            total_surface_m2 += surface_val * 100

    return convertir_surface_ha(total_surface_m2)


def calcul_surface_friche(features_data):
    total_surface_m2 = 0

    for f in features_data:
        possession = f.get("Possession")
        type_parc = f.get("typeParc")
        surface = f.get("SURFACE")
        indice_parc = f.get("indice_parc")

        # Ne pas inclure si indice_parc est défini (non nul, non vide)
        if indice_parc not in (None, "", 0):
            continue

        try:
            surface_val = float(surface)
        except (TypeError, ValueError):
            surface_val = 0

        if possession and type_parc in (8, 9, 14):
            total_surface_m2 += surface_val * 100

    surface_str = convertir_surface_ha(total_surface_m2)
    return surface_str

def calcul_parcelles_achat(features_data):
    total_surface_m2 = 0
    parcelles = []

    for f in features_data:
        type_parc = f.get("typeParc")
        section = f.get("section")
        numero = f.get("numero")
        surface = f.get("SURFACE")

        if type_parc != 12:
            continue

        if section and numero:
            parcelles.append(f"{section}{str(numero).strip()}")

        try:
            surface_val = float(surface)
        except (TypeError, ValueError):
            surface_val = 0

        total_surface_m2 += surface_val * 100

    surface_ares = round(total_surface_m2 / 100, 2)

    # Texte traduisible si aucune parcelle
    if surface_ares == 0:
        parcelles_text = tr("Pas d'achat en cours")
    else:
        parcelles_text = ", ".join(parcelles)

    return surface_ares, parcelles_text


def calcul_parcelles_echange(features_data):
    total_surface_m2 = 0
    parcelles = []

    for f in features_data:
        type_parc = f.get("typeParc")
        section = f.get("section")
        numero = f.get("numero")
        surface = f.get("SURFACE")

        if type_parc != 13:
            continue

        if section and numero:
            parcelles.append(f"{section}{str(numero).strip()}")

        try:
            surface_val = float(surface)
        except (TypeError, ValueError):
            surface_val = 0

        total_surface_m2 += surface_val * 100

    surface_ares = round(total_surface_m2 / 100, 2)

    # Texte traduisible si aucune parcelle
    if surface_ares == 0:
        parcelles_text = tr("Pas d'échanges en cours")
    else:
        parcelles_text = ", ".join(parcelles)

    return surface_ares, parcelles_text



def compter_parcelles_possedees(features_data):
    """
    Compte les entités dont le champ 'Possession' est True.
    """
    total = 0

    for f in features_data:
        if f.get("Possession"):
            total += 1

    return total



def analyse_types_parcelles(features_data, type_parc_min=2, type_parc_max=14, top_n=5, max_features=None):
    """
    Analyse la répartition des surfaces par type de parcelle (2 à 14),
    affiche les `top_n` types les plus représentés + une catégorie "Autres".
    Compatible avec lecture depuis cache (features_data : list[dict]).
    """

    surfaces_par_type = {}
    total_surface_m2 = 0.0  # float

    for i, f in enumerate(features_data):
        if max_features is not None and i >= max_features:
            break

        type_parc = f.get("typeParc")
        if type_parc is None:
            continue

        try:
            surface = float(f.get("SURFACE", 0))
        except (TypeError, ValueError):
            surface = 0.0

        if type_parc_min <= type_parc <= type_parc_max:
            total_surface_m2 += surface
            surfaces_par_type[type_parc] = surfaces_par_type.get(type_parc, 0.0) + surface

    if total_surface_m2 == 0:
        return "Aucune donnée disponible"

    # Calcul des pourcentages et tri
    pourcentages = {
        t: (s / total_surface_m2) * 100
        for t, s in surfaces_par_type.items()
    }
    sorted_types = sorted(pourcentages.items(), key=lambda x: x[1], reverse=True)

    top_types = sorted_types[:top_n]
    autres = sorted_types[top_n:]

    result_lines = []
    for type_parc, pourcentage in top_types:
        nom_type = TYPE_PARC_LIBELLES.get(type_parc, f"Type {type_parc}")
        result_lines.append(f"{nom_type} : {pourcentage:.1f} %")

    autres_pourcentage = sum(p[1] for p in autres)
    if autres_pourcentage > 0:
        result_lines.append(f"Autres : {autres_pourcentage:.1f} %")

    return "\n".join(result_lines)


def analyse_types_essences(features_data, top_n=5):
    """
    Analyse la répartition des surfaces pondérées par type d'essence,
    compatible avec lecture depuis cache (features_data : list[dict]).
    """

    surfaces_par_essence = {}
    total_surface_m2 = 0.0

    for f in features_data:
        try:
            surface = float(f.get("SURFACE", 0))
        except (TypeError, ValueError):
            surface = 0.0

        for i in range(1, 5):
            nom_essence = f.get(f"plant{i}")
            pourcent = f.get(f"Tx{i}")

            if not nom_essence:
                continue

            # Conversion forcée du pourcentage en float
            try:
                pourcent_val = float(pourcent)
            except (TypeError, ValueError):
                pourcent_val = 0.0

            poids = (pourcent_val / 100.0) * surface
            if poids == 0.0:
                continue

            total_surface_m2 += poids
            surfaces_par_essence[nom_essence] = surfaces_par_essence.get(nom_essence, 0.0) + poids

    if total_surface_m2 == 0:
        return "Aucune donnée disponible"

    # Calcul des pourcentages
    pourcentages = {
        essence: (surf / total_surface_m2) * 100
        for essence, surf in surfaces_par_essence.items()
    }
    sorted_essences = sorted(pourcentages.items(), key=lambda x: x[1], reverse=True)

    top_essences = sorted_essences[:top_n]
    autres = sorted_essences[top_n:]

    result_lines = [f"{essence} : {pourcentage:.1f} %" for essence, pourcentage in top_essences]

    autres_pourcentage = sum(p[1] for p in autres)
    if autres_pourcentage > 0:
        result_lines.append(f"Autres : {autres_pourcentage:.1f} %")

    return "\n".join(result_lines)

def convertir_surface_ha(surface_m2):
    """
    Convertit une surface en m² en hectares, ares et centiares.
    Ex: 21789 m² -> '2 ha 17 a 89 ca'
    """
    ha = int(surface_m2) // 10000
    reste = int(surface_m2) % 10000
    ares = reste // 100
    centiares = reste % 100
    return f"{ha} ha {ares} a {centiares} ca"

def format_surface_ha_are_ca(value_in_ares):
    try:
        hectares = value_in_ares / 100.0  # conversion ares → hectares décimaux
        ha = int(hectares)  # partie entière hectares
        reste = hectares - ha  # décimales hectares
        are = int(reste * 100)  # conversion décimale hectares → ares
        ca = int(round((reste * 100 - are) * 100))  # conversion décimale ares → centiares

        parts = []
        if ha > 0:
            parts.append(f"{ha} ha")
        if are > 0:
            parts.append(f"{are} a")
        if ca > 0:
            parts.append(f"{ca} ca")

        return " ".join(parts) if parts else "0 ha"
    except Exception:
        return str(value_in_ares)


def total_plantation(features_data):
    """
    Calcule le total de plants pour les entités fournies dans features_data (liste de dicts).
    Compatible avec valeurs NULL/QVariant.
    """
    total = 0
    for f in features_data:
        plants = f.get("totalplants")
        try:
            # Conversion sécurisée : on ignore tout ce qui ne peut pas être converti en int
            val = int(plants)
            total += val
        except (TypeError, ValueError):
            continue
    return total


def calcul_regroupement(features_data, max_features=None):
    """
    Regroupe les entités contiguës dont 'possession' est True,
    retourne les 3 plus grands regroupements en surface totale (champ 'contenance'),
    et affiche pour chaque groupe le numéro de la plus grande parcelle (avec section)
    et la surface totale.
    Optionnellement limité aux `max_features` premières entités.
    """

    # --- Construction des dictionnaires pour accès rapide ---
    features_dict = {}
    qgis_features = []  # liste de QgsFeature pour construire l'index spatial

    for i, f in enumerate(features_data):
        if max_features is not None and i >= max_features:
            break

        geom = f.get("_geom")
        if not isinstance(geom, QgsGeometry) or geom.isEmpty():
            continue

        fid = int(f.get("fid", i))  # toujours un entier pour cohérence
        features_dict[fid] = (f, geom)

        # On crée un QgsFeature minimal uniquement pour l'index spatial
        qf = QgsFeature(fid)
        qf.setGeometry(geom)
        qgis_features.append(qf)

    if not qgis_features:
        return "Aucune entité valide à regrouper."

    # --- Création de l’index spatial ---
    try:
        # Qt5 (anciennes versions)
        index = QgsSpatialIndex(qgis_features)
    except TypeError:
        # Qt6 et QGIS >= 3.36
        index = QgsSpatialIndex()
        for qf in qgis_features:
            index.addFeature(qf)

    visited = set()
    regroupements = []

    # --- Parcours principal ---
    for fid, (feat, geom) in features_dict.items():
        if fid in visited or not feat.get("Possession", False):
            continue

        groupe = []
        to_visit = [fid]

        while to_visit:
            current_fid = to_visit.pop()
            if current_fid in visited:
                continue
            visited.add(current_fid)

            current_feat, current_geom = features_dict[current_fid]
            if not current_feat.get("Possession", False) or current_geom is None:
                continue

            groupe.append(current_feat)

            # --- Recherche des voisins par bbox ---
            candidate_ids = index.intersects(current_geom.boundingBox())

            for neighbor_id in candidate_ids:
                if neighbor_id in visited:
                    continue
                neighbor_feat, neighbor_geom = features_dict.get(neighbor_id, (None, None))
                if neighbor_feat and neighbor_geom and neighbor_feat.get("Possession", False):
                    # Vérifie la contiguïté (touches ou overlap)
                    if current_geom.touches(neighbor_geom) or current_geom.intersects(neighbor_geom):
                        to_visit.append(neighbor_id)

        if groupe:
            regroupements.append(groupe)

    if not regroupements:
        return "Aucun regroupement trouvé."

    # --- Tri par surface totale décroissante ---
    regroupements.sort(
        key=lambda grp: sum(f.get("contenance", 0) or 0 for f in grp),
        reverse=True
    )

    top3 = regroupements[:3]
    result_lines = []

    for i, groupe in enumerate(top3, start=1):
        surface_totale = sum(f.get("contenance", 0) or 0 for f in groupe)
        surface_str = convertir_surface_ha(surface_totale)

        # Trouver la plus grande parcelle du groupe
        plus_grande_parcelle = max(groupe, key=lambda f: f.get("contenance", 0) or 0)
        section = plus_grande_parcelle.get("section", "")
        numero = plus_grande_parcelle.get("numero", "")

        result_lines.append(f"{i} - {section}{numero} = {surface_str}")

    return "\n".join(result_lines)

def export_standard_excel(self):
    layer = self.iface.activeLayer()
    if not layer or not isinstance(layer, QgsVectorLayer):
        QMessageBox.warning(None, tr("Export"), tr("Veuillez sélectionner une couche valide."))
        return

    champs_generaux = [
        "section", "numero", "indice_parc", "SURFACE",
        "plant1", "plant2", "plant3", "plant4", "Tx1", "Tx2", "Tx3", "Tx4",
        "annee", "totalplants", "typeParc"
    ]

    available_fields = [field.name() for field in layer.fields()]

    folder = QFileDialog.getExistingDirectory(None, tr("Choisir un dossier d'export"))
    if not folder:
        return
    output_file = os.path.join(folder, f"Export_{datetime.now().strftime('%Y%m%d')}.xlsx")
    workbook = xlsxwriter.Workbook(output_file)

    def write_sheet(sheet_name, specific_fields, check_function):
        fields = champs_generaux + specific_fields
        fields = [f for f in fields if f in available_fields]

        # --- Filtrer les colonnes vides ---
        non_empty_fields = []
        for field_name in fields:
            has_value = False
            for feature in layer.getFeatures():
                if not feature["Possession"]:
                    continue
                if not check_function(feature):
                    continue
                value = feature[field_name]
                if value not in (None, "NULL", "null", ""):
                    has_value = True
                    break
            if has_value:
                non_empty_fields.append(field_name)
        fields = non_empty_fields
        # ---------------------------------

        if not fields:
            return  # rien à écrire si toutes les colonnes sont vides

        worksheet = workbook.add_worksheet(sheet_name)
        row = 0

        # Écrire les entêtes
        for col, field_name in enumerate(fields):
            worksheet.write(row, col, field_name)
        row += 1

        # Initialisation des longueurs max
        max_lengths = {field_name: len(field_name) for field_name in fields}

        # Écriture des lignes filtrées + calcul longueurs
        for feature in layer.getFeatures():
            if not feature["Possession"]:
                continue
            if not check_function(feature):
                continue

            for col, field_name in enumerate(fields):
                value = feature[field_name]
                if value in (None, "NULL", "null"):
                    value_str = ""
                elif isinstance(value, QDate):
                    value_str = value.toString("dd/MM/yyyy")
                elif field_name == "typeParc":
                    try:
                        value_str = TYPE_PARC_LIBELLES.get(int(value), str(value))
                    except (ValueError, TypeError):
                        value_str = str(value)
                else:
                    value_str = str(value)

                worksheet.write(row, col, value_str)
                max_lengths[field_name] = max(max_lengths[field_name], len(value_str))
            row += 1

        # Ajustement automatique (en un seul passage)
        for col, field_name in enumerate(fields):
            worksheet.set_column(col, col, min(max_lengths[field_name] + 2, 25))

    # Feuille Travaux
    if self.dlg.checkTravExport.isChecked():
        specific_fields = [f"Tvx{i}" for i in range(1, 7)] + \
                          [f"dateTvx{i}" for i in range(1, 7)] + \
                          [f"remTvx{i}" for i in range(1, 7)]
        write_sheet("Travaux", specific_fields, lambda f: any(f[f"Tvx{i}"] not in (None, "", "NULL", "null") for i in range(1, 7)))

    # Feuille Traitements
    if self.dlg.checkTraitExport.isChecked():
        specific_fields = [f"Trait{i}" for i in range(1, 7)] + \
                          [f"dateTrait{i}" for i in range(1, 7)] + \
                          [f"remTrait{i}" for i in range(1, 7)]
        write_sheet("Traitements", specific_fields, lambda f: any(f[f"Trait{i}"] not in (None, "", "NULL", "null") for i in range(1, 7)))

    # Feuille Prévisions
    if self.dlg.checkPrevExport.isChecked():
        specific_fields = [f"Prev{i}" for i in range(1, 5)] + \
                          [f"datePrev{i}" for i in range(1, 5)] + \
                          [f"remPrev{i}" for i in range(1, 5)]
        write_sheet("Prévisions", specific_fields, lambda f: any(f[f"Prev{i}"] not in (None, "", "NULL", "null") for i in range(1, 5)))

    try:
        workbook.close()
    except xlsxwriter.exceptions.FileCreateError:
        # Le fichier est probablement ouvert dans Excel
        QMessageBox.warning(
            self.dlg,
            tr("Export"),
            tr(
                f"Le fichier '{output_file}' semble déjà ouvert.\n\n"
                "Veuillez le fermer puis relancer l’export."
            )
        )
        return
    except Exception as e:
        # Gestion de toute autre erreur imprévue
        QMessageBox.warning(
            self.dlg,
            tr("Erreur"),
            tr(f"Une erreur est survenue lors de la fermeture du fichier :\n{e}")
        )
        return

    # Si tout s’est bien passé
    QMessageBox.information(self.dlg, tr("Export"), tr(f"Export terminé :\n{output_file}"))

    if self.dlg.checkOpenExport.isChecked():
        try:
            os.startfile(output_file)
        except Exception as e:
            QMessageBox.warning(
                self.dlg,
                tr("Erreur"),
                tr(f"Impossible d’ouvrir le fichier :\n{e}")
            )


# Export de la valeur, date d'achat et type de parcelles
def export_typeval_excel(self):
    try:
        import xlsxwriter
        from xlsxwriter.utility import xl_rowcol_to_cell
    except ImportError:
        QMessageBox.critical(self.dlg, tr("Erreur"), tr("Module xlsxwriter non trouvé."))
        return

    layer = self.iface.activeLayer()
    if not layer or not hasattr(layer, "getFeatures"):
        QMessageBox.warning(self.dlg, tr("Export"), tr("Veuillez sélectionner une couche valide."))
        return

    required_fields = ["section", "numero", "SURFACE", "prix", "typeParc", "Possession", "dateAchat"]
    fields = [f.name() for f in layer.fields()]
    for req in required_fields:
        if req not in fields:
            QMessageBox.critical(self.dlg, tr("Erreur"), tr(f"Champ manquant : {req}"))
            return

    # Calcul surface totale des "Prés" (typeParc == 1)
    surface_pre = 0.0
    for f in layer.getFeatures():
        try:
            key = int(f["typeParc"])
        except (ValueError, TypeError):
            key = None
        if key == 1 and f["Possession"]:
            surface_pre += float(f["SURFACE"]) if f["SURFACE"] else 0.0

    # Filtrer les features à exporter : possession=True et typeParc n'existe pas
    features = []
    exclues = 0

    for f in layer.getFeatures():
        if not f["Possession"]:
            continue

        try:
            type_parc = int(f["typeParc"])
        except (ValueError, TypeError):
            type_parc = None

        # Exclure les prés (typeParc == 1)
        if type_parc == 1:
            continue

        # Ne garder que si indice_parc est vide ou absent
        if "indice_parc" not in f.fields().names():
            features.append(f)
            continue

        val = f["indice_parc"]
        # Compatibilité Qt5/Qt6 pour NULL / QVariant
        try:
            if val is None:
                val_str = ""
            elif hasattr(val, "isNull") and val.isNull():
                val_str = ""
            else:
                val_str = str(val).strip().upper()
        except Exception:
            val_str = ""

        # Inclure uniquement si vide, "NULL" ou "NONE"
        if val_str == "" or val_str in ("NULL", "NONE"):
            features.append(f)
        else:
            exclues += 1  # sous-parcelle, on exclut

    def get_libelle_nous(feature):
        try:
            key = int(feature["typeParc"])
            return TYPE_PARC_LIBELLES.get(key, "")
        except (ValueError, TypeError):
            return ""

    features.sort(key=get_libelle_nous)

    folder = QFileDialog.getExistingDirectory(None, "Choisir un dossier d'export")
    if not folder:
        return

    date_str = datetime.now().strftime("%Y%m%d")
    filename = f"Export_TypeVal_{date_str}.xlsx"
    output_file = os.path.join(folder, filename)

    # --- Vérification si le fichier est ouvert ---
    if os.path.exists(output_file):
        try:
            with open(output_file, "a"):
                pass
        except PermissionError:
            QMessageBox.warning(
                self.dlg,
                tr("Export"),
                tr(
                    f"Le fichier '{output_file}' semble déjà ouvert dans Excel.\n"
                    "Veuillez le fermer puis relancer l’export."
                )
            )
            return
        except Exception as e:
            QMessageBox.critical(
                self.dlg,
                tr("Erreur"),
                tr(f"Impossible d’accéder au fichier :\n{e}")
            )
            return
    # --------------------------------------------

    # Si le fichier existe mais n’est pas ouvert, on peut l’effacer pour le recréer
    if os.path.exists(output_file):
        try:
            os.remove(output_file)
        except Exception as e:
            QMessageBox.critical(
                self.dlg,
                tr("Erreur"),
                tr(f"Impossible d’écraser le fichier :\n{e}")
            )
            return

    sheet_name = re.sub(r'[:\\/*?\[\]]', '_', layer.name())[:31]

    try:
        workbook = xlsxwriter.Workbook(output_file)
        worksheet = workbook.add_worksheet(sheet_name)
    except Exception as e:
        QMessageBox.critical(
            self.dlg,
            tr("Erreur"),
            tr(f"Impossible de créer le fichier Excel :\n{e}")
        )
        return


#    sheet_name = re.sub(r'[:\\/*?\[\]]', '_', layer.name())[:31]

#    workbook = xlsxwriter.Workbook(output_file)
#    worksheet = workbook.add_worksheet(sheet_name)

    bold_format = workbook.add_format({'bold': True})
    euro_format = workbook.add_format({'num_format': '#,##0 €'})
    date_format = workbook.add_format({'num_format': 'dd/mm/yyyy'})

    export_fields = ["parcelle", "surface_lisible", "surface_ares", "typeParc", "prix", "dateAchat"]

    renamed_headers = {
        "typeParc": tr("Type de boisement"),
        "surface_lisible": tr("Surface (ha)"),
        "surface_ares": tr("Surface (a)"),
        "dateAchat": tr("Date acquisition")
    }

    # Écriture des en-têtes
    for col, field_name in enumerate(export_fields):
        worksheet.write(0, col, renamed_headers.get(field_name, field_name), bold_format)

    total_surface = 0.0
    total_prix = 0.0
    surface_par_type = {}
    row = 1

    def format_surface_ha_are_ca(surface):
        try:
            surface = float(surface)
        except (ValueError, TypeError):
            return ""

        total_cares = round(surface * 100)  # ares → centiares
        ha = total_cares // 10000
        remainder = total_cares % 10000
        a = remainder // 100
        ca = remainder % 100

        parts = []
        if ha > 0:
            parts.append(f"{ha} ha")
        if a > 0:
            parts.append(f"{a} a")
        if ca > 0:
            parts.append(f"{ca} ca")
        return " ".join(parts) if parts else "0 a"

    for feature in features:
        try:
            key = int(feature["typeParc"])
        except (ValueError, TypeError):
            key = None

        section = str(feature["section"]).strip()
        numero = str(feature["numero"]).strip()
        parcelle = f"{section}{numero}"
        surface = float(feature["SURFACE"]) if feature["SURFACE"] else 0.0
        surface_lisible = format_surface_ha_are_ca(surface)
        prix = int(feature["prix"]) if feature["prix"] else 0


        libelle = TYPE_PARC_LIBELLES.get(key, str(key) if key is not None else "")

        # Récupération dateAchat et formatage date
        date_achat_val = feature["dateAchat"]
        if date_achat_val and hasattr(date_achat_val, 'toPyDate'):
            date_achat_py = date_achat_val.toPyDate()
            date_achat_str = date_achat_py.strftime("%d/%m/%Y") if date_achat_py else ""
        else:
            date_achat_str = ""

        surface_par_type[libelle] = surface_par_type.get(libelle, 0.0) + surface
        total_surface += surface
        total_prix += prix

        values = [parcelle, surface_lisible, surface, libelle, prix, date_achat_str]
        for col, val in enumerate(values):
            field = export_fields[col]

            if field == "prix":
                try:
                    worksheet.write_number(row, col, float(val), euro_format)
                except (ValueError, TypeError):
                    worksheet.write(row, col, "")
            elif field == "surface_ares":
                try:
                    worksheet.write_number(row, col, float(val))
                except (ValueError, TypeError):
                    worksheet.write(row, col, "")
            elif field == "dateAchat":
                if val:
                    try:
                        dt = datetime.strptime(val, "%d/%m/%Y")
                        worksheet.write_datetime(row, col, dt, date_format)
                    except Exception:
                        worksheet.write(row, col, val)
                else:
                    worksheet.write(row, col, "")
            else:
                worksheet.write(row, col, val)

        row += 1

    # Totaux en bas de la liste
    if "surface_lisible" in export_fields:
        col_surf = export_fields.index("surface_lisible")
        worksheet.write(row, col_surf - 1, tr("Total surface"), bold_format)
        worksheet.write(row, col_surf, format_surface_ha_are_ca(total_surface), bold_format)

    if "prix" in export_fields:
        col_prix = export_fields.index("prix")
        worksheet.write(row, col_prix - 1, tr("Total valeur"), bold_format)
        cell1 = xl_rowcol_to_cell(1, col_prix)
        cell2 = xl_rowcol_to_cell(row - 1, col_prix)
        worksheet.write_formula(row, col_prix, f"=SUM({cell1}:{cell2})", euro_format)

    # Statistiques par type (hors "Pré")
    start_col = len(export_fields) + 1
    worksheet.write(0, start_col, tr("Type de boisement"), bold_format)
    worksheet.write(0, start_col + 1, tr("Surface (ha)"), bold_format)

    row_type = 1
    for typ, surf in sorted(surface_par_type.items()):
        worksheet.write(row_type, start_col, typ)
        worksheet.write(row_type, start_col + 1, format_surface_ha_are_ca(surf))
        row_type += 1

    # Total général (hors prés)
    worksheet.write(row_type, start_col, tr("Total général"), bold_format)
    worksheet.write(row_type, start_col + 1, format_surface_ha_are_ca(sum(surface_par_type.values())), bold_format)
    row_type += 2  # on saute une ligne

    # Total prés (typeParc=1)
    worksheet.write(row_type, start_col, tr("Total prés"), bold_format)
    worksheet.write(row_type, start_col + 1, format_surface_ha_are_ca(surface_pre))

    # Ajustement colonnes
    for col in range(len(export_fields)):
        worksheet.set_column(col, col, 20)
    worksheet.set_column(start_col, start_col, 25)
    worksheet.set_column(start_col + 1, start_col + 1, 22)

    workbook.close()

    QMessageBox.information(self.dlg, tr("Export"), tr(f"Export terminé :\n{output_file}"))

    if self.dlg.checkOpenExport.isChecked():
        try:
            os.startfile(output_file)
        except Exception as e:
            QMessageBox.warning(self.dlg, tr("Erreur"), tr(f"Impossible d’ouvrir le fichier :\n{e}"))
