How to password protect an excel file but allow read only in python?

Question

I'm working on automatization in python and and I already created a read only excel file but is it possible to make it password protect to write?

I've made it this far

with pd.ExcelWriter("asd.xlsx", engine="xlsxwriter") as excel_writer:
    df4.to_excel(excel_writer, index=False, sheet_name="Sheet1")
    excel_writer.book.read_only_recommended()

Answer 1

You may accomplish the task using Aspose.Cells for Python via Java easily. See the following sample code that will make the Excel file write-protected with password for your reference. e.g. Sample code:

import jpype
import asposecells
jpype.startJVM()
from asposecells.api import Workbook

# Load the MS Excel file.
workbook = Workbook("asd.xlsx")
    

# Write protect workbook with password.
workbook.getSettings().getWriteProtection().setPassword("1234");

# Specify author while write protecting workbook.
workbook.getSettings().getWriteProtection().setAuthor("testauthor1");


# Save the write-protected Excel file.
workbook.save("out_protected-asd.xlsx")

When you open the output file into MS Excel manually, you will be prompted to specify the write-protected password. If you want to change/update the contents in the file, you will give the correct password. Otherwise, you can open the file as "Read only" for read-only purpose.

You may also post your queries or comments in the dedicated forums.

PS. I am working as Support developer/ Evangelist at Aspose.

Answer 2

There is a myriad of ways you could accomplish this task. This answer has made several assumptions...you could only add password protection to an entire workbook, a specific excel worksheet, etc. There are a few things about your question that was not clear.

For example, would you like to accomplish the password protection using the Pandas library only? If so, then that is not possible (reference source) or if using another library to modify the workbook or sheet would suffice. I assumed the latter.

Some potential solutions are to add the password protection to your workbook using the Aspose.Cells library.

ASPOSE Protection Types:

• ALL - User cannot modify anything.
• CONTENTS - User cannot enter data.
• OBJECTS - User cannot modify drawing objects.
• SCENARIOS - User cannot modify saved scenarios.
• STRUCTURE - User cannot modify saved structure.
• WINDOWS - User cannot modify saved windows.
• NONE - No protection.

#Libraries
import jpype
import asposecells
import pandas as pd

jpype.startJVM()
from asposecells.api import Workbook, ProtectionType

#Load Excel File
workbook = Workbook("asd.xlsx")
    
#Add password protection
workbook.protect(ProtectionType.STRUCTURE, "password")

#Save the protected excel file
workbook.save("protected-asd.xlsx")

#Continue with Pandas dataframe here using the protected file...
with pd.ExcelWriter("protected-asd.xlsx", engine="xlsxwriter") as excel_writer:
df4.to_excel(excel_writer, index=False, sheet_name="Sheet1")

Answer 3

You need to open it by password using Python like:

import io
import openpyxl
import msoffcrypto

decryptedWorkbook = io.BytesIO()

with open('yourFile.xlsx', 'rb') as file:
    excelFile = msoffcrypto.OfficeFile(file)
    excelFile.load_key(password='your password')
    excelFile.decrypt(decryptedWorkbook)

myWorkbook = openpyxl.load_workbook(filename=decryptedWorkbook)