使用Python创建带密码保护的Excel文件（MSSQL数据导出场景）

我正尝试从MSSQL数据库提取数据，将结果保存为Excel文件，并为该文件设置密码保护。以下是我目前编写的代码（注：代码存在语法错误，已在修正版中标注）：

原始代码 #

from sqlalchemy import create_engine
import pandas as pd


Driver = 'ODBC Driver 17 for SQL Server'
Server = 'DESKTOP-BJV50NH\SQLEXPRESS'
Database = 'AdventureWorks2019'
database_con = f'mssql://@{Server}/{Database}?driver={Driver}'
    
engine = create_engine(database_con)
connection = engine.connect()

df= pd.read_sql_query("Select [jobtitle],[OrganizationLevel] from [AdventureWorks2019].[HumanResources].[Employee]",connection)
#df.to_excel("C:/Users/mrjod/Desktop/Python Training/test.xlsx")


df.to_excel("C:/Users/mrjod/Desktop/Python Training/Exporting SQL Query to Excel `Pt1.xlsx",index=False)`


import xlwings as xw

book = xw.Book("C:/Users/mrjod/Desktop/Python Training/Exporting SQL Query to Excel Pt1.xlsx")

book.api.SaveAs(r"C:/Users/mrjod/Desktop/Python Training/Exporting SQL Query to Excel `Pt2.xlsx", Password = '1234')`

修正后的代码及说明 #

原始代码因文件路径中多余的反引号触发语法错误，导致无法正常运行，以下是修正并优化后的版本：

from sqlalchemy import create_engine
import pandas as pd
import xlwings as xw  # 统一将导入语句放在顶部，符合Python规范

# 数据库连接配置
Driver = 'ODBC Driver 17 for SQL Server'
Server = 'DESKTOP-BJV50NH\SQLEXPRESS'
Database = 'AdventureWorks2019'
database_con = f'mssql://@{Server}/{Database}?driver={Driver}'

# 建立连接并提取数据（用with语句自动管理连接，避免资源泄漏）
engine = create_engine(database_con)
with engine.connect() as connection:
    df = pd.read_sql_query(
        "SELECT [jobtitle], [OrganizationLevel] FROM [AdventureWorks2019].[HumanResources].[Employee]",
        connection
    )

# 导出未加密的Excel文件
unencrypted_path = "C:/Users/mrjod/Desktop/Python Training/Exporting SQL Query to Excel Pt1.xlsx"
df.to_excel(unencrypted_path, index=False)

# 加密保存Excel文件（用with语句自动关闭工作簿）
encrypted_path = "C:/Users/mrjod/Desktop/Python Training/Exporting SQL Query to Excel Pt2.xlsx"
with xw.Book(unencrypted_path) as book:
    book.api.SaveAs(encrypted_path, Password='1234')

补充说明 #

• 若需要的是工作表编辑保护（而非打开文件的密码），可在SaveAs前添加以下代码：

  # 保护第一个工作表，设置编辑密码
  book.sheets[0].api.Protect(Password='1234', Contents=True)
  

• 使用with语句能自动释放数据库连接和Excel工作簿资源，避免内存占用问题

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