Python 之操作excel

一、常用方法

Workbook()：创建新的工作簿

create_sheet()：创建工作表

append()：加入一行数据

sheep.merge_cells('A1:E1')：合并单元格

详细：https://openpyxl.readthedocs.io/en/stable/api/openpyxl.html

二、示例代码

import openpyxl
from openpyxl.styles import Font, Alignment

'''创建excel'''
def create_excel(filename):
    # 创建新的工作簿
    wb = openpyxl.Workbook()
    # 创建工作表
    sheep = wb.create_sheet("测试", 0)

    # 设置样式

    sheep['A1'].value = "姓名"
    sheep['B1'].value = "英语"
    sheep['C1'].value = "语文"
    sheep['D1'].value = "数学"

    wb.save(filename)
    wb.close()

'''修改excel'''
def update_excel(filename):
    wb = openpyxl.load_workbook(filename)
    # 获取当前工作表，也可也直接 wb[名称]获取
    sheep = wb.active
    # 设置单元格值，必需从1开始，另一种可以 sheep['A1'].value = 'test'
    # 获取最大行
    rows_num = sheep.max_row
    # 获取最大列
    cols_num = sheep.max_column
    print(rows_num, cols_num)
    # 设置样式
    font = Font(
        name="微软雅黑",        # 字体
        size=14,               # 字号
        bold=True,             # 粗体
        italic=True,           # 斜体
        color="FF0000",        # 红色（RGB 十六进制）
        underline="single"     # 下划线
    )
    sheep.cell(1, 1).font = font

    # 批量插入数据
    for i in range(rows_num + 1, 11):
        sheep.cell(i, 1).value = f'样子{i}'
        sheep.cell(i, 2).value = 88
        sheep.cell(i, 3).value = 90.5
        cell4 = sheep.cell(i, 4)
        cell4.value = 78
    
    # 设置对齐方式
    align = Alignment(
        horizontal='center',
        vertical='center',
        wrap_text=True  # 长文本自动换行
    )
    for j in range(1, cols_num + 1):
        sheep.cell(1, j).alignment = align
    
    # 设置列宽 - 第一列
    sheep.column_dimensions[sheep.cell(1, 1).column_letter].width = 50

    # 插入数据后行数自动计算
    print(sheep.max_row)
    wb.save(filename)
    wb.close()

'''读取excel'''
def read_excel(filename):
    wb = openpyxl.load_workbook(filename)
    sheep = wb['测试']
    rows = sheep.max_row
    cols = sheep.max_column
    data = []
    # 读取单个单元格
    for i in range(1, rows + 1):
        data.append({
            "name": sheep.cell(i, 1).value,
            "y": sheep.cell(i, 2).value,
            "w": sheep.cell(i, 3).value,
            "m": sheep.cell(i, 4).value,
        })

    # 读取整行数据
    for row in sheep.iter_rows(min_row=2, max_row=rows, values_only=True):
        print(row) # 是一行数据的元组

    print(data)

if __name__ == '__main__':

    update_excel("test.xlsx")

    read_excel("test.xlsx")