批量生成符合规则的excel条目内容-创新互联
一、需求场景：
1、需要系统自动注册某账号时候，要按照规范编写Excel，再导入到系统中。
2、需要手工核对，或者使用七拼八凑的各网站功能生成结果再粘贴进Excel整理。
二、想法：
使用Python的openpyxl模块一次性完成表的填写
三、实践：
# -*- coding: utf-8 -*- import time import random import string import names from openpyxl import Workbook wb = Workbook() current_time = time.strftime('%Y-%m-%d_%H%M%S') dest_filename = 'Ali' + current_time + '.xlsx' ws = wb.active num = input("请输入需要生成的账号数量：") if num.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.digits c_col = "aliexpress" g_col = "信用卡" i_col = aq_col = "L-US-FLOW" n_col = "socks5" digits = string.digits p_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", "Eastern Samoa", "Arizona", "California", "Colorado", "Connecticut", "Delaware", "Florida", "Georgia", "Guam", "Hawaii", "Idaho", "Illinois", "Indiana", "Kansas", "Kentucky", "Louisiana", "Massachusetts", "Maryland", "Maine", "Marshall Island", "Michigan", "Minnesota", "Missouri", "Mississippi", "Montana", "Iowa", "North Dakota", "Nebraska", "New hampshire", "New jersey", "New mexico", "New York", "Ohio", "Oklahoma", "Oregon", "Pennsylvania", "Rhode island", "South carolina", "Tennessee", "Texas", "Utah", "Virginia", "Vermont", "Washington", "Wisconsin", "Wyoming", "Nevada", "North Carolina" ] ap_col = "克利夫兰机房" ## print(f"正在生成数量为{num}的速卖通账号，请稍后......",) length = len(column_name) n = 1 for t in column_name: if length: length -= 1 ws.cell(row=1, column=n, value=t) n += 1 ## for _ in range(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 + str3 ws.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) if current_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},如果该文件已存在将被覆盖") except PermissionError as reason: print(reason) print(f"出错啦！！请保持{dest_filename}为关闭状态")四、结果演示：
输入数量运行后，将生成如Ali2018-12-05_174523.xlsx名字的Excel
值得一提的是，使用了names可以准确地生成名字的“正确”性别。
| 名 | 姓 | 性别 | 生日 |
| George | Rhodarmer | 男 | 2001/11/03 |
| Ebony | Trinka | 女 | 1987/05/24 |
| Caron | Mitchell | 女 | 1985/09/27 |
| Trisha | Bell | 女 | 2002/12/22 |
| Maria | Spaulding | 女 | 1973/02/18 |
| Dorothy | Noyes | 女 | 1991/01/18 |
| Nicholas | Williams | 男 | 1992/06/11 |
| Charles | Dougherty | 男 | 1999/04/07 |
| Cortney | Moore | 女 | 1997/09/24 |
| Pilar | Miller | 女 | 1991/08/04 |
| Jayna | Ramirez | 女 | 1973/10/13 |
| Annie | Osullivan | 女 | 2003/09/21 |
| Leonard | Sapp | 男 | 2001/12/12 |
| Joe | Akins | 男 | 1971/09/29 |
另外有需要云服务器可以了解下创新互联cdcxhl.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。
名称栏目：批量生成符合规则的excel条目内容-创新互联
分享网址：http://ybzwz.com/article/ijogh.html
