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.

peter
  • 13
  • 2
  • 1
    You just need to copy the content of the dataframes into an excel sheet? https://stackoverflow.com/questions/36657288/copy-pandas-dataframe-to-excel-using-openpyxl –  Jul 28 '22 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? – peter Jul 28 '22 at 12:38
  • Doing this directly in openpyxl is easier, faster and more reliable than using Pandas, which has to call openpyxl anyway. – Charlie Clark Jul 28 '22 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. – moken Jul 29 '22 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)** – moken Jul 29 '22 at 12:52

0 Answers0