Python 之操作excel

一、常用方法

Workbook()：创建新的工作簿

create_sheet()：创建工作表

append()：加入一行数据

详细：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_columnprint(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 = 88sheep.cell(i, 3).value = 90.5cell4 = 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_rowcols = sheep.max_columndata = []# 读取单个单元格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")