Python’s openpyxl library is the standard for reading and writing Excel files (.xlsx format). It allows you to read cell values, write data, create charts, apply formatting, and even work with formulas.
This lesson covers everything you need to work with Excel files: loading workbooks, accessing sheets, reading and writing cells, iterating over rows, applying basic formatting, and creating new Excel files from scratch. You will also learn to handle large files efficiently and work with pandas as an alternative.
🕯️ Magic Note
Excel files (.xlsx) are actually ZIP archives containing XML files. The openpyxl library parses these XML files, so you do not need to understand the underlying format. It works with both .xlsx (Excel 2007+) and .xlsm (macro-enabled) files.
Bash
pip install openpyxl
# For reading old .xls files (Excel 97-2003)
pip install xlrd
# For writing old .xls files
pip install xlwt
Python
from openpyxl import load_workbook, Workbook
# Load existing workbook
wb = load_workbook(“data.xlsx”)
# Create new workbook
new_wb = Workbook()
# Save workbook
new_wb.save(“output.xlsx”)
# Get sheet names
print(wb.sheetnames)
Python
from openpyxl import load_workbook, Workbook
wb = load_workbook(“data.xlsx”)
# Get active sheet
sheet = wb.active
# Get sheet by name
sheet = wb[“Sheet1”]
# Get sheet by index
sheet = wb.worksheets[0]
# Create new sheet
new_sheet = wb.create_sheet(“NewSheet”, 0) # Insert at position 0
# Remove a sheet
wb.remove(wb[“Sheet2”])
# Rename a sheet
sheet.title = “RenamedSheet”
Python
from openpyxl import load_workbook
wb = load_workbook(“data.xlsx”)
sheet = wb.active
# By cell coordinates (1-indexed)
cell_a1 = sheet[“A1”]
value_a1 = sheet[“A1”].value
# By row and column numbers
cell = sheet.cell(row=2, column=3)
value = cell.value
# Get cell properties
print(f”Value: {cell.value}”)
print(f”Row: {cell.row}, Column: {cell.column}”)
print(f”Coordinate: {cell.coordinate}”) # e.g., “C2”
print(f”Data type: {cell.data_type}”)
Python
from openpyxl import Workbook
wb = Workbook()
sheet = wb.active
# Write by coordinate
sheet[“A1”] = “Hello”
sheet[“B1”] = 42
sheet[“C1”] = 3.14159
# Write by row and column
sheet.cell(row=2, column=1, value=”World”)
sheet.cell(row=2, column=2, value=100)
# Write formulas
sheet[“D1”] = “=A1 & \” \” & A2″ # Concatenation
sheet[“E1”] = “=SUM(B1:B2)”
wb.save(“written.xlsx”)
🕯️ Magic Note
You can write formulas as strings. Excel will evaluate them when the file is opened. But openpyxl will not calculate the formula results; it stores the formula text.
Python
from openpyxl import load_workbook
wb = load_workbook(“data.xlsx”)
sheet = wb.active
# Iterate over all rows
for row in sheet.iter_rows(values_only=True):
print(row) # tuple of values
# Iterate over rows with rows parameter
for row in sheet.iter_rows(min_row=2, max_row=10, min_col=1, max_col=5, values_only=True):
name, age, city = row[0], row[1], row[2]
print(f”{name} is {age} years old”)
# Iterate over columns
for col in sheet.iter_cols(min_row=1, max_row=5, min_col=1, max_col=3, values_only=True):
print(col)
# Get used range
for row in sheet.iter_rows(values_only=True):
if all(cell is None for cell in row):
break
print(row)
Python
from openpyxl import Workbook
data = [
{“name”: “Ali”, “age”: 25, “city”: “Tehran”},
{“name”: “Sara”, “age”: 30, “city”: “Shiraz”},
{“name”: “Reza”, “age”: 28, “city”: “Isfahan”}
]
def write_to_excel(data, output_path):
wb = Workbook()
sheet = wb.active
# Write headers
if data:
headers = list(data[0].keys())
for col, header in enumerate(headers, 1):
sheet.cell(row=1, column=col, value=header)
# Write data rows
for row_idx, row_data in enumerate(data, 2):
for col_idx, key in enumerate(headers, 1):
sheet.cell(row=row_idx, column=col_idx, value=row_data.get(key))
wb.save(output_path)
print(f”Excel file saved: {output_path}”)
write_to_excel(data, “output.xlsx”)
Python
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
wb = Workbook()
sheet = wb.active
sheet.title = “Formatted”
# Add sample data
data = [[“Name”, “Score”], [“Ali”, 95], [“Sara”, 87], [“Reza”, 92]]
for row_idx, row in enumerate(data, 1):
for col_idx, value in enumerate(row, 1):
sheet.cell(row=row_idx, column=col_idx, value=value)
# Header formatting
header_font = Font(bold=True, size=12, color=”FFFFFF”)
header_fill = PatternFill(start_color=”3366CC”, end_color=”3366CC”, fill_type=”solid”)
header_alignment = Alignment(horizontal=”center”, vertical=”center”)
for col in range(1, 3):
cell = sheet.cell(row=1, column=col)
cell.font = header_font
cell.fill = header_fill
cell.alignment = header_alignment
# Border for all cells
thin_border = Border(
left=Side(style=”thin”),
right=Side(style=”thin”),
top=Side(style=”thin”),
bottom=Side(style=”thin”)
)
for row in sheet.iter_rows(max_row=4, max_col=2):
for cell in row:
cell.border = thin_border
# Conditional formatting (color based on value)
for row in range(2, 5):
score_cell = sheet.cell(row=row, column=2)
if score_cell.value and score_cell.value >= 90:
score_cell.font = Font(color=”006600″, bold=True)
score_cell.fill = PatternFill(start_color=”CCFFCC”, end_color=”CCFFCC”, fill_type=”solid”)
elif score_cell.value and score_cell.value < 90:
score_cell.font = Font(color=”CC0000″)
# Adjust column width
sheet.column_dimensions[“A”].width = 15
sheet.column_dimensions[“B”].width = 12
wb.save(“formatted.xlsx”)
🕯️ Magic Note
Formatting in openpyxl applies to individual cells, not ranges. To format many cells at once, iterate over them. The PatternFill class uses hex color codes (e.g., “3366CC” is a shade of blue).
Python
from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference
wb = Workbook()
sheet = wb.active
sheet.title = “Sales Data”
# Sample data
data = [
[“Month”, “Sales”, “Expenses”],
[“Jan”, 1000, 600],
[“Feb”, 1200, 700],
[“Mar”, 900, 500],
[“Apr”, 1500, 800],
[“May”, 1800, 900]
]
for row in data:
sheet.append(row)
# Create bar chart
chart = BarChart()
chart.title = “Monthly Performance”
chart.x_axis.title = “Month”
chart.y_axis.title = “Amount ($)”
# Define data range
data_ref = Reference(sheet, min_col=2, max_col=3, min_row=1, max_row=6)
categ_ref = Reference(sheet, min_col=1, min_row=2, max_row=6)
chart.add_data(data_ref, titles_from_data=True)
chart.set_categories(categ_ref)
# Add chart to sheet
sheet.add_chart(chart, “E5”)
wb.save(“chart.xlsx”)
Python
from openpyxl import Workbook
from openpyxl.styles import Alignment, Font
wb = Workbook()
sheet = wb.active
# Merge cells A1 to E1
sheet.merge_cells(“A1:E1”)
sheet[“A1”] = “Annual Report 2025”
sheet[“A1”].font = Font(size=16, bold=True)
sheet[“A1″].alignment = Alignment(horizontal=”center”)
# Merge by row and column indices
sheet.merge_cells(start_row=2, start_column=1, end_row=2, end_column=5)
sheet[“A2”] = “Subtitle”
# Unmerge if needed
# sheet.unmerge_cells(“A1:E1”)
wb.save(“merged.xlsx”)
Python
from openpyxl import load_workbook
# Read-only mode (minimal memory, cannot write)
wb = load_workbook(“large_file.xlsx”, read_only=True)
sheet = wb.active
for row in sheet.iter_rows(values_only=True):
# Process each row without loading entire file into memory
process_row(row)
wb.close() # Important for read-only mode
# Write-only mode (stream writing, cannot read)
from openpyxl import Workbook
wb = Workbook(write_only=True)
sheet = wb.create_sheet()
for row_data in large_dataset:
sheet.append(row_data) # Write rows one by one without keeping in memory
wb.save(“large_output.xlsx”)
🕯️ Magic Note
Read-only mode is essential for processing huge Excel files (hundreds of thousands of rows). Write-only mode is useful for generating large files without memory overhead. Both have limitations (no formatting, no random access), but they save significant memory.
Python
import xlrd
# Open old Excel file
workbook = xlrd.open_workbook(“old_data.xls”)
sheet = workbook.sheet_by_index(0)
# Read cell values
for row in range(sheet.nrows):
for col in range(sheet.ncols):
value = sheet.cell_value(row, col)
print(value, end=” “)
print()
# Note: xlrd only reads, does not write
# For writing .xls files, use xlwt
Python
import pandas as pd
# Read Excel file
df = pd.read_excel(“data.xlsx”, sheet_name=”Sheet1″)
print(df.head()) # First 5 rows
print(df.info()) # Data types and info
# Filter data
filtered = df[df[“age”] > 25]
# Write to Excel
filtered.to_excel(“filtered_output.xlsx”, index=False)
# Read multiple sheets
all_sheets = pd.read_excel(“data.xlsx”, sheet_name=None)
for sheet_name, df_sheet in all_sheets.items():
print(f”Sheet: {sheet_name}, Shape: {df_sheet.shape}”)
Python
import csv
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.chart import BarChart, Reference
from datetime import datetime
def create_excel_report(csv_path, output_path):
“””Generate formatted Excel report from CSV data.”””
# Read CSV data
with open(csv_path, “r”, encoding=”utf-8″) as f:
reader = csv.DictReader(f)
data = list(reader)
headers = reader.fieldnames
# Create workbook
wb = Workbook()
sheet = wb.active
sheet.title = “Report”
# Title and metadata
sheet.merge_cells(“A1:D1”)
sheet[“A1″] = f”Data Report – {datetime.now().strftime(‘%Y-%m-%d’)}”
sheet[“A1”].font = Font(size=14, bold=True)
sheet[“A1″].alignment = Alignment(horizontal=”center”)
sheet[“A2″] = f”Total Records: {len(data)}”
sheet[“A2”].font = Font(italic=True)
# Write headers
for col, header in enumerate(headers, 1):
cell = sheet.cell(row=4, column=col, value=header)
cell.font = Font(bold=True, color=”FFFFFF”)
cell.fill = PatternFill(start_color=”3366CC”, end_color=”3366CC”, fill_type=”solid”)
cell.alignment = Alignment(horizontal=”center”)
# Write data rows
for row_idx, row in enumerate(data, 5):
for col, header in enumerate(headers, 1):
value = row.get(header, “”)
sheet.cell(row=row_idx, column=col, value=value)
# Auto-adjust column widths
for col in range(1, len(headers) + 1):
max_length = 0
column_letter = sheet.cell(row=4, column=col).column_letter
for row in range(4, len(data) + 5):
cell_value = sheet.cell(row=row, column=col).value
if cell_value:
max_length = max(max_length, len(str(cell_value)))
sheet.column_dimensions[column_letter].width = min(max_length + 2, 30)
# Save report
wb.save(output_path)
print(f”Report saved: {output_path}”)
# create_excel_report(“sales.csv”, “sales_report.xlsx”)
- Forgetting that row and column indices start at 1 (not 0)
- Loading large files without read_only mode (memory exhaustion)
- Not closing read_only workbooks (wb.close())
- Assuming formulas are evaluated (openpyxl stores formulas, not results)
- Using .xls files with openpyxl (use xlrd for old format)
- Forgetting to save after making changes
- How do you load an existing Excel file with openpyxl?
- Write code to read the value of cell B3 from an Excel sheet.
- How do you write a list of dictionaries to an Excel file with headers?
- What is the difference between read_only and write_only modes?
- How do you add a bar chart to an Excel sheet?
- What library would you use to read old .xls files?
⚡ Whisper
Excel files are the language of business. Spreadsheets track sales, inventory, budgets, and schedules. With openpyxl, you speak that language. You read cells like sheet[“B3”].value. You write rows with sheet.append(row). You add charts, merge cells, apply colors, and set fonts. The same reports that take hours manually run in seconds automatically. Monthly reports become one script. Data entry becomes a loop. Inventory updates become a function. Excel automation is not just convenience. It is transformation. You save time. You reduce errors. You focus on analysis, not copying. Learn the basics. Then iterate. Then format. Then chart. Your spreadsheets will never be the same.
