python生成excel模板_实例9：用Python自动生成Excel档每日出货清单-CSDN博客

公司研发部门每年都需要向税务局提交一份出货清单，以申报研发费用。要求按日期来汇总，每日只要有出货，就需要一份出货单。出货总表包含数百条出货记录。假设一年有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软件高效工作