openpyxl模块

引入#

from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, colors, Alignment

openpyxl模块#

# 创建新文件
wb = Workbook()      # 创建一个excel文件
sheet = wb.active    # 获取当前sheet表

sheet.title = "云朵"  # 设置sheet标题
sheet["C6"] = 1      # 给C6这个单元格写数据
sheet.append([1, 2, 3])  # 会从该sheet最下面空白行第一行，最左边开始，写入行数据

wb.save("我的excel.xlsx")  # 保存该文件到当前目录

# 加载已有文件
wb = load_workbook("我的excel.xlsx")
sheet = wb.active
sheet.title = "云朵2"
wb.save("我的excel.xlsx")

# 案例
# 获取sheet
wb = load_workbook('测试数据-表格.xlsx')
sheet = wb.active

# 声明字体样式，italic表示斜体
myfont = Font(name='宋体', size=30, italic=True, color=colors.BLUE, bold=True)

# 设置
sheet['A2'].font = myfont  # 设置字体
sheet['B3'].alignment = Alignment(horizontal='center', vertical='center')  # 设置对齐方式,还有left，right
sheet.row_dimensions[2].height = 80  # 设置第4行高度
sheet.column_dimensions['C'].width = 100  # 设置C列宽度

# 保存
wb.save('测试数据-表格.xlsx')

花式遍历excel数据#

# 打开excel文件
wb = load_workbook("测试数据-表格.xlsx")

# 获取sheet列表
print(wb.sheetnames)  # ['Sheet1', 'Sheet2']

# 获取指定表
sheet = wb['Sheet1']

# 输出B2的内容
print(sheet['B2'].value)  # 张艺卓

# 获取 行/列/块
print(sheet['A2':'C2'])  # ((<Cell 'Sheet1'.A2>, <Cell 'Sheet1'.B2>, <Cell 'Sheet1'.C2>),)
print(sheet['A1':'A5'])  # ((<Cell 'Sheet1'.A1>,), (<Cell 'Sheet1'.A2>,), (<Cell 'Sheet1'.A3>,), (<Cell 'Sheet1'.A4>,), (<Cell 'Sheet1'.A5>,))
print(sheet['A1':'B2'])  # ((<Cell 'Sheet1'.A1>, <Cell 'Sheet1'.B1>), (<Cell 'Sheet1'.A2>, <Cell 'Sheet1'.B2>))

print()

# 循环遍历块内容
for row in sheet['A1':'B2']:
    for cell in row:
        print(cell.value)

print()

# 循环Sheet1整个表
for row in sheet:
    for cell in row:
        print(cell.value)

print()

# 拿到每一行
for row in sheet.rows:
    print(row)

print()

# 拿到每一列
for column in sheet.columns:
    print(column)

print()

# 按行循环，循环2-4行，每行内容是2-3列内容
for row in sheet.iter_rows(min_row=2, max_row=4, min_col=2, max_col=3):
    for cell in row:
        print(cell.value, end=" ")
    print()

print()

# 按列循环，循环2-3列
for column in sheet.iter_cols(min_col=2, max_col=3):
    for cell in column:
        print(cell.value, end=" ")
    print()

删除表#

wb = load_workbook("测试数据-表格.xlsx")

print(wb.sheetnames)  # ['Sheet1', 'Sheet2']

sheet = wb['Sheet1']

# 删除sheet表
wb.remove(sheet)  # ['Sheet2']

print(wb.sheetnames)

# 保存之后才有效
wb.save("测试数据-表格.xlsx")