用Python3生成30万条excel数据方法（xlsx格式）

用Python3生成30万条excel数据方法（xlsx格式）

在B/S架构的系统测试中，有时需要通过导入excel文件来生成一些数据记录，当数据量小的时候，一般不会出现什么问题，而当导入的数据量巨大时，对系统的性能就是一个考验了。为了验证系统的性能，有时需要导入海量的数据，如30万条数据记录，而手头并没有这么多数据时该怎么办呢？一条一条复制粘贴，或者通过excel的下拉拖动来生成记录？这样会把测试人员累死，而且单调重复的工作，非常无趣。这时，我们只需借助python，便可以轻松生成大量数据，把人从单调重复的工作中解放出来，去做更有意义的事。

例如，我们需要生成重复的几W条如下数据

这是一条需要导入示例数据，其中包含的字段其他信息保持不变。

思路：先将表头和示例数据保存在一个示例文件中，通过xlrd来读取示例内容，通过xlsxwriter来按规则写入表头和不同的信息。

原文中：有所需要改变的字段，这里不需要改变
代码如下：

import xlrd
import xlsxwriter

filename=r'E:\API_AUTO\test_data\1.xlsx'     #文件名以及路径，前面加一个r防止生成不必要的转义。
data = xlrd.open_workbook(filename)
table = data.sheets()[0]  # 获取第1个sheet页
content =table.row(1)    # 获取第2行（第1条）数据
print('第1条示例数据为：',content)
# 通过上面print内容可以看到，直接读出的内容，虽然是list，但每条数据前加了
# text:字样，不能直接强转成tuple为我们所用，于是自定义一个转换方法将其转换为可以用的list以便后面强转成tuple
def convertRowToTuple(rowNum):
    data=[]
    # len(content)是上面获取到的该行的数据列数
    for i in range(len(content)):
        data.append(table.cell_value(rowNum,i))
    # 将每个单元格中的数据读取出来加到data这个list中并强转成元组返回
    return tuple(data)
# 将第0行（即excel中的第1行表头）数据读取并转成元组赋给header
header = convertRowToTuple(0)
# 将第1行（即excel中的第2行）示例数据读取并转成元组赋给data1
data1 = convertRowToTuple(1)
# print(header)
# print(data1)
# 即将写入数据的文件名
filename2 = r'E:\TestDatas2.xlsx'
# 如果数据量非常大，可以启用constant_memory，这是一种顺序写入模式，得到一行数据就立刻写入一行，而不会把所有的数据都保持在内存中。
# 如果不启用此模式，当数据量巨大时，程序很大概率地卡死
workbook = xlsxwriter.Workbook(filename2, {'constant_memory': True})
# 创建新的sheet页
worksheet = workbook.add_worksheet()
# startNum表示从第几行开始写，这里的数字是从1开始，因为后面要和字母组合对应在excel中，如A1代表第1行第A列
startNum = 1
#先将表头写入文件
worksheet.write_row('A'+str(startNum), header)
# 正式开始写入数据
for i in range(1000000):
    # 为了不让生成过程无聊，加此打印信息以便查看进度
    print('正创建第', i+1, '条数据')
    # 表头占据了第1行，所以首条数据从第2行开始，当i=0时，写入的数据从A2开始
    # 将data1中的数据依次写入A2、B2、C2……
    worksheet.write_row('A'+str(startNum+i+1), data1)
    # 前面相当于复制了第一条数据的所有内容
# 写完之后关闭workbook，否则会报错
workbook.close()

为了方便，写成了一个方法：

import xlrd
import xlsxwriter



class BulkImport:

    @staticmethod  # 静态函数
    def Bulk_import_data(file1, sheet_name1,generation_number, file2,startNum=1):
        """file1:读取excel文件的路径,  文件名以及路径，前面加一个r防止生成不必要的转义。
           file1=r'E:\API_AUTO\test_data\1.xlsx'
           sheet_name1：file1文件下所需读取对应的表单名（sheet）
           startNum: 默认参数（默认从1开始），表示从第几行开始写，因为后面要和字母组合对应在excel中，如A1代表第1行第A列
           generation_number:所需生成数据的总条数
           file2:即将写入数据的文件,  文件名以及路径，前面加一个r防止生成不必要的转义。
        """
        data = xlrd.open_workbook(file1)
        # 方法一：通过sheet下标获取第1个sheet页,这边方法传递的是表单名，所以这边不用方法一
        # table = data.sheets()[0]

        # 方法二：通过表单名（sheetname）获取第1个sheet页
        table = data.sheet_by_name(sheet_name1)
        content = table.row(1)    # 获取第2行（第1条）数据
        print('第1条示例数据为：', content)
        # 通过上面print内容可以看到，直接读出的内容，虽然是list，但每条数据前加了
        # text:字样，不能直接强转成tuple为我们所用，于是自定义一个转换方法将其转换为可以用的list以便后面强转成tuple

        def convertRowToTuple(rowNum):
            data=[]
            # len(content)是上面获取到的该行的数据列数
            for i in range(len(content)):
                data.append(table.cell_value(rowNum,i))
            # 将每个单元格中的数据读取出来加到data这个list中并强转成元组返回
            return tuple(data)
        # 将第0行（即excel中的第1行表头）数据读取并转成元组赋给header
        header = convertRowToTuple(0)
        # 将第1行（即excel中的第2行）示例数据读取并转成元组赋给data1
        data1 = convertRowToTuple(1)
        # print("excel中的第1行表头(header)的内容是：", header)
        # print("excel中的第2行的内容(data1)是：", data1)

        # 如果数据量非常大，可以启用constant_memory，这是一种顺序写入模式，得到一行数据就立刻写入一行，而不会把所有的数据都保持在内存中。
        # 如果不启用此模式，当数据量巨大时，程序很大概率地卡死
        # file2:即将写入数据的文件名，示例：file2 = r'E:\TestDatas2.xlsx'
        workbook = xlsxwriter.Workbook(file2, {'constant_memory': True})

        worksheet = workbook.add_worksheet()  # 创建新的sheet页


        #先将表头写入文件
        worksheet.write_row('A'+str(startNum), header)
        # 正式开始写入数据
        for i in range(generation_number):
            # generation_number：所需生成数据的总条数
            # 为了不让生成过程无聊，加此打印信息以便查看进度
            print('正创建第', i+1, '条数据')
            # 表头占据了第1行，所以首条数据从第2行开始，当i=0时，写入的数据从A2开始
            # 将data1中的数据依次写入A2、B2、C2……
            worksheet.write_row('A'+str(startNum+i+1), data1)
            # 前面相当于复制了第一条数据的所有内容
        # 写完之后关闭workbook，否则会报错
        workbook.close()


if __name__ == '__main__':
    # 调用
    # BulkImport.Bulk_import_data(file1=r'E:\API_AUTO\test_data\1.xlsx', sheet_name1='表单名',startNum=1,generation_number=10, file2=r'E:\TestDatas2.xlsx')

    filename1 = r'E:\API_AUTO\test_data\1.xlsx'
    sheet_name1 = '表单名'
    startnum = 2
    generation_number = 10
    filename2 = r'E:\TestDatas2.xlsx'

    BulkImport.Bulk_import_data(file1=filename1, sheet_name1=sheet_name1, generation_number=generation_number, file2=filename2,startNum=startnum)

完成后的测试数据如下