xlsread and password protected Excel files
43 次查看(过去 30 天)
显示 更早的评论
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 个评论
Ehtisham
2021-6-28
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.
采纳的回答
Guillaume
2014-9-11
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 个评论
更多回答(3 个)
Joseph Cheng
2014-9-11
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 个评论
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.
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!