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.