Master Excel Automation with Python’s openpyxl: From Basics to Advanced Operations

Various data need to be imported into Excel? Multiple Excel files need to be merged? Python has many libraries for handling Excel files; openpyxl is one of the most functional and performant. Below is an introduction to common Excel operations using openpyxl.

Opening Excel Files

Creating a new workbook:

from openpyxl import Workbook
wb = Workbook()

Opening an existing workbook:

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

When opening large files, use read_only=True or write_only=True to reduce memory usage:

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

Worksheets

Get the active worksheet:

ws = wb.active

Create new worksheets:

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

Access a worksheet by name:

ws3 = wb["New Title"]

List all worksheet names:

print(wb.sheetnames)
# ['Sheet2', 'New Title', 'Sheet1']
for sheet in wb:
    print(sheet.title)

Saving

Save to a temporary stream for network transmission:

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')

Cells

Access a cell by its address:

c = ws['A4']

Assign a value to a cell:

ws['A4'] = 4
c.value = 'hello, world'

Access a range of cells using slicing:

cell_range = ws['A1':'C2']

Set number format (e.g., date):

# set date using a Python datetime
ws['A1'] = datetime.datetime(2010, 7, 21)
ws['A1'].number_format
# 'yyyy-mm-dd h:mm:ss'

Insert a simple formula:

ws["A1"] = "=SUM(1, 1)"

Merge and unmerge cells:

ws.merge_cells('A2:D2')
ws.unmerge_cells('A2:D2')
# or using coordinates
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)

Rows and Columns

Access entire rows, columns, or ranges:

colC = ws['C']
col_range = ws['C:D']
row10 = ws[10]
row_range = ws[5:10]

Iterate rows:

for row in ws.iter_rows(min_row=1, max_col=3, max_row=2):
    for cell in row:
        print(cell)

Iterate columns:

for col in ws.iter_cols(min_row=1, max_col=3, max_row=2):
    for cell in col:
        print(cell)

Access rows or columns as tuples:

tuple(ws.rows)
tuple(ws.columns)

Append a new row or set cell values directly:

for row in range(1, 40):
    ws1.append(range(600))

for row in range(10, 20):
    for col in range(27, 54):
        ws3.cell(column=col, row=row, value="{0}".format(get_column_letter(col)))

Insert or delete rows and columns:

from openpyxl.utils import get_column_letter
ws.insert_rows(7)
ws.insert_cols(5)
ws.delete_rows(3)
ws.delete_cols(2)

Reading Values Only

Iterate over cell values without objects:

for row in ws.values:
    for value in row:
        print(value)

Use values_only flag with iterators:

for row in ws.iter_rows(min_row=1, max_col=3, max_row=2, values_only=True):
    print(row)