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修改了一下：

1. import xlwings as xw
   
2. from openpyxl import load_workbook
   
3. import threading
   
4. import os
   
5. import time
   
6. 
   
7. '''
   
8. 初步清洗数据：
   
9. 1.插入行
   
10. 2.写入表头
    
11. 3.放入公式
    
12. '''
    
13. 
    
14. 
    
15. # def insrt_col():#插入列
    
16. #     start_time=time.time()
    
17. #     sht.api.Columns(2).Insert()
    
18. #     sht.api.Columns(22).Insert()
    
19. #     sht.api.Columns(23).Insert()
    
20. #     sht.api.Columns(24).Insert()
    
21. #     sht.api.Columns(27).Insert()
    
22. #     sht.api.Columns(34).Insert()
    
23. #     sht.api.Columns(35).Insert()
    
24. #     sht.api.Columns(36).Insert()
    
25. #     sht.api.Columns(37).Insert()
    
26. #     sht.api.Columns(38).Insert()
    
27. #     end_time=time.time()
    
28. #     print('插入列完成，耗时{}'.format((end_time-start_time)))
    
29. def input_table_name():  # 插入表头
    
30.     start_time = time.time()
    
31.     sht['W1'].value = 'Target cell Name'
    
32.     sht['B1'].value = 'Time'
    
33.     sht['V1'].value = 'Target cell'
    
34.     sht['X1'].value = 'Target cell BCCH'
    
35.     sht['AA1'].value = 'Number of handover failure to the adjacent cell'
    
36.     sht['AH1'].value = 'Failure due to uplink quality'
    
37.     sht['AI1'].value = 'Failure due to downlink quality'
    
38.     sht['AJ1'].value = 'Failure due to uplink strength'
    
39.     sht['AK1'].value = 'Failure due to downlink strength'
    
40.     sht['AL1'].value = 'Failure due to PBGT'
    
41.     end_time = time.time()
    
42.     print('写入表头成功！耗时{}'.format((end_time - start_time)))
    
43. 
    
44. 
    
45. def input_vlaue():  # 线程1，遍历写入Range内容
    
46.     for i in range(2, rows + 1):
    
47.         sht['B' + str(i)].value = '=LEFT(C%s,10)' % (str(i))
    
48.         sht['V' + str(i)].value = '=RIGHT(U%s,10)' % (str(i))
    
49.         sht['W' + str(i)].value = '=VLOOKUP(V%s,Sheet2!A:B,2,)' % (str(i))
    
50.         sht['X' + str(i)].value = '=VLOOKUP(W%s,Sheet2!B:C,2,)' % (str(i))
    
51.         sht['AA' + str(i)].value = '=Y%s-Z%s' % (str(i), str(i))
    
52. 
    
53. 
    
54. 
    
55. def input_vlaue_1():  # 线程2，遍历写入Range内容
    
56.     for i in range(2, rows + 1):
    
57.         sht['AH' + str(i)].value = '=AD%s-AF%s' % (str(i), str(i))
    
58.         sht['AI' + str(i)].value = '=AE%s-AG%s' % (str(i), str(i))
    
59.         sht['AJ' + str(i)].value = '=AM%s-AO%s' % (str(i), str(i))
    
60.         sht['AK' + str(i)].value = '=AN%s-AP%s' % (str(i), str(i))
    
61.         sht['AL' + str(i)].value = '=AQ%s-AR%s' % (str(i), str(i))
    
62. 
    
63. 
    
64. 
    
65. if __name__ == '__main__':
    
66. 
    
67.     filepath = r'test1.xlsx'
    
68.     wb = load_workbook(filepath)
    
69.     sht = wb['Sheet1']
    
70.     cols = sht.max_column  # 获取总行数
    
71.     rows = sht.max_row  # 获取总列
    
72. 
    
73.     # insrt_col()
    
74.     input_table_name()  # 写入表头
    
75. 
    
76.     start_time = time.time()  # 计算开始时间
    
77. 
    
78.     a = threading.Thread(target=input_vlaue,daemon=True)
    
79.     b = threading.Thread(target=input_vlaue_1,daemon=True)
    
80.     a.start()
    
81.     b.start()
    
82.     thread_num = len(threading.enumerate())
    
83. 
    
84.     print("线程数量是{}".format(thread_num))  # 显示线程数
    
85. 
    
86.     end_time = time.time()
    
87.     print('写入公式完成{}'.format((end_time - start_time)))  ##
    
88. 
    
89.     wb.save('test1.xlsx')
    
90.     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格式的处理