Python办公自动化-自动更新汇总表

主要涉及以下操作：

os、glob模块处理文件

Pandas处理多个表格

openpyxl调整Excel样式

首先把这个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

由于后面多个表的更新后需要按日期顺序在汇总表里呈现，因此有一个策略是利用openpyxl按顺序遍历各表然后写回汇总表。但注意，表格中存在边框、居中等样式修改

这种情况下，openpyxl会识别样式，认为这些行是已经有数据的，故纯粹的sheet.append()方法是无法将数据写入这些所谓的空行，而会从没有样式的行开始写入

所以需要在各表写入的时候不断计算所在行，并利用sheet.iter_rows()定位。是不是有点麻烦？因此我们换个思路：利用pandas，其方便的地方在于无视表格样式

将生成的表写回汇总表即可，涉及的内容稍微比较复杂。由于直接使用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对第一页的样式调整。

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

# 设置对齐、线性、边框、字体
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'] = '汇总表'
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 = 36
sheet.row_dimensions[2].height = 36

# 设置列宽
letter_lst = [chr(i+64).upper() for i in range(2, ord(weight)-ord('A')+1+1)]
sheet.column_dimensions['A'].width = 10
for i in letter_lst:
     sheet.column_dimensions[f'{i}'].width = 16

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

这样我们就成功使用Python实现自动更新Excel汇总表，并且调整样式，一旦代码写完以后可以在有相关需求的Excel中直接使用，从而解放了双手。

关注公众号，每天可以领红包