Python自动化办公-让 Excel 飞起来
Python自动化办公-让 Excel 飞起来
Python 操作 Excel 可能是自动化办公最火热的需求了，看一看公众号文章底部的视频广告就知道了，里面尽是一些 5 分钟搞定 excel，将数据生成漂亮的图表。
5 分钟虽然有些夸张，但是快速操作 Excel 的需求确是真的。今天就来分享如何使用 Python 玩转 Excel。
主要内容：
- Python 操作 Excel 的轮子对比
- xlwings 读 Excel
- xlwings 写 Excel
- Excel 中插入图表
- Excel 转 Pdf
- Excel 拆分与合并
- Excel 转 REST API
Python 操作 Excel 的轮子
Python 操作 Excel 的轮子有很多，导致选择困难症，为了帮你做选择，我这里放个对比图：
从上面的表格中可以看出，xlwings 是唯一一个全部都支持️的，是 Python 最强大的处理 Excel 的库，今天的主角就是它，它有以下优点
- 1、Windows、Mac 都能用，Excel、WPS 也都能用。
- 2、功能齐全，支持 Excel 的新建、打开、修改、保存，pandas、xlsxwriter 不能全做到。
- 3、语法简单，用过一次后我就记住了。
- 4、可以调用 VBA，有丰富的 API。
- 5、可以与 pandas 等类库集成使用。
xlwings 安装
pip install xlwings
需要注意的是，请使用操作系统自带的终端来运行 xlwings 程序，否则可能遇到权限不足的问题。
xlwings 读取 Excel
读取 excel 比较简单，比如现在有这样一个 excel：
先确定范围，这里是 A1 到 F4，多读一些也没关系，没有数据的格子会显示为 None。
import xlwings as xw
work_book = xw.Book('测试.xlsx')
sheet1 = work_book.sheets[0]
print(sheet1.book)
c = 0
for cell in sheet1.range('A1','E6'):
c += 1
print(cell.value, end ='\t')
if c % 5 == 0:
print("")
执行结果如下图所示：
假如无法预知数据的范围，可以使用 last_cell 方式获取最下边且最右边的一个单元格。
比如更好的方式是这样写：
import xlwings as xw
work_book = xw.Book('测试.xlsx')
sheet1 = work_book.sheets[0]
print(sheet1.book)
last_cell = sheet1.used_range.last_cell
last_row = last_cell.row
last_col = last_cell.column
c = 0
for cell in sheet1.range((1,1),(last_row, last_col)):
c += 1
print(cell.value, end ='\t')
if c % last_col == 0:
print("")
还有各种灵活的单元格访问方式：
# A1单元格
rng=sheet1['A1']
rng=sheet1['a1']
# A1:B5单元格
rng=sheet1['A1:B5']
# 第一行的第一列即a1
rng=sheet1[0,0]
# B1单元格
rng=sheet1[0,1]
在读取到每一行，每一列的数据之后，我们就可以对这些数据进行加工，然后写回 excel 了。
xlwings 写入 Excel
现在来实现一个小小的需求：针对上述读取的 Excel，我们现在来统计分数的总和及平均数，并写入 Excel 的最后行。
import xlwings as xw
work_book = xw.Book('测试.xlsx')
sheet1 = work_book.sheets[0]
print(sheet1.book)
#last_cell = sheet1.used_range.last_cell
last_row = 4
last_col = 5
## 获取分数的列索引
score_col_index = ""
for cell in sheet1.range((1,1),(1,last_col)):
if cell.value == '分数':
score_col_index = cell.column
## 将分数存入列表
score_list = []
for row in range(2,last_row+1):
cell = sheet1.range((row,score_col_index))
score_list.append(cell.value)
print(score_list)
sum_score = sum(score_list)
avg_score = sum(score_list) / len(score_list)
## 计算出结果后写入 excel
sheet1.range((last_row + 1,1)).value = "合计"
sheet1.range((last_row + 1,last_col)).value = sum_score
sheet1.range((last_row + 2,1)).value = "平均值"
sheet1.range((last_row + 2,last_col)).value = round(avg_score,2)
work_book.save()
work_book.close()
代码的逻辑非常简单，首先获取分数所在的列，然后将所有的分数取出来保存在列表中，对其求和，求平均值，然后写回 Excel 的最后一行。
Excel 中插入图表
生成图表在 Excel 也是很常见的需求，除了可以用 Excel 本身的图表之外，还可以借助 Python 来插入图表。
比如说现在有一个股票的数据，我们用 Python 生成该股票的走势图，并插入到 Excel 中。
这里借助了 pandas 库，使用前请 pip install pandas 安装一下。
import xlwings as xw
import pandas as pd
wb = xw.Book('300369.xlsx')
sheet1 = wb.sheets[0]
print(sheet1.range('A1:D3').value)
data_frame = sheet1.range('A1:D354').options(pd.DataFrame).value
data_frame.drop(columns = ["股票代码","名称"],inplace = True)
print(data_frame.head())
ax = data_frame.plot()
fig = ax.get_figure()
sheet1.pictures.add(fig, name = '绿盟科技', update = True)
wb.save()
最终的效果如下：
Excel 转 Pdf
将一个工作簿转换为 Pdf 非常简单，一行代码就可以搞定：
import xlwings as xw
wb = xw.Book('300369.xlsx')
sheet1 = wb.sheets[0]
sheet1.to_pdf(path= '300369.pdf')
拆分与合并
现在，我们来解决这个问题：如何快速地批量处理内容相似的 Excel？
批量拆分： 假设你是公司的财务人员，你需要使用 Excel 对员工工资进行核算，之后再打印出来。但是公司要求员工薪水保密，所以每个员工的工资需要拆分成一个独立的文件，最后再转成 pdf 通过邮件发送出去。
excel 内容大致如下：
拆分后：
代码如下：
import xlwings as xw
work_book = xw.Book('excel拆分练习.xlsx')
sheet1 = work_book.sheets[0]
print(sheet1.book)
last_cell = sheet1.used_range.last_cell
last_row = last_cell.row
last_col = last_cell.column
"""
定义缓存
"""
head_titles = []
rows_content = []
for i in range(last_row - 1):
rows_content.append([])
"""
读取 excel 内容至缓存
"""
for cell in sheet1.range((1,1),(1, last_col)):
head_titles.append(cell.value)
col_index = 0
row_index = 0
for cell in sheet1.range((2,1),(last_row, last_col)):
rows_content[row_index].append(cell.value)
col_index += 1
if col_index % last_col == 0:
row_index += 1
col_index = 0
"""
将缓存写入 excel
"""
# 遍历 rows_content
for index, row in enumerate(rows_content):
work_book = xw.Book()
sheet1 = work_book.sheets[0]
for col_index, col in enumerate(row):
sheet1.range((1,col_index + 1)).value = head_titles[col_index]
sheet1.range((2,col_index + 1)).value = col
work_book.save(f'{row[0]}.xlsx')
work_book.close()
批量合并。假设你需要对某些工作内容进行问卷调查，这时你用 Excel 做了调查问卷模版。我想你会这样做：先把 Excel 通过工作群分发给所有员工，再把群里收集到的反馈附件汇总成一个文件。
现在你可以仿照上面拆分的方法来进行批量合并。
将 excel 内容转为 REST API
执行：
xlwings restapi run -host 0.0.0.0 -p 5000
就可以将已打开的 excel 文件内容转换为 REST API 接口：
然后就可以远程访问：
最后的话
xlwings 很强大，本文抛砖引玉，如果经常使用的话，还是到官方文档[1]去获取更多方法。
另外本文的代码及样例文件：https://gitee.com/somenzz/code-example/tree/master/excel[2]。
都看到这里来了，说明你也是个爱学习的人，点赞在看支持一下吧，如果还没关注的话，可以关注一下，顺手学个 Python 实用技巧。感谢关注。
留言讨论
参考资料
[1]
官方文档: https://docs.xlwings.org/en/stable/
[2]
https://gitee.com/somenzz/code-example/tree/master/excel: https://gitee.com/somenzz/code-example/tree/master/excel