How can I properly pass excel objects to functions?

3 visualizaciones (últimos 30 días)
Ryan Potter
Ryan Potter el 12 de Mzo. de 2020
I am accessing an excel sheet from MATLAB to read in some data, do some processing and edit the sheet. I had an original approach that did everything in a single script. Some of the individual operations were useful so I decided to break them out into functions. Now any changes I make to the file aren't saved?
% Check inputs
p = inputParser;
addRequired(p,'optIndexList', @(x) numel(x) == numel(flagList))
addRequired(p,'flagList', @(x) numel(x) == numel(optIndexList))
addRequired(p,'svnInfo')
parse(p,optIndexList,flagList,svnInfo)
% Open excel
e = actxserver('excel.application');
eW = e.Workbooks;
eF = eW.Open(filePath);
eS = eF.Sheets.get('Item','optIndexes');
eS.Activate;
% Find columns of interest
endCol = eS.Range('A1').End('xlToRight').Column;
colNames = eS.Range(['A1:' xlscol(endCol) '1']).Value;
[~,colPos] = ismember('svnVersion',colNames);
svnCol = xlscol(colPos);
[~,colPos] = ismember('optIndex',colNames);
indexCol = xlscol(colPos);
% Get optIndex column
allOptIndexes = getAllOptIndexes('indexCol',indexCol);
% Create svn string
svnVerString = ['URL: ' svnInfo.url ', rev: ' num2str(svnInfo.revision)];
nOpts = length(optIndexList);
for iOpt = 1:nOpts
optIndex = optIndexList(iOpt);
[~,rowPos] = ismember(optIndex,allOptIndexes);
svnCellAddress = [svnCol num2str(1+rowPos)]; % svnCol for optIndex of interest we're going to edit
switch flagList(iOpt)
case 0
% SVN verison was unchanged
% Do nothing
continue
case -1
% SVN version was changed but the code is not working
% Colour red
eS.Range(svnCellAddress).Interior.Color = RGB([255, 199, 206]);
eS.Range(svnCellAddress).Font.Color = RGB([156, 0, 6]);
case 1
% SVN version was changed and the code is working
% Colour red and overwire cell contents
eS.Range(svnCellAddress).Interior.Color = RGB([198, 239, 206]);
eS.Range(svnCellAddress).Font.Color = RGB([0, 97, 0]);
eS.Range(svnCellAddress).Value = svnVerString;
end
end
% Auto-fit column
eS.Range([svnCol '1']).EntireRow.AutoFit;
% Save and close the sheet
eF.Save;
eF.Close; % close the file
e.Quit; % close Excel entirely
end
Functions thats are called.
function allOptIndexes = getAllOptIndexes(varargin)
% Manage flexible inputs
p = inputParser;
addParameter(p,'colNames',nan)
addParameter(p,'indexCol',nan)
parse(p,varargin{:})
e = actxserver('excel.application');
eW = e.Workbooks;
eF = eW.Open(filePath);
eS = eF.Sheets.get('Item','optIndexes');
eS.Activate;
if ismember('indexCol',p.UsingDefaults)
if ismember('colNames',p.UsingDefaults)
endCol = eS.Range('A1').End('xlToRight').Column;
colNames = eS.Range(['A1:' xlscol(endCol) '1']).Value;
else
colNames = p.Results.colNames;
end
[~,colPos] = ismember('optIndex',colNames);
indexCol = xlscol(colPos);
else
indexCol = p.Results.indexCol;
end
% Get optIndex column
endRow = eS.Range([indexCol '2']).End('xlDown').Row;
allOptIndexes = cell2mat(eS.Range([indexCol '2:' indexCol num2str(endRow)]).Value);
% Close excel
eF.Close; % close the file
e.Quit; % close Excel entirely
If I do lots of these operations, the result is quite slow as excel is always being opened and closed.
e = actxserver('excel.application');
eW = e.Workbooks;
eF = eW.Open('O:\Engineering\Motor_Modelling\Optimisations\optIndexRegister.xlsb');
eS = eF.Sheets.get('Item','optIndexes');
eS.Activate;
I trield to pass eF/eS to the second function e.g.
getAllOptIndexes(eF)
However, if I do this the changes I make to the excel are no longer saved? It's as if there are two instances of the file open. Can anyone explain this? How can I get around the problem of opening and closing excel?
Many thanks,
Ryan

Respuestas (0)

Etiquetas

Productos


Versión

R2019b

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by