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.