Batch generation of excel items that meet the rules

Demand scenario:

1. When the system needs to automatically register an account, Excel should be compiled according to the specification and then imported into the system.

2. It needs to be checked manually, or the results generated by using the functions of various websites are pasted into Excel for sorting.

Idea:

Using openpyxl module of Python to fill in the form at one time

Practice:

# -*- 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("Please enter the number of accounts to be generated:")
if num.isdigit():
        num = int(num)
##
print("Initialization data......")
column_name = [
    'Account user name', 'Password', 'Website code', 'Cell-phone number', 'Mobile task machine code',
    'pc Mission machine code', 'Payment type', 'Label name', 'Agency mode', 'agent IP', 'Agent port number',
    'Proxy user name', 'Proxy password', 'Agency agreement', 'Payment account card No', 'Payment account binding method',
    'Purchase scheme name', 'state', 'Initialization status', 'Push mobile number', 'Whether it can be sold', 'Whether to comment',
    'account type', 'name', 'surname', 'Gender', 'Birthday', 'mailbox', 'Country', 'continent/province', 'City', 'Zip code',
    'Detailed address', 'Recipient name', 'Recipient's surname', 'Recipient phone', 'Address usage', 'Registration account status',
    'Group name', 'Payment account usage amount', 'Area code', 'Name of computer room', 'Mobile agent mode'
]
email_list = ['@hotmail.com', '@outlook.com', '@msn.com', '@gmail.com']
src = string.ascii_letters + string.digits
c_col = "aliexpress"
g_col = "Credit Card"
i_col = aq_col = "L-US-FLOW"
n_col = "socks5"
digits = string.digits
p_col = "fixed"
q_col = "Test scheme of pendulum system"
r_col = "Enable"
s_col = "Initialization successful"
t_col = "promoteAdmin1"
w_col = "New number"
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 = "U.S.A"
ak_col = "Use address library address"
al_col = "unregistered"
am_col = "Speed sell 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 = "Cleveland machine room"
##
print(f"Building quantity of{num}Please wait a moment......",)
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='male')
    else:
        ws.cell(row=_, column=26, value='female')
    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)
    #State, city, zip code, detailed address, recipient's phone number, region code ------ incomplete
    #
    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"Quick sell account generation completed, please check the current directory{dest_filename},If the file already exists, it will be overwritten")
except PermissionError as reason:
    print(reason)
    print(f"Something went wrong!! Please keep{dest_filename}Is off")

Results demonstration:

After entering the quantity and running, Excel with the name of ali2018-12-05_.xlsx will be generated

It is worth mentioning that names can accurately generate the "correct" gender of names.

name surname Gender Birthday
George Rhodarmer male 2001/11/03
Ebony Trinka female 1987/05/24
Caron Mitchell female 1985/09/27
Trisha Bell female 2002/12/22
Maria Spaulding female 1973/02/18
Dorothy Noyes female 1991/01/18
Nicholas Williams male 1992/06/11
Charles Dougherty male 1999/04/07
Cortney Moore female 1997/09/24
Pilar Miller female 1991/08/04
Jayna Ramirez female 1973/10/13
Annie Osullivan female 2003/09/21
Leonard Sapp male 2001/12/12
Joe Akins male 1971/09/29



Tags: Python Excel Mobile

Posted on Sat, 30 Nov 2019 11:43:21 -0800 by justcrapx