How can I properly pass excel objects to functions?

3 次查看（过去 30 天）
Ryan Potter
Ryan Potter 2020-3-12
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

请先登录，再回答此问题。

回答（0 个）

类别

在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

产品


版本

R2019b

Community Treasure Hunt

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

Start Hunting!

Translated by