How to open a password protected excel file using python?

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"\HRAMyfile.xlsx", 'caa team'
xlwb = xlApp.Workbooks.Open(filename, Password=password)

Contents hide

Answers:

Method 1

Method 2

Method 3

Method 4

Method 5

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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.

Method 2

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)

Method 3

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

It worked for me 🙂

Method 4

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

Method 5

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)

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating