一、需求场景：

1、需要系统自动注册某账号时候，要按照规范编写Excel，再导入到系统中。

2、需要手工核对，或者使用七拼八凑的各网站功能生成结果再粘贴进Excel整理。

二、想法：

使用Python的openpyxl模块一次性完成表的填写

三、实践：

#-*-coding:utf-8-*-importtimeimportrandomimportstringimportnamesfromopenpyxlimportWorkbookwb=Workbook()current_time=time.strftime('%Y-%m-%d_%H%M%S')dest_filename='Ali'+current_time+'.xlsx'ws=wb.activenum=input("请输入需要生成的账号数量：")ifnum.isdigit():num=int(num)##print("初始化数据......")column_name=['账号用户名','密码','网站代码','手机号','手机任务机code','pc任务机code','支付类型','标签名称','代理方式','代理IP','代理端口号','代理用户名','代理密码','代理协议','支付账号卡号','支付账号绑定方式','购买方案名称','状态','初始化状态','推手手机号','是否可销售','是否可评论','账号类型','名','姓','性别','生日','邮箱','国家','洲/省','城市','邮编','详细地址','收件人名','收件人姓','收件人电话','地址使用方式','注册帐号状态','分组名称','支付账号使用金额','地区代码','机房名称','手机代理方式']email_list=['@hotmail.com','@outlook.com','@msn.com','@gmail.com']src=string.ascii_letters+string.digitsc_col="aliexpress"g_col="信用卡"i_col=aq_col="L-US-FLOW"n_col="socks5"digits=string.digitsp_col="固定"q_col="摆布系统测试方案"r_col="启用"s_col="初始化成功"t_col="promoteAdmin1"w_col="新号"gender=['male','female']start=(1971,1,1,0,0,0,0,0,0)end=(2005,12,31,23,59,59,0,0,0)start_time=time.mktime(start)end_time=time.mktime(end)ac_col="美国"ak_col="使用地址库地址"al_col="未注册"am_col="速卖通2"ao_col=["Alaska","Alabama","Arkansas","EasternSamoa","Arizona","California","Colorado","Connecticut","Delaware","Florida","Georgia","Guam","Hawaii","Idaho","Illinois","Indiana","Kansas","Kentucky","Louisiana","Massachusetts","Maryland","Maine","MarshallIsland","Michigan","Minnesota","Missouri","Mississippi","Montana","Iowa","NorthDakota","Nebraska","Newhampshire","Newjersey","Newmexico","NewYork","Ohio","Oklahoma","Oregon","Pennsylvania","Rhodeisland","Southcarolina","Tennessee","Texas","Utah","Virginia","Vermont","Washington","Wisconsin","Wyoming","Nevada","NorthCarolina"]ap_col="克利夫兰机房"##print(f"正在生成数量为{num}的速卖通账号，请稍后......",)length=len(column_name)n=1fortincolumn_name:iflength:length-=1ws.cell(row=1,column=n,value=t)n+=1##for_inrange(2,num+2):str1=random.choice([names.get_last_name(),names.get_first_name()])str2=''.join(random.sample(string.ascii_letters+string.digits,8))str3=random.choice(email_list)strs=str1+str2+str3ws.cell(row=_,column=1,value=strs)#list_passwd_all=random.sample(src,9)list_passwd_all.extend(random.sample(string.digits,1))list_passwd_all.extend(random.sample(string.ascii_lowercase,1))list_passwd_all.extend(random.sample(string.ascii_uppercase,1))random.shuffle(list_passwd_all)str_passwd=''.join(list_passwd_all)ws.cell(row=_,column=2,value=str_passwd)#ws.cell(row=_,column=3,value=c_col)ws.cell(row=_,column=7,value=g_col)ws.cell(row=_,column=9,value=i_col)ws.cell(row=_,column=14,value=n_col)#payment_account='9'+''.join(random.sample(digits,9))ws.cell(row=_,column=15,value=payment_account)#ws.cell(row=_,column=16,value=p_col)ws.cell(row=_,column=17,value=q_col)ws.cell(row=_,column=18,value=r_col)ws.cell(row=_,column=19,value=s_col)ws.cell(row=_,column=20,value=t_col)ws.cell(row=_,column=23,value=w_col)#current_gender=random.choice(gender)name1=names.get_first_name(gender=current_gender)ws.cell(row=_,column=24,value=name1)ws.cell(row=_,column=34,value=name1)ifcurrent_gender=='male':ws.cell(row=_,column=26,value='男')else:ws.cell(row=_,column=26,value='女')name2=names.get_last_name()ws.cell(row=_,column=25,value=name2)ws.cell(row=_,column=35,value=name2)#date_touple=time.localtime(random.randint(start_time,end_time))date=time.strftime("%Y/%m/%d",date_touple)ws.cell(row=_,column=27,value=date)#ws.cell(row=_,column=28,value=ac_col)#州，城市，邮编，详细地址，收件人电话，地区代码-------未完成#ws.cell(row=_,column=37,value=ak_col)ws.cell(row=_,column=38,value=al_col)ws.cell(row=_,column=39,value=am_col)#address_code=random.choice(ao_col)ws.cell(row=_,column=41,value=address_code)#ws.cell(row=_,column=42,value=ap_col)ws.cell(row=_,column=43,value=aq_col)try:wb.save(filename=dest_filename)print(f"速卖通账号生成完毕，请查看当前目录下的{dest_filename},如果该文件已存在将被覆盖")exceptPermissionErrorasreason:print(reason)print(f"出错啦！！请保持{dest_filename}为关闭状态")

四、结果演示：

输入数量运行后，将生成如Ali2018-12-05_174523.xlsx名字的Excel

值得一提的是，使用了names可以准确地生成名字的“正确”性别。

名姓性别生日GeorgeRhodarmer男2001/11/03EbonyTrinka女1987/05/24CaronMitchell女1985/09/27TrishaBell女2002/12/22MariaSpaulding女1973/02/18DorothyNoyes女1991/01/18NicholasWilliams男1992/06/11CharlesDougherty男1999/04/07CortneyMoore女1997/09/24PilarMiller女1991/08/04JaynaRamirez女1973/10/13AnnieOsullivan女2003/09/21LeonardSapp男2001/12/12JoeAkins男1971/09/29