import math from typing import Optional from datetime import datetime, date import pandas as pd from PyQt6.QtWidgets import QTableWidget from PyQt6.QtCore import Qt from openpyxl import Workbook from openpyxl.utils import get_column_letter from openpyxl.styles import Font, Alignment, PatternFill, Border, Side, numbers from loguru import logger def _qt_alignment_to_excel(alignment_flag: int) -> Alignment: try: if alignment_flag & int(Qt.AlignmentFlag.AlignRight): return Alignment(horizontal="right", vertical="center") if alignment_flag & int(Qt.AlignmentFlag.AlignHCenter): return Alignment(horizontal="center", vertical="center") except Exception: pass return Alignment(horizontal="left", vertical="center") def _pixels_to_excel_width(pixels: int) -> float: # Rough conversion: Excel width units are approx characters of the default font. # 1 char ~= 7 pixels for Segoe UI/Calibri 11. Add small padding. if pixels <= 0: return 10.0 width = max(8.0, round((pixels / 7.0) + 1.0, 1)) return width def export_qtablewidget_to_excel( table: QTableWidget, file_path: str, sheet_name: str = "Dettaglio", theme: Optional[dict] = None, original_data: Optional[pd.DataFrame] = None, date_columns: Optional[list] = None, number_columns: Optional[list] = None, ): """ Esporta lo stato VISIBILE di una QTableWidget in Excel, mantenendo: - ordine attuale delle colonne (anche se riordinate dall'utente) - larghezze approssimate delle colonne - formattazione delle intestazioni e allineamento celle Args: table: QTableWidget da esportare file_path: Path del file Excel di output sheet_name: Nome del foglio Excel theme: Tema colori opzionale original_data: DataFrame originale con dati non formattati (opzionale) date_columns: Lista di nomi colonne nel DataFrame che contengono date (opzionale) number_columns: Lista di nomi colonne nel DataFrame che contengono numeri (opzionale) Nota: Se original_data e date_columns/number_columns sono forniti, le colonne verranno esportate come valori nativi di Excel invece che come testo, permettendo l'ordinamento e i calcoli corretti in Excel. """ if table is None or table.columnCount() == 0: raise ValueError("Tabella vuota o non valida") header = table.horizontalHeader() col_count = table.columnCount() row_count = table.rowCount() # Calcola l'ordine VISIVO delle colonne correnti visual_to_logical = [header.logicalIndex(v) for v in range(col_count)] headers_text = [ (table.horizontalHeaderItem(log_idx).text() if table.horizontalHeaderItem(log_idx) else "") for log_idx in visual_to_logical ] wb = Workbook() ws = wb.active ws.title = (sheet_name or "Dettaglio")[:31] logger.info(f"Excel export: creating workbook '{file_path}', sheet='{ws.title}' with {row_count} rows and {col_count} cols") # Tema intestazioni table_theme = (theme or {}).get("table", {}) if theme else {} def _argb(hex_color: str) -> str: h = (hex_color or "").lstrip("#").upper() if len(h) == 6: return "FF" + h if len(h) == 8: return h return "FF0078D4" # default blue header_bg = _argb(table_theme.get("header_bg", "#0078D4")) header_text = _argb(table_theme.get("header_text", "#FFFFFF")) header_fill = PatternFill(start_color=header_bg, end_color=header_bg, fill_type="solid") header_font = Font(bold=True, color=header_text) header_alignment = Alignment(horizontal="center", vertical="center") thin = Side(border_style="thin", color="DDDDDD") header_border = Border(top=thin, left=thin, right=thin, bottom=thin) # Scrivi intestazioni con stile for c, title in enumerate(headers_text, start=1): cell = ws.cell(row=1, column=c, value=title) cell.fill = header_fill cell.font = header_font cell.alignment = header_alignment cell.border = header_border # Mappa header text -> colonna DataFrame (se disponibile) header_to_df_col = {} if original_data is not None and hasattr(table, 'column_mapping'): # column_mapping รจ un dict {"Header Text": "df_column_name"} header_to_df_col = getattr(table, 'column_mapping', {}) # Set di colonne date e numeri per lookup veloce date_cols_set = set(date_columns or []) number_cols_set = set(number_columns or []) # Scrivi le righe secondo l'ordine visivo e l'allineamento esistente for r in range(row_count): for v, log_idx in enumerate(visual_to_logical, start=1): item = table.item(r, log_idx) header_text = headers_text[v - 1] # Determina se questa colonna dovrebbe usare valore nativo use_native_date = False use_native_number = False df_col_name = None if original_data is not None: # Trova il nome della colonna nel DataFrame df_col_name = header_to_df_col.get(header_text) if df_col_name: if df_col_name in date_cols_set: use_native_date = True elif df_col_name in number_cols_set: use_native_number = True # Estrai valore appropriato if use_native_date and df_col_name and df_col_name in original_data.columns: # Usa valore data nativo dal DataFrame try: raw_value = original_data.iloc[r][df_col_name] if pd.notna(raw_value): # Converti a datetime se necessario if isinstance(raw_value, str): date_value = pd.to_datetime(raw_value).date() elif isinstance(raw_value, datetime): date_value = raw_value.date() elif isinstance(raw_value, date): date_value = raw_value else: date_value = pd.to_datetime(raw_value).date() cell = ws.cell(row=r + 2, column=v, value=date_value) # Applica formato data italiano cell.number_format = 'DD/MM/YYYY' else: cell = ws.cell(row=r + 2, column=v, value="") except (ValueError, TypeError, AttributeError) as e: # Fallback a testo se conversione fallisce logger.warning(f"Date conversion failed for row {r}, col {header_text}: {e}") value = item.text() if item else "" cell = ws.cell(row=r + 2, column=v, value=value) elif use_native_number and df_col_name and df_col_name in original_data.columns: # Usa valore numerico nativo dal DataFrame try: raw_value = original_data.iloc[r][df_col_name] if pd.notna(raw_value): # Converti a numero (int o float) if isinstance(raw_value, (int, float)): numeric_value = raw_value else: # Prova a convertire stringhe o altri tipi numeric_value = float(raw_value) cell = ws.cell(row=r + 2, column=v, value=numeric_value) # Opzionale: applica formato numero con separatore migliaia # cell.number_format = '#,##0' else: cell = ws.cell(row=r + 2, column=v, value="") except (ValueError, TypeError, AttributeError) as e: # Fallback a testo se conversione fallisce logger.warning(f"Number conversion failed for row {r}, col {header_text}: {e}") value = item.text() if item else "" cell = ws.cell(row=r + 2, column=v, value=value) else: # Usa testo visualizzato nella tabella (comportamento originale) value = item.text() if item else "" cell = ws.cell(row=r + 2, column=v, value=value) # Allineamento in base all'item della tabella if item is not None: cell.alignment = _qt_alignment_to_excel(item.textAlignment()) # Larghezze colonne (approssimazione da pixel a width Excel) for v, log_idx in enumerate(visual_to_logical, start=1): px = table.columnWidth(log_idx) ws.column_dimensions[get_column_letter(v)].width = _pixels_to_excel_width(px) # Migliorie foglio: freeze pane e filtro automatico ws.freeze_panes = "A2" ws.auto_filter.ref = f"A1:{get_column_letter(col_count)}{row_count + 1}" # Altezza riga header leggermente maggiore ws.row_dimensions[1].height = 22 wb.save(file_path) logger.info(f"Excel export: saved file '{file_path}' successfully")