Master openpyxl: Automate Excel Tasks with Python in Minutes

Introduction

When you need to perform repetitive operations on Excel files daily, doing it manually is time‑consuming and boring. Python offers many modules for handling Excel, and openpyxl provides a comprehensive set of features to automate these tasks.

openpyxl Installation

Install the library directly from PyPI: pip install openpyxl Or use the Douban mirror:

pip install -i https://pypi.douban.com/simple openpyxl

Open/Create Workbook

Example workbook

Sheet "一等奖"

Sheet "二等奖"

(1) Open a local workbook

from openpyxl import load_workbook
wb = load_workbook('获奖名单.xlsx')

(2) Create an empty workbook

from openpyxl import Workbook
wb1 = Workbook()

Access Worksheets

Create a new sheet at a specific position (0 for first, -1 for last): wb.create_sheet('new_sheet', 0) Get all sheet names: wb.sheetnames List comprehension to get titles: [sheet.title for sheet in wb] Access a sheet by name: wb['二等奖'] Get the active sheet (the one opened first): wb.active Get row/column dimensions of a sheet:

sheet1 = wb['一等奖']
sheet1.max_column
sheet1.max_row

Cell Operations

Access a single cell

sheet1['D3']
sheet1.cell(row=3, column=4)

Read or modify its value:

sheet1.cell(3,4).value
sheet1.cell(3,4, value='Python').value

Retrieve cell attributes:

sheet1['D3'].value
sheet1['D3'].coordinate
sheet1['D3'].row
sheet1['D3'].column

Access multiple cells

Excel slicing is inclusive on both ends. Example selections:

sheet1['A1':'B2']
sheet1['D']
sheet1['B:C']
sheet1[3]
sheet1[2:3]

Iterate over data

Iterate rows in a range:

for row in sheet1.iter_rows(min_row=2, max_row=3, min_col=2, max_col=3):
    for cell in row:
        print(cell.coordinate)
# Output: B2 C2 B3 C3

Iterate columns in a range:

for col in sheet1.iter_cols(min_row=2, max_row=3, min_col=2, max_col=3):
    for cell in col:
        print(cell.coordinate)
# Output: B2 B3 C2 C3

Iterate all rows or columns:

tuple(sheet1.rows)
tuple(sheet1.columns)

Modify Worksheet

Cell assignment

Add a new column that counts the number of authors (comma‑separated):

for row_index in range(2, sheet1.max_row + 1):
    sheet1.cell(row_index, 8).value = len(sheet1.cell(row_index, 6).value.split(','))

Use a formula to count total authors:

sheet1['H7'] = '=SUM(H1:H6)'

Append rows

Append a list of values:

sheet1.append([str(n) for n in range(6)])

Append using a dictionary (column index : value):

sheet1.append({'A':'1','C':'3'})

Insert blank rows

sheet1.insert_rows(idx=2, amount=2)

Delete a worksheet

wb.remove(wb['new_sheet'])

Save workbook

wb.save('获奖名单V1.xlsx')

Style Modifications

Font

from openpyxl.styles import Font
new_font = Font(name='微软雅黑', size=20, color='3333CC', bold=True)
sheet1['B2'].font = new_font

Cell background color

from openpyxl.styles import PatternFill, colors
sheet1["A2"].fill = PatternFill("solid", fgColor=colors.BLUE)
sheet1["A3"].fill = PatternFill("solid", fgColor='FF66CC')

Alignment

from openpyxl.styles import Alignment
sheet1['D2'].alignment = Alignment(horizontal='center', vertical='center')

Row height / Column width

sheet1.row_dimensions[2].height = 40
sheet1.column_dimensions['C'].width = 20

Merge / Unmerge cells

sheet.merge_cells('A1:C3')
sheet.unmerge_cells('A1:C3')