Introduction to Python OpenPyxl

In Python, when working with Excel files, one of the most widely used libraries is OpenPyxl. It allows us to create, modify, and manipulate Excel files without needing to install Excel itself. Python OpenPyxl is particularly useful for automating tasks like generating reports, data analysis, and data extraction from .xlsx files.

This article will serve as an introduction to the OpenPyxl library, covering its essential features, installation process, and some basic use cases for working with Excel files.

What is 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.

Key Features of OpenPyxl

• 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:

In this example:

• We create a new Excel workbook using openpyxl.Workbook().
• We access the active sheet and set its title.
• Finally, we save the workbook to the file sample_workbook.xlsx.

2. Writing Data to an Excel File

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

import openpyxl

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

# Select the active sheet
sheet = wb.active

# Write data to specific cells
sheet["A1"] = "Name"
sheet["B1"] = "Age"

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

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

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

Output:

In this example, we write data to specific cells like A1, B1, A2, and B2 to create a small table with names and ages.

Also Read: Writing to an excel file using openpyxl module

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

# Load the workbook
wb = openpyxl.load_workbook("data_workbook.xlsx")

# Select the active sheet
sheet = wb.active

# Read and print the data
for row in sheet.iter_rows(min_row=1, max_row=3, values_only=True):
    print(row)

Output:

Here, we use iter_rows() to iterate through the rows and print the values. This method returns the values of each cell in a row as a tuple.

Also Read: Reading an excel file using Python openpyxl module

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

# Load the existing workbook
wb = openpyxl.load_workbook("data_workbook.xlsx")

# Select the active sheet
sheet = wb.active

# Modify the value in a specific cell
sheet["B2"] = 26

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

Output:

In this example, we load the existing file data_workbook.xlsx, change the value in cell B2 from 25 to 26, and save the updated workbook as data_workbook_modified.xlsx.

Also Read: Modifying an Existing Excel File in OpenPyxl

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.

Here’s an example of how to apply basic formatting:

from openpyxl import Workbook
from openpyxl.styles import Font

# Create a new Workbook
wb = Workbook()

# Select the active sheet
sheet = wb.active

# Write data with formatting
sheet["A1"] = "Formatted Text"

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

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

Output:

In this example, we apply a font style to cell A1, making the text bold, red, and size 14.

Also Read: Formatting Cells using openpyxl in Python

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

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

# Select the active sheet
sheet = wb.active

# Write data
sheet["A1"] = 10
sheet["A2"] = 20

# Add a formula
sheet["A3"] = "=A1 + A2"

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

Output:

In this example, cell A3 will display the result of the formula =A1 + A2, which is the sum of the values in cells A1 and A2.

Also Read: Adding Formulas to Cells in OpenPyxl

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:

In this example, cells from A1 to B2 are merged, and the text Merged Cells is written to the merged cell.

Also Read: Merging and Unmerging Cells in OpenPyxl

Conclusion

OpenPyxl is a versatile and powerful library for working with Excel files in Python. From reading and writing data to adding formulas and creating charts, it provides a wide array of functionalities to automate Excel-related tasks. Whether we're building reports, manipulating large datasets, or automating data workflows, OpenPyxl can make Excel manipulation easy and efficient in Python.

By following this guide, we've seen how to:

• Install and set up OpenPyxl.
• Create, modify, and read Excel files.
• Apply formatting, merge cells, and add formulas.

OpenPyxl is a must-know library if we're working with Excel files in Python, and this introduction should give us a solid starting point for using it in our projects.