Decrypt XLS file with Python for pandas

Question

I am trying to decrypt an XLS file to be read into a Dataframe. I am on a Linux system so xlwings and win32com is not possible. I also tried with msoffcrypto-tool but it seems that XLS is still in experimental so it did not work with my file either. Have anybody ever had this problem before?

Answer 1

I faced a very similar problem as you. I'm not sure what issues you were having with your xls file, but I was able to use msoffcrypto-tool fairly easily. I'm hoping that my solution will help you in figuring out your issue and may also help others facing similar issues. In my situation, I was able to open the xls file in excel without being prompted for a password which meant that the workbook is was encrypted with the default password 'VelvetSweatshop'

import pandas as pd
import io
import msoffcrypto

file_path = "test.xls"
decrypted = io.BytesIO()
with open(file_path, "rb") as f:
    data = msoffcrypto.OfficeFile(f)
    # Default passwords for encrypted excel sheets
    # Add your password here if it differs than the default
    data.load_key(password="VelvetSweatshop")
    data.decrypt(decrypted)
f = pd.read_excel(decrypted)
f.to_csv("test.csv")

Answer 2

Plenty of people want to encrypt/decrypt files. You will need to know what it was encrypted with and possibly other information in order to decrypt it.

here is an example of the process for b64decoding:

import base64
import io
import pandas as pd

with open('encoded_data.txt','rb') as d:
    data=d.read()
print(data)
decrypted=base64.b64decode(data)
print(decrypted)

xls_filelike = io.BytesIO(decrypted)
df = pd.read_excel(xls_filelike, worksheet=0)