Using openpyxl for Excel Automation in Python

Python offers several libraries for Excel file manipulation, and openpyxl provides a good balance of features and performance. The following guide shows how to perform common Excel operations with openpyxl.

Opening Excel files

from openpyxl import Workbook
wb = Workbook()

To open an existing file:

from openpyxl import load_workbook
wb2 = load_workbook('test.xlsx')

When handling large files, use read‑only or write‑only modes to reduce memory usage:

wb = load_workbook(filename='large_file.xlsx', read_only=True)
wb = Workbook(write_only=True)

Getting and creating worksheets

ws = wb.active
ws1 = wb.create_sheet("Mysheet")  # insert at the end (default)
ws2 = wb.create_sheet("Mysheet", 0)  # insert at first position
ws3 = wb.create_sheet("Mysheet", -1)  # insert at penultimate position
ws3 = wb["New Title"]  # access by sheet name
print(wb.sheetnames)  # list all sheet names
for sheet in wb:
    print(sheet.title)

Saving workbooks

from tempfile import NamedTemporaryFile
from openpyxl import Workbook
wb = Workbook()
with NamedTemporaryFile() as tmp:
    wb.save(tmp.name)
    tmp.seek(0)
    stream = tmp.read()
# Save to a file
wb = Workbook()
wb.save('balances.xlsx')
# Save as a template
wb = load_workbook('document.xlsx')
wb.template = True
wb.save('document_template.xltx')

Cell operations

c = ws['A4']
ws['A4'] = 4
c.value = 'hello, world'
cell_range = ws['A1':'C2']
ws['A1'] = datetime.datetime(2010, 7, 21)
ws['A1'].number_format = 'yyyy-mm-dd h:mm:ss'
ws['A1'] = "=SUM(1, 1)"
ws.merge_cells('A2:D2')
ws.unmerge_cells('A2:D2')
ws.merge_cells(start_row=2, start_column=1, end_row=4, end_column=4)
ws.unmerge_cells(start_row=2, start_column=1, end_row=4, end_column=4)

Row and column handling

colC = ws['C']
col_range = ws['C:D']
row10 = ws[10]
row_range = ws[5:10]
for row in ws.iter_rows(min_row=1, max_col=3, max_row=2):
    for cell in row:
        print(cell)
for col in ws.iter_cols(min_row=1, max_col=3, max_row=2):
    for cell in col:
        print(cell)
for row in ws.values:
    for value in row:
        print(value)
for row in ws.iter_rows(min_row=1, max_col=3, max_row=2, values_only=True):
    print(row)

Inserting and deleting rows or columns can be done with Worksheet.insert_rows(), Worksheet.insert_cols(), Worksheet.delete_rows(), and Worksheet.delete_cols():

from openpyxl.utils import get_column_letter
ws.insert_rows(7)
row7 = ws[7]
for col in range(27, 54):
    ws3.cell(column=col, row=7, value="{0}".format(get_column_letter(col)))

These examples provide a quick 5‑minute overview of using openpyxl for everyday Excel automation tasks in Python.