# First, install xlrd if you do not have it
# On Windows, open Command Prompt and run:
# pip install xlrd
import xlrd
print('xlrd imported!')

# Example: Open an Excel .xls file and get the Book object
workbook = xlrd.open_workbook('example.xls')
print('Workbook loaded:', workbook)

# Get all sheet names in the workbook
sheets = workbook.sheet_names()
print('Sheet names:', sheets)

# Access a sheet by index
sheet0 = workbook.sheet_by_index(0)
print('First sheet:', sheet0.name)

# Access a sheet by name
sheet_by_name = workbook.sheet_by_name(sheets[0])
print('Accessed sheet:', sheet_by_name.name)

# Get number of rows and columns in a sheet
row_count = sheet0.nrows
col_count = sheet0.ncols
print('Rows:', row_count, '| Columns:', col_count)

# Read a single cell value
cell_value = sheet0.cell_value(rowx=0, colx=0)
print('Top left cell:', cell_value)

# Read an entire row as a list
first_row = sheet0.row_values(0)
print('First row values:', first_row)

# Read an entire column as a list
first_col = sheet0.col_values(0)
print('First column values:', first_col)

# Loop through all rows and print cell values in the first column
for i in range(sheet0.nrows):
    print(f'Row {i}:', sheet0.cell_value(i, 0))

# Read all sheet data into a list of lists
all_data = [sheet0.row_values(row) for row in range(sheet0.nrows)]
print('All data:', all_data)

# Read a cell and its type
cell = sheet0.cell(0, 0)
print('Cell value:', cell.value, '| Cell type:', cell.ctype)

# Filter rows by a value in a specific column
target_value = 'Yes'
filtered_rows = [sheet0.row_values(r) for r in range(sheet0.nrows) if sheet0.cell_value(r, 1) == target_value]
print('Rows where column 1 is Yes:', filtered_rows)

# Skip the header row and read data rows only
for row_num in range(1, sheet0.nrows):
    print('Data row:', sheet0.row_values(row_num))

# Handling missing files: Try to open a file that does not exist
try:
    broken_book = xlrd.open_workbook('missing.xls')
except FileNotFoundError as e:
    print('File not found:', e)

# Handling cell errors: Catch out-of-range errors
try:
    bad_cell = sheet0.cell_value(rowx=999, colx=999)
except IndexError as e:
    print('Cell not found:', e)

# Best practice: Read all sheets in a workbook
for sheet_name in workbook.sheet_names():
    sh = workbook.sheet_by_name(sheet_name)
    print(f'Sheet {sheet_name}: {sh.nrows} rows, {sh.ncols} columns')

# Best practice: Handle all cell types
cell_types = ['empty', 'text', 'number', 'date', 'boolean', 'error']
for r in range(sheet0.nrows):
    for c in range(sheet0.ncols):
        cell = sheet0.cell(r, c)
        print(f'Cell[{r},{c}] type:', cell_types[cell.ctype], '| value:', cell.value)

# Mini-project: Print summary stats for every sheet
def summarize_xls(path):
    wb = xlrd.open_workbook(path)
    for sheet in wb.sheets():
        print('Sheet:', sheet.name)
        print('Rows:', sheet.nrows, 'Cols:', sheet.ncols)
        print('First row:', sheet.row_values(0))
        print('---')

summarize_xls('example.xls')