Python读取与写入Excel模块:openpyxl

之前我们介绍过几个模块：xlrd、xlwt、XLsxWriter等，但是这些模块要门只能只读，要么只能只写，那么有没有即可读，也可写的模块，答案是肯定的，如pandas、openpyxl、文2com等，在此介绍其中一个：openpyxl。

openpyxl 是一个用于读写Excel的模块，支持xlsx、xlsm、xltx、xltm等文件的读写，不支持xls格式，如果读取大的Excel文件，可以使用read_only（只读模式），如果写入大的Excel文件，可以使用write_only（只写模式）。

一、openpysx模块创建Excel文件4步法

1.1、Workbook()：创建Excel文件

此函数是用来创建Excel文件的一个对象，返回的是一个对象，可以用此对象，创建工作表，保存文件等，如：

import openpyxl  # 导入模块

wb = Workbook()  # 生成 Excel 文件对象
print(wb)

执行结果：

1.2、create_sheet("sheet_name")：创建工作表

openpyxl 在创建 Excel 文件对象时，会默认创建一个名叫“sheet”的工作表，如果想生成工作表，则需要使用 create_sheet() 函数，可用 wb.sheetnames 属性查看所有工作表的名称，如：

import openpyxl # 导入模块

wb = Workbook() # 生成 Excel 文件对象
print(wb.sheetnames) # 默认生成的工作表 sheet
wb.create_sheet("new_sheet")
print(wb.sheetnames) # 增加了工作表 new_sheet

执行结果：

备注：openpyxl 在只写模式下，不会生成默认工作表，此时只能使用 create_sheet() 函数创建。

1.3、向工作表中添加内容

openpyxl 添加内容比较接近 Python 语法，可以使用赋值语句 "="，也可以使用 append()语句（按行加入），如：

import openpyxl # 导入模块

wb = Workbook() # 生成 Excel 文件对象
ws = wb.active # 默认工作表 sheet

# 添加内容（赋值语句）
ws["A1"] = 3
ws["A2"] = "hello world"

print(ws["A1"].value)
print(ws["A2"].value)

# 添加内容（append语句）,按行添加
ws.append(["hello python","hello java","hello php"])
print(ws["A3"].value)
print(ws["B3"].value)
print(ws["C3"].value)

执行结果：

1.4、save("filename")：保存文件

当我们完成所有的操作后，就可以使用 save() 函数来保存我们创建的 Excel 文件，filename 是文件名称及文件所保存的位置，如：

save("file/op/添加数据.xlsx") # 保存文件

执行结果：

以上就是 openpyxl 创建 Excel 文件的4个步骤，相比于 XLsxWriter 模块，openpyxl 在数据添加这块简单，方便，与 python 语法接近。

二、openpyxl 读取 Excel 文件

openpyxl 模块可以使用 load_workbook("filename") 方法来加载本地 Excel 文件，filename 为文件路径，如：

import openpyxl # 导入模块

wb = load_workbook("file/op/编程语言排名榜单.xlsx")  # 加载本地 Excel 文件
ws = wb["2019年"]

print("修改前 B3 的值：",ws["B3"].value) # 打印单元格 B3 的值

ws["B3"].value = "HTML" # 修改单元格 B3 的值

print("修改后 B3 的值：",ws["B3"].value) # 打印单元格 B3 的值

执行结果：

备注：openpyxl 不能完全读取所有的内容，因此，如果本地Excel表格含有图片或者图表之类的，openpyxl 是不能读取的，且在打开或者保存相同名称的Excel表格时，原先表格的图片或者图表将会丢失。

三、openpyxl 进阶用法

3.1、合并和拆分单元格

3.1.1、merge_cells(*args)：合并单元格

在 openpyxl 模块中，我们可以用 merge_cells(start_row, start_column, end_row, end_column) 方法来合并单元格，其中：

start_row：开始行

start_column：开始列

end_row：结束行

end_column：结束列

import openpyxl # 导入模块
from openpyxl.styles import Alignment # 导入格式模块

wb = Workbook() # 创建Excel文件对象
ws = wb.active  # 选取当前sheet工作表

ws.merge_cells(start_row = 2,start_column = 1,end_row = 4,end_column = 4) # 合并单元格
# 也可以用 ws.merge_cells("A2:D2")
ws["A2"].alignment = Alignment(horizontal = "center",vertical = "center") # 添加样式

ws["A2"].value = "合并单元格" # 添加内容

wb.save("file/op/合并单元格.xlsx") # 保存文件

执行结果：

3.1.2、unmerge_cells()：拆分单元格

与 merge_cells() 相反，在 openpyxl 模块中，我们可以用 unmerge_cells(start_row, start_column, end_row, end_column) 方法来拆分单元格，其中：

start_row：开始行

start_column：开始列

end_row：结束行

end_column：结束列

import openpyxl # 导入模块

wb = load_workbook("file/op/合并单元格.xlsx")  # 加载本地 Excel 文件
ws = wb.active # 选取工作表格

ws.unmerge_cells("A2:D4") # 拆分单元格
ws["A2"].value = "拆分单元格" # 添加内容

wb.save("file/op/合并单元格.xlsx") # 保存文件

执行结果：

3.2、行高和列宽

在 openpyxl 中，ws.row_dimensions["row_index"].height 用于设置行高，ws.column_dimensions["col_index"].width 用于设置列宽，如：

import openpyxl # 导入模块

wb = Workbook() # 创建 Excel 文件对象
ws = wb.active  # 选取工作表

ws.row_dimensions[1].height = 40 # 设置行高为 40 字符
ws.column_dimensions["D"].width = 40 # 设置列宽为 40 字符

ws["A1"] = "行高为20个字符" # 添加内容
ws["D1"] = "列宽为40个字符" # 添加内容

wb.save("file/op/设置行高和列宽.xlsx")

执行结果：

3.3、add_image(Image("image_path"),"cell")：插入图片

在 openpyxl 模块中，可以使用 add_image() 方法来向单元格添加图片，如：

import openpyxl # 导入模块
from openpyxl.drawing.image import Image # 导入图片模块

wb = Workbook() # 创建 Excel 文件对象
ws = wb.active  # 选取工作表

ws.add_image(Image("file/test.jpg"),"C3") # 在 C3 单元格插入图片

wb.save("file/op/插入图片.xlsx")

执行结果：

3.4、HYPERLINK("url","name")：添加链接

在 openpyxl 中，添加超链接很简单，使用 HYPERLINK() 即可向单元格里添加超链接，如：

import openpyxl # 导入模块
from openpyxl.styles import Font

wb = Workbook() # 创建 Excel 文件对象
ws = wb.active  # 选取工作表

ws["C3"].font = Font(color = '000000FF', i = True, underline = 'single') # 超链接文字样式
ws["C3"].value = '=HYPERLINK("http://www.baidu.com","百度")' # 添加超链接

wb.save("file/op/添加链接.xlsx") #保存文件

执行结果：

3.4、样式设置

openpyxl 样式设置主要有 字体、填充、边框、对齐、数字格式、保护等，具体用法如下：

下面我们介绍几个：

import openpyxl # 导入模块
from openpyxl.styles import Font, Border, Alignment, PatternFill, Side

wb = Workbook() # 创建 Excel 文件对象
ws = wb.active  # 选取工作表

# 字体样式设置
font = Font(name="黑体",
            size=20,
            bold=True,
            italic=True,
            color = '00CC99FF'
           )

# 填充样式设置
fill = PatternFill(fill_type = None,
                   start_color = 'FFFFFFFF',
                   end_color = 'FF000000'
                  )

# 边框样式设置
border = Border(left = Side(border_style = 'dotted',color = 'FF000000'),
                right = Side(border_style = 'dotted',color = 'FF000000'),
                top = Side(border_style = None,color = 'FF000000'),
                bottom = Side(border_style = None,color = 'FF000000'),
                vertical = Side(border_style = None,color = 'FF000000'),
                horizontal = Side(border_style = None,color = 'FF000000')
               )

# 对齐方式设置
align = Alignment(horizontal = 'center',
                      vertical = 'center'
                     )

C3 = ws["C3"] # 获取 C3 单元格
C3.value = "I Love Python" # 添加内容
C3.font = font # 设置字体样式
C3.fill = fill # 设置填充样式
C3.border = border # 设置边框样式
C3.alignment = align # 设置对齐方式

wb.save("file/op/样式设置.xlsx") #保存文件

执行结果：

3.5、单元格属性获取

单元格的属性比较多，下面列举几个比较常用的，如下：

from openpyxl import load_workbook # 导入模块

wb = load_workbook("file/op/编程语言排名榜单.xlsx") # 打开本地文件
ws = wb.active # 选择工作表

b5 = ws["B5"] # 获取单元格 B5
print("单元格 B5 的值：",b5.value) # 获取 B5 的值
print("单元格 B5 的行索引：",b5.row) # 获取 B5 的行索引
print("单元格 B5 的列索引：",b5.col_idx) # 获取 B5 的列索引
print("单元格 B5 的列名称：",b5.column_letter) # 获取 B5 的列名称
print("单元格 B5 的坐标：",b5.coordinate) # 获取 B5 的坐标
print("单元格 B5 的数据类型：",b5.data_type) # 获取 B5 的数据类型
print("单元格 B5 的默认编码：",b5.encoding) # 获取 B5 的默认编码
print("单元格 B5 的样式：",b5.style) # 获取 B5 的样式
print("单元格 B5 的样式id：",b5.style_id) # 获取 B5 的样式id

执行结果：

3.6、单元格数据获取

单元格的数据获取可以使用 value 属性获取，如：

from openpyxl import load_workbook # 导入模块

wb = load_workbook("file/op/编程语言排名榜单.xlsx") # 打开本地文件
ws = wb.active # 选择工作表

# 单个数据获取
print("单个数据获取：",ws["B5"],ws["B6"])

# 按行获取数据
print("按行获取数据：")
for i in ws.values:
    print(i)
    
# 范围获取数据
print("范围获取数据：")
for i in ws["A3:E6"]:
    for j in i:
        print(j,j.value)

执行结果：

以上为 openpyxl 的一些简单介绍，还有很多用法，在此就不一一介绍了，感兴趣的朋友，可以参考官方文档。