excel-read

Excel Read Operations with Python openpyxl

Read, analyze, and extract data from Excel files using Python openpyxl.

Environment

Before running any script, determine the Python runner (checked in order, first match wins):

1. Global: Read ~/.claude/qingyi.local.md, parse YAML frontmatter for excel_runner
2. Project-level: Read .claude/qingyi.local.md from the project root, parse YAML frontmatter for excel_runner ( overrides global)
3. Default: python

Run command: <excel_runner> <script_path>

Workflow

1. Create Python script at scripts/excel/ from project root
2. Execute script: <excel_runner> scripts/excel/<script_name>.py
3. Keep scripts for reference and reusability

Output Formatting

When displaying Excel data to the user, format as markdown tables:

| Column A | Column B | Column C |
|----------|----------|----------|
| value1   | value2   | value3   |

For sparse data with many empty cells, show only populated rows/columns.

Standard Imports

from openpyxl import load_workbook
from openpyxl.utils import get_column_letter
from pathlib import Path

Reading Operations

Load Workbook

wb = load_workbook(path, data_only=True)  # data_only=True for formula results
ws = wb.active  # Active sheet
ws = wb['SheetName']  # Named sheet

Read Single Cell

value = ws['A1'].value
value = ws.cell(row=1, column=1).value

Read Range

# Specific range
for row in ws['A1:C10']:
    for cell in row:
        print(cell.value)

# All data as 2D list
data = [[cell.value for cell in row] for row in ws.iter_rows()]

# With bounds
data = [[cell.value for cell in row]
        for row in ws.iter_rows(min_row=1, max_row=10, min_col=1, max_col=5)]

Read Column/Row

col_values = [cell.value for cell in ws['A']]  # Column A
row_values = [cell.value for cell in ws[1]]  # Row 1

Iterate Rows with Values Only

for row in ws.iter_rows(values_only=True):
    print(row)  # tuple of values

Get Sheet Dimensions

print(f"Rows: {ws.max_row}, Columns: {ws.max_column}")
print(f"Data range: {ws.dimensions}")

List All Worksheets

for sheet_name in wb.sheetnames:
    print(sheet_name)

Find Cells by Value

for row in ws.iter_rows():
    for cell in row:
        if cell.value == "SearchTerm":
            print(f"Found at {cell.coordinate}")

Get Merged Cell Ranges

for merged_range in ws.merged_cells.ranges:
    print(merged_range)

Analysis Helpers

# Count non-empty cells
count = sum(1 for row in ws.iter_rows() for cell in row if cell.value is not None)

# Unique values in column
unique = set(cell.value for cell in ws['A'] if cell.value is not None)

# Summary statistics
headers = [cell.value for cell in ws[1]]
print(f"Columns: {len(headers)}, Data rows: {ws.max_row - 1}, Headers: {headers}")

Script Template

from openpyxl import load_workbook
from pathlib import Path


def main():
    path = Path(r"C:\path\to\file.xlsx")
    wb = load_workbook(path, data_only=True)

    print(f"Sheets: {wb.sheetnames}")

    for sheet_name in wb.sheetnames:
        ws = wb[sheet_name]
        print(f"\n=== {sheet_name} ===")
        print(f"Dimensions: {ws.dimensions}")

        for i, row in enumerate(ws.iter_rows(values_only=True)):
            if i >= 10:
                print("...")
                break
            print(row)


if __name__ == "__main__":
    main()