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’)
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200813145438348.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTA3OTc3,size_16,color_FFFFFF,t_70#pic_center)
注意以上代码要单独运行先生成数据,再运行后续代码,OK现在数据就创建好了,然后正式进入我们的问题
## 合并多个sheet并写入汇总sheet
由于后面多个表的更新后需要按日期顺序在汇总表里呈现,因此有一个策略是利用openpyxl按顺序遍历各表然后写回汇总表。但注意,表格中存在边框、居中等样式修改
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200813145455143.png#pic_center)
这种情况下,openpyxl会识别样式,认为这些行是已经有数据的,故纯粹的sheet.append()方法是无法将数据写入这些所谓的空行,而会从没有样式的行开始写入
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200813145505562.png#pic_center)
所以需要在各表写入的时候**不断计算所在行**,并利用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
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200813145607291.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTA3OTc3,size_16,color_FFFFFF,t_70#pic_center)
将生成的表写回汇总表即可,涉及的内容稍微比较复杂。由于直接使用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’)
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200813145627733.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTA3OTc3,size_16,color_FFFFFF,t_70#pic_center)
这就完成了吗?没有。
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’)
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200813145701823.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTA3OTc3,size_16,color_FFFFFF,t_70#pic_center)
到这里,我们就成功使用Python实现自动更新Excel表格,并且调整样式,可能看上去有点复杂,但核心就是使用Pandas处理并使用openpyxl调整样式,并且相比于在Excel中实现,一个更大的优势就是一旦代码写完以后可以在有相关需求的Excel中直接使用,从而解放了双手。
参考链接 ;
Python办公自动化(六)|自动更新表格 : https://mp.weixin.qq.com/s/QhTJ6g0JvQrMdFwc3IGqxw