对于公司产品的生产,每天都需要领取物料,那每天都会有一张领料单。去年共有230来天领取了物料,则一共有230多张领料单。现在需要将这230多张表合为1张表。在Excel中,只能用VBA写个小程序才能搞定了。还是用更简单的Python来完成吧。
import time
t0=time.time()# 程序开始运行的时间
from openpyxl import load_workbook
wb = load_workbook("data每日领料单.xlsx")
sheet_names=wb.get_sheet_names() #获得工作簿的所有工作表名
data=[] #用于储存抓取的数据
for sheet_name in sheet_names: #遍历每个工作表,抓取数据
ws=wb[sheet_name]
#读取各个Excel中的数据,并存入列表
for row in range(4, ws.max_row-3): #从第4行开始至倒数第5行遍历工作表每一行,将数据提取出来
factory = ws['A' + str(row)].value #工厂
move_order= ws['B' + str(row)].value #挑库单号
line = None #没有数据
qty= ws['D' + str(row)].value #请求数量
work_order= ws['E' + str(row)].value #工单号
work_center="QQ10"
status="完成"
sub= ws['H' + str(row)].value #库位
unit = ws['I' + str(row)].value #单位
seq = ws['J' + str(row)].value #项次
material_pn = ws['K' + str(row)].value #物料编号
locator = ws['L' + str(row)].value #架位
lot_number = ws['M' + str(row)].value #批次号
lot_qty = ws['N' + str(row)].value #数量
FG_model = ws['O' + str(row)].value #成品机型号
material_desp = ws['P' + str(row)].value #物料描述
pick_date = ws['C' + str(ws.max_row)].value.date() #日期
info_list=[factory, move_order,line,qty,work_order,work_center,status,sub,unit,seq,material_pn,locator,
lot_number,lot_qty,FG_model,material_desp,pick_date] #将以上信息放入列表info_list
data.append(info_list) #将单个物料信息的列表放入包含所有物料的大列表
t1=time.time() #程序结束运行的时间
print('程序用时:',str(round(t1-t0))+'秒。')
>>程序用时: 58秒。
我发现使用%%time
偶尔会使得程序运行出现问题,但又不会报错。为了继续观测程序会用多长时间,这里导入了time
模块。在程序开始之前定义一个时间t0
,再在程序结束后定义一个时间t1
,这两个时间都是当时Windows系统的时间。用结束时间减去开始时间,即可得到程序运行的时间。
上述程序还是使用老朋友,openpyxl
库。先打开Excel工作簿,然后通过sheet_names=wb.get_sheet_names()
获取所有工作表的名称,随后遍历所有工作表,将其中每行的数据取出,存入列表info_list
,然后再存入大列表data
。程序1分钟就运行完成,然后检查数据,len(data)
看看有多少行数据。再选取最有一条数据data[34155]
,与工作表中的对比一下,完全正确。
len(data)
>>34156
data[34155]
>>
['AAA',
1118610,
None,
'150',
'A19X9531866',
'QQ10',
'完成',
'WH-B',
'EA',
2,
'10030XR0081100',
'LC034156',
'PC34156',
'150',
'A34156',
'电池门',
datetime.date(2019, 12, 28)]
然后就可以写入总表进行汇总了。
#汇总数据到主文件
t0=time.time()# 程序开始运行的时间
from openpyxl.styles import Border, Side, PatternFill, Font, GradientFill, Alignment
thin = Side(border_style="thin", color="000000")#定义边框粗细及颜色
wb_main=load_workbook("data领料表.xlsx") #打开需要写入数据的文件
ws_main=wb_main.active #选取需要写入数据的工作表
for row in range(4,len(data)+4):
for col in range(1,18):
ws_main.cell(row=row,column=col,value=data[row-4][col-1])
#设置字号,对齐,缩小字体填充,加边框
#Font(bold=True)可加粗字体
for row_number in range(4, ws_main.max_row+1):
for col_number in range(1,18):
c = ws_main.cell(row=row_number,column=col_number)
c.font = Font(size=8) #字号设为8
c.border = Border(top=thin, left=thin, right=thin, bottom=thin)#加全边框
c.alignment = Alignment(horizontal="left", vertical="center",shrink_to_fit = True)
#水平方向左对齐,垂直方向居中对齐,字太多单元格太小的话缩小字体填充单元格
wb_main.save("data合并.xlsx") #保存数据
t1=time.time() #程序结束运行的时间
print('程序用时:',str(round(t1-t0))+'秒。')
程序用时: 295秒。
用时差不多5分钟,毕竟3万多行数据,还是蛮快的了。以上,先打开模板“领料单”,然后选取其活动工作表,从第四行开始写入数据,列则是从第1列到17列(for col in range(1,18)
)。数据写完之后,设置字号,边框及对齐方式。其中,shrink_to_fit = True
指如果字太多,单元格太小的话,缩小字体填充。也可以用wrap_text = True
,指Excel中的自动换行。结果如下:
如果您有需要处理的问题,可发邮件到我邮箱:donyo@qq.com,一起探讨解决方案。
以上在Jupyter notebook上完成,所用到的代码及Excel 资料已上传GitHub及百度网盘, 欢迎下载到本地随意玩。
Python版本:Python 3.6 64bit
操作系统:Windows 7
GitHub:Office_Automation_by_Using_Python
百度Pan:http://pan.baidu.com/s/1WXcoYts_uNJmccfJ0lrmWg 提取码: kry7
http://weixin.qq.com/r/_S4HHzPEbm4RrXd893vr (二维码自动识别)