Convert HTML Tables to Excel Reports in Python

import pandas as pd
from bs4 import BeautifulSoup
from pathlib import Path

def html_table_to_excel(html_file: str, excel_file: str) -> None:
    """Convert HTML table to formatted Excel report."""
    with open(html_file, 'r', encoding='utf-8') as f:
        html_content = f.read()
    
    soup = BeautifulSoup(html_content, 'html.parser')
    table = soup.find('table')
    
    if not table:
        raise ValueError("No table found in HTML file")
    
    # Extract table data
    data = []
    for row in table.find_all('tr'):
        cols = row.find_all(['th', 'td'])
        data.append([col.get_text(strip=True) for col in cols])
    
    # Create DataFrame
    df = pd.DataFrame(data[1:], columns=data[0]) if data else pd.DataFrame()
    
    # Write to Excel with formatting
    with pd.ExcelWriter(excel_file, engine='openpyxl') as writer:
        df.to_excel(writer, index=False, sheet_name='Report')
        
        # Auto-adjust column widths
        worksheet = writer.sheets['Report']
        for column in worksheet.columns:
            max_length = max(len(str(cell.value or '')) for cell in column)
            worksheet.column_dimensions[column[0].column_letter].width = max_length + 2

if __name__ == "__main__":
    # Create sample HTML table
    sample_html = Path("sample_table.html")
    sample_html.write_text(
        "<table><tr><th>Name</th><th>Age</th><th>City</th></tr>"
        "<tr><td>Alice</td><td>30</td><td>New York</td></tr>"
        "<tr><td>Bob</td><td>25</td><td>Los Angeles</td></tr>"
        "<tr><td>Charlie</td><td>35</td><td>Chicago</td></tr></table>"
    )
    
    html_table_to_excel("sample_table.html", "report.xlsx")
    print("Excel report generated: report.xlsx")
    
    # Cleanup
    sample_html.unlink()