使用 win32com 的 Python 不会使用所需的加载项更新 Excel 表 [英] Python using win32com wont update excel sheet with needed Add-ins

问题描述

我正在尝试获取一个 python 脚本来创建一个带有公式的 excel 文件，该文件可以在工作簿打开时执行(它需要一个仅在 excel 中可用的加载项).打开后，excel 将计算公式，python 将关闭并保存文件.然后我可以将这个 excel 文件用于我的 python 脚本的其余部分.但是，在我手动打开 python 创建的 excel 文件之前，我无法使用 add in 来执行公式.

I am trying to get a python script to create an excel file with a formula ready to be executed when the workbook opens(it requires an add-in only available in excel). Once open, excel will compute the formula and python will close and save the file. I can then use this excel file for the rest of my python script. However I can't get the add in to execute the formula until I manually open the python created excel file.

我尝试了各种方法来让 win32com 打开加载了所有加载项的 excel 文件.但是，似乎没有任何效果.

I have tried various ways to get win32com to open the excel file with all add-ins loaded. However, none seem to work.

workbook = xlsxwriter.Workbook('C:/Users/ccraig/Desktop/QR Price_Bonds/t.xlsx')
worksheet1=workbook.add_worksheet()
worksheet2=workbook.add_worksheet()
worksheet1.write(1,1,'Hello')
worksheet1.write_formula('A2','=BDH(Sheet2!A1&"@TRAC Corp","TRADE",Sheet2!B2,Sheet2!BA2,"IntrRw=True","RPSCodes=S","RPTContra=S","Price=S","Type=S","Yield=S","Size=S","IndicatorCodes=S","CondCodes=S","QRM=S","Dir=V","cols=9;rows=312")')
worksheet2.write('A1','1730T0R50')
worksheet2.write('B2','8/7/2019')
worksheet2.write('A2','8/7/2019')
workbook.close()
xlapp = win32com.client.DispatchEx("Excel.Application")
xlapp.RegisterXLL('C:/blp/API/Office Tools/bofaddin.dll')
# for addin in xlapp.AddIns:
#     addin.Installed=True
wb = xlapp.Workbooks.Open('C:/Users/ccraig/Desktop/QR Price_Bonds/t.xlsx',None,False)
wb_addin = ('C:/blp/API/Office Tools/bofaddin.dll')
wb.RefreshAll()
xlapp.CalculateUntilAsyncQueriesDone()
wb.Save()
xlapp.Quit()

我没有收到任何错误.该文件是根据需要创建的，但在我手动打开文件之前，公式不会执行.

I don't get any errors. The file is created as desired, but the formula wont execute until i manually open the file.

推荐答案

xlapp = win32com.client.DispatchEx("Excel.Application")
xlapp.RegisterXLL('C:/blp/API/Office Tools/bofaddin.dll')
xlapp.Workbooks.Open('C:\\blp\\API\\Office Tools\\BloombergUI.xla')
wb = xlapp.Workbooks.Open(filepath,None,False)
xlapp.Visible = True
wb_addin = ('C:/blp/API/Office Tools/bofaddin.dll')
wb.RefreshAll()
sleep(60)
wb.Save()
xlapp.Quit()

这是工作代码.

这篇关于使用 win32com 的 Python 不会使用所需的加载项更新 Excel 表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！