from bs4 import BeautifulSoup
import pandas as pd

def extract_and_export_to_excel(file_path, output_excel):
    try:
        with open(file_path, 'r', encoding='utf-8') as f:
            soup = BeautifulSoup(f, 'html.parser')
    except Exception as e:
        print(f"Error reading file: {e}")
        return

    try:
        tables = soup.find_all('table')
        target_table = None
        header = []
        data = []

        # Find table with parameter rows (title attribute)
        for table in tables:
            for row in table.find_all('tr'):
                tds = row.find_all('td')
                if tds and tds[0].has_attr('title') and '=' in tds[0]['title']:
                    target_table = table
                    break
            if target_table:
                break

        if not target_table:
            print("No table with parameter rows found.")
            return

        rows = target_table.find_all('tr')
        for row in rows:
            tds = row.find_all('td')
            if not tds:
                continue

            if not header:
                header = [td.get_text(strip=True) for td in tds]
                continue

            title = tds[0].get('title')
            if title and '=' in title:
                values = [td.get_text(strip=True) for td in tds]
                metrics = {header[i] if i < len(header) else f'Col{i+1}': val for i, val in enumerate(values)}
                params = dict(item.strip().split('=') for item in title.strip('; ').split(';'))
                merged = {**metrics, **params}
                data.append(merged)

        if data:
            df = pd.DataFrame(data)
            param_keys = [k for k in df.columns if k not in header]
            df = df[header + param_keys]
            # Convert numeric columns to proper numbers
            for col in df.columns:
                try:
                    df[col] = pd.to_numeric(df[col])
                except Exception:
                    pass
            df.to_excel(output_excel, index=False)
            print(f"Exported {len(data)} rows to {output_excel}")
        else:
            print("No data rows found.")
    except Exception as e:
        print(f"Error: {e}")

# Usage
extract_and_export_to_excel('OptimizationReport.htm', 'OptimizationFullReport.xlsx')