openpyxl 库

1. 简介与安装

2.具体示例

2.1 获取 workbook 与 sheet 对象

2.2 访问单元格及其值

2.3 写数据

2.4 设置样式 

 

  

1. 简介与安装

openpyxl 简介

openpyxl 是一款比较综合的工具，不仅能够同时读取和修改 Excel 文档，而且可以对 Excel 文件内单元格进行详细设置，包括单元格样式等内容，甚至还支持图表插入、打印设置等内容。使用 openpyxl 可以读写 xltm、xltx、xlsm、xlsx 等类型的文件，且可以处理数据量较大的 Excel 文件，跨平台处理大量数据是其它模块没法相比的。因此，openpyxl 成为处理 Excel 复杂问题的首选库函数。

openpyxl 与 xlrd/xlwt 的比较

两者都是对于 excel 文件操作的模块，其主要区别在于：

• 写操作：
  • xlwt：针对E Excel 2007 之前的版本（.xls），无法生成 xlsx 文件。
  • openpyxl：主要针对 Excel 2007 之后的版本（.xlsx）。
• 读写速度：
  • xlrd/xlwt 在读写方面的速度都要优于 openpyxl。
• 文件大小：
  • xlrd/xlwt：对单个 sheet 不超过 65535 行。
  • openpyxl：对文件大小没有限制。

所以想要尽量提高效率又不影响结果时，可以考虑用 xlrd 读取，用 openpyxl 写入。

 

2.具体示例

具体的使用流程如下：

1. 导入 openpyxl 模块；
2. 调用 openpyxl.load_workbook() 函数或 openpyxl.Workbook()，取得 Workbook 对象；
3. 调用 get_active_sheet() 或 get_sheet_by_name() 工作簿方法，取得 Worksheet 对象；
4. 使用索引或工作表的 cell() 方法，带上 row 和 column 关键字参数，取得 Cell 对象，读取或编辑 Cell 对象的 value 属性。

 

2.1 获取 workbook 与 sheet 对象

 创建新文件

from openpyxl import Workbook

# 创建工作簿对象
wb = Workbook()

# 激活sheet；拿到当前文件对象中默认操作的一个sheet，如上次关闭文件时所打开的sheet
ws = wb.active
# 表格在被创建的时候会自动的有一个名字。它们被命名在一个队列中（sheet, ...），可以使用title属性在任何时候来改变它们的名字。
ws.title = "tmp"

# 删除sheet
wb.remove(ws)

# 创建sheet
ws1 = wb.create_sheet("tmp1")  # 默认在最后插入新sheet
ws2 = wb.create_sheet("tmp2", 0)  # 在索引为0的位置插入

# 保存文件（覆盖同名文件的全部内容）
wb.save("文件名称.xlsx")

打开已有文件

# 打开已有文件
from openpyxl import load_workbook

# 打开指定文件
wb = load_workbook("e:\\test.xlsx")

# 查看所有sheet名
print(wb.sheetnames)  # 返回列表
# 遍历所有sheet名
for sheet in wb.sheetnames:
    print(sheet.title())

# 选择sheet
ws3 = wb["tmp3"]  # 方法1：名称可以作为key进行查找
ws4 = wb.get_sheet_by_name("tmp4")  # 方法2
print(ws3 is ws4)  # True

# 在原有内容上进行修改并保存
wb.save("e:\\test.xlsx")

 

2.2 访问单元格及其值

注意：当一个工作表在内存中被创建时，它里面默认是没有表格对象的，它们只有在第一次被访问的时候才会被创建，从而减少内存占用。因为这个特性，我们要循环表格而不是直接访问它们，这样会将所有的表格对象在内存中创建，就算你没有访问它们中的任何一个值。

openpyxl 读写单元格时，单元格的坐标位置起始值是（1,1），即下标最小值为1。

访问单个单元格

# 获取最大行列（返回数值）
print(ws3.max_row)
print(ws3.max_column)

# 方法1：指定行列
print(ws3["A2"].value)

# 方法2：指定行列
print(ws3.cell(row=2, column=2).value)  # 行号和列号从1开始

# 方法3：只要访问就会创建对应单元格对象
for i in range(1, 10):
    for j in range(1, 10):
        print(ws4.cell(i, j).value)

行列序号转换

from openpyxl.utils import get_column_letter, column_index_from_string

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

访问多个单元格

# 访问指定行数据

print(ws3[1])  # 方法1：索引从1开始
print(ws3[1:3])  # 切片方式，返回二维元组
print(tuple(ws3.rows)[1])  # 方法2：索引从0开始，sheet.rows为生成器, 里面是每一行的数据，每一行又由一个tuple包裹
# 遍历获取每个单元格的值
for cell in ws3[1]:
    print(cell.value)


# 访问指定列数据

print(ws3["A"])
print(tuple(ws3.columns)[1])  # 访问第2列单元格
print(ws3["A:C"])  # 返回二维元组


# 指定范围

# 方法1
print(ws3["A1:B4"])
# 方法2：最多访问两行两列的单元格
for row in ws3.iter_rows(min_row=1, max_row=2, max_col=2):  # 行号和列号从1开始
    for cell in row:
        print(cell)
'''执行结果：
<Cell 'tmp3'.A1>
<Cell 'tmp3'.B1>
<Cell 'tmp3'.A2>
<Cell 'tmp3'.B2>
'''

for row in ws3.iter_cols(min_row=1, max_row=2, max_col=2):  # 行号和列号从1开始
    for cell in row:
        print(cell)
'''注意与上述iter_rows的获取顺序不同
<Cell 'tmp3'.A1>
<Cell 'tmp3'.A2>
<Cell 'tmp3'.B1>
<Cell 'tmp3'.B2>
'''

矩阵置换

rows = [
    ['Number', 'data1', 'data2'],
    [2, 40, 30],
    [3, 40, 25],
    [4, 50, 30],
    [5, 30, 10],
    [6, 25, 5],
    [7, 50, 10]]
print(list(zip(*rows)))  # 传入二维序列时需要解包
'''执行结果：
[('Number', 2, 3, 4, 5, 6, 7), ('data1', 40, 40, 50, 30, 25, 50), ('data2', 30, 25, 30, 10, 5, 10)]
'''

# 注意：该方法会舍弃缺少数据的列(行)
a1 = [1, 2]
a2 = [4, 5, 6]
print(list(zip(a1, a2)))
'''执行结果：
[(1, 4), (2, 5)]
'''

 

2.3 写数据

写入单元格值

# 写入常规值

# 方法1
ws3["A1"] = 1
# 方法2：行号和列号从1开始
ws3.cell(row=2, column=2, value="A2")
# 方法3：追加一行数据（即最下方空白处的最左第一个单元格开始）
ws3.append([1, 2, 3])


# 写时间
import time

# 方法1
now_time_1 = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
ws3.cell(row=1, column=1, value=now_time_1)

# 方法2
import locale
locale.setlocale(locale.LC_ALL, 'en')
locale.setlocale(locale.LC_CTYPE, 'chinese')
now_time_2 = time.strftime("%Y年%m月%d日 %H:%M:%S")  # 自动传入当前时间
ws3.cell(row=1, column=2, value=now_time_2)

合并单元格

合并单元格以合并区域的左上角的那个单元格为基准，覆盖其他单元格，使之称为一个大的单元格。

相反，拆分单元格后将这个大单元格的值返回到原来的左上角位置。

ws3.merge_cells('A1:B1')  # 合并一行中的几个单元格
ws3.merge_cells('B2:C11')  # 合并一个矩形区域中的单元格

合并后只可以往左上角写入数据，也就是区间中最左上角的坐标。

如果这些要合并的单元格都有数据，只会保留左上角的数据，其他则丢弃。换句话说若合并前不是在左上角写入数据，那么合并后的单元格则不会有数据。

拆分单元格

ws3.unmerge_cells('A1:B1')  # 拆分后，值回写到A1位置
ws3.unmerge_cells('B2:C11')  # 拆分后，值回写到B2位置

 

2.4 设置样式

sheet标签颜色

ws3.sheet_properties.tabColor = "1072BA"

效果：

行高与列宽

# 第2行行高
ws3.row_dimensions[2].height = 40
# C列列宽
ws3.column_dimensions['C'].width = 30

单元格样式

'''字体'''
# 等线24号，加粗斜体，字体颜色红色
bold_italic_24_font = Font(name='等线', size=24, italic=True, color=colors.RED, bold=True)
# 直接使用cell的font属性，将Font对象赋值给它
ws3['A1'].font = bold_italic_24_font

'''对齐方式'''
# 设置B1中的数据垂直居中和水平居中（除了center，还可以使用right、left等参数）
ws3['B1'].alignment = Alignment(horizontal='center', vertical='center')