xlsread and password protected Excel files

There is nothing in the xlsread help file about passwords & the xlsprotect fuction (file exchange) does not work (all it does is hang & you have to shift to the Excel window to enter your password).

Does anybody know how to do this properly from a Matlab file?

[Ehtisham]

https://www.mathworks.com/matlabcentral/fileexchange/94983-xlsprotect-password-protect-excel-files-through-matlab

Go and download this code. It will protect the excel file with password. The files won't remain editable unless you unprotect them by providing the password.

Answer 1

[Guillaume]

The short answer is that xlsread is not written to handle password protected file. According to Microsoft documentation, when matlab opens a protected workbook Excel should prompt for a password.

You then have two options:

1. Read the excel file yourself, using COM, the save way matlab does. It's actually not that complicated if you're familiar with VBA or COM or .Net. You'd start by instantiating excel, then open the file, the access whatever range you need in whatever workbook:

excel = actxserver('excel.application');
workbook = excel.Workbooks.Open(filename, [], true, [], password);
...

2. Copy xlsread.m, xlsReadCOM.m and the private folder associated with it to a folder and add that folder to matlab path so that it shadows xlsread.

Then, edit your xlsread.m and change the function definition to

function [numericData, textData, rawData, customOutput] = xlsread(file, sheet, range, mode, customFun, password)

After the if nargin >= 5 ... end block add

if nargin >= 6
  %do argument validation if you want
else
  password = [];
end

Edit the call to xlsReadCOM to:

[numericData, textData, rawData, customOutput] = xlsreadCOM(file, sheet, range, Excel, customFun, password);

Edit xlsReadCOM.m in the private folder you've created, so that the call to OpenExcelWorkbook reads:

[~, workbookHandle] = openExcelWorkbook(Excel, file, readOnly, password);

And finally edit OpenExcelWorkbook.m (again in the private folder) so that the Open call reads:

Excel.workbooks.Open(filename, 0, readOnly, [], password);

You should then be able to read a password protected excel file with:

xlsread(filename, sheet, range, [], [], password)

Note that it will give a you warning about the fifth argument of xlsread, you can safely ignore it.

Also note, that it's untested.

[chengtao liu]

the second option was tested and it worked. Thank you very much.

Answer 2

[Joseph Cheng]

you would have to use activeX. Since getting data from excel using activeX is documented fairly well i'll leave here how to open a file with the password.

[fname pname]=uigetfile('protected.xlsx');
password = 'password';
xlsAPP = actxserver('excel.application');
xlsWB = xlsAPP.Workbooks;
xlsFile = xlsWB.Open([pname fname],[],false,[],password)

xlsFile.Visible = 1;

where i hard coded the password with password and my test xls file was protected.xlsx.

[Joseph Cheng]

there is a function on the file exchange called xlsprotect which looks to be able to unprotect and protect the file again but that maybe problematic in a security standpoint (the xls files were protected for some reason) and if program crash or hangup the reprotect wouldn't occur.

http://www.mathworks.com/matlabcentral/fileexchange/6046-xlsprotect

Answer 3

[Les]

Then you can use xls read?

Answer 4

[Les]

Nope, it still just hangs at the Excel interface