Master Excel Automation with Python: A Complete openpyxl Guide

Introduction

Hello, in previous office automation articles we demonstrated Python automation using real case requirements, often with openpyxl for spreadsheet handling. This article provides a comprehensive checklist of openpyxl operations for reading, writing, and styling Excel files.

This article explains how to read, write, and style Excel files with detailed code examples, serving as a quick reference manual.

Installation

openpyxl

is a third‑party library and must be installed via pip on Windows or macOS.

pip install openpyxl

Prerequisite Knowledge

Understanding Excel structure is essential: a workbook contains one or more sheets; each sheet consists of rows and columns; rows and columns intersect in cells.

Reading Excel

1. Load Workbook

from openpyxl import load_workbook
workbook = load_workbook(filename='测试.xlsx')
print(workbook.sheetnames)

Note: load_workbook can only open existing files; it cannot create a new workbook.

2. Access a Sheet by Name

from openpyxl import load_workbook
workbook = load_workbook(filename='其他.xlsx')
print(workbook.sheetnames)
sheet = workbook['工作业务']

If there is only one sheet, you can use:

sheet = workbook.active

3. Get Sheet Dimensions

print(sheet.dimensions)

4. Read a Specific Cell

Use cell.value to obtain the value.

5. Get Cell Row, Column, and Coordinate

print(cell.row, cell.column, cell.coordinate)

6. Read Multiple Cells

Iterate over cells to print each value:

for cell in cells:
    print(cell.value)

To read a rectangular range (e.g., rows 2‑5, columns 1‑3), use the appropriate range syntax.

7. Iterate All Rows

for row in sheet.rows:
    print(row)

Writing Excel

1. Save Workbook

workbook.save(filename='Excel工作表1.xlsx')

If the read and write paths are identical, the original file is modified.

If the paths differ, a new file is created.

2. Write a Single Cell

cell = sheet['A1']
cell.value = '业务需求'

3. Write One or Multiple Rows

(Images illustrate the process.)

4. Write a Formula

sheet['K11'] = '=AVERAGE(K1:K10)'

5. Insert Rows

(Image illustrates insertion.)

6. Insert Columns

(Image illustrates insertion.)

7. Delete Multiple Rows

(Image illustrates deletion.)

8. Delete Multiple Columns

(Image illustrates deletion.)

9. Move a Range of Data

(Image illustrates moving data.)

10. Create a New Workbook

from openpyxl import Workbook
workbook = Workbook()

Adjusting Excel Styles

1. Font Styles

(Image shows font settings.)

2. Alignment

(Image shows alignment options.)

Horizontal: distributed, justify, center, left, fill, centerContinuous, right, general

Vertical: bottom, distributed, justify, center, top

3. Border Styles

(Image shows border options.)

Border line styles include double, mediumDashDotDot, slantDashDot, dashDotDot, dotted, hair, mediumDashed, dashed, dashDot, thin, mediumDashDot, medium, thick.

4. Fill Patterns

(Image shows fill settings.)

5. Row Height and Column Width

sheet.row_dimensions[1].height = 50
sheet.column_dimensions['C'].width = 20

6. Merge and Unmerge Cells

# Merge
sheet.merge_cells('A1:B2')
sheet.merge_cells(start_row=1, start_column=3, end_row=2, end_column=4)

# Unmerge
sheet.unmerge_cells('A1:B2')
sheet.unmerge_cells(start_row=1, start_column=3, end_row=2, end_column=4)