""" MediaTrack Excel Export Generator Generates Excel files from MediaTrack product data with filtering capabilities. Uses openpyxl for server-side Excel generation. """ import re from datetime import datetime, date from pathlib import Path from typing import Optional, Dict, List, Any _ILLEGAL_CHARS_RE = re.compile(r'[\x00-\x08\x0b\x0c\x0e-\x1f\x7f]') def _safe_str(value) -> str: """Converte un valore in stringa rimuovendo caratteri illegali per openpyxl.""" if not value: return "" return _ILLEGAL_CHARS_RE.sub('', str(value)) import io from openpyxl import Workbook from openpyxl.utils import get_column_letter from openpyxl.styles import Font, Alignment, PatternFill, Border, Side, numbers def generate_export_excel( products: List[Dict[str, Any]], filtri: Dict[str, Any], includi_gemma: bool = False ) -> io.BytesIO: """ Generate Excel file from filtered product data. Args: products: List of product dictionaries from database query filtri: Filter parameters used (for metadata sheet) includi_gemma: Whether Gemma products are included Returns: BytesIO object containing the Excel file """ wb = Workbook() ws = wb.active ws.title = "Prodotti MediaTrack" # Define column structure # Base columns (always present) base_columns = [ ("ID Prodotto", "id_media", 15), ("Fonte", "fonte", 10), ("Titolo", "titolo", 35), ("Tipo", "tipo", 12), ("Distributore", "distributore", 25), ("Anno Produzione", "anno_produzione", 15), ] # Serie-specific columns (conditional) serie_columns = [ ("Formato", "formato", 15), ("Piattaforma", "piattaforma", 15), ] # Event columns (last event info) event_columns = [ ("Mercato/Contesto", "contesto", 20), ("Data Evento", "data_evento", 12), ("Ask Italia", "ask_ita", 15), ("Ask Spagna", "ask_spa", 15), ("Delivery", "delivery", 15), ("Note", "note", 30), ] # IMDb columns imdb_columns = [ ("Rating IMDb", "rating_imdb", 12), ("Genere", "genere", 25), ] # Gemma TV channel columns (conditional - only if includi_gemma=True) gemma_columns = [] if includi_gemma: gemma_columns = [ ("RDA", "v_rda", 8), ("C5", "v_c5", 8), ("I1", "v_i1", 8), ("R4", "v_r4", 8), ("LAS", "v_las", 8), ("I2", "v_i2", 8), ("IRIS", "v_iris", 8), ("TOP", "v_top", 8), ("FOC", "v_foc", 8), ("C20", "v_c20", 8), ("C34", "v_c34", 8), ("27", "v_27", 8), ] # Combine all columns all_columns = base_columns + serie_columns + event_columns + imdb_columns + gemma_columns # Header styling header_fill = PatternFill(start_color="FF0078D4", end_color="FF0078D4", fill_type="solid") header_font = Font(bold=True, color="FFFFFFFF") header_alignment = Alignment(horizontal="center", vertical="center", wrap_text=True) thin = Side(border_style="thin", color="FFDDDDDD") header_border = Border(top=thin, left=thin, right=thin, bottom=thin) # Write headers for col_idx, (header_text, _, col_width) in enumerate(all_columns, start=1): cell = ws.cell(row=1, column=col_idx, value=header_text) cell.fill = header_fill cell.font = header_font cell.alignment = header_alignment cell.border = header_border ws.column_dimensions[get_column_letter(col_idx)].width = col_width # Header row height ws.row_dimensions[1].height = 22 # Cell styling for data rows data_alignment_left = Alignment(horizontal="left", vertical="center") data_alignment_center = Alignment(horizontal="center", vertical="center") data_alignment_right = Alignment(horizontal="right", vertical="center") # Write data rows for row_idx, product in enumerate(products, start=2): for col_idx, (_, field_name, _) in enumerate(all_columns, start=1): value = product.get(field_name, "") # Handle None values if value is None: value = "" cell = ws.cell(row=row_idx, column=col_idx) # Type-specific formatting if field_name == "data_evento" and value: # Date field - native Excel date try: if isinstance(value, str): date_value = datetime.strptime(value, "%Y-%m-%d").date() elif isinstance(value, datetime): date_value = value.date() elif isinstance(value, date): date_value = value else: date_value = None if date_value: cell.value = date_value cell.number_format = 'DD/MM/YYYY' cell.alignment = data_alignment_center else: cell.value = "" except (ValueError, TypeError): cell.value = str(value) cell.alignment = data_alignment_center elif field_name in ["ask_ita", "ask_spa"]: # Currency fields - native Excel number with € format try: if value: numeric_value = float(value) cell.value = numeric_value cell.number_format = '#,##0 "€"' cell.alignment = data_alignment_right else: cell.value = "" except (ValueError, TypeError): cell.value = str(value) cell.alignment = data_alignment_right elif field_name == "rating_imdb": # Rating field - native Excel number try: if value: numeric_value = float(value) cell.value = numeric_value cell.number_format = '0.0' cell.alignment = data_alignment_center else: cell.value = "" except (ValueError, TypeError): cell.value = str(value) cell.alignment = data_alignment_center elif field_name == "anno_produzione": # Year field - integer try: if value: cell.value = int(value) cell.alignment = data_alignment_center else: cell.value = "" except (ValueError, TypeError): cell.value = str(value) cell.alignment = data_alignment_center elif field_name in ["fonte", "tipo", "data_evento", "contesto"]: # Centered fields cell.value = str(value) cell.alignment = data_alignment_center elif field_name == "note": # Note field - text wrap, left align cell.value = _safe_str(value) cell.alignment = Alignment(horizontal="left", vertical="center", wrap_text=True) else: # Default: left aligned text cell.value = _safe_str(value) cell.alignment = data_alignment_left # Freeze header row ws.freeze_panes = "A2" # Add autofilter if products: ws.auto_filter.ref = f"A1:{get_column_letter(len(all_columns))}{len(products) + 1}" # Add metadata sheet with filter info ws_meta = wb.create_sheet("Info Esportazione") ws_meta.column_dimensions['A'].width = 25 ws_meta.column_dimensions['B'].width = 40 meta_header_fill = PatternFill(start_color="FF0078D4", end_color="FF0078D4", fill_type="solid") meta_header_font = Font(bold=True, color="FFFFFFFF") # Metadata headers meta_row = 1 cell = ws_meta.cell(row=meta_row, column=1, value="Parametro") cell.fill = meta_header_fill cell.font = meta_header_font cell = ws_meta.cell(row=meta_row, column=2, value="Valore") cell.fill = meta_header_fill cell.font = meta_header_font # Export info meta_row += 1 ws_meta.cell(row=meta_row, column=1, value="Data Esportazione") ws_meta.cell(row=meta_row, column=2, value=datetime.now().strftime("%d/%m/%Y %H:%M")) meta_row += 1 ws_meta.cell(row=meta_row, column=1, value="Totale Prodotti") ws_meta.cell(row=meta_row, column=2, value=len(products)) meta_row += 1 ws_meta.cell(row=meta_row, column=1, value="Include Gemma") ws_meta.cell(row=meta_row, column=2, value="Sì" if includi_gemma else "No") # Filter parameters if filtri.get("distributori"): meta_row += 1 ws_meta.cell(row=meta_row, column=1, value="Distributori") ws_meta.cell(row=meta_row, column=2, value=", ".join(filtri["distributori"])) if filtri.get("tipologia"): meta_row += 1 ws_meta.cell(row=meta_row, column=1, value="Tipologia") ws_meta.cell(row=meta_row, column=2, value=filtri["tipologia"]) if filtri.get("data_da"): meta_row += 1 ws_meta.cell(row=meta_row, column=1, value="Data Da") ws_meta.cell(row=meta_row, column=2, value=filtri["data_da"]) # Save to BytesIO output = io.BytesIO() wb.save(output) output.seek(0) return output def get_export_filename(filtri: Dict[str, Any]) -> str: """ Generate a descriptive filename based on filter parameters. Args: filtri: Filter parameters Returns: Filename string (without extension) """ timestamp = datetime.now().strftime("%Y%m%d_%H%M%S") parts = ["MediaTrack_Export"] if filtri.get("tipologia"): parts.append(filtri["tipologia"]) if filtri.get("distributori") and len(filtri["distributori"]) == 1: # Single distributor - include in filename parts.append(filtri["distributori"][0][:15]) # Truncate if too long parts.append(timestamp) return "_".join(parts)