Pandas unable to open this Excel file

I am trying to use python pandas to open an Excel file. Code is simple as shown below;

import pandas as pd df = pd.read_excel('../TestXLWings.xlsm', sheetname="TestSheet") 

I got an error below;

Traceback (most recent call last): File "C:\Program Files\JetBrains\PyCharm Community Edition 2017.2\helpers\pydev\pydevd.py", line 1599, in <module> globals = debugger.run(setup['file'], None, None, is_module) File "C:\Program Files\JetBrains\PyCharm Community Edition 2017.2\helpers\pydev\pydevd.py", line 1026, in run pydev_imports.execfile(file, globals, locals) # execute the script File "C:\Program Files\JetBrains\PyCharm Community Edition 2017.2\helpers\pydev\_pydev_imps\_pydev_execfile.py", line 18, in execfile exec(compile(contents+"\n", file, 'exec'), glob, loc) File "C:/Users/testing/Dropbox/Test-XLwings/test.py", line 3, in <module> df = pd.read_excel('../TestXLWings.xlsm', sheetname="TestSheet") File "C:\ProgramData\Anaconda3\lib\site-packages\pandas\io\excel.py", line 203, in read_excel io = ExcelFile(io, engine=engine) File "C:\ProgramData\Anaconda3\lib\site-packages\pandas\io\excel.py", line 260, in __init__ self.book = xlrd.open_workbook(io) File "C:\ProgramData\Anaconda3\lib\site-packages\xlrd\__init__.py", line 441, in open_workbook ragged_rows=ragged_rows, File "C:\ProgramData\Anaconda3\lib\site-packages\xlrd\book.py", line 87, in open_workbook_xls ragged_rows=ragged_rows, File "C:\ProgramData\Anaconda3\lib\site-packages\xlrd\book.py", line 595, in biff2_8_load raise XLRDError("Can't find workbook in OLE2 compound document") xlrd.biffh.XLRDError: Can't find workbook in OLE2 compound document 

My Excel file is xlsm and protected by password. What does OLE2 compound document mean exactly? Does pandas have problems opening this kind of Excel files? I am using python v3.6

2 Answers

I will answer my own question. In one of the comments from ayhan, Excel-protected files cannot be read by xlrd. One solution is to remove the protection.

I need the command to unprotect an Excel file from python

Another solution to read the Excel-protected file is to use xlwings. I have verified that xlwings is able to read protected Excel files when the Excel file is opened.

I would create a new excel file and remove sensitivity label in excel. Then be able to read the file with pd.