自动生成大量测试数据

利用Python生成大量测试数据

import random

import pandas as pd
from faker import Faker
from openpyxl import Workbook

'''
    生成大量测试数据
'''

fake = Faker()


df = pd.read_excel("账套信息.xlsx")

company_inner_code_range = ['1601F100', '1602F100', '7210F100', '1625F100', '1623F100', '1646F100', '7213F100',
                            '7208F100', '8013F100', '1644F100', '7211F100', '7215F100', '8032F100', '7214F100',
                            '8010F100', '7216F100', '8009F100']


# 随机生成账套编码
def generate_company_inner_code():
    return random.choice(company_inner_code_range)


# 获取账套名称
def get_company_inner_code_cname(company_code):
    # 检查数据是否包含账套编码列
    if '账套编码' not in df.columns or '账套名称' not in df.columns:
        raise ValueError("Excel文件中没有'账套编码'或'账套名称'列")
    # 根据账套编码获取账套名称
    result = df.loc[df['账套编码'] == company_code, '账套名称']

    if result.empty:
        return f"NULL"
    else:
        return result.iloc[0]


# 币种集合
currency_code_range = ['CNY', 'USD', 'HKD']


# 随机生成币种
def generate_currency_code():
    return random.choice(currency_code_range)


# 生成汇率
def generate_exchange_rate(currency_code):
    if currency_code == 'CNY':
        return 1.0000
    else:
        return round(random.uniform(5.0, 7.5), 2)


# 用户集合
use_range = ['T01', 'T02', 'T03', 'T04', 'T05', 'T06', 'T07', 'T08', 'T09', 'T10', 'T11', 'T12', 'T13', 'T14', 'T15']


# 随机生成用途
def generate_use():
    return random.choice(use_range)

# 客商属性集合
customer_attribute_range = ['K01', 'K02', 'K03', 'K04', 'K05', 'K06', 'K07', 'K08']


# 随机生成客商属性
def generate_customer_attribute():
    return random.choice(customer_attribute_range)


# 生成 应收原值（本币）
def generate_receivable_amount_local(currency_code, receivable_amount_original):
    if currency_code == 'CNY':
        return receivable_amount_original
    else:
        return round(random.uniform(1000, 100000), 2)


# 生成 应收余额（本币）
def generate_receivable_balance_local(currency_code, receivable_balance_original):
    if currency_code == 'CNY':
        return receivable_balance_original
    else:
        return round(random.uniform(1000, 100000), 2)


# 生成测试数据的函数
def generate_test_data(num_records):
    data = []
    for _ in range(num_records):
        company_inner_code = generate_company_inner_code()
        company_inner_code_cname = get_company_inner_code_cname(company_inner_code)
        account_code = 1122
        account_code_cname = '应收账款'
        currency_code = generate_currency_code()
        receivable_amount_original = round(random.uniform(1000, 100000), 2)
        receivable_amount_local = generate_receivable_amount_local(currency_code, receivable_amount_original)
        exchange_rate = generate_exchange_rate(currency_code)
        receivable_balance_original = round(random.uniform(1000, 100000), 2)
        receivable_balance_local = generate_receivable_balance_local(currency_code, receivable_balance_original)
        entry_date = fake.date_this_year()
        due_date = fake.date_this_year()
        data_date = fake.date_this_year()
        use = generate_use()
        customer_attribute = generate_customer_attribute()

        # 生成一条数据
        data.append(
            [company_inner_code, company_inner_code_cname, account_code, account_code_cname, receivable_amount_original,
             receivable_amount_local, currency_code, exchange_rate,
             receivable_balance_original, receivable_balance_local, entry_date, due_date, data_date,
             use, customer_attribute])

    return data


# 创建Excel文件并填充数据
def create_excel_file(data):
    wb = Workbook()
    sheet = wb.active
    sheet.title = "测试数据"

    # 设置表头
    sheet.append(['账套编码', '账套名称', '会计科目', '会计科目名称', '应收金额（原币）', '应收金额（本币）', '币种', '汇率', '应收余额（原币）', '应收余额（本币）',
                  '入账日期', '到期日期', '数据日期', '款项用途', '客商属性'])

    # 添加生成的测试数据
    for record in data:
        sheet.append(record)

    # 保存Excel文件
    wb.save("测试数据.xlsx")


# 生成100000条测试数据
test_data = generate_test_data(100000)

# 创建并保存Excel文件
create_excel_file(test_data)

print("Excel文件已成功生成！")