使用python动态读取和/或覆盖excel文件而不会出现覆盖警报 [英] dynamically read and/or overwrite an excel file with python without the overwrite alert appearing

查看:62
本文介绍了使用python动态读取和/或覆盖excel文件而不会出现覆盖警报的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

出于某种原因,以下代码运行良好,但即使我已设置 xl.EnableEvents = False,文件覆盖警报仍不断出现,除非我手动单击覆盖文件弹出窗口,否则代码不会进一步执行.有谁知道如何解决这一问题?

For some reason the following code runs fine but the file overwrite alert keeps coming up even though I have set xl.EnableEvents = False, the code won't execute further unless I manually click the overwrite file popup. Does anyone know how to fix this?

代码打开一个excel文件,其中包含一个允许excel文件连接到bloomberg api的字符串,我使用了这个解决方案此处 使其工作.只要文件打开足够长的时间,数据就会被拉入文件,然后保存并退出.获取数据大约需要 35 秒,pandas 表开始显示我请求的内容

The code opens an excel file which contains a string which allows the excel file to connect to the bloomberg api, I used this solution here to get this to work. As long as the file is open for enough time the data is pulled into the file and then saves and exits. It takes around ~35 seconds to get the data and the pandas table starts displaying the content I'm requesting

问题是弹出窗口!- 我需要查看字符串 '#N/A Requesting Data...' 何时不再出现在文件中,并且如果不定期保存文件就看不到执行此操作的方法.一种让我无需保存即可动态查看文件内容的解决方案会很棒.

The problem is the popups! - I need to see when the string '#N/A Requesting Data...' is no longer in the file and can't see a way to do it without periodically saving the file. A solution that allows me to see the file contents dynamically without having to save would be great.

解决方案这里对我来说停止弹出窗口不起作用,我可能每次都可以创建一个新文件,然后在最后将它们全部删除,但这似乎有点笨拙.这个问题扩展了这个问题这里 如果有人想在更完整的上下文中查看代码和问题.

The solution here didn't work for me to stop the popups, I could probably make a new file each time and then delete them all at the end but this seems a bit clunky. This question extends this problem here if anyone wants to see the code and the problem in a more full context.

WB = 'C:/path/to/my/file.xlsx'
location = "EGLL"

def run_VWA(WB, location):
    """open the excel file, allow enough time to pull the data, then close and save"""

    bb = 'C:/blp/API/Office Tools/BloombergUI.xla'
    xl=win32com.client.DispatchEx("Excel.Application")  
    xl.Workbooks.Open(bb)
    xl.AddIns("Bloomberg Excel Tools").Installed = True

    wb = xl.Workbooks.Open(Filename=WB) #opens workbook in readonly mode.

    xl.Visible = False
    xl.EnableEvents = False
    xl.DisplayAlerts = False

    total=0
    colstring='#N/A Requesting Data...'
    while total < 40:
        wb.Save()   
        df = df_from_excel(WB, location)
        if colstring not in df:
            break
        time.sleep(3)
        total+=3


    wb.Close(SaveChanges=1)
    xl.DisplayAlerts = True
    xl.Quit()
    #Cleanup the com reference. 
    del xl   

    return

非常感谢您对此的任何帮助,我对 win32com 库的经验非常有限.

Any help with this is much appreciated, I have very limited experience with the win32com library.

推荐答案

经过几个小时的挖掘,我找到了如何动态解决这个问题,而无需每次迭代都保存文件.如果其他人遇到这个问题,大部分解决方案都被找到 此处.非常感谢 assylias 提供一些有用的指针.

After a good few hours digging I've found how to solve this dynamically without the need for saving the file each iteration. If anyone else comes up against this problem most of the solution was found here. Many thanks assylias for some useful pointers.

def run_VWA(WB, location):
    """open the excel file, allow enough time to pull the data, then close and save"""

    bb = 'C:/blp/API/Office Tools/BloombergUI.xla'
    xl=win32com.client.DispatchEx("Excel.Application")  
    xl.Workbooks.Open(bb)
    xl.AddIns("Bloomberg Excel Tools").Installed = True

    wb = xl.Workbooks.Open(Filename=WB) #opens workbook in readonly mode.

    xl.Visible = False
    xl.EnableEvents = False
    xl.DisplayAlerts = False

    count=0
    while True:
        readData = wb.Worksheets(location)
        allData = readData.UsedRange
        if allData.Rows.Count > 1 or allData.Columns.Count > 1:
            print('rows: {}'.format(allData.Rows.Count))
            print('cols: {}'.format(allData.Columns.Count))
            break
        print(wb)
        print(count)
        time.sleep(3)
        count+=3


    wb.Close(SaveChanges=1)
    xl.DisplayAlerts = True
    xl.Quit()
    #Cleanup the com reference. 
    del xl   

    return

这篇关于使用python动态读取和/或覆盖excel文件而不会出现覆盖警报的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆