Introduction to Openpyxl: The Swiss Army Knife for Excel in Python

Openpyxl is a powerful library in Python that allows you to read, write, and manipulate Excel files. Whether you are processing data or generating reports, Openpyxl makes handling Excel files easy and efficient. In this article, we’ll explore some of the most useful APIs provided by Openpyxl with code examples.

Installing Openpyxl

Before getting started, you need to install the library. You can do this using pip:

pip install openpyxl

Basic Excel Operations with Openpyxl

Creating a Workbook

The first step usually involves creating a workbook:

from openpyxl import Workbook

# Create a workbook and grab the active worksheet
wb = Workbook()
ws = wb.active

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

Loading an Existing Workbook

You can also load existing Excel files:

from openpyxl import load_workbook

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

Working with Sheets

Openpyxl allows you to create, delete, and switch between sheets:

# Create a new Sheet
ws1 = wb.create_sheet("NewSheet")

# Rename the active sheet
ws.title = "MySheet"

# Delete a sheet
wb.remove(ws1)

# Select a sheet by name
ws2 = wb["MySheet"]

Working with Cells

Accessing and modifying cell values is straightforward:

# Access a Cell
value = ws['A1'].value

# Modify a Cell
ws['A1'] = "Hello, World!"

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

Iterating Through Rows and Columns

You can easily loop through rows and columns:

# Loop through rows
for row in ws.iter_rows(min_row=1, max_col=3, max_row=2):
    for cell in row:
        print(cell.value)

# Loop through columns
for col in ws.iter_cols(min_row=1, max_col=3, max_row=2):
    for cell in col:
        print(cell.value)

Advanced Features

Adding Styles

You can apply styles to cells:

from openpyxl.styles import Font, Color

# Apply a font style
bold_font = Font(bold=True)
ws['A1'].font = bold_font

# Apply a color
ws['A1'].font = Font(color="FF0000")

Formulas

Inserting Excel formulas is as simple as writing them into cells:

# Insert a formula
ws['A1'] = "=SUM(B1:B10)"
wb.save("example.xlsx")

Charting

You can even create charts:

from openpyxl.chart import BarChart, Reference

# Create some data
for i in range(10):
    ws.append([i])

# Create a bar chart
values = Reference(ws, min_col=1, min_row=1, max_col=1, max_row=10)
chart = BarChart()
chart.add_data(values)

# Add the chart to the sheet
ws.add_chart(chart, "E5")
wb.save("example.xlsx")

Real-World Example: Sales Report

Let’s create a simple app that generates a sales report:

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

# Sample data
data = [
    ["Product", "Sales"],
    ["Product A", 30],
    ["Product B", 45],
    ["Product C", 20],
    ["Product D", 10]
]

# Create workbook and sheet
wb = Workbook()
ws = wb.active
ws.title = "Sales Report"

# Write data to the sheet
for row in data:
    ws.append(row)

# Create a bar chart
chart = BarChart()
values = Reference(ws, min_col=2, min_row=2, max_row=5)
chart.add_data(values)

# Add chart to the sheet
ws.add_chart(chart, "E5")

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

Conclusion

From basic operations to advanced features, Openpyxl provides all the tools you need to manipulate Excel files in Python. Whether you’re automating reports, processing data, or creating visualizations, Openpyxl can be your go-to library.

Hash: fab008f09c50907c943bccc5f49b221f48e601873b3d31be529b38663da36ffa