Of course! Working with Excel cells in Python is a common task, and the most popular library for this is openpyxl. It's powerful, relatively easy to learn, and can read, write, and even modify existing Excel files (.xlsx).

Here’s a comprehensive guide covering the basics and common operations.

Installation

First, you need to install the library. Open your terminal or command prompt and run:

pip install openpyxl

The Core Concepts: Workbook, Worksheet, and Cell

Before you can work with a cell, you need to understand the hierarchy:

• Workbook (Workbook): This is the entire Excel file (e.g., my_report.xlsx).
• Worksheet (Worksheet): A single sheet within the workbook (e.g., "Sheet1", "January Data").
• Cell (Cell): The individual intersection of a row and a column (e.g., A1, B2).

You always access a cell by first getting the workbook, then the worksheet, and finally the cell itself.

Step-by-Step Guide: Reading and Writing to Cells

Let's go through a complete workflow.

Step 1: Create a New Workbook and Select a Sheet

If you're creating a new file, you start by creating a Workbook object. A new workbook automatically has one sheet named "Sheet1".

from openpyxl import Workbook
# Create a new workbook
wb = Workbook()
# Select the active sheet (usually the first one)
ws = wb.active
# You can also select a sheet by its name
# ws = wb["Sheet1"]
# You can rename the sheet= "Employee Data"

Step 2: Write Data to a Cell

Writing to a cell is as simple as assigning a value to ws['cell_address'].

# --- Writing Data ---
# Write to a specific cell using its address (e.g., A1)
ws['A1'] = 'Name'
ws['B1'] = 'Age'
ws['C1'] = 'Department'
# You can also use the cell() method. It's useful for loops.
# .cell(row=1, column=1) is equivalent to ws['A1']
ws.cell(row=2, column=1, value='Alice')
ws.cell(row=2, column=2, value=30)
ws.cell(row=2, column=3, value='Engineering')
ws.cell(row=3, column=1, value='Bob')
ws.cell(row=3, column=2, value=25)
ws.cell(row=3, column=3, value='Marketing')
print("Data written to the worksheet.")

Step 3: Read Data from a Cell

Reading is just as easy. You access the cell and get its .value attribute.

# --- Reading Data ---
# Read from a specific cell
name_in_b2 = ws['B2'].value
print(f"The value in cell B2 is: {name_in_b2}") # Output: The value in cell B2 is: Alice
# Using the cell() method to read
age_in_b3 = ws.cell(row=3, column=2).value
print(f"The value in cell B3 is: {age_in_b3}") # Output: The value in cell B3 is: 25
# Reading a cell that doesn't exist yet returns None
value_in_d1 = ws['D1'].value
print(f"The value in cell D1 is: {value_in_d1}") # Output: The value in cell D1 is: None

Step 4: Save the Workbook

This is a crucial step! If you don't save, your changes will be lost.

# Save the workbook to a file
# Make sure the file doesn't already exist, or it will be overwritten.
filename = 'employee_data.xlsx'
wb.save(filename)
print(f"Workbook saved as {filename}")

Common Operations and Features

Here are some other useful things you'll frequently need to do.

Looping Through Rows and Columns

It's very common to process data in a range. openpyxl makes this easy.

# Let's add some more data first
ws.append(['Charlie', 35, 'Sales']) # .append() adds a new row
ws.append(['Diana', 28, 'HR'])
# --- Looping Through Data ---
print("\n--- Looping through rows ---")
for row in ws.iter_rows(min_row=2, max_row=4, min_col=1, max_col=3):
    # Each 'row' is a tuple of cell objects
    name = row[0].value
    age = row[1].value
    department = row[2].value
    print(f"Name: {name}, Age: {age}, Department: {department}")
print("\n--- Looping through columns ---")
for column in ws.iter_cols(min_row=1, max_row=4, min_col=1, max_col=3):
    # Each 'column' is a tuple of cell objects
    for cell in column:
        print(f"Cell {cell.coordinate} has value: {cell.value}")

Modifying Cell Properties (Styling)

You can change the font, color, borders, and alignment of cells.

from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
# Get the cell you want to style
header_cell = ws['A1']
# 1. Font
bold_font = Font(name='Calibri', size=11, bold=True, color='FFFFFF')
header_cell.font = bold_font
# 2. Fill Color (background)
blue_fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')
header_cell.fill = blue_fill
# 3. Alignment
header_cell.alignment = Alignment(horizontal='center', vertical='center')
# 4. Borders (a bit more complex)
thin_border = Border(
    left=Side(style='thin'),
    right=Side(style='thin'),
    top=Side(style='thin'),
    bottom=Side(style='thin')
)
# Apply border to a range of cells
for row in ws['A1:C4']:
    for cell in row:
        cell.border = thin_border
print("\nCell styling applied.")

Formulas

You can write formulas just like you would in Excel.

# Add a "Total Bonus" column header
ws['D1'] = 'Total Bonus'
# Add a formula to a cell
# Assume bonus is 10% of age for simplicity
ws['D2'] = '=B2*0.1'
ws['D3'] = '=B3*0.1'
# Save to see the formula results in Excel
wb.save('employee_data_with_formulas.xlsx')
print("Workbook with formulas saved.")

Reading from an Existing Excel File

What if you don't want to create a new file, but just read one? It's just as simple.

from openpyxl import load_workbook
# Load an existing workbook
try:
    existing_wb = load_workbook('employee_data.xlsx')
    existing_ws = existing_wb.active
    # Read data from the existing file
    print("\n--- Reading from existing file ---")
    print(f"Sheet name: {existing_ws.title}")
    print(f"Value in A2: {existing_ws['A2'].value}")
    print(f"Value in C4: {existing_ws.cell(row=4, column=3).value}")
    # You can also modify it and save it with a new name
    existing_ws['E1'] = 'Notes'
    existing_ws['E2'] = 'Top Performer'
    existing_wb.save('employee_data_modified.xlsx')
    print("Existing file read and modified, saved as 'employee_data_modified.xlsx'")
except FileNotFoundError:
    print("Error: The file 'employee_data.xlsx' was not found.")

Summary Table: Key openpyxl Actions