MATLAB Answers

How to use an excel file with multiobjective optimization

3 views (last 30 days)
Artur Udovichenko
Artur Udovichenko on 9 Sep 2020
Commented: Walter Roberson on 9 Sep 2020
Hello,
I am trying to perform a multi-objective optimization using 'gamultiobj'. However, the catch is that my function is within a large excel file that contains all the data and calculations and I have certain cells in the worksheet acting as the input cells and some that are the output or 'objective' cells. I figures out that I cna use an excel server to pass data back and forth, which works fine.
However, because the excel 'function' needs to be able to talk to multiple functions in my script (initialization fcn, main objective fcn, and also the non linear constraint fcn) I was forced to make the Excel server into a global variable and define it as such in each function. However, as expected it runs very slowly now.
So, my main questions are 1) is this method (through the server feature) of using an excel function with optimization a good option? 2) is there a way to avoid using global variables or in general increase the speed of the optimizaiton in this case?
The main code is shown below
Thanks, and regards.
Artur
%% Initialize Excel and output record
clear
global Excel
global sheet1
%% Specify file name
file = 'C:\Users\Artur\Documents\Engineering\Graduate\SFPC\Optimization\Multiobjective_Optimization\HES_Optimization\SFPC OP 9.3.xlsx'; % This must be full path name
%% Open Excel Automation server
Excel = actxserver('Excel.Application');
Workbooks = Excel.Workbooks;
% Make Excel visible
Excel.Visible = 1;
% Open Excel file
Workbook = Workbooks.Open(file);
% Make first workbook open the active one
Workbook.Activate
% Make the 'design' sheet from the active workbook active
Sheets = Excel.ActiveWorkBook.Sheets;
sheet1 = get(Sheets, 'Item', 1);
%% Set up optimization function %% Open Hourly Data sheet and read data
invoke(sheet1, 'Activate');
ActivesheetRange = get(sheet1,'Range','B10');
set(ActivesheetRange, 'Value', number);
nvars=6; % number of decision variables
A=[]; % inequality constraints
b=[];
Aeq=[0 1 1 1 0 0;...
0 0 0 0 1 1;...
0 0 0 0 0 0;...
0 0 0 0 0 0;...
0 0 0 0 0 0;...
0 0 0 0 0 0]; % equality constraints for flowrates
beq=[1 1 0 0 0 0];
lb=[0.1 0 0 0 0 0]; % set upper and lower bounds on flowrates
ub=[];
% configure settings of function
options=optimoptions('gamultiobj','PopulationSize',100,'Display','iter','OutputFcn',{@Generations @ParetoFront});
%% Execute optimization function
[z,f,exit,output,pop,scores]=gamultiobj(@SFPC,nvars,A,b,Aeq,beq,lb,ub,@nlcon,options);
% save variables and figures
v=[z,f];
save(filename2,'v');
%% Save file, Close Excel and clean up
invoke(Workbook,'Save')
invoke(Excel,'Quit');
delete(Excel);
diary off
%% Main Excel function
function y=SFPC(x)
global Excel
global sheet1
%% Put MATLAB data into the active Excel
dataIN=x;
ActivesheetRange = get(sheet1,'Range',C46);
set(ActivesheetRange, 'Value', dataIN(1));
C73=append(letter,'4');
ActivesheetRange = get(sheet1,'Range',C73);
set(ActivesheetRange, 'Value', dataIN(2));
C74=append(letter,'5');
ActivesheetRange = get(sheet1,'Range',C74);
set(ActivesheetRange, 'Value', dataIN(3));
C75=append(letter,'6');
ActivesheetRange = get(sheet1,'Range',C75);
set(ActivesheetRange, 'Value', dataIN(4));
C77=append(letter,'8');
ActivesheetRange = get(sheet1,'Range',C77);
set(ActivesheetRange, 'Value', dataIN(5));
C78=append(letter,'9');
ActivesheetRange = get(sheet1,'Range',C78);
set(ActivesheetRange, 'Value', dataIN(6));
%% Put Excel results into MATLAB
E=Excel.Activesheet.Range('I15');
y(1)=E.Value;
C=Excel.Activesheet.Range('E18');
y(2)=C.Value;
end
function [c,ceq] = nlcon(x) % evaluate non linear upper bound constraints
global Excel
global sheet1
dataIN=x;
ActivesheetRange = get(sheet1,'Range',C46);
set(ActivesheetRange, 'Value', dataIN(1));
B19=append(letter,'19');
ubACH=Excel.Activesheet.Range(B19);
ubACH=ubACH.Value;
c(1)= x(1) - ubACH;
B20=append(letter,'20');
ubF=Excel.Activesheet.Range(B20);
ubF=ubF.Value;
c(2) = x(2)-ubF;
B21=append(letter,'21');
ubB=Excel.Activesheet.Range(B21);
ubB=ubB.Value;
c(3) = x(3)-ubB;
B22=append(letter,'22');
ubGh=Excel.Activesheet.Range(B22);
ubGh=ubGh.Value;
c(4) = x(4)-ubGh;
B23=append(letter,'23');
ubA=Excel.Activesheet.Range(B23);
ubA=ubA.Value;
c(5) = x(5)-ubA;
B24=append(letter,'24');
ubGc=Excel.Activesheet.Range(B24);
ubGc=ubGc.Value;
c(6) = x(6)-ubGc;
ceq = [];
end

  1 Comment

Walter Roberson
Walter Roberson on 9 Sep 2020
If you have enough memory available, read in everything you need from xlsx ahead of time and pass it around. http://www.mathworks.com/help/matlab/math/parameterizing-functions.html

Sign in to comment.

Answers (0)

Community Treasure Hunt

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

Start Hunting!

Translated by