How to add password to a csv/excel created with pandas dataframe

Question

have dataframe how to encrypt it with a password

import pandas as pd 
  
# intialise data of lists. 
data = {'Name':['Tom', 'nick', 'krish', 'jack'], 'Age':[20, 21, 19, 18]} 
  
# Creating DataFrame 
df = pd.DataFrame(data)

are there any pandas options to add a password excel or csv which is created with data frame.

So that a password is required to open that csv or excel with GUI.

Tried this:

df.to_excel('123.xlsx')

from openpyxl import Workbook
from openpyxl import load_workbook

test_spreadsheet = "123.xlsx"
wb = load_workbook(test_spreadsheet)
wb.security.workbookPassword = "password"

from openpyxl import Workbook
from openpyxl import load_workbook

test_spreadsheet = "123.xlsx"
wb = load_workbook(test_spreadsheet)
ws = wb.worksheets[0]
ws.protection
ws.protection.set_password('test')
wb.save('12344.xlsx')

from openpyxl import load_workbook
wb = load_workbook(filename = '123.xlsx')

wb.security.workbookPassword = 'test'
wb.security.lockStructure = True

wb.save('123_password.xlsx')

wb = Workbook('123.xlsx') 
ws = wb.worksheets[0] 
ws.protect('abc123.xlsx')

But when i open it. the file is opening without any prompt of password . tried in google sheets and libre office

Found out that : Openpyxl's WorkbookProtection only works for preventing modifying sheets that are there.

os: Linux

Does this answer your question? [Password Protecting Excel file using Python](https://stackoverflow.com/questions/36122496/password-protecting-excel-file-using-python) — Recursing, Jul 06 '20 at 10:36
No tried all those ways. please check the codes i provided which i tried — yopmuios, Jul 06 '20 at 10:37
@S3DEV tried this `wb = Workbook('123.xlsx') ws = wb.worksheets[0] ws.protect('abc123.xlsx')` getting `list index out of range` — yopmuios, Jul 06 '20 at 11:07

score 1 · Answer 1 · answered Jun 20 '23 at 13:04

Use this:

import os
import win32com.client as w32

def encrypt_xlsx(path, password):
    xl = w32.gencache.EnsureDispatch('Excel.Application')
    path = path if os.path.isabs(path) else os.path.abspath(path)
    
    wb = xl.Workbooks.Open(path)
    xl.DisplayAlerts = False
    wb.SaveAs(path, 51, password)
    xl.Quit()

Include the import statements and also the encrypt_xlsx function. Next, call the function with the path and password.

Example:

>>> df = pd.DataFrame({'a':[1,2], 'b':[3,4]}); df.to_excel('123.xlsx')
>>> encrypt_xlsx('123.xlsx', 'password123')
>>> # try to open the Excel file and it will prompt you to enter a password.

Explanation of the code:

w32.gencache.EnsureDispatch launches the Excel Application.
os.path.abspath is responsible for changing the path to an absolute path, otherwise you might not be able to open the Excel file.
xl.Workbooks.Open opens the Excel file.
xl.DisplayAlerts = False switches off the prompts when saving to the same path.
wb.SaveAs saves the file using the original name, and 51 indicates the format of the Excel workbook. Here's the link to the Microsoft reference: https://learn.microsoft.com/en-us/previous-versions/office/developer/office-2007/bb241279(v=office.12)
xl.Quit() closes the Excel application. If you choose to remove this line, please remember to set the xl.DisplayAlerts back to True

If you want to open the Excel file or encrypt it with a strong password, then I would recommend you to use the ExcelHelper class which I wrote. Refer to my article in TowardsDataScience.

How to add password to a csv/excel created with pandas dataframe

1 Answers1