Python 玩转 Excel

在前面抓取高考分数线的文章中,我们用到了 openpyxl 模块来存储数据到 Excel,今天带大家学习一下该模块的详细使用。

根据官方文档,openpyxl 是一个用来处理 xlsx/xlsm/xltx/xltm 格式 Excel 文件的 Python 代码库,同时支持 Pandas 和 NumPy 等包,能够绘制图表,并且同样支持格式控制等,详细文档可以参考: https://openpyxl.readthedocs.io/

openpyxl 用起来非常简单,对照文档就可以解决一些基本需求,比如常见的都写操作。

现在还有很多人在用 Excel 2003 版本,即 xls 格式,那么 xlsxlsx 有什么区别呢?

xls 是一个特有的二进制格式,其核心结构是复合文档类型的结构,而 xlsx 的核心结构是 XML 类型的结构,采用的是基于 XML 的压缩方式,使其占用的空间更小。xlsx 中最后一个 x 的意义就在于此。

1 基本概念

在 openpyxl 中,主要用到三个概念:Workbook,Sheet,Cell:

  • Workbook:就是一个 excel 工作簿,其中包含多个 sheet;
  • Sheet:工作簿中的一张表页;
  • Cell:就是简单的一个单元格,用来存储数据对象;

openpyxl 的主要操作就是围绕着这三个概念进行的,无怪乎:打开 Workbook,定位 Sheet,操作 Cell。下面就分别介绍 openpyxl 几个常见的方法。

2 安装

openpyxl 的安装很简单,使用 pip 直接安装即可。

1
pip install openpyxl

3 基本操作

提前新建一个测试 Excel:

测试 Excel

  • 导入模块
1
>>> import openpyxl

3.1 Workbook 相关

  • 读取已存在的 xlsx
1
>>> wb = openpyxl.load_workbook("test.xlsx")

openpyxl.load_workbook() 函数接受文件名,返回一个 Workbook 数据类型的值。这个 Workbook 对象代表这个 Excel 文件,有点类似 File 对象代表一个打开的文本文件。

  • 以只读模式读取
1
>>> wb = openpyxl.load_workbook("test.xlsx", read_only=True)
  • 保存 Workbook

在对 Workbook 进行了相关操作后,可以调用 save(filename) 方法进行保存。

另外,在只读模式下保存时,会报 Workbook is read-only 异常。

1
2
3
4
5
6
>>> wb.save('test.xlsx')
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "/usr/local/lib/python3.5/dist-packages/openpyxl/workbook/workbook.py", line 363, in save
raise TypeError("""Workbook is read-only""")
TypeError: Workbook is read-only

3.2 Sheet 相关

  • 获取 Workbook 中的 sheet 列表

返回一个 sheet 的 list。

1
>>> sheet = wb.worksheets
  • 获取 sheet 页的名称列表
1
2
>>> wb.sheetnames
['各专业历年录取分数线', '测试页']
  • 读取 sheet 页
1
2
3
4
5
# 根据名称读取
>>> sheet = wb['测试页']

# 通过索引 index 读取
>>> sheet = wb.worksheets[1]
  • 获取当前正在使用的 sheet 页
1
>>> sheet =  wb.active
  • sheet 页属性
1
2
3
4
5
6
7
8
9
10
>>> sheet.title
'测试页'

# 最大列数
>>> sheet.max_column
4

# 最大行数
>>> sheet.max_row
13
  • 新建 sheet 页
1
2
3
4
5
6
7
8
9
>>> wb.create_sheet('test2')
<Worksheet "test2">
>>> wb.sheetnames
['各专业历年录取分数线', '测试页', 'test2']

# 在指定索引处新建
>>> sheet = wb.create_sheet('test2',1)
>>> wb.sheetnames
['各专业历年录取分数线', 'test21', 'test2', '测试页']

若 sheet 页重名,会自动进行重命名。

  • 修改 sheet 页名称
1
2
3
4
>>> sheet = wb['test2']
>>> sheet.title = 'test3'
>>> wb.sheetnames
['各专业历年录取分数线', '测试页', 'test3']
  • 删除 sheet 页

要先获取到 sheet 页才能删除,不能直接用 sheet 页的名称删除

1
2
3
4
5
6
7
>>> sheet = wb['test3']
>>> wb.remove(sheet)
>>> wb.sheetnames
['各专业历年录取分数线', '测试页']

# 也可以使用 del 进行删除
>>> del wb['test2']

3.3 行和列

  • 获取指定行/列
1
2
3
4
5
6
7
8
9
10
# 获取第 1 行
>>> sheet[1]
(<Cell '测试页'.A1>, <Cell '测试页'.B1>, <Cell '测试页'.C1>, <Cell '测试页'.D1>)

# 获取第 1 列
>>> sheet['A']
(<Cell '测试页'.A1>, <Cell '测试页'.A2>, <Cell '测试页'.A3>, <Cell '测试页'.A4>, <Cel
l '测试页'.A5>, <Cell '测试页'.A6>, <Cell '测试页'.A7>, <Cell '测试页'.A8>, <Cell '测
试页'.A9>, <Cell '测试页'.A10>, <Cell '测试页'.A11>, <Cell '测试页'.A12>, <Cell '测试
页'.A13>)
  • 对行/列切片获取
1
2
3
4
5
6
7
8
9
10
11
>>> sheet[2:3]
((<Cell '测试页'.A2>, <Cell '测试页'.B2>, <Cell '测试页'.C2>, <Cell '测试页'.D2>), (<
Cell '测试页'.A3>, <Cell '测试页'.B3>, <Cell '测试页'.C3>, <Cell '测试页'.D3>))
>>> sheet['A:B']
((<Cell '测试页'.A1>, <Cell '测试页'.A2>, <Cell '测试页'.A3>, <Cell '测试页'.A4>, <Ce
ll '测试页'.A5>, <Cell '测试页'.A6>, <Cell '测试页'.A7>, <Cell '测试页'.A8>, <Cell '
测试页'.A9>, <Cell '测试页'.A10>, <Cell '测试页'.A11>, <Cell '测试页'.A12>, <Cell '测
试页'.A13>), (<Cell '测试页'.B1>, <Cell '测试页'.B2>, <Cell '测试页'.B3>, <Cell '测试
页'.B4>, <Cell '测试页'.B5>, <Cell '测试页'.B6>, <Cell '测试页'.B7>, <Cell '测试页'.B
8>, <Cell '测试页'.B9>, <Cell '测试页'.B10>, <Cell '测试页'.B11>, <Cell '测试页'.B12>
, <Cell '测试页'.B13>))
  • 获取所有行/列

返回的是一个 Generator 对象,它包含该区域中的 Cell 对象。里面是每一行(列)的数据,每一行(列)又由一个 tuple 包裹。

1
2
3
4
5
6
7
8
9
>>> rows = sheet.rows
>>> rows
<generator object Worksheet._cells_by_row at 0x7f778a7978e0>

>>> columns = sheet.columns

>>> for row in sheet.rows:
... for cell in row:
... print(cell.value)

因为 sheet.rows 是生成器类型,不能直接使用索引,需要先转换成 list 之后才行,如 list(sheet.rows)2 这样就获取到第三行的 tuple 对象。

  • 添加一行值
1
2
>>> sheet.append(row)
>>> row = [1,2,3,4,5,6]

3.4 Cell 相关

  • 读取 Cell
1
2
>>> cell = sheet['B2']
>>> cell = sheet.cell(2,1)

需要注意的是:openpyxl 中 row 和 column 为了和 Excel 中的表达方式一致,并不和编程语言的习惯以 0 表示第一个值,而是 1 开始。

  • Cell 属性
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 所在列
>>> cell.column
'A'

# 所在行
>>> cell.row
2

# 所属坐标
>>> cell.coordinate
'A2'

# 对应的值
>>> cell.value
'A2'
  • 写入 Cell
1
2
3
4
5
# 直接给单元格赋值
>>> cell.value = 'test'
# 这里可以不写 value?
>>> sheet['A1'] = 'kk'
>>> sheet.cell(1,1).value = 'ff'
  • 写入公式
1
2
3
4
5
6
7
8
9
# 写入和值
>>> sheet['A14'] = "=SUM(B14:D14)"
>>> sheet['A14'].value
'=SUM(B14:D14)'

# 写入平均值
>>> sheet['A14'] = "=AVERAGE(B14:D14)"
>>> sheet['A14'].value
'=AVERAGE(B14:D14)'

这里可发现,在读取的时候,返回的是公式本身 '=AVERAGE(B14:D14)',而不是计算结果。若要返回计算结果,只有手动打开 test.xlsx 文件,然后点击保存更改。

  • 单元格合并与拆分
1
2
>>> sheet.merge_cells('A1:A3')
>>> sheet.merge_cells('B1:D2')

合并后的效果

如果这些要合并的单元格都有数据,只会保留左上角的数据,其他则丢弃。

分解类似:

1
2
>>> sheet.unmerge_cells('A1:A3')
>>> sheet.unmerge_cells('B1:D2')
  • 单元格样式
1
2
3
4
5
6
7
8
9
10
11
12
from openpyxl.styles import Font, colors, Alignment

# 设置字体: 等线 24 号加粗斜体,字体颜色红色
bold_itatic_24_font = Font(name="等线", size=24, italic=True, color=colors.RED, bold=True)
sheet["B1"].font = bold_itatic_24_font

# 对齐方式: B1 中的数据垂直居中和水平居中
sheet["C1"].alignment = Alignment(horizontal="center", vertical="center")

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

设置后的效果:

单元格样式


openpyxl 模块的使用就到这里,完整使用示例可以参考我的上篇:Python 助你填写高考志愿

其实还有很多高级用法,但个人觉得用的较少,有兴趣的可以参考官网:https://openpyxl.readthedocs.io/en/stable/

hoxis wechat
一个脱离了高级趣味的程序员,关注回复1024有惊喜~
赞赏一杯咖啡
  • 本文作者: hoxis | 微信公众号【不正经程序员】
  • 本文链接: https://hoxis.github.io/python-openpyxl.html
  • 版权声明: 本博客所有文章除特别声明外,均采用 CC BY-NC-SA 3.0 许可协议。转载请注明出处!
  • 并保留本声明和上方二维码。感谢您的阅读和支持!
0%