【openpyxl】python处理excel的常用操作

openpyxl

openpyxl是一个处理Excel文件的python库，也是python办公自动化的一大利器，本文主要介绍一些openpyxl的常用操作

相关处理Excel的python库

• OpenPyXL 是个读写 Excel 2010 xlsx/xlsm/xltx/xltm 的 Python 库，简单易用，功能广泛，单元格格式/图片/表格/公式/筛选/批注/文件保护等等功能应有尽有，图表功能是其一大亮点
• xlwings 是一个基于 BSD 授权协议的 Python 库，可以轻松的使用 Python 操作 Excel，也可以在 Excel 中调用 Python，以接近 VBA 语法的实现 Excel 编程，支持 Excel 宏，并且可以作为 Web 服务器，提供 REST API 接口
• pandas 数据处理是 pandas 的立身之本，Excel 作为 pandas 输入/输出数据的容器
• win32com 从命名上就可以看出，这是一个处理 windows 应用的扩展，Excel 只是该库能实现的一小部分功能。该库还支持 office 的众多操作。需要注意的是，该库不单独存在，可通过安装 pypiwin32 或者 pywin32 获取
• Xlsxwriter 拥有丰富的特性，支持图片/表格/图表/筛选/格式/公式等，功能与 openpyxl 相似，优点是相比 openpyxl 还支持 VBA 文件导入，迷你图等功能，缺点是不能打开/修改已有文件，意味着使用 xlsxwriter 需要从零开始
• DataNitro 一个 Excel 的付费插件，内嵌到 Excel 中，可完全替代 VBA，在 Excel 中使用 python 脚本。既然被称为 Excel 中的 python，同时可以与其他 python 库协同。
• xlutils 基于 xlrd/xlwt，老牌 python 包，算是该领域的先驱，功能特点中规中矩，比较大的缺点是仅支持 xls 文件。

openpyxl安装

pip install openpyxl

openpyxl使用

基本概念

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

创建加载和保存

from openpyxl import load_workbook,Workbook

# 创建一个 workbook
wb = Workbook()
# 获取被激活的 worksheet
ws = wb.active

#加载已有的excel文件
wb = load_workbook('test.xlsx')
ws = wb.active
# 显示文档中包含的 表单 名称
print(wb.sheetnames)

#保存文件
wb.save("sample1.xlsx")

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

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

操作和修改

from openpyxl import load_workbook

wb = load_workbook('sample.xlsx')

#操作sheet
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 标签背景色

#操作单元格
# 通过单元格名称设置
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.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)        

参考