xlwings对excel公式下拉填充的方法，但多线程一直起不来！
网上找了很久，没有找到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 =
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
运行完成
不用多线程只有更快 疾风怪盗 发表于 2020-9-26 00:44
你用这个写，貌似会报错
https://fishc.com.cn/thread-128580-1-1.html
openxl 针对‘xlsx'格式，使用xlwing的主要原因是xlwing支持xls格式的处理
页:
[1]