公司研发部门每年都需要向税务局提交一份出货清单,以申报研发费用。要求按日期来汇总,每日只要有出货,就需要一份出货单。出货总表包含数百条出货记录。假设一年有200天都出过货,那按照常规的方法,需要从总表中复制每天的出货记录,然后粘贴到每日出货清单里面,重复200次。效率低下不说,还容易出错。
下面我们就让Python来代劳,一次写码,终身受益,呲牙......
总表和模板分别长这样的:
#1.从总表中提取信息
from openpyxl import load_workbook
wb = load_workbook("data总表.xlsx")
ws= wb['Sheet']
data = {} #用于储存提取的信息
for row in range(2, ws.max_row+1): #从第2行开始(第1行是标题)遍历工作表每一行,将数据提取出来
customer = ws['B' + str(row)].value #B列为客户信息
model= ws['C' + str(row)].value #C列为型号
PN= ws['F' + str(row)].value #F列为零件号
qty= ws['G' + str(row)].value #G列为数量
date = ws['D' + str(row)].value.date() #D列为日期时间,因只要日期,不要时间,所以用date()只提取日期
info_list=[customer,model,PN,qty] #将以上信息放入列表info_list
data.setdefault(date,[]) #data字典将以日期date作为键,当天的所有产品信息组成的列表嵌套列表作为值
data[date].append(info_list) #将单个产品信息的列表放入包含所有产品的大列表
我们先从总表中提取信息,放到一个字典里面,方便写入Excel工作表时调用。导入openpyxl
模块,用于打开并读取总表里面的信息。使用load_workbook
打开总表,存入变量wb
,然后选择工作表Sheet
,存入变量ws
。建立空字典data
,用于存储数据。一开始,我们需要构思好数据结构。由于我们需要将每天的所有出货项目填入一页表,所以要用日期作为字典的键。字典的值就是产品的4个信息,即客户、型号、零件号和数量。有时候,一天只有一条出货信息,但有时有两个即以上,所以我们需要使用嵌套列表来作为字典的值。
然后我们使用for
循环遍历总表,从第二行开始直到最后一行。因为range(a,b)是取不到b的,所以需要ws.max_row+1。每读取一行,我们就将客户信息,型号,零件号和数量存入info_list里面。data.setdefault(date,[])
是用于将日期作为键,且在遍历到具有相同日期的产品信息的时候,不覆盖原来的键(日期),而是将其值添加到后面的空列表内,即嵌套列表。
数据搜集完成后,我们可以打印其键值对,以便观察是否是我们想要的。我们可以看到这正是我们要的结果,键就是日期,值就是当天出货的所有的产品信息的嵌套列表。
for key,value in data.items(): #打印键值对,以便观察
print(key,value)
>>
2017-03-15 [['客户4', 'XYZ0069', 'QP006UUU00', 6]]
2017-03-21 [['客户1', 'XYZ0038', 'XW009UUU00', 80]]
2017-03-27 [['客户8', 'XYZ0043', 'PQ016UUU00', 22]]
2017-04-03 [['客户3', 'XYZ0022', 'EP002UUU00', 9], ['客户3', 'XYZ0022', 'EP002UUU00', 4]]
2017-04-08 [['客户7', 'XYZ0027', 'PUUU072UUU00', 11]]
2017-05-05 [['客户7', 'XYZ0002', 'PUUU210UUU00', 6], ['客户7', 'XYZ0002', 'PUUU210UUU00', 8], ['客户7', 'XYZ0027', 'PUUU072UUU00', 114]]
2017-05-06 [['客户7', 'XYZ0027', 'PUUU072UUU00', 70]]
2017-05-10 [['客户11', 'XYZ0024', 'ST001UUU00', 140]]
2017-05-15 [['客户3', 'XYZ0021', 'EP002UUU00', 360]]
2017-05-19 [['客户5', 'XYZ0047', 'QE003UUU00', 8], ['客户5', 'XYZ0047', 'QE003UUU00', 11]]
2017-05-24 [['客户12', 'XYZ0054', 'SQ149UUU00', 2], ['客户12', 'XYZ0049', 'SQ148UUU00', 21], ['客户12', 'XYZ0051', 'SQ148UUU00', 29], ['客户12', 'XYZ0050', 'SQ148UUU00', 29], ['客户12', 'XYZ0048', 'SQ148UUU00', 20], ['客户12', 'XYZ0052', 'SQ148UUU00', 21], ['客户12', 'XYZ0053', 'SQ148UUU00', 21], ['客户13', 'XYZ0072', 'TS057UUU00', 20]]
-----略-----
数据获取完成后,就可以开始创建并写入每日出货清单了。
#2.将提取的信息按日期写入新建的Excel表
wb_day = load_workbook("data出货单模板.xlsx")
ws_day= wb_day['temp']
for date in data.keys():
ws_new = wb_day.copy_worksheet(ws_day) #复制模板中的工作表
ws_new.title=str(date)[-5:] #以日期为新工作表命名
ws_new.cell(row=3,column=5).value=date #E3单元格固定填写日期
i=5 #计数器,从第5行开始填写起始值为5
for product in data[date]: #获取每天出货的每个产品信息,逐个写入工作表
ws_new.cell(row=i,column=2).value=product[0]
ws_new.cell(row=i,column=3).value=product[1]
ws_new.cell(row=i,column=4).value=product[2]
ws_new.cell(row=i,column=5).value=product[3]
i+=1 #每写一行,计数器就需要加1,以便从下一行接着写入
wb_day.save("data出货单.xlsx")
我们事先设置好了一个产品信息为空的Excel模板,随后让程序将每天的出货信息填入即可。先打开模板“出货单模板.xlsx”,然后获取其工作表“temp”,以便后续复制并写入数据。我们建立一个for
循环,遍历字典data
里面的所有的键(即日期)。使用copy_worksheet
复制模板工作表,存入ws_new
,并将其标题重命名为日期ws_new.title
。E3单元格是填入固定的值,即日期,所以直接赋值为date
。因为每天可能有2个及以上的出货信息,那就需要在出货清单中填写几行信息,所以需要设置一个行计数器i,其初始值为5,因为出货清单是从第5行开始的。每填完一行信息,计数器就加1i+=1
,然后就可以填写下一行了。
所有信息填写完后,就保存数据wb_day.save("data出货单.xlsx")
,任务完成。几百个工作表瞬间填完,结果如下图:
所有源代码和说明都在Jupyter notebook上完成,所用到的Excel 资料已上传GitHub, 欢迎Fork或下载到本地随意玩。。。转载请注明出处,谢谢。GitHub链接:https://github.com/weidylan/Office_Automation_by_Using_Python微信公众号:Python操作Office软件高效工作