将DataFrame导入已有Excel：Jupyter正常VS Code异常问题排查

核心问题 #

代码在Jupyter Notebook中可正常运行，但在VS Code里无法将数据集导入Excel文件，以下是原代码及问题分析：

import pyodbc
import pandas as pd
from datetime import datetime
import win32com.client as win32
import openpyxl

#SQL Server connection established

cnxn = pyodbc.connect(
    "Driver={SQL Server};",
    "Server=\"ip\";",
    "Database='';",
    "UID=Jaydeb.bhunia;",
    "PWD=password;",
    "Trused_Connection=yes")

#Query executie and export csv
query = pd.read_sql_query('''select top 10 * from CashOrderTrn(nolock)''',cnxn)
df = pd.DataFrame(query)

#Export output to local folder
df.to_excel(r"C:\Users\THL1012\Desktop\Python Output\FTD_MTD_Sales.xlsx", sheet_name='Sheet1')

#data import to master file
book = openpyxl.load_workbook(r'C:\Users\THL1012\Desktop\Daily Alert_Automation\FTD_WTD_MTD_Sales.xlsx')
df = pd.read_excel(r"C:\Users\THL1012\Desktop\Python Output\FTD_MTD_Sales.xlsx", sheet_name='Sheet1', index_col=[0,1])

with pd.ExcelWriter(r"C:\Users\THL1012\Desktop\Daily Alert_Automation\FTD_WTD_MTD_Sales.xlsx", mode="w",
                    engine="openpyxl", ) as writer:
    writer.book = book
    writer.sheets = {ws.title:ws for ws in book.worksheets}
    df.to_excel(writer, sheet_name='import')

问题排查与解决 #

1. 修复已知拼写错误 #

代码中Trused_Connection=yes是拼写错误，必须改为Trusted_Connection=yes，该错误会直接导致SQL Server连接失败。

2. 环境依赖版本不一致 #

Jupyter和VS Code可能使用了不同的Python虚拟环境，导致依赖库版本差异：

• 在VS Code终端执行以下命令，查看当前环境的依赖版本：

  pip list | findstr "pyodbc pandas openpyxl"
  

• 对比Jupyter内核环境的依赖版本，确保两者一致，必要时重新安装对应版本的库。

3. 文件权限与占用问题 #

• 检查目标Excel文件是否被其他程序（如Excel客户端）打开，文件被占用时无法完成写入操作。
• 确认VS Code运行的用户账户对目标目录（Python Output和Daily Alert_Automation）有读写权限。

4. ExcelWriter模式冲突 #

代码中使用mode="w"（覆盖模式）加载已存在的工作簿，逻辑上存在冲突，会清空原文件内容后再写入，应改为mode="a"（追加模式），并处理工作表已存在的情况：

with pd.ExcelWriter(r"C:\Users\THL1012\Desktop\Daily Alert_Automation\FTD_WTD_MTD_Sales.xlsx", 
                    mode="a", engine="openpyxl", if_sheet_exists="replace") as writer:
    writer.book = book
    writer.sheets = {ws.title:ws for ws in book.worksheets}
    df.to_excel(writer, sheet_name='import', index=False)

5. 路径兼容性问题 #

虽然使用了原始字符串（r"路径"），仍可尝试将反斜杠替换为正斜杠，避免潜在的转义问题：

r"C:/Users/THL1012/Desktop/Python Output/FTD_MTD_Sales.xlsx"

修正后的完整代码示例 #

import pyodbc
import pandas as pd
import openpyxl

# 修正SQL连接字符串拼写错误
cnxn = pyodbc.connect(
    "Driver={SQL Server};"
    "Server='ip';"
    "Database='';"
    "UID=Jaydeb.bhunia;"
    "PWD=password;"
    "Trusted_Connection=yes"
)

# 执行查询并转换为DataFrame
query = pd.read_sql_query('select top 10 * from CashOrderTrn(nolock)', cnxn)
df = pd.DataFrame(query)

# 导出到临时Excel文件
temp_excel_path = r"C:\Users\THL1012\Desktop\Python Output\FTD_MTD_Sales.xlsx"
df.to_excel(temp_excel_path, sheet_name='Sheet1', index=False)

# 读取临时文件数据
df_import = pd.read_excel(temp_excel_path, sheet_name='Sheet1')

# 写入主文件（兼容文件不存在的情况）
master_excel_path = r"C:\Users\THL1012\Desktop\Daily Alert_Automation\FTD_WTD_MTD_Sales.xlsx"
try:
    book = openpyxl.load_workbook(master_excel_path)
    with pd.ExcelWriter(master_excel_path, mode="a", engine="openpyxl", if_sheet_exists="replace") as writer:
        writer.book = book
        writer.sheets = {ws.title: ws for ws in book.worksheets}
        df_import.to_excel(writer, sheet_name='import', index=False)
except FileNotFoundError:
    df_import.to_excel(master_excel_path, sheet_name='import', index=False)

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