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 的轮子有很多,导致选择困难症,为了帮你做选择,我这里放个对比图:

Python自动化办公-让 Excel 飞起来_第1张图片

从上面的表格中可以看出,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:

Python自动化办公-让 Excel 飞起来_第2张图片

先确定范围,这里是 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("")

执行结果如下图所示:

Python自动化办公-让 Excel 飞起来_第3张图片

假如无法预知数据的范围,可以使用 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 中。

Python自动化办公-让 Excel 飞起来_第4张图片

这里借助了 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()

最终的效果如下:

Python自动化办公-让 Excel 飞起来_第5张图片

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 内容大致如下:

Python自动化办公-让 Excel 飞起来_第6张图片

拆分后:

Python自动化办公-让 Excel 飞起来_第7张图片

代码如下:

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 接口:

Python自动化办公-让 Excel 飞起来_第8张图片

然后就可以远程访问:

Python自动化办公-让 Excel 飞起来_第9张图片

最后的话

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

你可能感兴趣的