[Python] openpyxl 模块用法小结

• 安装
• 创建新的 Excel 文件
• 创建新的表 sheet
• 打开已有的 Excel 文件
• 访问单个单元格 cell
• 访问多个单元格
• 各种其它操作
• 样式和格式

安装

pip install openpyxl

创建新的 Excel 文件

import datetime
from openpyxl import Workbook

# 实例化。Workbook 对应 Excel 文件
wb = Workbook()
# 取得当前的 sheet。Worksheet 对应 Excel 文件；文件里的 sheet，即表格
ws = wb.active
# 看看默认的worksheet名称是什么
print(ws.title)
# 起个 sheet 名字
ws.title = '名单'
# 写数据
# 方式一：数据可以直接分配到单元格中(可以输入公式)
ws['A1'] = '名人排行榜'
# 方式二：可以附加行，从第一列开始附加(从最下方空白处，最左开始)(可以输入多行)
ws.append(['姓名', '年龄', '朝代'])
ws.append(['公孙轩辕', '5349', '上古'])
# 方式三：Python 类型会被自动转换
ws['B1'] = datetime.datetime.now().strftime("%Y-%m-%d")

# 保存数据
wb.save('history.xlsx')

创建新的表 sheet

# 方式一：插入到最后(default)
ws1 = wb.create_sheet("故事") 
# 方式二：插入到最开始的位置
ws2 = wb.create_sheet("备注", 0)

打开已有的 Excel 文件

from openpyxl import load_workbook

# 注意，用了 load_
wb = load_workbook('history.xlsx')
print(wb.active.title)

# 通过[表名]选择表 sheet
ws3 = wb["名单"]

# 显示所有表名
print(wb.sheetnames)
# 遍历所有表
for sheet in wb:
    print(sheet.title)

输出：(sheetnames属性是个按顺序排列的列表)

备注
['备注', '名单', '故事']
备注
名单
故事

注意如果 Excel 里有公式，你不需要

, data_only=True

访问单个单元格 cell

from openpyxl import load_workbook

wb = load_workbook('history.xlsx')
ws = wb.active

# 方法一
c1 = ws['A4']
# 方法二：row 行；column 列
c2 = ws.cell(row=4, column=2, value=10)
# 方法三：只要访问就创建
for i in range(1, 101):
    for j in range(1, 101):
        ws.cell(row=i, column=j, value=2)

wb.save('history.xlsx')

访问多个单元格

from openpyxl import load_workbook

wb = load_workbook('history.xlsx')
ws = wb['名单']
# 通过切片
cell_range = ws['A2':'C3']
print(cell_range)
# 通过行(列)
col_c = ws['C']
print(col_c)
col_range = ws['C:D']
print(col_range)
row10 = ws[2]
print(row10)
row_range = ws[1:4]
print(row_range)
# 通过指定范围(行 → 行)
for row in ws.iter_rows(min_row=1, max_col=3, max_row=2, values_only=True):
    for cell in row:
        print(cell)

ws['C9'] = 'hello world'

# 遍历所有 方法一
print(tuple(ws.rows))

# 遍历所有 方法二
print(tuple(ws.columns))

各种其它操作

from openpyxl import load_workbook

wb = load_workbook('history.xlsx')
ws = wb['名单']
# 获得最大列和最大行
print(ws.max_row)
print(ws.max_column)

# 获取每一行，每一列
# sheet.rows为生成器, 里面是每一行的数据，每一行又由一个tuple包裹。
# sheet.columns类似，不过里面是每个tuple是每一列的单元格。
# 因为按行，所以返回A1, B1, C1这样的顺序
for row in ws.rows:
    for cell in row:
        print(cell.value)

# A1, A2, A3这样的顺序
for column in ws.columns:
    for cell in column:
        print(cell.value)


# 根据数字得到字母，根据字母得到数字
from openpyxl.utils import get_column_letter, column_index_from_string

# 根据列的数字返回字母
print(get_column_letter(2))
# 根据字母返回列的数字
print(column_index_from_string('D'))

# 删除工作表

# 方式一
wb.remove('备注')
# 方式二
del wb['备注']
# 矩阵置换（行 → 列）
rows = [
    ['Number', 'data1', 'data2'],
    [2, 40, 30],
    [3, 40, 25],
    [4, 50, 30],
    [5, 30, 10],
    [6, 25, 5],
    [7, 50, 10]]

list(zip(*rows))

# out
[('Number', 2, 3, 4, 5, 6, 7),
 ('data1', 40, 40, 50, 30, 25, 50),
 ('data2', 30, 25, 30, 10, 5, 10)]

# 注意 方法会舍弃缺少数据的列(行)
rows = [
    ['Number', 'data1', 'data2'],
    [2, 40    ],    # 这里少一个数据
    [3, 40, 25],
    [4, 50, 30],
    [5, 30, 10],
    [6, 25, 5],
    [7, 50, 10],
]
# out
[('Number', 2, 3, 4, 5, 6, 7), ('data1', 40, 40, 50, 30, 25, 50)]

样式和格式

from openpyxl import load_workbook
# 需要导入的类
from openpyxl.styles import Font, colors, Alignment

wb = load_workbook('history.xlsx')
ws = wb['名单']
# 改变 sheet 标签按钮颜色
ws.sheet_properties.tabColor = "1072BA"

# 字体：下面的代码指定了等线24号，加粗斜体，字体颜色蓝色。直接使用cell的font属性，将Font对象赋值给它。
ws['A1'].font = Font(name='等线', size=24, italic=True, color=colors.BLUE, bold=True)

# 上面的color也可以直接用rgb字符串，比如 color='ffc7ce'

# 对齐方式：直接使用cell的属性Alignment，这里指定垂直居中和水平居中。除了center，还可以使用right、left等等参数
ws['B1'].alignment = Alignment(horizontal='center', vertical='center')

# 设置行高和列宽
ws.row_dimensions[2].height = 40
ws.column_dimensions['C'].width = 30

# 合并和拆分单元格：以合并区域的左上角的那个单元格为基准，覆盖其他单元格使之称为一个大的单元格
# 拆分单元格后将这个大单元格的值返回到原来的左上角位置
# 合并一行中的几个单元格
ws.merge_cells('B1:G1')
# 合并一个矩形区域中的单元格
ws.merge_cells('A7:C10')

# ws.unmerge_cells('A7:C10')
wb.save('his111.xlsx')