How to open a password protected excel file using python?

Question

I looked at the previous threads regarding this topic, but they have not helped solve the problem.

I'm trying to open a password protected file in excel without any user interaction. I searched online, and found this code which uses win32com.client When I run this, I still get the prompt to enter the password...

from xlrd import *
import win32com.client
import csv
import sys

xlApp = win32com.client.Dispatch("Excel.Application")
print "Excel library version:", xlApp.Version
filename,password = r"\\HRA\Myfile.xlsx", 'caa team'
xlwb = xlApp.Workbooks.Open(filename, Password=password)

eidorb · Accepted Answer · 2021-03-30 22:13:53Z

38

I recently discovered a Python library that makes this task simple.

It does not require Excel to be installed and, because it's pure Python, it's cross-platform too!

msoffcrypto-tool supports password-protected (encrypted) Microsoft Office documents, including the older XLS binary file format.

Install msoffcrypto-tool:
```
 pip install msoffcrypto-tool
```
You could create an unencrypted version of the workbook from the command line:
```
msoffcrypto-tool Myfile.xlsx Myfile-decrypted.xlsx -p "caa team"
```

Or, you could use msoffcrypto-tool as a library. While you could write an unencrypted version to disk like above, you may prefer to create an decrypted in-memory file and pass this to your Python Excel library (openpyxl, xlrd, etc.).

import io

import msoffcrypto
import openpyxl


decrypted_workbook = io.BytesIO()

with open('Myfile.xlsx', 'rb') as file:
    office_file = msoffcrypto.OfficeFile(file)
    office_file.load_key(password='caa team')
    office_file.decrypt(decrypted_workbook)

# `filename` can also be a file-like object.
workbook = openpyxl.load_workbook(filename=decrypted_workbook)

answered Mar 30, 2021 at 22:13

eidorb

6411 gold badge7 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

dancow Over a year ago

I cannot upvote this solution enough. Unlike various other solutions (such as using xlwings), this method allows you to read the decrypted data with the library you're already using, e.g. pandas.read_excel(decrypted_workbook) or openpyxl.load_workbook(decrypted_workbook)

KailiC Jul 29 at 2:36

Anyone encounters this error code msoffcrypto.exceptions.FileFormatError: Not an OLE file?

user459872 · Accepted Answer · 2020-08-28 09:34:36Z

26

I don't think that named parameters work in this case. So you'd have to do something like:

xlwb = xlApp.Workbooks.Open(filename, False, True, None, password)

See http://msdn.microsoft.com/en-us/library/office/ff194819.aspx for details on the Workbooks.Open method.

edited Aug 28, 2020 at 9:34

user459872

25.9k4 gold badges51 silver badges74 bronze badges

answered Jan 21, 2014 at 13:39

Bjoern Stiel

4,1911 gold badge25 silver badges20 bronze badges

Comments

GpandaM · Accepted Answer · 2020-04-17 09:38:53Z

1

If your file size is small, you can probably save that as ".csv". and then read

It worked for me :)

answered Apr 17, 2020 at 9:38

GpandaM

474 bronze badges

Comments

Raj · Accepted Answer · 2019-06-19 07:41:00Z

-1

Openpyxl Package works if you are using linux system. You can use secure the file by setting up a password and open the file using the same password.

For more info: https://www.quora.com/How-do-I-open-read-password-protected-xls-or-xlsx-Excel-file-using-python-in-Linux

answered Jun 19, 2019 at 7:41

Raj

6334 gold badges18 silver badges32 bronze badges

2 Comments

Chuck Over a year ago

No, this doesn't work. I looked and found that Openpyxl cannot open a password-protected Excel.

Laszlo Marai Over a year ago

This link doesn't point to a specific answer but to the Quora question itself and there are a bunch of guesses (and ignorant non-answers) there. There may have been only a single answer when you added the link but this is definitely not the case anymore.

pnv · Accepted Answer · 2022-01-04 10:31:15Z

-2

Thank you so much for the great answers on this topic. Trying to collate all of it. My requirement was to open a bunch of password protected excel files ( all had same password ) so that I could do some more processing on those. Please find the code below.

import pandas as pd
import os

from xlrd import *
import win32com.client as w3c
import csv
import sys
from tempfile import NamedTemporaryFile

    df_list=[]
#    print(len(files))
    for f in files:
#    print(f)
    if('.xlsx' in f):


        xlwb = xlapp.Workbooks.Open('C:\\users\\files\\'+f, False, True, None, 'password')

        temp_f = NamedTemporaryFile(delete=False, suffix='.csv')  
        temp_f.close()
        os.unlink(temp_f.name)  

        xlwb.SaveAs(Filename=temp_f.name, FileFormat=xlCSVWindows) 
        df = pd.read_csv(temp_f.name,encoding='Latin-1')  # Read that CSV from Pandas
        df.to_excel('C:\\users\\files\\password_removed\\'+f)

edited Jan 4, 2022 at 10:31

answered Apr 9, 2021 at 5:52

pnv

1,5093 gold badges25 silver badges58 bronze badges

1 Comment

Chuck Over a year ago

FYI this doesn't work as is, you gotta set xlapp as you did originally

Collectives™ on Stack Overflow

How to open a password protected excel file using python?

5 Answers 5

2 Comments

Comments

Comments

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related