Python openpyxl Module: A Comprehensive Guide

The openpyxl module in Python is a powerful library used for reading and writing Excel files in the .xlsx format. It provides extensive features for managing Excel workbooks, worksheets, cells, and formatting. This guide covers the key features and functionalities of the openpyxl module with detailed examples.

Introduction to openpyxl

The openpyxl module allows you to work with Excel files (.xlsx) directly from Python. It supports a wide range of features, including reading, writing, formatting, and charting Excel files.

Installation

To use openpyxl, you need to install it via pip. You can install it with the following command:

pip install openpyxl

Creating a Workbook

To create a new Excel workbook and add a worksheet, use the following code:

from openpyxl import Workbook

# Create a new workbook and add a worksheet
wb = Workbook()
ws = wb.active
ws.title = "Sheet1"

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

Accessing and Modifying Worksheets

Accessing a Worksheet

To access a specific worksheet in an existing workbook:

from openpyxl import load_workbook

# Load an existing workbook
wb = load_workbook("example.xlsx")

# Access a specific sheet by name
ws = wb["Sheet1"]

Modifying Worksheet Properties

You can rename, add, or remove worksheets.

# Add a new worksheet
new_ws = wb.create_sheet(title="NewSheet")

# Rename an existing worksheet
ws.title = "RenamedSheet"

# Remove a worksheet
wb.remove(new_ws)

# Save the changes
wb.save("example_modified.xlsx")

Reading and Writing Data

Writing Data to Cells

To write data to specific cells:

from openpyxl import Workbook

# Create a workbook and access the default sheet
wb = Workbook()
ws = wb.active

# Write data to cells
ws['A1'] = "Hello"
ws['B1'] = 123
ws.cell(row=2, column=1, value="Python")

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

Reading Data from Cells

To read data from specific cells:

from openpyxl import load_workbook

# Load an existing workbook
wb = load_workbook("example.xlsx")
ws = wb.active

# Read data from cells
value_a1 = ws['A1'].value
value_b1 = ws['B1'].value

print(f"A1: {value_a1}, B1: {value_b1}")

Formatting Cells

You can format cells with various styles, such as font, fill, and border.

Applying Font and Fill Styles

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

# Create a workbook and access the default sheet
wb = Workbook()
ws = wb.active

# Apply font style
bold_font = Font(bold=True, color="FF0000")
ws['A1'].font = bold_font
ws['A1'] = "Bold Red Text"

# Apply fill style
yellow_fill = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")
ws['A2'].fill = yellow_fill
ws['A2'] = "Yellow Background"

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

Applying Border Style

from openpyxl import Workbook
from openpyxl.styles import Border, Side

# Create a workbook and access the default sheet
wb = Workbook()
ws = wb.active

# Define border style
border = Border(left=Side(border_style="thin", color="000000"),
                right=Side(border_style="thin", color="000000"),
                top=Side(border_style="thin", color="000000"),
                bottom=Side(border_style="thin", color="000000"))

# Apply border to a cell
ws['A1'].border = border
ws['A1'] = "Cell with Border"

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

Adding and Modifying Charts

You can add and customize charts within Excel files.

Creating a Chart

from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference

# Create a workbook and access the default sheet
wb = Workbook()
ws = wb.active

# Write data for the chart
data = [
    ['Month', 'Sales'],
    ['Jan', 100],
    ['Feb', 150],
    ['Mar', 200],
]

for row in data:
    ws.append(row)

# Create a bar chart
chart = BarChart()
data_ref = Reference(ws, min_col=2, min_row=1, max_col=2, max_row=4)
chart.add_data(data_ref, titles_from_data=True)
ws.add_chart(chart, "E5")

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

Using Formulas

You can use Excel formulas directly in cells.

from openpyxl import Workbook

# Create a workbook and access the default sheet
wb = Workbook()
ws = wb.active

# Write some data
ws['A1'] = 10
ws['A2'] = 20

# Write a formula
ws['A3'] = "=A1 + A2"

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

Handling Images

You can insert images into Excel sheets.

from openpyxl import Workbook
from openpyxl.drawing.image import Image

# Create a workbook and access the default sheet
wb = Workbook()
ws = wb.active

# Add an image
img = Image("logo.png")
ws.add_image(img, "B2")

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

Managing Multiple Sheets

You can create and manage multiple worksheets within a workbook.

Adding and Accessing Multiple Sheets

from openpyxl import Workbook

# Create a workbook
wb = Workbook()

# Add multiple worksheets
ws1 = wb.create_sheet(title="Sheet1")
ws2 = wb.create_sheet(title="Sheet2")

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

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

Error Handling

Handling errors ensures robustness when working with Excel files.

from openpyxl import load_workbook

try:
    # Load a workbook
    wb = load_workbook("nonexistent.xlsx")

except FileNotFoundError:
    print("The specified file does not exist.")

except Exception as e:
    print(f"An error occurred: {e}")

Conclusion

The openpyxl module provides extensive capabilities for reading, writing, and manipulating Excel files in Python. With features for data management, cell formatting, chart creation, and formula handling, openpyxl is a versatile tool for Excel automation and reporting. By mastering its functionalities, you can efficiently handle a wide range of tasks involving Excel spreadsheets.