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