专栏首页Python七号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

本文分享自微信公众号 - Python七号(PythonSeven),作者:somenzz

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2021-08-17

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Python办公自动化|从Word到Excel

    大家好,今天有一个公务员的小伙伴委托我给他帮个忙,大概是有这样一份Word(由于涉及文件私密所以文中的具体内容已做修改)

    刘早起
  • Python办公自动化|从Excel到Word

    在前几天的文章中我们讲解了如何从Word表格中提取指定数据并按照格式保存到Excel中,今天我们将再次以一位读者提出的真实需求来讲解如何使用Python从Exc...

    刘早起
  • Python控制Excel实现自动化办公!

    python学习教程
  • Python办公自动化| word 表格转excel

    之前写过一篇 Python办公自动化 | 批量word报告生成工具 ,有小伙伴提出了逆向需求,即:从批量word中获取内容并写入excel,需求背景是汇总一些材...

    披头
  • Python自动化办公系列之Python操作Excel

    ③ 在python中使用excel函数公式(很有用)         

    磐创AI
  • Python办公自动化 | excel读取和写入

    python在办公自动化领域应用广泛,本文学习一下如何使用python读取和写入excel。EXCEL读取和写入可以用到两个包:xlrd 和 xlwt。

    披头
  • Python办公自动化|批量提取Excel数据

    今天我们来讲解一个比较简单的案例,使用openpyxl从Excel中提取指定的数据并生成新的文件,之后进一步批量自动化实现这个功能,通过本例可以学到的知识点:

    刘早起
  • Python自动化办公之Excel报表自动化指南!全文3W字

    来源:https://blog.csdn.net/u014779536/article/details/108182833

    统计学家
  • python自动化办公——python操作Excel、Word、PDF集合大全

    本文是鉴于有些粉丝的工作需求,有时候需要遇到这些文件的处理。因此,我写了一个文章集合,供大家参考,整篇文章已经整理成册(如下图所示)。由于文档获取人数太多,大家...

    用户7886150
  • 最全总结 | 聊聊 Python 办公自动化之 Excel(下)

    前面谈到 Python 处理 Excel 文件最常见的两种方式,即:xlrd/xlwt、openpyxl

    AirPython
  • 最全总结 | 聊聊 Python 办公自动化之 Excel(上)

    但是,经常会遇到一些重复繁琐的事情,这时候手工操作显得效率极其低下;通过 Python 实现办公自动化变的很有必要

    AirPython
  • 最全总结 | 聊聊 Python 办公自动化之 Excel(中)

    上一篇文章中,我们聊到使用 xlrd、xlwt、xlutils 这一组合操作 Excel 的方法

    AirPython
  • 驱使Python蟒蛇为自己工作

    "飞碟瓜,最近战事紧张,多个植物花园发生了激烈的战斗,麻烦你统计一下上个月的战斗成果,看一下植物战士们的战斗力有没有提高。今天晚上向我汇报。“火炬树桩交待了这个...

    张俊红
  • Python 自动化办公-玩转 PPT

    如果你有一堆 PPT 要做,他们的格式是一样的,只是填充的内容不一样,那你就可以使用 Python 来减轻你的负担。

    somenzz
  • Python 自动化办公-玩转 Word

    只要是简单重复的工作,就想办法用 Python 来帮你解决吧,人生苦短,你需要 Python。

    somenzz
  • Python办公自动化|光速对比并提取两份Word/Excel中的不同元素

    如果你经常与Excel或Word打交道,那么从两份表格/文档中找到不一样的元素是一件让人很头疼的工作,当然网上有很多方法、第三方软件教你如何对比两份文件。本文就...

    刘早起
  • 自动化办公:python操作Excel5.Excel中添加数据图表

    大牧莫邪
  • Python自动化办公之Word批量转成自定义格式的Excel

    python实现word转成自定义格式的excel文档(解决思路和代码)支持按照文件夹去批量处理,也可以单独一个文件进行处理,并且可以自定义标识符。

    Python小二
  • Python办公自动化|批量合并PDF,拿来就用

    大家好,今天分享一个实用的办公脚本:将多个PDF合并为一个PDF,例如我手上现在有如下3个PDF分册,需要整合成一个完整的PDF

    刘早起

扫码关注云+社区

领取腾讯云代金券