pandas 使用 Python 刷新 Excel 外部数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40893870/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Refresh Excel External Data with Python
提问by mk8efz
I have an Excel file that I run a Python script on. The Excel file has external data connections that need to be refreshed before the Python script is run. The functionality I'm referring to is here:
我有一个运行 Python 脚本的 Excel 文件。Excel 文件具有需要在运行 Python 脚本之前刷新的外部数据连接。我所指的功能在这里：
I'm using Python 2.7 and am relying on Pandas for most of the Excel data parsing.
我正在使用 Python 2.7，并且大部分 Excel 数据解析都依赖 Pandas。
回答by Chris J
CalculateUntilAsyncQueriesDone() will hold the program and wait until the refresh has completed.
CalculateUntilAsyncQueriesDone() 将保持程序并等待刷新完成。
xlapp = win32com.client.DispatchEx("Excel.Application")
wb = xlapp.Workbooks.Open(<path_to_excel_workbook>)
wb.RefreshAll()
xlapp.CalculateUntilAsyncQueriesDone()
wb.Save()
xlapp.Quit()
回答by Julien Marrec
If you're on windows, and I believe you are given the screenshot, you can use the win32commodule. It will allow you - from python - to open up Excel, load a workbook, refresh all data connections and then quit. The syntax ends up being pretty close to VBA.
如果您使用的是 Windows，并且我相信您已获得屏幕截图，则可以使用该win32com模块。它将允许您 - 从 python - 打开 Excel，加载工作簿，刷新所有数据连接，然后退出。语法最终非常接近 VBA。
I suggest you install pypiwin32via pip (pip install pypiwin32).
我建议你pypiwin32通过 pip ( pip install pypiwin32)安装。
import win32com.client
# Start an instance of Excel
xlapp = win32com.client.DispatchEx("Excel.Application")
# Open the workbook in said instance of Excel
wb = xlapp.workbooks.open(<path_to_excel_workbook>)
# Optional, e.g. if you want to debug
# xlapp.Visible = True
# Refresh all data connections.
wb.RefreshAll()
wb.Save()
# Quit
xlapp.Quit()
回答by ratherstrange
Adding this as an answer since this is the first Google link - the code in the first answer worked but has incorrect capitalization, it should be:
添加此作为答案，因为这是第一个 Google 链接 - 第一个答案中的代码有效但大小写不正确，它应该是：
import win32com.client
import time
xlapp = win32com.client.DispatchEx("Excel.Application")
wb = xlapp.Workbooks.Open(<path_to_excel_workbook>)
wb.RefreshAll()
time.sleep(5)
wb.Save()
xlapp.Quit()
回答by Xaviour
Adding on top of what everyone else has said, I kept getting the save dialog again when the code got to the Quit line. I set the DisplayAlerts flag to false and it fixed my issue. I didn't need the sleep timer either. This is what worked for me:
除了其他人所说的之外，当代码到达 Quit 行时，我再次收到保存对话框。我将 DisplayAlerts 标志设置为 false 并解决了我的问题。我也不需要睡眠定时器。这对我有用：
xlapp = win32com.client.DispatchEx("Excel.Application")
wb = xlapp.Workbooks.Open(<path_to_excel_workbook>)
wb.RefreshAll()
xlapp.CalculateUntilAsyncQueriesDone()
xlapp.DisplayAlerts = False
wb.Save()
xlapp.Quit()
