read_multiple_excel_xlwings.py

"""
Reading Multiple Excel Files with xlwings
==========================================

Demonstrates how to read multiple Excel files from a list using xlwings.

NOTE: xlwings (free) requires Microsoft Excel to be installed on macOS/Windows.
      When Excel is not available the script automatically falls back to openpyxl
      so the code still runs without modification.

Dependencies
------------
    pip install xlwings openpyxl pandas
"""

import xlwings as xw
import openpyxl
import pandas as pd
from pathlib import Path

# ==============================================================================
# CONFIGURATION
# ==============================================================================

SAMPLE_DIR = Path(__file__).parent / "sample_files"

# List of Excel files to read (exclude temp lock files starting with ~$)
EXCEL_FILES = [
    SAMPLE_DIR / "Q1_2024.xlsx",
    SAMPLE_DIR / "Q2_2024.xlsx",
    SAMPLE_DIR / "Q3_2024.xlsx",
    SAMPLE_DIR / "Q4_2024.xlsx",
]


# ==============================================================================
# XLWINGS READER  (falls back to openpyxl when Excel is not installed)
# ==============================================================================

def _is_excel_available() -> bool:
    """Return True if Microsoft Excel is installed and xlwings can connect."""
    try:
        app = xw.App(visible=False, add_book=False)
        app.quit()
        return True
    except Exception:
        return False


EXCEL_AVAILABLE = _is_excel_available()
print(f"Microsoft Excel available via xlwings: {EXCEL_AVAILABLE}")
print()


def read_sheet(filepath: Path, sheet_name: str | int = 0) -> pd.DataFrame:
    """
    Read one sheet from an Excel file and return a DataFrame.

    Uses xlwings when Excel is installed; falls back to openpyxl otherwise.

    Parameters
    ----------
    filepath   : path to the .xlsx file
    sheet_name : sheet name (str) or 0-based index (int); defaults to first sheet
    """
    if EXCEL_AVAILABLE:
        return _read_with_xlwings(filepath, sheet_name)
    return _read_with_openpyxl(filepath, sheet_name)


def _read_with_xlwings(filepath: Path, sheet_name: str | int = 0) -> pd.DataFrame:
    """Read via xlwings (requires Microsoft Excel)."""
    app = xw.App(visible=False, add_book=False)
    try:
        wb = app.books.open(str(filepath))
        sheet = wb.sheets[sheet_name]
        data = sheet.used_range.value      # list of rows; first row = headers
        wb.close()
    finally:
        app.quit()

    if not data:
        return pd.DataFrame()

    headers, *rows = data
    return pd.DataFrame(rows, columns=headers)


def _read_with_openpyxl(filepath: Path, sheet_name: str | int = 0) -> pd.DataFrame:
    """Read via openpyxl (fallback; no Excel required)."""
    wb = openpyxl.load_workbook(str(filepath), read_only=True, data_only=True)
    ws = wb.worksheets[sheet_name] if isinstance(sheet_name, int) else wb[sheet_name]

    data = [[cell.value for cell in row] for row in ws.iter_rows()]
    wb.close()

    if not data:
        return pd.DataFrame()

    headers, *rows = data
    return pd.DataFrame(rows, columns=headers)


def get_sheet_names(filepath: Path) -> list[str]:
    """Return all sheet names in the workbook."""
    if EXCEL_AVAILABLE:
        app = xw.App(visible=False, add_book=False)
        try:
            wb = app.books.open(str(filepath))
            names = [s.name for s in wb.sheets]
            wb.close()
        finally:
            app.quit()
        return names
    wb = openpyxl.load_workbook(str(filepath), read_only=True)
    names = wb.sheetnames
    wb.close()
    return names


# ==============================================================================
# 1. READ EACH FILE FROM THE LIST INDIVIDUALLY
# ==============================================================================

print("=" * 70)
print("1. READING EACH FILE INDIVIDUALLY")
print(f"   Engine: {'xlwings (Excel)' if EXCEL_AVAILABLE else 'openpyxl (fallback)'}")
print("=" * 70)

dataframes: dict[str, pd.DataFrame] = {}

for filepath in EXCEL_FILES:
    if not filepath.exists():
        print(f"  ✗ Not found: {filepath.name}")
        continue

    df = read_sheet(filepath)
    dataframes[filepath.name] = df

    print(f"\n  File : {filepath.name}")
    print(f"  Shape: {df.shape[0]} rows × {df.shape[1]} columns")
    print(f"  Cols : {list(df.columns)}")
    print(df.head(3).to_string(index=False))

print()

# ==============================================================================
# 2. COMBINE ALL FILES INTO ONE DATAFRAME
# ==============================================================================

print("=" * 70)
print("2. COMBINED DATAFRAME (all files stacked)")
print("=" * 70)

if dataframes:
    combined = pd.concat(dataframes.values(), ignore_index=True)
    print(f"Total rows : {len(combined)}")
    print(f"Total cols : {len(combined.columns)}")
    print()
    print(combined.head(10).to_string(index=False))
else:
    print("No files were loaded.")

print()

# ==============================================================================
# 3. READ ALL SHEETS FROM A SINGLE FILE
# ==============================================================================

print("=" * 70)
print("3. READ ALL SHEETS FROM A SINGLE FILE")
print("=" * 70)

target_file = SAMPLE_DIR / "annual_2024.xlsx"

if target_file.exists():
    sheet_names = get_sheet_names(target_file)
    print(f"  File  : {target_file.name}")
    print(f"  Sheets: {sheet_names}\n")

    for name in sheet_names:
        df = read_sheet(target_file, sheet_name=name)
        print(f"  Sheet '{name}': {len(df)} rows × {len(df.columns)} cols")
else:
    print(f"  ✗ {target_file.name} not found.")

print()

# ==============================================================================
# 4. DYNAMIC DISCOVERY — READ ALL .xlsx IN A FOLDER
# ==============================================================================

print("=" * 70)
print("4. READ ALL .xlsx FILES IN A FOLDER (auto-discovered)")
print("=" * 70)

discovered = sorted(
    f for f in SAMPLE_DIR.glob("*.xlsx") if not f.name.startswith("~$")
)
print(f"  Found {len(discovered)} files in {SAMPLE_DIR.name}/\n")

all_frames: list[pd.DataFrame] = []

for filepath in discovered:
    df = read_sheet(filepath)
    df["_source_file"] = filepath.name  # tag each row with its source
    all_frames.append(df)
    print(f"  ✓ {filepath.name:40s} {len(df):>4} rows")

print()
print("Done.")