黑马程序员技术交流社区

标题: 【上海校区】Python 玩转 Excel [打印本页]

作者: 不二晨 时间: 2018-11-5 09:48
标题: 【上海校区】Python 玩转 Excel

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

根据官方文档，openpyxl 是一个用来处理 xlsx/xlsm/xltx/xltm 格式 Excel 文件的 Python 代码库，同时支持 Pandas 和 NumPy 等包，能够绘制图表，并且同样支持格式控制等。

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

现在还有很多人在用 Excel 2003 版本，即 xls 格式，那么 xls 和 xlsx 有什么区别呢？
xls 是一个特有的二进制格式，其核心结构是复合文档类型的结构，而 xlsx 的核心结构是 XML 类型的结构，采用的是基于 XML 的压缩方式，使其占用的空间更小。xlsx 中最后一个 x 的意义就在于此。

1 基本概念

在 openpyxl 中，主要用到三个概念：Workbook，Sheet，Cell：

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

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

2 安装

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

pip install openpyxl3 基本操作

提前新建一个测试 Excel：

测试 Excel

导入模块

>>> import openpyxl3.1 Workbook 相关

读取已存在的 xlsx

>>> wb = openpyxl.load_workbook("test.xlsx")

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

以只读模式读取

>>> wb = openpyxl.load_workbook("test.xlsx", read_only=True)

保存 Workbook

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

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

>>> 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-only3.2 Sheet 相关

获取 Workbook 中的 sheet 列表

返回一个 sheet 的 list。

>>> sheet = wb.worksheets

获取 sheet 页的名称列表

>>> wb.sheetnames['各专业历年录取分数线', '测试页']

读取 sheet 页

# 根据名称读取>>> sheet = wb['测试页']# 通过索引 index 读取>>> sheet = wb.worksheets[1]

获取当前正在使用的 sheet 页

>>> sheet = wb.active

sheet 页属性

>>> sheet.title'测试页'# 最大列数>>> sheet.max_column4# 最大行数>>> sheet.max_row13

新建 sheet 页

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

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

修改 sheet 页名称

>>> sheet = wb['test2']>>> sheet.title = 'test3'>>> wb.sheetnames['各专业历年录取分数线', '测试页', 'test3']

删除 sheet 页

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

>>> sheet = wb['test3']>>> wb.remove(sheet)>>> wb.sheetnames['各专业历年录取分数线', '测试页']# 也可以使用 del 进行删除>>> del wb['test2']3.3 行和列

获取指定行/列

# 获取第 1 行>>> sheet[1](<Cell '测试页'.A1>, <Cell '测试页'.B1>, <Cell '测试页'.C1>, <Cell '测试页'.D1>)# 获取第 1 列>>> sheet['A'](<Cell '测试页'.A1>, <Cell '测试页'.A2>, <Cell '测试页'.A3>, <Cell '测试页'.A4>, <Cell '测试页'.A5>, <Cell '测试页'.A6>, <Cell '测试页'.A7>, <Cell '测试页'.A8>, <Cell '测试页'.A9>, <Cell '测试页'.A10>, <Cell '测试页'.A11>, <Cell '测试页'.A12>, <Cell '测试页'.A13>)

对行/列切片获取

>>> 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>, <Cell '测试页'.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 '测试页'.B8>, <Cell '测试页'.B9>, <Cell '测试页'.B10>, <Cell '测试页'.B11>, <Cell '测试页'.B12>, <Cell '测试页'.B13>))

获取所有行/列

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

>>> 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 对象。

添加一行值

>>> sheet.append(row)>>> row = [1,2,3,4,5,6]3.4 Cell 相关

读取 Cell

>>> cell = sheet['B2']>>> cell = sheet.cell(2,1)

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

Cell 属性

# 所在列>>> cell.column'A'# 所在行>>> cell.row2# 所属坐标>>> cell.coordinate'A2'# 对应的值>>> cell.value'A2'

写入 Cell

# 直接给单元格赋值>>> cell.value = 'test'# 这里可以不写 value？>>> sheet['A1'] = 'kk'>>> sheet.cell(1,1).value = 'ff'

写入公式

# 写入和值>>> 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 文件，然后点击保存更改。

单元格合并与拆分

>>> sheet.merge_cells('A1:A3')>>> sheet.merge_cells('B1:D2')

合并后的效果

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

分解类似：

>>> sheet.unmerge_cells('A1:A3')>>> sheet.unmerge_cells('B1:D2')

单元格样式

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 = 40sheet.column_dimensions["C"].width = 30

设置后的效果：

单元格样式

openpyxl 模块的使用就到这里

【转载】https://juejin.im/entry/5b3b2c7d6fb9a04fe820c5a3

作者: 不二晨 时间: 2018-11-7 09:05

ヾ(◍°∇°◍)ﾉﾞ

作者: 梦缠绕的时候 时间: 2018-11-8 16:54

作者: 魔都黑马少年梦 时间: 2018-11-8 17:03

欢迎光临黑马程序员技术交流社区 (http://bbs.itheima.com/)

黑马程序员IT技术论坛 X3.2