xlsread and password protected Excel files
43 visualizaciones (últimos 30 días)
Mostrar comentarios más antiguos
Les
el 11 de Sept. de 2014
Comentada: Ehtisham
el 28 de Jun. de 2021
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 comentario
Ehtisham
el 28 de Jun. de 2021
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.
Respuesta aceptada
Guillaume
el 11 de Sept. de 2014
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 comentario
Más respuestas (3)
Joseph Cheng
el 11 de Sept. de 2014
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 comentario
Joseph Cheng
el 11 de Sept. de 2014
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.
Ver también
Categorías
Más información sobre Spreadsheets en Help Center y File Exchange.
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!