xlsread and password protected Excel files

Question

Les 2014-9-11

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/154430-xlsread-and-password-protected-excel-files

评论： Ehtisham 2021-6-28

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?

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

Ehtisham 2021-6-28

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 2014-9-11

2
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/154430-xlsread-and-password-protected-excel-files#answer_151417

在 MATLAB Online 中打开

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.

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

chengtao liu 2018-6-8

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

请先登录，再进行评论。

Answer 2

Joseph Cheng 2014-9-11

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/154430-xlsread-and-password-protected-excel-files#answer_151409

在 MATLAB Online 中打开

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.

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

Joseph Cheng 2014-9-11

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

请先登录，再进行评论。