使用python拼接报文

一、问题：造测试数据时，有时需要往队列中丢报文，如果报文格式复杂，手动拼接报文很容易出错

二、解决方法：
将报文模板放在testdata.xlsx文件中，执行脚本时会将模板拷贝到out.xlsx中，并更改字段值，生成新的报文

三、脚本

#!/usr/bin/python
#-*- coding:utf-8 -*-

from xlutils.copy import copy
from xlrd import open_workbook
import xlwt
import os.path
import pymysql

if os.path.exists("out.xlsx"):
    os.remove("out.xlsx")
  
#定义变量
zcount = ""
zxdh = ""
jhd = ""
sku = ""
dw = ""
num = ""
sap_num = ""
zxdh1 = ""
zxdh2 = ""
sku1 = ""
sku2 = ""
sku3 = ""
dw1 = ""
dw2 = ""
dw3 = ""
num1 = ""
num2 = ""
num3 = ""

#根据jhd在数据库中查询其他变量值	
def get_data(zct):
    if zct == "1":                          #当zct=1时   ，有一个zxdh                                             
        global jhd
        zxdh = input("zhuangxiangdanhao：\n")
        jhd = input("jiaohuodan：\n")
    elif zct == "2":                       #   当zct=2时，又二个zxdh            
        zxdh1 = input("zhuangxiangdanhao1：\n")
        zxdh2 = input("zhuangxiangdanhao2：\n")
        jhd = input("jiaohuodan：\n")
        
    #连接数据库
    conn = pymysql.connect(host='ip地址',port =端口号,user='用户名', password='密码',database= ‘数据库名称',charset='utf8')              
    cursor = conn.cursor()  
    command = "SQL脚本"
    print(command）
    cursor.execute(command)
    values = cursor.fetchall()
    print(values)

#赋值
    if zct == "1":
        sku = values[0][0]
        dw = values[0][1]
        num = int(values[0][2])
        sap_num = values[0][3]
    elif zct == "2":
        sku1 = values[0][0]
        sku2 = values[1][0]
        sku3 = values[2][0]
        dw1 =  values[0][1]
        dw2 =  values[1][1]
        dw3 =  values[2][1]
        num1 = int(values[0][2])
        num2 = int(values[1][2])
        num3 = int(values[2][2])
        sap_num = values[0][3]
		
#一个包裹
def one_package(ws1):
    ws1.write(8,1,"\""+zxdh+"\""+",")
    ws1.write(9,1,"\""+zxdh+"\""+",")
    ws1.write(12,1,"\""+zxdh+"\"")
    ws1.write(21,1,"\""+zxdh+"\""+",")
    ws1.write(22,1,"\""+zxdh+"\""+",")
    ws1.write(24,1 ,"\""+zxdh+"\""+",")
    ws1.write(39, 1, "\""+zxdh+"\""+",")
    ws1.write(11, 1, "\""+jhd+"\""+",")
    ws1.write(23, 1, "\""+jhd+"\""+",")
    ws1.write(37, 1, "\""+jhd+"\""+",")
    ws1.write(15, 1, "\""+sku+"\""+",")
    ws1.write(46, 1, "\""+sku+"\""+",")
    ws1.write(17, 1, "\""+dw+"\""+",")
    ws1.write(18, 1, "\""+num+"\""+",")
    ws1.write(45, 1, "\""+num+"\""+",")
    ws1.write(25, 1, "\""+sap_num+"\""+",")

def two_package(ws1):
    zxdh1 = input("装箱单号1：\n")
    zxdh2 = input("装箱单号2：\n")
    jhd = input("交货单：\n")
    sku1 = input("sku1:\n")
    sku2 = input("sku2:\n")
    sku3 = input("sku3:\n")
    dw1 = input("单位1：\n")
    dw2 = input("单位2：\n")
    dw3 = input("单位3：\n")
    num1 = input("数量1：\n")
    num2 = input("数量2：\n")
    num3 = input("数量3：\n")
    sap_num = input("sap单号：\n")
    ws1.write(8, 1, "\""+zxdh1+"\""+",")
    ws1.write(9, 1, "\""+zxdh1+"\""+",")
    ws1.write(12, 1, "\""+zxdh1+"\""+",")
    ws1.write(19, 1, "\""+zxdh2+"\""+",")
    ws1.write(20, 1, "\""+zxdh2+"\""+",")
    ws1.write(23, 1, "\""+zxdh2+"\""+",")
    ws1.write(33, 1, "\""+zxdh1+"\""+",")
    ws1.write(34, 1, "\""+zxdh1+"\""+",")
    ws1.write(36, 1, "\""+zxdh1+"\""+",")
    ws1.write(46, 1, "\""+zxdh1+"\""+",")
    ws1.write(47, 1, "\""+zxdh1+"\""+",")
    ws1.write(49, 1, "\""+zxdh1+"\""+",")
    ws1.write(59, 1, "\""+zxdh2+"\""+",")
    ws1.write(60, 1, "\""+zxdh2+"\""+",")
    ws1.write(62, 1, "\""+zxdh2+"\""+",")
    ws1.write(8, 5, "\""+zxdh1+"\""+",")
    ws1.write(31, 5, "\""+zxdh2+"\""+",")
    ws1.write(11, 1, "\""+jhd+"\""+",")
    ws1.write(22, 1, "\""+jhd+"\""+",")
    ws1.write(35, 1, "\""+jhd+"\""+",")
    ws1.write(48, 1, "\""+jhd+"\""+",")
    ws1.write(61, 1, "\""+jhd+"\""+",")
    ws1.write(6, 5, "\""+jhd+"\""+",")
    ws1.write(29, 5, "\""+jhd+"\""+",")
    ws1.write(6, 5, "\""+jhd+"\""+",")
    ws1.write(27, 1, "\""+sku1+"\""+",")
    ws1.write(40, 1, "\""+sku2+"\""+",")
    ws1.write(53, 1, "\""+sku3+"\""+",")
    ws1.write(14, 5, "\""+sku1+"\""+",")
    ws1.write(37, 5, "\""+sku2+"\""+",")
    ws1.write(46, 5, "\""+sku3+"\""+",")
    ws1.write(29, 1, "\""+dw1+"\""+",")
    ws1.write(42, 1, "\""+dw2+"\""+",")
    ws1.write(55, 1, "\""+dw3+"\""+",")
    ws1.write(30, 1, "\""+num1+"\""+",")
    ws1.write(43, 1, "\""+num2+"\""+",")
    ws1.write(56, 1, "\""+num3+"\""+",")
    ws1.write(14, 5, "\""+num1+"\""+",")
    ws1.write(37, 5, "\""+num2+"\""+",")
    ws1.write(46, 5, "\""+num3+"\""+",")
    ws1.write(37, 1, "\""+sap_num+"\""+",")
    ws1.write(50, 1, "\""+sap_num+"\""+",")
    ws1.write(63, 1, "\""+sap_num+"\""+",")
    
def package():
    rb = open_workbook("./TestData.xlsx")
    # r_sheet = rb.sheet_by_index(0)             
    wb = copy(rb)                                
    zcount = input("请输入包裹数目:\n")
    if zcount == "1":     
        w_sheet = wb.get_sheet(0)                
        get_data(zcount)
        one_package(w_sheet)
    elif zcount == "2":      
        w_sheet = wb.get_sheet(1)  
        get_data(zcount )
        two_package(w_sheet)
    wb.save("out.xlsx")

if __name__ == '__main__':
    package()