xlwings对excel公式下拉填充的方法，但多线程一直起不来！

luojunfeng@yaho

网上找了很久，没有找到xlwings对excel公式下拉填充的方法，于是自己搞了一个，但是运行速度真的丧心病狂，找了一晚上也没找到无法开启多线程的问题所在。
附上代码求大神指点，新手小白。感谢感谢
import xlwings as xw
import threading
import os
import time
from numba import jit

# def __init__():
app = xw.App(visible=False, add_book=False)
# app.screen_updating = False
filepath=r'D:PH7\History Performance_GSMRELATION(GSM)_20200921213641.xls'
wb = app.books.open(filepath)
sht=wb.sheets['sheet1']
cols = sht.used_range.last_cell.column # 获取总行数
rows = sht.used_range.last_cell.row#获取总列
'''
初步清洗数据：
1.插入行
2.写入表头
3.放入公式
'''


def insrt_col():#插入列
    start_time=time.time()
    sht.api.Columns(2).Insert()
    sht.api.Columns(22).Insert()
    sht.api.Columns(23).Insert()
    sht.api.Columns(24).Insert()
    sht.api.Columns(27).Insert()
    sht.api.Columns(34).Insert()
    sht.api.Columns(35).Insert()
    sht.api.Columns(36).Insert()
    sht.api.Columns(37).Insert()
    sht.api.Columns(38).Insert()
    end_time=time.time()
    print('插入列完成，耗时{}'.format((end_time-start_time)))
def input_table_name():#插入表头
    start_time=time.time()
    sht.range('W1').value = ['Target cell Name']
    sht.range('B1').value = ['Time']
    sht.range('V1').value = ['Target cell']
    sht.range('X1').value = ['Target cell BCCH']
    sht.range('AA1').value = ['Number of handover failure to the adjacent cell']
    sht.range('AH1').value = ['Failure due to uplink quality']
    sht.range('AI1').value=['Failure due to downlink quality']
    sht.range('AJ1').value=['Failure due to uplink strength']
    sht.range('AK1').value=['Failure due to downlink strength']
    sht.range('AL1').value=['Failure due to PBGT']
    end_time=time.time()
    print('写入表头成功！耗时{}'.format((end_time-start_time)))
# @jit
def input_vlaue():#线程1，遍历写入Range内容
    for i in range(2,rows+1):
        sht.range('B'+str(i)).value = ['=LEFT(C%s,10)'%(str(i))]
        sht.range('V'+str(i)).value = ['=RIGHT(U%s,10)'%(str(i))]
        sht.range('W'+str(i)).value = ['=VLOOKUP(V%s,Sheet2!A:B,2,)'%(str(i))]
        sht.range('X'+str(i)).value = ['=VLOOKUP(W%s,Sheet2!B:C,2,)'%(str(i))]
        sht.range('AA'+str(i)).value = ['=Y%s-Z%s'%(str(i),str(i))]
    # input_vlaue()
# @jit
def input_vlaue_1():#线程2，遍历写入Range内容
    for i in range(2,rows+1):
        sht.range('AH'+str(i)).value = ['=AD%s-AF%s'%(str(i),str(i))]
        sht.range('AI'+str(i)).value = ['=AE%s-AG%s'%(str(i),str(i))]
        sht.range('AJ'+str(i)).value = ['=AM%s-AO%s'%(str(i),str(i))]
        sht.range('AK'+str(i)).value =['=AN%s-AP%s'%(str(i),str(i))]
        sht.range('AL'+str(i)).value=['=AQ%s-AR%s'%(str(i),str(i))]
        end_time=time.time()

def save():
    wb.save(filepath)

if __name__ == '__main__':
    insrt_col()
    input_table_name()  # 写入表头
    start_time = time.time()#计算开始时间
    x = [threading.Thread(target=input_vlaue()),threading.Thread(target=input_vlaue_1())]
    thread_num = len(threading.enumerate())
    for t in x:
        t.start()
        t.join()
    print("线程数量是{}".format(thread_num))#显示线程数

    end_time = time.time()
    print('写入公式完成{}'.format((end_time - start_time)))  ##
    # self.save()
    print('运行完成')

疾风怪盗

你用这个写，貌似会报错
https://fishc.com.cn/thread-128580-1-1.html

用openpyxl修改了一下：

import xlwings as xw
from openpyxl import load_workbook
import threading
import os
import time

'''
初步清洗数据：
1.插入行
2.写入表头
3.放入公式
'''


# def insrt_col():#插入列
#     start_time=time.time()
#     sht.api.Columns(2).Insert()
#     sht.api.Columns(22).Insert()
#     sht.api.Columns(23).Insert()
#     sht.api.Columns(24).Insert()
#     sht.api.Columns(27).Insert()
#     sht.api.Columns(34).Insert()
#     sht.api.Columns(35).Insert()
#     sht.api.Columns(36).Insert()
#     sht.api.Columns(37).Insert()
#     sht.api.Columns(38).Insert()
#     end_time=time.time()
#     print('插入列完成，耗时{}'.format((end_time-start_time)))
def input_table_name():  # 插入表头
    start_time = time.time()
    sht['W1'].value = 'Target cell Name'
    sht['B1'].value = 'Time'
    sht['V1'].value = 'Target cell'
    sht['X1'].value = 'Target cell BCCH'
    sht['AA1'].value = 'Number of handover failure to the adjacent cell'
    sht['AH1'].value = 'Failure due to uplink quality'
    sht['AI1'].value = 'Failure due to downlink quality'
    sht['AJ1'].value = 'Failure due to uplink strength'
    sht['AK1'].value = 'Failure due to downlink strength'
    sht['AL1'].value = 'Failure due to PBGT'
    end_time = time.time()
    print('写入表头成功！耗时{}'.format((end_time - start_time)))


def input_vlaue():  # 线程1，遍历写入Range内容
    for i in range(2, rows + 1):
        sht['B' + str(i)].value = '=LEFT(C%s,10)' % (str(i))
        sht['V' + str(i)].value = '=RIGHT(U%s,10)' % (str(i))
        sht['W' + str(i)].value = '=VLOOKUP(V%s,Sheet2!A:B,2,)' % (str(i))
        sht['X' + str(i)].value = '=VLOOKUP(W%s,Sheet2!B:C,2,)' % (str(i))
        sht['AA' + str(i)].value = '=Y%s-Z%s' % (str(i), str(i))



def input_vlaue_1():  # 线程2，遍历写入Range内容
    for i in range(2, rows + 1):
        sht['AH' + str(i)].value = '=AD%s-AF%s' % (str(i), str(i))
        sht['AI' + str(i)].value = '=AE%s-AG%s' % (str(i), str(i))
        sht['AJ' + str(i)].value = '=AM%s-AO%s' % (str(i), str(i))
        sht['AK' + str(i)].value = '=AN%s-AP%s' % (str(i), str(i))
        sht['AL' + str(i)].value = '=AQ%s-AR%s' % (str(i), str(i))



if __name__ == '__main__':

    filepath = r'test1.xlsx'
    wb = load_workbook(filepath)
    sht = wb['Sheet1']
    cols = sht.max_column  # 获取总行数
    rows = sht.max_row  # 获取总列

    # insrt_col()
    input_table_name()  # 写入表头

    start_time = time.time()  # 计算开始时间

    a = threading.Thread(target=input_vlaue,daemon=True)
    b = threading.Thread(target=input_vlaue_1,daemon=True)
    a.start()
    b.start()
    thread_num = len(threading.enumerate())

    print("线程数量是{}".format(thread_num))  # 显示线程数

    end_time = time.time()
    print('写入公式完成{}'.format((end_time - start_time)))  ##

    wb.save('test1.xlsx')
    print('运行完成')

写入表头成功！耗时0.0
线程数量是1
写入公式完成0.002007722854614258
运行完成

但是不使用多线程：
写入表头成功！耗时0.0
写入公式完成0.0010006427764892578
运行完成

不用多线程只有更快

luojunfeng@yaho

疾风怪盗 发表于 2020-9-26 00:44
你用这个写，貌似会报错
https://fishc.com.cn/thread-128580-1-1.html

openxl 针对‘xlsx'格式，使用xlwing的主要原因是xlwing支持xls格式的处理