代码自动生成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 ---    

 

 

 

 

 

posted @ 2021-05-20 00:40  正在学Python  阅读(1060)  评论(0编辑  收藏  举报