Reading and Writing Excel Files with openpyxl

Working with Excel files in Python has become significantly easier with the openpyxl library. As a powerful and flexible library, openpyxl allows developers to read, write, and manipulate Excel 2010 xlsx/xlsm/xltx/xltm files. Whether you’re managing business data, automating reports, or performing data analysis, openpyxl offers a comprehensive set of tools to handle spreadsheet files efficiently.

Installing openpyxl

To begin working with openpyxl, you need to ensure it’s installed in your Python environment. You can install it using pip:

bash
pip install openpyxl

Reading Excel Files

Reading data from an Excel file is straightforward with openpyxl. The process involves loading the workbook, selecting the desired worksheet, and iterating through the data.

Loading a Workbook

python
from openpyxl import load_workbook

# Load an existing workbook
workbook = load_workbook('example.xlsx')

Accessing Worksheets

After loading the workbook, you can access sheets using their names or by index:

python
# List all sheet names
print(workbook.sheetnames)

# Access a specific sheet
sheet = workbook['Sheet1']

Reading Cell Values

You can read the values of specific cells or iterate through a range:

python
# Read value from cell A1
value = sheet['A1'].value
print(value)

# Iterate through a range of cells
for row in sheet.iter_rows(min_row=1, max_row=5, min_col=1, max_col=3):
    for cell in row:
        print(cell.value, end=" ")
    print()

Writing to Excel Files

Writing data to an Excel file using openpyxl is equally efficient. You can write new values to existing cells or create entirely new workbooks and sheets.

Creating a Workbook

python
from openpyxl import Workbook

# Create a new workbook
wb = Workbook()

# Select the active sheet
ws = wb.active

# Rename the sheet
ws.title = "DataSheet"

Writing Cell Data

python
# Writing data to cells
ws['A1'] = "Name"
ws['B1'] = "Age"
ws['A2'] = "Alice"
ws['B2'] = 30

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

Appending Rows

openpyxl also supports appending rows, which is especially useful when building data tables dynamically:

python
# Append rows to the sheet
ws.append(["ID", "Product", "Price"])
ws.append([1, "Laptop", 1200])
ws.append([2, "Mouse", 25])

Working with Formulas

You can also write Excel formulas directly into cells. When opening the file in Excel, the formulas will be calculated automatically.

python
# Insert a formula
ws['C4'] = "=SUM(B2:B3)"

Styling and Formatting

openpyxl offers comprehensive formatting options to make your spreadsheets more readable.

Font and Fill

python
from openpyxl.styles import Font, PatternFill

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

# Apply cell background color
fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
ws['A1'].fill = fill

Column Width and Row Height

Adjusting the size of rows and columns improves the appearance of your Excel file:

python
# Set column width
ws.column_dimensions['A'].width = 20

# Set row height
ws.row_dimensions[1].height = 30

Merging and Unmerging Cells

Merging cells is useful for creating headings or grouping data:

python
# Merge cells from A1 to C1
ws.merge_cells('A1:C1')

# Unmerge if needed
ws.unmerge_cells('A1:C1')

Inserting and Deleting Rows and Columns

To manage your data layout more flexibly, you can insert or delete rows and columns:

python
# Insert a row at index 3
ws.insert_rows(3)

# Delete column B
ws.delete_cols(2)

Working with Multiple Sheets

Creating and managing multiple worksheets is another key feature:

python
# Create a new sheet
ws2 = wb.create_sheet(title="Summary")

# Write to the new sheet
ws2['A1'] = "Summary Data"

You can navigate between sheets and even copy data from one to another easily.

Reading Large Files with Read-Only Mode

For very large Excel files, openpyxl offers a read-only mode that reduces memory usage:

python
workbook = load_workbook('large_file.xlsx', read_only=True)
sheet = workbook.active

for row in sheet.iter_rows(values_only=True):
    print(row)

This method improves performance when processing large datasets.

Data Validation and Dropdowns

openpyxl supports adding data validation to cells, including dropdown lists:

python
from openpyxl.worksheet.datavalidation import DataValidation

# Create data validation for dropdown
dv = DataValidation(type="list", formula1='"Option1,Option2,Option3"', showDropDown=True)

# Add the validation to a range
ws.add_data_validation(dv)
dv.add("A2:A10")

Charts and Graphs

Visualizing data is essential in many scenarios. openpyxl allows adding various charts like bar, pie, and line charts:

python
from openpyxl.chart import BarChart, Reference

# Sample data
for i in range(1, 6):
    ws.append([i, i * 10])

# Create a bar chart
chart = BarChart()
data = Reference(ws, min_col=2, min_row=1, max_row=5)
chart.add_data(data)
ws.add_chart(chart, "D5")

Conditional Formatting

Conditional formatting can be used to highlight cells based on their values:

python
from openpyxl.formatting.rule import CellIsRule
from openpyxl.styles import Font, PatternFill

# Highlight cells greater than 50
fill = PatternFill(start_color='FF9999', end_color='FF9999', fill_type='solid')
rule = CellIsRule(operator='greaterThan', formula=['50'], fill=fill)
ws.conditional_formatting.add("B2:B10", rule)

Password Protection and Security

openpyxl supports basic protection features, such as protecting sheets from editing:

python
# Protect worksheet
ws.protection.sheet = True
ws.protection.password = 'secure123'

Note that this is not encryption-level security but useful for preventing accidental edits.

Conclusion

The openpyxl library provides a robust framework for automating Excel file manipulation in Python. Its capabilities range from basic reading and writing to advanced tasks such as data validation, conditional formatting, and charting. By integrating openpyxl into your data workflows, you can automate repetitive spreadsheet tasks, ensure data consistency, and enhance productivity.

Share This Page: