0

I am trying to write a password protected excel file with number of sheets. I want each sheet include a separate data frame that I produced as a part of my analysis. I am trying to use openpyxl to do this. Here is the code I have:

from openpyxl import Workbook
import pandas as pd

output1 = pd.DataFrame(df1)
output2 = pd.DataFrame(df2)
output3 = pd.DataFrame(df3)
output4 = pd.DataFrame(df4)

wb = Workbook()

ws1 = wb.create_sheet('Sheet1',0)
ws2 = wb.create_sheet('Sheet1',1)
ws3 = wb.create_sheet('Sheet1')
ws4 = wb.create_sheet('Sheet1')

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

wb.save(datapath + 'file.xlsx')

So, I think this produces the template I need. However, I am not sure how to assign specific data frames to appropriate sheets within the workbook. Any help would be appreciated.

Improve this question
5
  • 1
    You just need to copy the content of the dataframes into an excel sheet? stackoverflow.com/questions/36657288/… Commented Jul 28, 2022 at 11:16
  • Thanks Sembei, I didn't expect that I will have to iterate through rows of each df to copy it over to the excel; I thought it would be matter of simply pointing python to specific df and saying save it in this sheet. anyway I managed to copy the data over to the excel. However, now I noticed that the protection is not working. File opens without asking for password? Commented Jul 28, 2022 at 12:38
  • Doing this directly in openpyxl is easier, faster and more reliable than using Pandas, which has to call openpyxl anyway. Commented Jul 28, 2022 at 13:01
  • Read the Openpyxl document again. What your code will do is prevent users from viewing hidden worksheets, adding, moving, deleting, or hiding and renaming worksheets unless you go to 'Review' --> 'Changes' --> Protect Workbook' then enter the password. Commented Jul 29, 2022 at 12:02
  • Openpyxl does not support 'save as' password protection so that the workbook asks for a password on opening. The last word on this from Charlie Clark as far as I know is that it will not be supported, however as he notes an xlsx file is just a zip file so you can use that to password protect the file. Again if you are using Windows or MACOS and can use xlwings, that module will allow a password to be included with the book save. save( path=None, password=None) Commented Jul 29, 2022 at 12:52
Related questions
23
How to open a password protected excel file using python?
1
Python xlrd - open_workbook method with password protected worksheets
8
Working with Password Protected Excel Sheets in Python on Linux
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.