python 操作Excel openpyxl

OpenPyXL 是个读写 Excel 2010 xlsx/xlsm/xltx/xltm 的 Python 库，简单易用，功能广泛，单元格格式/图片/表格/公式/筛选/批注/文件保护等等功能应有尽有，图表功能是其一大亮点
OpenPyXl 几乎可以实现所有的 Excel 功能，而且接口清晰，文档丰富，学习成本相对较低，今天就以 OpenPyXL 为例，了解下如何操作 Excel
安装

pip install openpyxl

基本概念

• workbook 相当于一个 Excel 文件档，每个被创建和打开的 Excel 文件都是独立的 Workbook 对象
• sheet Excel 文档中的表单，每个 Excel 文档至少需要一个 sheet
• cell 单元格，是不可分割的基本数据存储单元

f

from openpyxl import Workbook
# 创建一个 workbook
wb = Workbook()
# 获取被激活的 worksheet
ws = wb.active
# 设置单元格内容
ws['A1'] = 42
# 设置一行内容
ws.append([1, 2, 3])
# python 数据类型可以被自动转换
import datetime
ws['A2'] = datetime.datetime.now()
# 保存 Excel 文件
wb.save("sample.xlsx")

需要注意的是：

新创建的 workbook 对象，会自带一个名为 Sheet 的表单，Office Excel 新建会创建 3 个
创建的 workbook 会将第一个 表单 激活，通过 wb.active 获取引用
像 python-docx work 库一样，save 方法会立即保存，不会有任何提示，建议选择不同文件名来保存

常用功能
OpenPyXl 功能很多，从单元格处理到图表展示，涵盖了几乎全部的 Excel 功能，这里就一些常用的功能做展示，更多的用法可以参考 OpenPyXl 文档（文末参考里有链接）

创建和打开 Excel

如果要加载一个已存在的 Excel 文件，需要用 load_workbook 方法，给定文件路径，返回 workbook 对象：

from openpyxl import load_workbook

wb = load_workbook('test.xlsx')

# 显示文档中包含的 表单 名称
print(wb.sheetnames)

load_workbook 除了参数 filename外为还有一些有用的参数：

• read_only：是否为只读模式，对于超大型文件，要提升效率有帮助
• keep_vba ：是否保留 vba 代码，即打开 Excel 文件时，开启并保留宏
• guess_types：是否做在读取单元格数据类型时，做类型判断
• data_only：是否将公式转换为结果，即包含公式的单元格，是否显示最近的计算结果
• keep_links：是否保留外部链接

操作 sheet

from openpyxl import Workbook
wb = Workbook()
ws = wb.active

ws1 = wb.create_sheet("sheet")  #创建一个 sheet 名为 sheet
ws1.title = "新表单"  # 设置 sheet 标题
ws2 = wb.create_sheet("mysheet", 0) # 创建一个 sheet，插入到最前面 默认插在后面
ws2.title = u"你好"  # 设置 sheet 标题

ws1.sheet_properties.tabColor = "1072BA"  # 设置 sheet 标签背景色

# 获取 sheet
ws3 = wb.get_sheet_by_name(u"你好")
ws4 = wb['New Title']


# 复制 sheet
ws1_copy = wb.copy_worksheet(ws1)

# 删除 sheet
wb.remove(ws1)
# 删除 sheet
del wb['Sheet']

• 每个 Workbook 中都有一个被激活的 sheet，一般都是第一个，可以通过 active 直接获取
• 可以通过 sheet 名来获取 sheet 对象
• 创建 sheet时需要提供 sheet 名称参数，如果该名称的 sheet 已经存在，则会在名称后添加 1，再有重复添加 2，以此类推
• 获得 sheet 对象后，可以设置 名称（title），背景色等属性
• 同一个 Workbook 对象中，可以复制 sheet，需要将源 sheet 对象作为参数，复制的新 sheet 会在最末尾
• 可以删除一个 sheet，参数是目标 sheet 对象

操作单元格
单元格（cell）是 Excel 中存放数据的最小单元，就是图形界面中的一个个小格子

OpenPyXl 可以操作单个单元格，也可以批量操作单元格

单独操作
单独操作，即通过 Excel 单元格名称或者行列坐标获取单元格，进行操作

ws1 = wb.create_sheet("Mysheet")  #创建一个sheet
# 通过单元格名称设置
ws1["A1"]=123.11
ws1["B2"]="你好"

# 通过行列坐标设置
d = ws1.cell(row=4, column=2, value=10)

批量操作
需要一下子操作多个单元格时，可以用批量操作来提高效率

指定行列

# 操作单列
for cell in ws["A"]:
    print(cell.value)
# 操作单行
for cell in ws["1"]:
    print(cell.value)
# 操作多列
for column in ws['A:C']:
    for cell in column:
        print(cell.value)
# 操作多行
for row in ws['1:3']:
    for cell in row:
        print(cell.value)
# 指定范围
for row in ws['A1:C3']:
    for cell in row:
        print(cell.value)
所有行或者列
# 所有行
for row in ws.iter_rows():
    for cell in row:
        print(cell.value)
# 所有列
for column in ws.iter_cols():
    for cell in column:
        print(cell.value)
设置整行数据
ws.append((1,2,3))

合并单元格

# 合并
ws.merge_cells('A2:D2')
# 解除合并
ws.unmerge_cells('A2:D2')

ws.merge_cells(start_row=2,start_column=1,end_row=2,end_column=4)
ws.unmerge_cells(start_row=2,start_column=1,end_row=2,end_column=4)

• Lsheet 对象的 merge_cells 方法是合并单元格，unmerge_cells 是解除合并
• 分别有两种参数形式，一种是用单元格名称方式指定，另一种是通过命名参数指定
• 注意：对于没有合并过单元格的位置调用 unmerge_cells 时会报错
  单元格格式
  OpenPyXl 用6种类来设置单元格的样式
• NumberFormat 数字
• Alignment 对齐
• Font 字体
• Border 边框
• PatternFill 填充
• Protection 保护

from openpyxl.styles import Font, PatternFill, Border, Side, Alignment, Protection
from openpyxl.styles import numbers

wb = Workbook()
ws = wb.active
ws.cell(row=1, column=1, value='宋体').font = Font(name=u'宋体', size=12, bold=True, color='FF0000')
ws.cell(row=2, column=2, value='右对齐').alignment = Alignment(horizontal='right')
ws.cell(row=3, column=3, value='填充渐变色').fill = PatternFill(fill_type='solid', start_color='FF0000')
ws.cell(row=4, column=4, value='设置边线').border = Border(left=Side(border_style='thin', color='FF0000'), right= Side(border_style='thin', color='FF0000'))
ws.cell(row=5, column=5, value='受保护的').protection = Protection(locked=True, hidden=True)
ws.cell(row=6, column=6, value=0.54).number_format =numbers.FORMAT_PERCENTAGE

引入字体类
用 cell 方法，为单元格设置值的同时，设置格式
每种格式都有特定的属性，为其设置特定的格式对象
数字格式有点区别，通过设置格式名称来完成，numbers.FORMAT_PERCENTAGE 是个字符串
Border 类，需要配合 Side 类使用，它们都在 openpyxl.styles 中定义
需要注意的是，单元格样式属性只能通过样式对象赋予，而无法通过样式属性来修改，例如 ws.cell(1, 1).font.color = ‘00FF00’ 会报错，如果真要换，需要重新创建一个样式实体，重新赋值
上面展示的是单个单元格格式的设置，也可以批量设置，有两种方式，一种是循环范围内的所有单元格，逐个设置，另一种是对整列或者整行设置：

font = Font(bold=True)

# 遍历范围内的单元格
for row in ws['A1:C3']:
    for cell in row:
        cell.font = font

# 设置整行
row = ws.row_dimensions[1]
row.font = font

# 设置整列
column = ws.column_dimensions["A"]
column.font = font

https://openpyxl.readthedocs.io/en/stable/index.html
https://openpyxl.readthedocs.io/en/stable/usage.html#write-a-workbook