Image by Editor | Midjourney

If you work with Excel files in Python, you might be familiar with using pandas’ read_excel() and to_excel() functions. What you might not know is that behind the scenes, pandas is often using openpyxl to handle these Excel operations. In this article, we’ll explore when and why you might want to use openpyxl directly, and understand its relationship with pandas.

The Pandas-Openpyxl Relationship

When you write code like this:

import pandas as pd
df = pd.read_excel('myfile.xlsx')
df.to_excel('output.xlsx')

Pandas is actually using openpyxl as a backend dependency to handle the Excel file operations. You don’t need to import openpyxl explicitly, similar to how pandas uses other libraries like numpy behind the scenes.

What Happens Without Openpyxl?

If you try to read an Excel file with pandas without having openpyxl installed, you’ll get this error:

import pandas as pd
df = pd.read_excel('sample.xlsx')

# Result:
ImportError: Missing optional dependency 'openpyxl'.  Use pip or conda to install openpyxl.

To fix this, simply install openpyxl:

pip install openpyxl
# or
conda install openpyxl

Working with Excel Files in Pandas

Once openpyxl is installed, you can easily create and manipulate Excel files using pandas. Here’s a simple example:

import pandas as pd

# Create sample data
data = {
    'Name': ['John', 'Alice', 'Bob'],
    'Department': ['Sales', 'Engineering', 'Marketing'],
    'Salary': [50000, 75000, 60000],
    'Start Date': ['2023-01-15', '2023-02-01', '2023-03-10']
}

# Create DataFrame
df = pd.DataFrame(data)

# Display the DataFrame
print(df)

# Save to Excel
df.to_excel('sample.xlsx', index=False)

This code creates a DataFrame and saves it as an Excel file. Here’s what the DataFrame looks like:

    Name   Department  Salary  Start Date
0   John        Sales   50000  2023-01-15
1  Alice  Engineering   75000  2023-02-01
2    Bob    Marketing   60000  2023-03-10

The data is now saved in ‘sample.xlsx’ with proper formatting and column headers. Behind the scenes, pandas uses openpyxl to handle the Excel file creation and formatting. When you open this file using Excel, you should get the below result: 

Image By Author

Excel Engines in Pandas

According to the pandas documentation, several engines are available for reading Excel files:

pd.read_excel('file.xlsx', engine='openpyxl')  # default for .xlsx files

The available engines include:

• openpyxl: Supports newer Excel file formats
• calamine: Supports Excel (.xls, .xlsx, .xlsm, .xlsb) and OpenDocument (.ods)
• odf: Supports OpenDocument formats (.odf, .ods, .odt)
• pyxlsb: Supports Binary Excel files
• xlrd: Supports old-style Excel files (.xls)

When no engine is specified, pandas automatically selects the appropriate engine based on the file format.

When to Use What?

Pandas is Great For:

• Data analysis and manipulation
• Working with tabular data where you want to treat the Excel file as a dataframe
• Quick data importing and exporting
• Statistical operations and data transformations

Openpyxl Shines When You Need:

• Detailed control over Excel-specific features
• Formatting (colors, fonts, borders)
• Working with multiple sheets programmatically
• Handling and maintaining Excel formulas
• Manipulating cell properties
• Creating charts within Excel
• Setting print areas and page layouts

Excel Format Compatibility

Here’s a comprehensive breakdown of Excel formats and their compatibility with openpyxl:

*Note: openpyxl can read and preserve macros in .xlsm files when loaded with keep_vba=True, but cannot modify or execute the macro code.

The Pattern in Openpyxl’s Compatibility

Openpyxl follows a clear pattern in its file format support:

• Generally supports Office Open XML formats (.xlsx, .xlsm, .xltx, .xltm)
• Does not support Binary formats (.xls, .xlsb)
• Focuses on workbook files rather than add-ins
• Intentionally excludes .xlam (add-in) support despite it being an Office Open XML format, as add-ins include specialized elements outside openpyxl’s core focus

This design decision keeps the library streamlined for its primary use case: data manipulation and analysis.

When You Need Openpyxl

This example shows how to create a formatted Excel report with custom styling and formulas using openpyxl:

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

# Create a new workbook and select the active sheet
wb = Workbook()
ws = wb.active

# Add data with formatting
ws['A1'] = 'Sales Report'
ws['A1'].font = Font(bold=True, size=14)
ws['A1'].fill = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")

# Work with formulas
ws['B2'] = 100
ws['B3'] = 200
ws['B4'] = '=SUM(B2:B3)'  # Excel will calculate this

wb.save('formatted_report.xlsx')

This kind of detailed formatting and formula handling would be much more complex (or impossible) with pandas alone. When you open your file using Excel, you should see the below: 

Image By Author

Conclusion

While pandas is excellent for data analysis and basic Excel operations, openpyxl provides the detailed control you need for more complex Excel tasks. Understanding when to use each tool will make your Python-Excel workflows more efficient and capable.