使用Python创建受密码保护的Excel文件

Question

我正试图从mssql数据库中提取数据，将结果保存为excel，并希望将其保存为受密码保护的工作表。这就是我目前拥有的

from sqlalchemy import create_engine
import pandas as pd

Driver = 'ODBC Driver 17 for SQL Server'
Server = 'DESKTOP-BJV50NHSQLEXPRESS'
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')`

Answer 1

通过panda创建excel文档后，最好使用openpyxl python库设置密码。

这是我在互联网上找到的一个示例代码(它生成一个VBS脚本，并从您的python脚本中调用它(：

def set_password(excel_file_path, pw):
from pathlib import Path
excel_file_path = Path(excel_file_path)
vbs_script = 
f"""' Save with password required upon opening
Set excel_object = CreateObject("Excel.Application")
Set workbook = excel_object.Workbooks.Open("{excel_file_path}")
excel_object.DisplayAlerts = False
excel_object.Visible = False
workbook.SaveAs "{excel_file_path}",, "{pw}"
excel_object.Application.Quit
"""
# write
vbs_script_path = excel_file_path.parent.joinpath("set_pw.vbs")
with open(vbs_script_path, "w") as file:
file.write(vbs_script)
#execute
subprocess.call(['cscript.exe', str(vbs_script_path)])
# remove
vbs_script_path.unlink()
return None

Answer 2

我得到了错误和答案，我有"而不是"在文件路径中

我现在工作得很好！！

它打开文件，添加密码，保存然后关闭

Driver = 'ODBC Driver 17 for SQL Server'
Server = 'DESKTOP-BJV50NHSQLEXPRESS'
Database = 'AdventureWorks2019'
database_con = f'mssql://@{Server}/{Database}?driver={Driver}'

engine = create_engine(database_con)
connection = engine.connect()
dfNorthEast= pd.read_sql_query("""SELECT [group], [CustomerID],[PersonID],[StoreID],c.[TerritoryID],
[AccountNumber]FROM [AdventureWorks2019].[Sales].[Customer] as c 
inner join [AdventureWorks2019].[Sales].[SalesTerritory] as st on c.territoryid = st.territoryid
where st.[group] = 'North America'""",connection)
#df.to_excel("C:/Users/mrjod/Desktop/Python Training/test.xlsx")
dfCentral= pd.read_sql_query("""SELECT [group], [CustomerID],[PersonID],[StoreID],c.[TerritoryID],
[AccountNumber]FROM [AdventureWorks2019].[Sales].[Customer] as c 
inner join [AdventureWorks2019].[Sales].[SalesTerritory] as st on c.territoryid = st.territoryid
where st.[group] = 'Europe'""",connection)
dfFrance= pd.read_sql_query("""SELECT [group], [CustomerID],[PersonID],[StoreID],c.[TerritoryID],
[AccountNumber]FROM [AdventureWorks2019].[Sales].[Customer] as c 
inner join [AdventureWorks2019].[Sales].[SalesTerritory] as st on c.territoryid = st.territoryid
where st.[group] = 'Pacific'""",connection)
Pass = '1234'
dfNorthEast.to_excel("C:/Users/mrjod/Desktop/Python Training/Exporting SQL Query to Excel Pt1.xlsx",index=False)
dfCentral.to_excel("C:/Users/mrjod/Desktop/Python Training/Exporting SQL Query to Excel Pt2.xlsx",index=False)
dfFrance.to_excel("C:/Users/mrjod/Desktop/Python Training/Exporting SQL Query to Excel Pt3.xlsx",index=False)
book1 = xw.Book(r"C:Usersmrjod/DesktopPython TrainingExporting SQL Query to Excel Pt1.xlsx")
book1.api.SaveAs(r"C:Usersmrjod/DesktopPython TrainingExporting SQL Query to Excel Pt1 str(pass).xlsx", Password = Pass, Local = 'True')
book2 = xw.Book(r"C:Usersmrjod/DesktopPython TrainingExporting SQL Query to Excel Pt2.xlsx")
book2.api.SaveAs(r"C:Usersmrjod/DesktopPython TrainingExporting SQL Query to Excel Pt2 password.xlsx", Password = Pass, Local = 'True')
book3 = xw.Book(r"C:Usersmrjod/DesktopPython TrainingExporting SQL Query to Excel Pt3.xlsx")
book3.api.SaveAs(r"C:Usersmrjod/DesktopPython TrainingExporting SQL Query to Excel Pt3 password.xlsx", Password = Pass, Local = 'True')
book1.close()
book2.close()
book3.close()