大家好,又到了 Python 办公自动化专题。
今天我们讲解的案例是如何使用 Python 自动更新 Excel 表格,简单来说就是 每天都会对 Excel 中多个 sheet 进行更新,需要操作完后可以用程序完成第一张 sheet 汇总表的更新 ,大概就是这样👇
当然实现这一功能可以使用 VBA 或者 Excel 中的其他操作,但是查了相关操作略显复杂,现在我们使用 Python 来完成,主要涉及以下操作:
-
os、glob
模块处理文件 -
Pandas
处理多个表格 -
openpyxl
调整 Excel 样式
创建多个随机数据
为了尽可能模拟读者的生产环境多了这一步。首先我们先用 Python 来创建一些随机数据,数据已经充分则可以跳到下一步骤。
我们需要把这个 excel 文件命名好放在桌面的 data 文件夹中
from openpyxl import load_workbook
import os
import glob
import random
def GetDesktopPath():
return os.path.join(os.path.expanduser("~"), 'Desktop')
# 调用glob可以利用通配符获取指定命名格式的文件
path = glob.glob(f'{GetDesktopPath()}/data/*.xls*')[0]
workbook = load_workbook(filename=path)
sheet_init = workbook.active
接着是创建一些供随机的内容,可以随意写,我们还是皮卡丘化
name_lst = ['皮卡丘', '小火龙', '杰尼龟', '妙蛙种子', '风速狗', '小拳石', '飞天螳螂']
place_lst = [chr(i).upper() for i in range(97, 123)]
# 我忘记大写字母的码了哈哈哈 这样变着法换大写字母
activity_lst = ['椭圆机', '篮球', '足球', '羽毛球', '跳绳']
source_lst = ['朋友介绍', '微信聊天', '网页弹窗', '其他']
for i in range(30):
# 不断去拷贝第一页并重命名
sheet = workbook.copy_worksheet(sheet_init)
sheet.title = f'{i+1}日'
for j in range(random.randint(10, 30)):
# 从第三行开始行遍历
for row in sheet.iter_rows(min_row=3+j, max_row=3+j):
info = [f'{j+1}', f'{i+1}日', f'{random.choice(name_lst)}', f'{random.choice(place_lst)}馆',
f'{random.choice(activity_lst)}', f'{random.choice(source_lst)}', f'{random.randint(1, 10)}',
'无', f'{random.choice(["Y", "N"])}', f'{random.choice(["Y", "N"])}', f'{random.choice(["Y", "N"])}']
# 嵌套循环,对当前行的格子进行遍历把内容写入
for index, k in enumerate(info):
row[index].value = k
print(f'第{i+1}日已完成')
workbook.save(filename=f'{GetDesktopPath()}/data/results.xlsx')
注意以上代码要单独运行先生成数据,再运行后续代码,OK 现在数据就创建好了,然后正式进入我们的问题
合并多个 sheet 并写入汇总 sheet
由于后面多个表的更新后需要按日期顺序在汇总表里呈现,因此有一个策略是利用openpyxl
按顺序遍历各表然后写回汇总表。但注意,表格中存在边框、居中等样式修改
这种情况下,openpyxl
会识别样式,认为这些行是已经有数据的,故纯粹的sheet.append()
方法是无法将数据写入这些所谓的空行,而会从没有样式的行开始写入
所以需要在各表写入的时候不断计算所在行,并利用sheet.iter_rows()
定位。是不是有点麻烦?因此我们换个思路:利用pandas
,其方便的地方在于无视表格样式
path_new = glob.glob(f'{GetDesktopPath()}/data/results.xls*')[0]
workbook_new = load_workbook(filename=path_new)
# 方便获取总表数便于遍历
sheetnames = workbook.sheetnames
df_lst = []
for i in range(1, len(sheetnames)):
df = pd.read_excel(path_new , encoding='utf-8', sheet_name=i, skiprows=1)
df_lst.append(df)
# 把获取的各表纵向合并,注意纵向合并常常需要重置索引
df_total = pd.concat(df_lst,axis=0,ignore_index=True)
# 索引是从0开始,利用索引+1重置各记录的编号
df_total['编号'] = df_total.index + 1
将生成的表写回汇总表即可,涉及的内容稍微比较复杂。由于直接使用dataframe.to_excel
会覆盖原 excel 导致只有一张 sheet,其他全部丢失,需要利用pd.ExcelWriter
,具体见代码。删除原来的汇总表并写入新的汇总表。因为新写入的 sheet 会置于末尾,可以用list.insert(0, list.pop())
将最后一个元素置于开头
writer = pd.ExcelWriter(path_new, engine='openpyxl')
writer.book = workbook
workbook.remove(workbook['汇总表'])
df_total.to_excel(excel_writer=writer, sheet_name=u'汇总表', index=None)
writer.close()
workbook._sheets.insert(0, workbook._sheets.pop())
workbook.save(filename=f'{GetDesktopPath()}/data/results.xlsx')
这就完成了吗?没有。
pandas
的优势 “无视样式” 也成为了它的缺陷:写入文件时没有样式信息,因此最后再用openpyxl
对第一页的样式调整。
openpyxl 调整样式
调整样式部分我们直接看代码,关键部分都给了详细注释
# 设置对齐、线性、边框、字体
from openpyxl.styles import Alignment
from openpyxl.styles import Side, Border
from openpyxl.styles import Font
sheet = workbook[sheetnames[0]]
sheet.insert_rows(idx=0) # 插入第一行
font = Font(name='宋体', size=18, bold=True)
sheet['A1'] = '皮卡丘体育2020年06月新学员信息登记表'
sheet['A1'].font = font # 设置字体大小和加粗
req = ':(\w)'
weight = re.findall(req, sheet.dimensions)[0]
sheet.merge_cells(f'A1:{weight}1')
# 样式先准备好
alignment = Alignment(horizontal='center', vertical='center')
side = Side(style='thin', color='000000')
border = Border(left=side, right=side, top=side, bottom=side)
# 遍历cell设置样式
rows = sheet[f'{sheet.dimensions}']
for row in rows:
for cell in row:
cell.alignment = alignment
cell.border = border
# 设置前两行的行高
sheet.row_dimensions[1].height = 38
sheet.row_dimensions[2].height = 38
# 设置列宽
letter_lst = [chr(i+64).upper() for i in range(2, ord(weight)-ord('A')+1+1)]
sheet.column_dimensions['A'].width = 8
for i in letter_lst:
sheet.column_dimensions[f'{i}'].width = 14
workbook.save(filename=f'{GetDesktopPath()}/data/results.xlsx')
到这里,我们就成功使用 Python 实现自动更新 Excel 表格,并且调整样式,可能看上去有点复杂,但核心就是使用Pandas
处理并使用openpyxl
调整样式,并且相比于在 Excel 中实现,一个更大的优势就是一旦代码写完以后可以在有相关需求的 Excel 中直接使用,从而解放了双手。拜拜,我们下个案例见~
这里给大家分享一份Python全套学习资料,包括学习路线、软件、源码、视频、面试题等等,都是我自己学习时整理的,希望可以对正在学习或者想要学习Python的朋友有帮助!
CSDN大礼包:全网最全《全套Python学习资料》免费分享🎁
😝有需要的小伙伴,可以点击下方链接免费领取或者V扫描下方二维码免费领取🆓
1️⃣零基础入门
① 学习路线
对于从来没有接触过Python的同学,我们帮你准备了详细的学习成长路线图。可以说是最科学最系统的学习路线,你可以按照上面的知识点去找对应的学习资源,保证自己学得较为全面。
② 路线对应学习视频
还有很多适合0基础入门的学习视频,有了这些视频,轻轻松松上手Python~
③练习题
每节视频课后,都有对应的练习题哦,可以检验学习成果哈哈!
因篇幅有限,仅展示部分资料
2️⃣国内外Python书籍、文档
① 文档和书籍资料
3️⃣Python工具包+项目源码合集
①Python工具包
学习Python常用的开发软件都在这里了!每个都有详细的安装教程,保证你可以安装成功哦!
②Python实战案例
光学理论是没用的,要学会跟着一起敲代码,动手实操,才能将自己的所学运用到实际当中去,这时候可以搞点实战案例来学习。100+实战案例源码等你来拿!
③Python小游戏源码
如果觉得上面的实战案例有点枯燥,可以试试自己用Python编写小游戏,让你的学习过程中增添一点趣味!
4️⃣Python面试题
我们学会了Python之后,有了技能就可以出去找工作啦!下面这些面试题是都来自阿里、腾讯、字节等一线互联网大厂,并且有阿里大佬给出了权威的解答,刷完这一套面试资料相信大家都能找到满意的工作。
5️⃣Python兼职渠道
而且学会Python以后,还可以在各大兼职平台接单赚钱,各种兼职渠道+兼职注意事项+如何和客户沟通,我都整理成文档了。
上述所有资料 ⚡️ ,朋友们如果有需要 📦《全套Python学习资料》的,可以扫描下方二维码免费领取 🆓
😝有需要的小伙伴,可以点击下方链接免费领取或者V扫描下方二维码免费领取🆓