Introduction to Python OpenPyxl

OpenPyxl is a Python library that provides tools for reading, writing, and manipulating Excel files in the Excel 2010 format (which uses the .xlsx extension). This is a high-level library that abstracts away many low-level details, making it simple to work with spreadsheets programmatically.

• Read and Write Excel Files: OpenPyxl can be used to both read from and write to .xlsx files.
• Work with Cells: We can access, modify, and create new cells in Excel sheets.
• Support for Formulas: OpenPyxl supports the evaluation of Excel formulas.
• Styles and Formatting: We can modify the look and feel of cells (font, color, borders, etc.).
• Charting: OpenPyxl also provides the ability to create simple charts in Excel files.
• Working with Pivot Tables: Though limited, OpenPyxl offers the capability to interact with pivot tables.

Installing OpenPyxl

To use OpenPyxl, we'll first need to install it. We can do this using pip:

pip install openpyxl

Once installed, we're ready to start working with Excel files.

Basic Usage of OpenPyxl

1. Creating a New Excel File

Creating a new Excel file and saving it is quite straightforward with OpenPyxl. Here’s how to create a basic Excel workbook and sheet:

import openpyxl

# Create new workbook
wb = openpyxl.Workbook()

# Access active sheet
sheet = wb.active
sheet.title = "Sample Sheet"

# Save workbook
wb.save("sample_workbook.xlsx")

Explanation:

• Workbook() creates a new Excel file.
• .active returns the default sheet.
• .save() writes the file to disk.

2. Writing Data to an Excel File

Let's now populate the Excel sheet with some data:

import openpyxl

wb = openpyxl.Workbook()
sheet = wb.active

sheet["A1"] = "Name"
sheet["B1"] = "Age"

sheet["A2"] = "Alice"
sheet["B2"] = 25

sheet["A3"] = "Bob"
sheet["B3"] = 30

wb.save("data_workbook.xlsx")

Output:

Explanation:

• Data is written directly by referencing cell names (e.g., "A1").
• Creates a simple table with names and ages.

3. Reading Data from an Excel File

Reading data from an Excel file is equally simple. Let’s read the data we wrote in the previous example:

import openpyxl

wb = openpyxl.load_workbook("data_workbook.xlsx")
sheet = wb.active

for row in sheet.iter_rows(min_row=1, max_row=3, values_only=True):
    print(row)

Output:

Explanation:

• load_workbook() opens an existing file.
• iter_rows() retrieves rows as tuples.
• values_only=True returns only cell values, not cell objects.

4. Modifying an Existing Excel File in OpenPyxl

We can easily modify existing Excel files by loading them and then editing the content:

import openpyxl

wb = openpyxl.load_workbook("data_workbook.xlsx")
sheet = wb.active

sheet["B2"] = 26  # Update age

wb.save("data_workbook_modified.xlsx")

Output:

Explanation: Load an existing file → update cells → save as a new file.

5. Formatting Cells in OpenPyxl

OpenPyxl allows us to apply styles and formatting to cells, such as changing the font size, color, or applying bold formatting:

from openpyxl import Workbook
from openpyxl.styles import Font

wb = Workbook()
sheet = wb.active

sheet["A1"] = "Formatted Text"
sheet["A1"].font = Font(size=14, bold=True, color="FF0000")

wb.save("formatted_workbook.xlsx")

Output:

Explanation:

• Use Font() to set bold text, size, and color.
• Formats are applied directly to cell objects.

6. Adding Formulas to Cells in OpenPyxl

OpenPyxl also supports adding Excel formulas. We can assign a formula to a cell just like any other value:

import openpyxl

wb = openpyxl.Workbook()
sheet = wb.active

sheet["A1"] = 10
sheet["A2"] = 20
sheet["A3"] = "=A1 + A2"  # Formula

wb.save("formula_workbook.xlsx")

Output:

Explanation:

• Assign formulas as plain strings.
• Excel evaluates the formula when the file is opened.

7. Merging and Unmerging Cells in OpenPyxl

We can merge and unmerge cells in Excel using OpenPyxl. Here's how to merge and unmerge a range of cells:

import openpyxl

# Create a new Workbook
wb = openpyxl.Workbook()

# Select the active sheet
sheet = wb.active

# Merge cells from A1 to B2
sheet.merge_cells("A1:B2")

# Write data in the merged cell
sheet["A1"] = "Merged Cells"

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

Output:

Explanation:

• merge_cells() merges a specified range.
• Data is written to the top-left cell of the merged region.

Related Articles:

• Merging and Unmerging Cells in OpenPyxl
• Adding Formulas to Cells in OpenPyxl
• Formatting Cells using openpyxl in Python
• Modifying an Existing Excel File in OpenPyxl
• Writing to an excel file using openpyxl module
• Reading an excel file using Python openpyxl module