Python实现SQL数据导入Excel及透视表自动刷新的技术求助

I’ve run into similar headaches before when automating Excel updates with pandas—pandas is fantastic for data handling, but it doesn’t cover all of Excel’s advanced features like sheet visibility or pivot table refreshes. Here’s a step-by-step solution combining pyodbc/pandas for data fetching, openpyxl for sheet manipulation, and win32com for pivot table refreshes (it’s the most reliable way to refresh pivots programmatically):

1. Setup Dependencies #

First, make sure you have all required libraries installed:

pip install pyodbc pandas openpyxl pywin32

2. Fetch SQL Data #

Use pyodbc to connect to your SQL database and pull data into pandas DataFrames. Map your SQL tables to their corresponding Excel data sheets for easy batch processing:

import pyodbc
import pandas as pd

# Configure your SQL connection (adjust driver/server/db credentials as needed)
conn_str = (
    "DRIVER={SQL Server};"
    "SERVER=your_sql_server_name;"
    "DATABASE=your_database_name;"
    "UID=your_username;"
    "PWD=your_password"
)

# Map SQL table names to Excel data sheet names
table_sheet_map = {
    "sql_table_1": "data_sheet_1",
    "sql_table_2": "data_sheet_2",
    # Add the remaining 12 table-sheet pairs here
}

# Fetch data from all SQL tables
data_frames = {}
with pyodbc.connect(conn_str) as conn:
    for table_name, sheet_name in table_sheet_map.items():
        query = f"SELECT * FROM {table_name}"
        data_frames[sheet_name] = pd.read_sql(query, conn)

3. Update Excel Data Sheets (Unhide → Replace Data → Re-hide) #

Use openpyxl to modify the Excel workbook—this library lets you control sheet visibility and manipulate cell data directly:

from openpyxl import load_workbook
from openpyxl.utils.dataframe import dataframe_to_rows

# Load your existing workbook
wb = load_workbook("your_workbook.xlsx")

for sheet_name, df in data_frames.items():
    # Access the data sheet and unhide it
    ws = wb[sheet_name]
    ws.sheet_state = "visible"  # Unhides the sheet (works for both hidden/veryHidden)
    
    # Clear all old data (delete all rows with content)
    if ws.max_row > 0:
        ws.delete_rows(1, ws.max_row)
    
    # Write new data from the DataFrame (includes headers)
    for row in dataframe_to_rows(df, index=False, header=True):
        ws.append(row)
    
    # Re-hide the sheet (use "veryHidden" if you want to prevent manual unhiding)
    ws.sheet_state = "hidden"

# Save the updated workbook (we'll use this for pivot table refreshes next)
wb.save("updated_workbook.xlsx")

4. Refresh Pivot Tables #

For pivot table refreshes, win32com is the most reliable tool—it mimics Excel VBA behavior perfectly:

import win32com.client as win32
import os

# Get the full path to your updated workbook to avoid path issues
file_path = os.path.abspath("updated_workbook.xlsx")

# Launch Excel in background mode
excel = win32.Dispatch("Excel.Application")
excel.Visible = False  # Set to True if you want to see the process

try:
    # Open the workbook
    wb = excel.Workbooks.Open(file_path)
    
    # Refresh all pivot tables across all report sheets
    for sheet in wb.Sheets:
        for pivot in sheet.PivotTables():
            pivot.RefreshTable()
    
    # Save and close the workbook
    wb.Save()
finally:
    # Ensure Excel process is closed even if an error occurs
    wb.Close()
    excel.Quit()
    # Clean up COM object to avoid lingering Excel processes
    del excel

Key Troubleshooting Tips #

• Can’t unhide sheets? If your sheets were set to veryHidden (not just regular hidden), ws.sheet_state = "visible" still works to unhide them.
• Old data not being cleared? If ws.max_row returns 0 (e.g., sheet has empty rows but no data), you can use df.shape[0] + 1 to delete enough rows to cover the new data.
• Pivot tables not refreshing? Make sure your pivot tables are linked to the correct data sheets, and that the Excel file isn’t open in another process when running the script.

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