通过python生成表格

通过python第三方库xlwt生成表格，也可以通过pandas实现

from datetime import datetime, date
import random

import xlwt

from account.data.xlrd_read import readexcel
from faker import Faker
#初始化，设置locale为中文；默认是英文
fake = Faker(locale = 'zh_CN')

pathp = r''    #读取导入模板文件
pathsave1= r''  #保存批量导入文件
path1=r''  #读取生成的导入文件
path2=r''       #读取修改模板文件
pathsave2=r''   #保存生成的批量修改文件

def plus():
    now = datetime.now()
    # 获取今天
    datetime.today()
    timea = now.strftime("%Y-%m-%d")
    timeb = now.strftime("%Y/%m/%d")
    # global brandchannel
    # global registertype

    brandperson=random.choices(['','平安福','安鑫宝'])
    country=fake.country()
    name=random.choices(['','',''])
    brandchannel = random.choice(['',''])  #

    if brandchannel=='':

        registertype=random.choices(['','-DE','58976367'])
        # global registertype
    else :  #brandchannel=='Aliexpress'
        # global registertype
        registertype = random.choices(['131231-', '456412321-', '16880115-'])
        # global registertype
    # elif brandchannel=='Amazon':
    #     registertype=random.choices(['1688010', '-cn', '-cn'])

    registercategory=fake.first_name_male() # fake.name()
    applicationno=random.choice([timea, timeb])
    registerno=random.choice([timea, timeb])
    applicationdata = random.randint(1,100)
    registerdata = random.choice(['', 'CNY'])
    validitydata = random.randint(1,1000)
    maney=random.choice(['', 'CNY'])
    status=random.choices(['','',''])
    certificate=str(random.randint(1,10000000))+''
    contractdata=fake.name()
    brandyuming=fake.name()
    fuwushang =fake.province()
    # dailicompany = '' + str(random.randint(1, 1000000))
    # fuwushangperson='admin'
    # services = '=Aa' + str(random.randint(1, 1000000))
    # serviceuse = '=Aa' + str(random.randint(1, 1000000))
    # dailicc = '=Aa' + str(random.randint(1, 1000000))
    # remarknote = '=Aa' + str(random.randint(1, 1000000))
    return   brandperson,country,name,brandchannel,registertype,registercategory,applicationno,registerno,applicationdata,registerdata,validitydata,maney,status,certificate,contractdata,brandyuming,fuwushang
            # dailicompany,fuwushangperson,services,serviceuse,dailicc,remarknote


def sortdel():
    carddef = list(plus())
    # print(carddef)
    return carddef
    print(carddef)



def mkinsurance(rowss=52,):
    wb = xlwt.Workbook(encoding="utf-8")
    ws = wb.add_sheet('sheet', cell_overwrite_ok=True)  # 增加sheet

    # 调用表格方法
    # path1 = r''
    reade = readexcel(pathp)
    row0=reade[2]  #第一行数据，
    row1=reade[3]  #第二行数据

    # 设置列数据 ,i代表行数，j代表列数，操作列数据
    for i in range(rowss):
        # sortde=plus()
        sortde = sortdel()
        # sortde=a
        # print(sortde)
        for j in range(reade[1]):   #调用read，字段有10列
            ws.write(i, j, sortde[j])

    # 操作第二行规则数据，1行，列遍历
    for i in range(len(row1)):
        ws.write(1, i, row1[i])

    # 操作第一行表头数据，0行，列遍历
    for i in range(len(row0)):
        ws.write(0, i, row0[i])

    # 保存文件位置
    # wb.save(r'.xls')
    wb.save(pathsave1)

def modifyinsurance():


    #读取批量导入文件的数据
    # path1=r'.xls'
    reade=readexcel(path1)
    print(reade[4])

    #读取批量修改模板文件的数据
    # path2=r''
    readtemplate=readexcel(path2)
    # print(readtemplate)
    print(readtemplate[0])

    #创建批量修改文件
    wb = xlwt.Workbook(encoding="utf-8")
    ws = wb.add_sheet('sheet', cell_overwrite_ok=True)  # 增加sheet
    # 表头字段
    row0 = readtemplate[2]
    row2 = readtemplate[3]    #规则第二行

    # 设置列数据 ,i代表行数，j代表列数，操作列数据
    for i in range((reade[0])):  #reade[0] 行数
        sortde=sortdel()
        for j in range(reade[1]):  #reade[1] 列数
            ws.write(i, j+1, sortde[j])

    # 操作前三列数据，0行，列遍历  规则    申请国家及地区
    for i in range(len(row0)):
        # ws.write(1, i, col_data[0])
        for j in  range(len(reade[8])): #reade[4]获取第一列的数据
            ws.write(j,0,reade[8][j])

    # # 操作前三列数据，0行，列遍历  规则    商标名称
    # for i in range(len(row0)):
    #     # ws.write(1, i, col_data[0])
    #     for j in  range(len(reade[6])): #reade[4]获取第一列的数据
    #         ws.write(j,0,reade[6][j])
    #
    # # 操作前三列数据，0行，列遍历  规则   注册类别
    # for i in range(len(row0)):
    #     # ws.write(1, i, col_data[0])
    #     for j in  range(len(reade[7])): #reade[4]获取第一列的数据
    #         ws.write(j,2,reade[7][j])


    # 操作第二行表头数据，0行，列遍历  规则
    for i in range(len(row2)):
        ws.write(1, i, row2[i])

    # 操作第一行表头数据，0行，列遍历
    for i in range(len(row0)):
        ws.write(0, i, row0[i])

    # 保存文件位置
    # wb.save(r'.xls')
    wb.save(pathsave2)



mkinsurance(rowss=52,)
modifyinsurance()