Python 3读取实时更新Excel文件的问题求助

Hey there! I’ve run into this exact problem before—when dealing with Excel files that update every second, it’s super frustrating to keep pulling old data even after re-running your read code. Let’s break down why this happens and how to fix it.

Why You’re Getting Old Data #

Most likely, the issue boils down to unclosed file handles or caching—either from your Python code holding onto the old file reference, or the system/Excel itself caching the file content. When you re-run pd.read_excel without properly closing the file first, you’re just reading the cached version instead of the updated file.

Solutions to Try #

1. Use a with Statement to Automatically Close the File #

This is the simplest fix. The with context manager ensures the file is properly closed after each read, so you don’t leave lingering handles that cache old data.

import pandas as pd

def fetch_latest_excel_data(file_path, sheet_name="Default"):
    # Open the file within a with block to guarantee it gets closed
    with open(file_path, "rb") as excel_file:
        parsed_data = pd.read_excel(excel_file, sheet_name=sheet_name)
    return parsed_data

# Example usage: call this function every time you need fresh data
latest_data = fetch_latest_excel_data("PATH TO YOUR FILE")
print(latest_data)

2. Switch to the openpyxl Engine (For .xlsx Files) #

If you’re working with .xlsx files, the default xlrd engine (especially older versions) can have caching issues. Using openpyxl instead often resolves this, as it reads directly from the file each time without holding onto old references.

First, install openpyxl if you haven’t already:

pip install openpyxl

Then update your code:

import pandas as pd

def get_fresh_data(file_path):
    # Specify openpyxl as the engine for .xlsx files
    fresh_df = pd.read_excel(file_path, sheet_name="Default", engine="openpyxl")
    return fresh_df

# Test it out by reading every second
import time
for _ in range(5):
    print(get_fresh_data("PATH TO YOUR FILE"))
    time.sleep(1)

3. Ensure the Excel File Isn’t Locked by Another Process #

If the Excel file is open in the Excel desktop app (or another program) while it’s being updated, the app might be holding a lock on the file, preventing Python from reading the latest changes. Make sure:

• The Excel app is closed when the file is being updated
• The program that’s updating the file properly closes the file after each save (no lingering handles there either)

4. Force Flush System File Cache (Windows Only) #

On Windows, the OS sometimes caches file content to improve performance. If the above fixes don’t work, you can force a cache flush using Windows API calls. This is a bit more advanced, but useful for stubborn cases:

import pandas as pd
import ctypes
from ctypes import wintypes

def flush_windows_file_cache(file_path):
    kernel32 = ctypes.WinDLL('kernel32', use_last_error=True)
    
    # Open the file with flags to bypass caching
    handle = kernel32.CreateFileW(
        file_path,
        wintypes.DWORD(0x80000000 | 0x40000000),  # Read + Write access
        wintypes.DWORD(0),
        None,
        wintypes.DWORD(3),  # Open existing file
        wintypes.DWORD(0x20000000 | 0x80000000),  # No buffering + write through
        None
    )
    
    if handle == wintypes.HANDLE(-1).value:
        raise ctypes.WinError(ctypes.get_last_error())
    
    # Flush the cache and close the handle
    kernel32.FlushFileBuffers(handle)
    kernel32.CloseHandle(handle)

def get_updated_data(file_path):
    flush_windows_file_cache(file_path)
    with open(file_path, "rb") as f:
        return pd.read_excel(f, sheet_name="Default")

Final Tips #

Start with the first two solutions—they’re the most likely to fix your issue without extra complexity. If you’re working with .xls files instead of .xlsx, stick with the with statement approach, as openpyxl doesn’t support .xls.

内容的提问来源于stack exchange，提问作者user9514996