Python Openpyxl Tutorial: Read Write Excel XLSX Files

Working with Excel files is common in programming. Python makes it easy with openpyxl.

This library lets you read and write Excel files. It supports modern .xlsx format.

What is Openpyxl?

Openpyxl is a Python library. It reads and writes Excel 2010 xlsx/xlsm files.

It does not require Microsoft Excel to be installed. It works on all operating systems.

You can create new spreadsheets. You can modify existing ones too.

Installing Openpyxl

First, you need to install the library. Use pip for installation.

If you need detailed steps, see our Install Openpyxl in Python Step by Step guide.

pip install openpyxl

If you get an error, check our guide on [Solved] ModuleNotFoundError: No module named 'openpyxl'.

Basic Openpyxl Concepts

Understand these key concepts before coding.

A Workbook represents the Excel file. It contains one or more worksheets.

Worksheets are the individual tabs. They contain the actual data in cells.

Cells are the individual boxes. They hold data like text or numbers.

Creating a New Excel File

Let's create a new Excel workbook from scratch.

Import the Workbook class. Create a new workbook object.

from openpyxl import Workbook

# Create a new workbook
wb = Workbook()

# Get the active worksheet
ws = wb.active

# Set worksheet title
ws.title = "Employees"

# Save the workbook
wb.save("employees.xlsx")

This creates a new file called employees.xlsx. It has one worksheet named Employees.

Writing Data to Cells

You can write data to specific cells. Use cell coordinates or references.

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

# Write data using coordinates
ws['A1'] = "Name"
ws['B1'] = "Department"
ws['C1'] = "Salary"

# Write data using cell method
ws.cell(row=2, column=1, value="John Doe")
ws.cell(row=2, column=2, value="Engineering")
ws.cell(row=2, column=3, value=75000)

# Save the file
wb.save("employee_data.xlsx")

The cell() method is very flexible. It lets you specify row and column numbers.

Reading Excel Files

Reading existing files is straightforward. Use the load_workbook function.

from openpyxl import load_workbook

# Load existing workbook
wb = load_workbook('employee_data.xlsx')

# Select worksheet
ws = wb.active

# Read cell value
name = ws['A2'].value
department = ws['B2'].value
salary = ws['C2'].value

print(f"Name: {name}")
print(f"Department: {department}")
print(f"Salary: ${salary}")

Name: John Doe
Department: Engineering
Salary: $75000

The load_workbook() function opens existing files. You can then access any cell.

Working with Multiple Worksheets

Excel files often have multiple sheets. Openpyxl handles this well.

from openpyxl import Workbook

wb = Workbook()

# Create multiple worksheets
ws1 = wb.create_sheet("Sheet1")
ws2 = wb.create_sheet("Sheet2")
ws3 = wb.create_sheet("Sheet3")

# Remove default sheet if needed
default_sheet = wb['Sheet']
wb.remove(default_sheet)

# Write to different sheets
ws1['A1'] = "Data in Sheet1"
ws2['A1'] = "Data in Sheet2"

# Save the file
wb.save("multiple_sheets.xlsx")

You can create, remove, and rename worksheets. This gives full control over workbook structure.

Reading Multiple Cells

Often you need to read ranges of cells. Use loops and ranges for efficiency.

from openpyxl import load_workbook

wb = load_workbook('employee_data.xlsx')
ws = wb.active

# Read all data from first three rows
for row in range(1, 4):
    for col in range(1, 4):
        cell_value = ws.cell(row=row, column=col).value
        print(f"Row {row}, Col {col}: {cell_value}")

Row 1, Col 1: Name
Row 1, Col 2: Department
Row 1, Col 3: Salary
Row 2, Col 1: John Doe
Row 2, Col 2: Engineering
Row 2, Col 3: 75000
Row 3, Col 1: None
Row 3, Col 2: None
Row 3, Col 3: None

This approach reads entire ranges. It's perfect for processing large datasets.

Working with Formulas

Openpyxl supports Excel formulas. They calculate when Excel opens the file.

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

# Add sample data
ws['A1'] = 10
ws['A2'] = 20
ws['A3'] = 30

# Add formula to calculate sum
ws['A4'] = "=SUM(A1:A3)"

# Save the file
wb.save("with_formulas.xlsx")

When you open this in Excel, cell A4 will show 60. The formula calculates automatically.

Formatting Cells

Basic formatting makes spreadsheets more readable. Openpyxl provides formatting options.

from openpyxl import Workbook
from openpyxl.styles import Font, Alignment

wb = Workbook()
ws = wb.active

# Write header with formatting
header_font = Font(bold=True, size=14)
header_alignment = Alignment(horizontal='center')

ws['A1'] = "Sales Report"
ws['A1'].font = header_font
ws['A1'].alignment = header_alignment

# Merge cells for title
ws.merge_cells('A1:C1')

wb.save("formatted_report.xlsx")

You can change fonts, colors, and alignment. This makes professional-looking reports.

Handling Large Files

For large Excel files, use optimized modes. This saves memory and improves performance.

from openpyxl import load_workbook

# Read-only mode for large files
wb = load_workbook('large_file.xlsx', read_only=True)
ws = wb.active

# Write-only mode for creating large files
wb2 = Workbook(write_only=True)
ws2 = wb2.create_sheet()

# Add data in batches
for row in range(1, 1001):
    ws2.append([f"Data {row}", row * 10])

wb2.save('large_output.xlsx')

Read-only and write-only modes are essential for large files. They prevent memory issues.

Common Operations

Here are some frequently used operations. They solve common Excel tasks.

from openpyxl import load_workbook

wb = load_workbook('example.xlsx')
ws = wb.active

# Get maximum row and column
max_row = ws.max_row
max_col = ws.max_column

print(f"Rows: {max_row}, Columns: {max_col}")

# Get all sheet names
sheet_names = wb.sheetnames
print(f"Sheet names: {sheet_names}")

# Access specific sheet by name
specific_sheet = wb['Sheet1']

Rows: 5, Columns: 3
Sheet names: ['Sheet1', 'Sheet2']

These properties help you understand workbook structure. They're useful for automation.

Error Handling

Always handle potential errors. This makes your code more robust.

from openpyxl import load_workbook
import os

try:
    if os.path.exists('my_file.xlsx'):
        wb = load_workbook('my_file.xlsx')
        ws = wb.active
        print("File loaded successfully")
    else:
        print("File does not exist")
        
except Exception as e:
    print(f"Error loading file: {e}")

Check if files exist before loading. Handle exceptions gracefully.

Uninstalling Openpyxl

If you need to remove openpyxl, use pip uninstall. Sometimes this is necessary for troubleshooting.

See our detailed guide on How to Uninstall Openpyxl in Python for complete instructions.

Conclusion

Openpyxl is a powerful Python library. It makes Excel file handling simple.

You can read existing files. You can create new workbooks from scratch.

The library supports formulas, formatting, and multiple worksheets. It works with large files efficiently.

Start using openpyxl for your Excel automation tasks. It will save you time and effort.

Remember to handle errors and use optimized modes for large datasets.