Using openpyxl to Create, Read, and Manipulate Excel Files in Python

The openpyxl library enables Python developers to handle and edit Excel files for automation, data analysis, and report generation.

Installation

Run the following command to install the library: pip install openpyxl Basic Operations

1. Create a new Excel file

from openpyxl import Workbook
wb = Workbook()
ws = wb.active
ws.title = "报告"
ws['A1'] = "姓名"
ws['B1'] = "成绩"
ws.append(["张三", 95])
ws.append(["李四", 88])
wb.save("report.xlsx")

2. Read an existing Excel file

from openpyxl import load_workbook
wb = load_workbook("report.xlsx")
ws = wb["报告"]
for row in ws.iter_rows(min_row=2, values_only=True):
    name, score = row
    print(f"{name} 的成绩是 {score}")

Common Functionalities

1. Iterate over cell values

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

2. Modify a cell

ws['B2'] = 100  # modify 李四's score
wb.save("report.xlsx")

3. Set font and fill style

from openpyxl.styles import Font, PatternFill
ws['A1'].font = Font(bold=True, color="FFFFFF")
ws['A1'].fill = PatternFill("solid", fgColor="0000FF")

4. Merge or split cells

ws.merge_cells('A4:B4')
ws['A4'] = "总分"
# ws.unmerge_cells('A4:B4')  # to split

5. Freeze panes ws.freeze_panes = 'B2' 6. Add a formula

ws['C1'] = "总和"
ws['C2'] = "=SUM(B2:B10)"

7. Adjust column width and row height

ws.column_dimensions['A'].width = 20
ws.row_dimensions[1].height = 25

Save and Close wb.save("final_report.xlsx") Practical Scenarios

• Bulk data writing

data = [
    ["姓名", "语文", "数学"],
    ["小明", 89, 92],
    ["小红", 76, 88]
]
for row in data:
    ws.append(row)

• Writing a pandas DataFrame to Excel (pandas uses openpyxl internally for .xlsx files)

import pandas as pd
df = pd.DataFrame(data[1:], columns=data[0])
df.to_excel("report_by_pandas.xlsx", index=False)

Tips

Use values_only=True in iter_rows to get raw values instead of cell objects.

Convert column numbers to letters with openpyxl.utils.get_column_letter(n).

Load a workbook with data_only=True to retrieve calculated formula results.