如何使用python脚本定时刷新Excel表格

可以使用python的刷新excel，同时可以写一个定时器或多线程，每隔固定时间刷新一下。
安装如下模块

pip install pywin32

如果只是想用脚本刷新一下,可使用如下脚本

#!/bin/python3

# Importing the pywin32 module 
import win32com.client
  
# Opening Excel software using the win32com 
File = win32com.client.Dispatch("Excel.Application")
  
# Optional line to show the Excel software 
File.Visible = 1
  
# Opening your workbook 
Workbook = File.Workbooks.open("C:/Users/na.lang/Desktop/Book1.xlsx")
  
# Refeshing all the shests 
Workbook.RefreshAll()
  
# Saving the Workbook 
Workbook.Save()
  
# Closing the Excel File
File.Quit()

上面的脚本保存为refresh.py，可以写进.bat文件，只需要每次点击bat文件执行即可

C:\Users\XX\AppData\Local\python.exe C:\Users\XX\Desktop\refresh.py

如果需要定时刷新，如下是每10s刷新一下，同时打印刷新时间、文件大小

#!/bin/python3

# Importing the pywin32 module 
import win32com.client

# 导入线程模块
import threading
import time
import os

def thread_Timer():     
    # Opening Excel software using the win32com 
    File = win32com.client.Dispatch("Excel.Application")
    
    # Optional line to show the Excel software 
    #File.Visible = 1
    try:
        # Opening your workbook 
        Workbook = File.Workbooks.open("C:/Users/XXX/Desktop/Book1.xlsx")
        size = os.path.getsize('C:/Users/XXX/Desktop/Book1.xlsx')
        size = size/float(1024*1024)
        size = round(size,2)
        size = str(size) + "MB"
        # Refeshing all the shests 
        Workbook.RefreshAll()
        
        # Saving the Workbook 
        Workbook.Save()
        
        # Closing the Excel File
        #Workbook.Close(SaveChanges=True)
        File.Quit()
        print(time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time())), size)
        # 声明全局变量
        global t1
        # 创建并初始化线程
        t1 = threading.Timer(10,thread_Timer)
        # 启动线程
        t1.start()
    except:
        time.sleep(5)
      
    
if __name__ == "__main__":
    # 创建并初始化线程
    try:
        t1 = threading.Timer(10,thread_Timer)
        # 启动线程
        t1.start()
    except AttributeError:
        time.sleep(5)

如下是试了一下AI写的刷新脚本，可参考

import time
import openpyxl
def refresh excel(file path):
# 打开Excel文件
workbook = openpyxl.load workbook(file path)
sheet = workbook.active
while True:
# 刷新数据
sheet.calculate dimension()
# 保存Excel文件
workbook.save(file_path)
# 休眠一段时间(例如每5分钟刷新一次)
time.sleep(300)
# 调用函数并传入Excel文件路径refresh_excel('path to excel file.xlsx')