openpyxl is a Python library for reading from and writing to Excel 2010+ (.xlsx) files. This comprehensive guide covers everything from basic operations to advanced features.
Installation
Install openpyxl using pip:
pip install openpyxlBasic Operations Using openpyxl
Creating a New Workbook
from openpyxl import Workbook
# Create a new workbook
wb = Workbook()
# Get the active sheet
sheet = wb.active# Write values to cells
sheet['A1'] = 'Hello'
sheet['B1'] = 'World'
# Save the workbook
wb.save('example.xlsx')This code snippet creates a new workbook, writes values to cells A1 and B1, and saves it as 'example.xlsx'.
Reading an Existing Workbook
from openpyxlimport load_workbook
# Load the workbook
wb = load_workbook('example.xlsx')
# Get sheet names
print(wb.sheetnames)
# Select a specific sheet
sheet = wb['Sheet1']
# Read cell value
value = sheet['A1'].value
print(value)
# Read multiple cells
cell_range = sheet['A1:B2']
for row in cell_range:
for cell in row:
print(cell.value)This code demonstrates how to load an existing workbook, access sheets, and read cell values.
Working with Cells and Ranges
Cell Access Methods
# Different ways to access cellssheet['A1'] = 'Method 1'
sheet.cell(row=1, column=1, value='Method 2')# Accessing cell valuesvalue1 = sheet['A1'].value
value2 = sheet.cell(row=1, column=1).valueThe different access methods each have their advantages:
- Direct assignment (A1 style) is more readable and familiar to Excel users
- Row/column notation is better for programmatic access and loops
- Both methods provide access to the same underlying cell object
Working with Ranges
# Iterating through a rangefor row in sheet['A1:C3']:for cell in row:
print(cell.value)# Using cell rangescell_range = sheet['A1:C3']
tuple(sheet['A1:C3'])# Convert range to tuple
# Working with rows and columns
for row in sheet.iter_rows(min_row=1, max_row=3, min_col=1, max_col=3):
for cell in row:
print(cell.value)
Range operations offer several advantages:
- More efficient than accessing individual cells
- Allow for bulk operations on data
- Support both row-wise and column-wise iteration
- Can be used with list comprehensions for data extraction
Sheet Operations
Managing Sheets
# Create a new sheet
wb.create_sheet("NewSheet")
# Change sheet title
sheet.title = "Modified Sheet"
# Copy a sheet
source = wb['Sheet1']
wb.copy_worksheet(source)
# Remove a sheet
wb.remove(wb['Sheet to Remove'])Sheet Properties
# Set sheet tab color
sheet.sheet_properties.tabColor = "1072BA"
# Freeze panes
sheet.freeze_panes = 'B2'
# Set column width
sheet.column_dimensions['A'].width = 20
# Set row height
sheet.row_dimensions[1].height = 30Cell Formatting
Basic Formatting
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
# Create font
font = Font(name='Arial',
size=12,
bold=True,
italic=True,
color='FF0000')
# Apply font to cell
cell = sheet['A1']
cell.font = font# Background color
cell.fill = PatternFill(start_color='FFFF00',
end_color='FFFF00',
fill_type='solid')
# Alignment
cell.alignment = Alignment(horizontal='center',
vertical='center')
# Borders
border = Border(left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin'))
cell.border = borderNumber Formats
# Date format
cell.number_format = 'DD/MM/YYYY'
# Currency format
cell.number_format = '$#,##0.00'
# Percentage format
cell.number_format = '0.00%'
Data Validation and Formulas
Adding Data Validation
from openpyxl.worksheet.datavalidation import DataValidation
# Create validation for whole numbers
dv = DataValidation(type="whole",
operator="between",
formula1=1,
formula2=100)
dv.error = "Please enter a number between 1 and 100"
dv.errorTitle = "Invalid Entry"
# Add validation to worksheet
sheet.add_data_validation(dv)
dv.add('A1:A100')Working with Formulas
# Add simple formula
sheet['D1'] = '=SUM(A1:C1)'
# Add more complex formula
sheet['E1'] = '=IF(D1>100,"High","Low")'Advanced Features
Merge and Unmerge Cells
# Merge cells
sheet.merge_cells('A1:D1')
# Unmerge cells
sheet.unmerge_cells('A1:D1')Working with Images
from openpyxl.drawing.image import Image
# Add image
img = Image('logo.png')
sheet.add_image(img, 'A1')Charts
from openpyxl.chart import BarChart, Reference
# Create chart
chart = BarChart()
data = Reference(sheet, min_col=1, min_row=1, max_row=10, max_col=1)
chart.add_data(data)
# Add chart to sheet
sheet.add_chart(chart, "E1")Best Practices and Performance Tips
Memory Optimization
# For large files, use read-only mode
wb = load_workbook(filename='large_file.xlsx', read_only=True)
# For writing large files, use write-only mode
wb = Workbook(write_only=True)Error Handling
try:
wb = load_workbook('example.xlsx')
except FileNotFoundError:
print("File not found")
except Exception as e:
print(f"An error occurred: {e}")Saving with Backup
from datetime import datetime
def save_with_backup(workbook, filename):
"""Save workbook with timestamp backup"""
timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
backup_name = f"{filename.split('.')[0]}_{timestamp}.xlsx"
workbook.save(backup_name)Common Patterns and Examples
Copying Data Between Sheets
def copy_sheet_data(source_sheet, target_sheet):
"""Copy data from one sheet to another"""
for row in source_sheet.rows:
for cell in row:
target_sheet[cell.coordinate].value = cell.value
target_sheet[cell.coordinate].style = cell.styleConditional Formatting
from openpyxl.formatting.rule import ColorScaleRule# Add conditional formatting
color_scale = ColorScaleRule(start_color='FFEB84',end_color='FF7128',
start_type='percentile',
start_value=0,
end_type='percentile',
end_value=100)
sheet.conditional_formatting.add('A1:A100', color_scale)There is no doubt that openpyxl provides powerful capabilities for Excel automation in Python. By understanding its features and following best practices, you can create efficient and maintainable Excel automation scripts.