Creating Password protected Excel file with Python-CodePudding

I am attempting to extract data from an mssql database, saving the results as an excel and would like to save it as a password protected work sheet. This is what I have so far

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')`

CodePudding user response：

It's better to use openpyxl python library to set a password after you have created an excel document via pandas.

This is a sample code I found on the internet (It generates a VBS script and calls it from within your python script):

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

CodePudding user response：

I got the error and the answer, I had "/" instead of "" in the file path

I works perfectly now!!

It opened the file, added the password, saved then closed it

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()

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:\Users\mrjod/Desktop\Python Training\Exporting SQL Query to Excel Pt1.xlsx")
book1.api.SaveAs(r"C:\Users\mrjod/Desktop\Python Training\Exporting SQL Query to Excel Pt1 str(pass).xlsx", Password = Pass, Local = 'True')

book2 = xw.Book(r"C:\Users\mrjod/Desktop\Python Training\Exporting SQL Query to Excel Pt2.xlsx")
book2.api.SaveAs(r"C:\Users\mrjod/Desktop\Python Training\Exporting SQL Query to Excel Pt2 password.xlsx", Password = Pass, Local = 'True')

book3 = xw.Book(r"C:\Users\mrjod/Desktop\Python Training\Exporting SQL Query to Excel Pt3.xlsx")
book3.api.SaveAs(r"C:\Users\mrjod/Desktop\Python Training\Exporting SQL Query to Excel Pt3 password.xlsx", Password = Pass, Local = 'True')

book1.close()
book2.close()
book3.close()