python自动更新excel_Python杀死了Excel｜自动更新表格 告别繁琐

大家好，又到了Python办公自动化专题。

今天我们讲解的案例是如何使用Python自动更新Excel表格，简单来说就是每天都会对Excel中多个sheet进行更新，需要操作完后可以用程序完成第一张sheet 汇总表的更新，大概就是这样👇

当然实现这一功能可以使用VBA或者Excel中的其他操作，但是查了相关操作略显复杂，现在我们使用Python来完成，主要涉及以下操作：

os、glob模块处理文件

Pandas处理多个表格

openpyxl调整Excel样式

创建多个随机数据

为了尽可能模拟读者的生产环境多了这一步。首先我们先用Python来创建一些随机数据，数据已经充分则可以跳到下一步骤。

我们需要把这个excel文件命名好放在桌面的data文件夹中

fromopenpyxlimportload_workbook

importos

importglob

importrandom

defGetDesktopPath():

returnos.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()foriinrange(97,123)]

#我忘记大写字母的码了哈哈哈这样变着法换大写字母

activity_lst=['椭圆机','篮球','足球','羽毛球','跳绳']

source_lst=['朋友介绍','微信聊天','网页弹窗','其他']

foriinrange(30):

#不断去拷贝第一页并重命名

sheet=workbook.copy_worksheet(sheet_init)

sheet.title=f'{i+1}日'

forjinrange(random.randint(10,30)):

#从第三行开始行遍历

forrowinsheet.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"])}']

#嵌套循环，对当前行的格子进行遍历把内容写入

forindex,kinenumerate(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=[]

foriinrange(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调整样式

调整样式部分我们直接看代码，关键部分都给了详细注释

#设置对齐、线性、边框、字体

fromopenpyxl.stylesimportAlignment

fromopenpyxl.stylesimportSide,Border

fromopenpyxl.stylesimportFont

sheet=workbook[sheetnames[0]]

sheet.insert_rows(idx=0)#插入第一行

font=Font(name='宋体',size=18,bold=True)

sheet['A1']='皮卡丘体育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}']

forrowinrows:

forcellinrow:

cell.alignment=alignment

cell.border=border

#设置前两行的行高

sheet.row_dimensions[1].height=38

sheet.row_dimensions[2].height=38

#设置列宽

letter_lst=[chr(i+64).upper()foriinrange(2,ord(weight)-ord('A')+1+1)]

sheet.column_dimensions['A'].width=8

foriinletter_lst:

sheet.column_dimensions[f'{i}'].width=14

workbook.save(filename=f'{GetDesktopPath()}/data/results.xlsx')

到这里，我们就成功使用Python实现自动更新Excel表格，并且调整样式，可能看上去有点复杂，但核心就是使用Pandas处理并使用openpyxl调整样式，并且相比于在Excel中实现，一个更大的优势就是一旦代码写完以后可以在有相关需求的Excel中直接使用，从而解放了双手。拜拜，我们下个案例见～

注:本文使用的数据与源码下载

链接:/s/139Nh59T7nPfJAW205QmDBw 密码:863v

来源：oschina

链接：/u/4390260/blog/4290356