python操作excel

import time
import math
import pandas as pd
import xlsxwriter as xw

def test1():
    with pd.ExcelWriter('药典数据_nex.xlsx') as writer:
        df = pd.read_excel('药典数据.xlsx', sheet_name=0)

        s = 0
        t0 = time.time()

        numOnce = 2000
        total = 100000
        for i in range(math.ceil(total / numOnce)):
            for j in range(numOnce):
                df.loc[s, '中文名'] = '测试'

                if s%1000 == 0:
                    print(s)

                s += 1

        df.to_excel(writer, sheet_name='活性成分', index=False)
        print(time.time() - t0)


def test():
    with pd.ExcelWriter('药典数据_nex.xlsx') as writer:
        df = pd.read_excel('药典数据.xlsx', sheet_name=0)

        t0 = time.time()

        rows = list()
        numOnce = 2000
        total = 100000
        for i in range(math.ceil(total / numOnce)):
            for j in range(numOnce):
                v = {}

                v['中文名'] = '测试1'
                v['英文名'] = 'english'

                rows.append(v)

        d2 = pd.DataFrame(rows, columns=df.columns)
        d2.to_excel(writer, sheet_name='活性成分', index=False)

        print(time.time() - t0)


def xw_toexcel():  # xlsxwriter库储存数据到excel
    workbook = xw.Workbook('药典数据_nex.xlsx')  # 创建工作簿
    worksheet1 = workbook.add_worksheet("活性成分")  # 创建子表
    worksheet1.activate() # 激活表

    t0 = time.time()

    title = ['中文名', '英文名']  # 设置表头
    worksheet1.write_row('A1', title)  # 从A1单元格开始写入表头

    k = 2 # 从第二行开始写入数据
    numOnce = 2000
    total = 100000
    for i in range(math.ceil(total / numOnce)):
        for j in range(numOnce):
            insertData = ['测试1', 'english1']
            row = 'A' + str(k)
            worksheet1.write_row(row, insertData)
            k += 1

    workbook.close() # 关闭表
    print(time.time() - t0)


if __name__ == '__main__':
    xw_toexcel()

方法2最占内存,也最快
方法3 内存占用和速度居中