代码自动生成Excel表格
在实际的工作中,经常会遇到,把一个表信息 颠过来倒过去的情况。这会产生大量的重复工作。
比如说:发货表里头记录了 很多客户发货订单信息。但是我们给客户发货的时候,需要制作一张送货单,告诉客户 货物的详情。
这个时候如果代码可以搞定,就可以减少很多不必要的工作啦!
截图比较随意,大致就是这个意思了。
下面是代码,截图太恶心了 不理解需求的可以看下。
import xlwings as xw
def delivery_doc(l,src,dst,model): wb = xw.Book(r'%s'%src) ws = wb.sheets["2021年"] ws.activate i = l[0] order_name = ws.range('c%s'%i).value order_place = ws.range('f%s'%i).value order_id = ws.range('p%s'%i).value receiver = str(ws.range('g%s'%i).value)+""+str(int(ws.range('h%s'%i).value)) goods_name = ws.range('k%s'%i).value goods_model = ws.range('l%s'%i).value goods_amount = ws.range('m%s'%i).value wb1 = xw.Book(r"%s"%model) ws1 = wb1.sheets['牙膏'] ws1.range('c5').value = order_name ws1.range('g5').value = order_place ws1.range('c8').value = order_id ws1.range('g9').value = receiver ws1.range('b12').value = goods_name ws1.range('c12').value = goods_model ws1.range('d12').value = goods_amount for j in range(1,len(l)): i= l[j] order_name = ws.range('c%s'%i).value order_place = ws.range('f%s'%i).value order_id = ws.range('p%s'%i).value receiver = str(ws.range('g%s'%i).value)+""+str(int(ws.range('h%s'%i).value)) goods_name = ws.range('k%s'%i).value goods_model = ws.range('l%s'%i).value goods_amount = ws.range('m%s'%i).value if ws1.range('c5').value == order_name: if ws1.range('b12').value == goods_name: ws1.range('a%s'%r).value += goods_amount r = 12 + j ws1.api.Rows(r).Insert() ws1.range('a%s'%r).value = '%s'% (j+1) ws1.range('b%s'%r).value = goods_name ws1.range('c%s'%r).value = goods_model ws1.range('d%s'%r).value = goods_amount ws1.range('e%s'%r).value = '个' ws1.name = order_place wb1.save(r'%s\验收回执单-%s.xlsx'%(dst,order_place)) wb1.close() wb.close() l = [40,41,42] src = 'E:\\Yzh\\3.16 中烟\\中烟订单操作\\湖北中烟发货表-2021年.xlsx' dst = 'E:\\Yzh\\中烟\\5月 中烟\\5.13 中烟\\回执' model = "E:\\Yzh\\中烟\\5月 中烟\\5.13 中烟\\回执\\验收回执单-南平 牙膏.xlsx" delivery_doc(l,src,dst,model)
这里提炼几个xlwings 的常用方法:
# 插入一行 第十三行 ws1.api.Rows(13).Insert() # 删除整行 ws1.range('a12').api.EntireRow.Delete() # 复制sheet 页 ws1.api.Copy(After=ws1.api)
更新一下,增加一下判断条件
order_name不同时,另起一张回执
order_name == ‘待补单’时,待补单空白,有多少个产品,起多少张回执
order_name 相同,但是 order_place 不同时,另起一张回执
order_name 相同,order_place 相同,但是order_id 不同时,另起一张回执
import xlwings as xw import time l = range(681,688) src = 'E:\\发货表-2021年.xlsx' dst = 'E:\\回执' model = "E:\\回执\\model.xlsx" wb = xw.Book(r'%s' %src) ws = wb.sheets["2021年"] ws.activate wb1 = xw.books.open(r"%s"%model) model_sheet = wb1.sheets["model"] dict = {} for i in l: order_name = ws.range('c%s'%i).value order_place = ws.range('f%s'%i).value order_id = ws.range('p%s'%i).value + "-1" receiver = ws.range('g%s'%i).value + ws.range('h%s'%i).value goods_name = ws.range('k%s'%i).value goods_model = ws.range('l%s'%i).value goods_amount = ws.range('m%s'%i).value if order_name not in dict: model_sheet.api.Copy(After = model_sheet.api) ws1 = wb1.sheets['model (2)']
if order_name == '待补单':
ws1.range('c5').value = ''
else: ws1.range('c5').value = order_name ws1.range('g5').value = order_place ws1.range('c8').value = order_id ws1.range('g9').value = receiver ws1.range('b12').value = goods_name ws1.range('c12').value = goods_model ws1.range('d12').value = goods_amount ws1.name = order_name + order_place dict[order_name] = [order_place] else: if order_name == '待补单': model_sheet.api.Copy(After = model_sheet.api) ws1 = wb1.sheets['model (2)'] ws1.range('c5').value = ‘’ ws1.range('g5').value = order_place ws1.range('c8').value = order_id ws1.range('g9').value = receiver ws1.range('b12').value = goods_name ws1.range('c12').value = goods_model ws1.range('d12').value = goods_amount ws1.name = order_name + order_place+str(len(dict[order_name])) dict[order_name].append(order_place) else: if order_place not in dict[order_name]: model_sheet.api.Copy(After = model_sheet.api) ws1 = wb1.sheets['model (2)'] ws1.range('c5').value = order_name ws1.range('g5').value = order_place ws1.range('c8').value = order_id ws1.range('g9').value = receiver ws1.range('b12').value = goods_name ws1.range('c12').value = goods_model ws1.range('d12').value = goods_amount ws1.name = order_name + order_place dict[order_name].append(order_place) else: j = dict[order_name].count(order_place) r = 12 + j ws1.api.Rows(r).Insert() ws1.range('a%s'%r).value = '%s'% (j+1) ws1.range('b%s'%r).value = goods_name ws1.range('c%s'%r).value = goods_model ws1.range('d%s'%r).value = goods_amount ws1.range('e%s'%r).value = '个' dict[order_name].append(order_place) time_now = time.strftime("%Y%m%d %H%M%S",time.localtime()) model_sheet.delete() wb1.save(r'%s\验收回执单 %s.xlsx'%(dst,time_now)) wb1.close() wb.close()
order_name | order_place | order_id | receiver | goods_name | goods_model | goods_amount | cellphone | address | ||
HD_20210511 | shenzhen | FH20210511 | zhangsan | apple | apple 11 | 1 | 1234567 | -- | ||
HD_20210511 | guangzhou | FH20210511 | lisi | lenovo | lenevo plus | 1 | 1234567 | --- |