Prev: latex equations with publish
Next: CRC check, Modbus for xPC Target
From: Naftali Herscovici on 19 Jul 2010 10:07
Hello

I am running an optimization and I would like to store the results of each iteration in a SEPARATE worksheet in excel.
Please find below the short version of the code relevant to the excel part.
I’m trying to get a collection of 50 sheets, named “Calc1” to “Calc50” with the appropriate data in each of them. For some reason After 2 counts the data gets overwritten on the same sheet. Please advice.

Thanks

Tuli
============ CODE STARTS ================
% This works checking the loop
%
% Test_xbit=1
%
% X= VARIABLES VALUES
% E:\Quick\20100316_0343PM_Create_Structure_Type_Organized_Symmetry_Arje\Matlab
% Create_Structure_Type_Organized_Symmetry_Loop_20100513_0307PM.m - TBD
% location of 3 quarters
%
% 1. Open file using Dialog
% 2. Check existence of directory, lock and auto
% 3. if exists then Delete
% 4. Sets Project
% 5. Sets Project
% 6. Sets Design
% 7. Creates a Box
% 8. SaveAs
% 9. Quit Application
%
% UPDATES
%
% 20100526 - LIFT LAYER 1 BY ddz TO AVOID SHORT
% 20100524 - use fixed directories
% 20100524 - defined Q=fo/(f2-f1) as goal function
% 20100513 - Matlab_HFSS_Create_Box WORKS!
% 20100514 - Change names to an array
% 20100528 - Use Brad's routine
% 20100528 - Export Z also
% had 8 numbers now 16
% made sure the connector is not shorted. ALWAYS the xbit of maximum index
% THIS WORKS. SOLID PROBLEMS APPEAR IN 3rd CALC. MOVE TO HEMISPHERE
% 20100531 0217PM works OK. now need change order of mirror

% on layer 1 are zero
clear all;
clc
global globstat icalc fidx B_P Q_P F_P Q_8
icalc=0;
path_fln=pwd;
path_prj=strcat(pwd,'\prj\');
filenames=textread('mypath.txt','%s');
path=filenames(1);
path=pwd;
file=filenames(2);
gicu{1}=strcat(path,'\Arje_Functions\');
addpath(gicu{1});
for n=6:23
title{n}=filenames{n-3};
end
title{1}='Now';
title{2}='Freq[GHz]';
title{3}='S11 [dB]';
title{4}='Re[Zin]';
title{5}='Im[Zin]';
%======================================= EXCEL INITIALIZATION STARTS
%% PARAMETERS (if you make this a function, these should be inputs)
%% OPEN EXCEL APPLICATION
h = actxserver('Excel.Application');
% Show the Excel window
set(h, 'Visible', 1);
%% INSERT NEW WORKBOOK
W = h.Workbooks.Add;

%======================================= EXCEL INITIALIZATION ENDS
data_file1=cell(256);
data_file2=cell(256);
gicu{1}=cell(256);
%^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
for p=1:50 %<<<<<<<<<<<<<<<<<<<<<<<< P LOOP STARTS >>>>>>>>>>>>>>>>>>>>>>>>
%^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
file_prj=strcat('test',num2str(p),'.hfss');
icalc=icalc+1;
%% WORKBOOKS CONTAIN WORKSHEETS
Sheets = h.ActiveWorkBook.Sheets;
% Add a fourth sheet (by default, a workbook contains
% three worksheets - add a new one before [], after #3)

if icalc>2
Sheets.Add( [], Sheets.Item(1) )
end

%% EXCEL SECTION STARTS


%% ADD DATA AND CHARTS


%% Rename
Sheets.Item(icalc).Name = ['Calc' int2str(icalc)];


%% Make it "Active"
Sheets.Item(icalc).Activate;
Activesheet = h.Activesheet;


%% Insert data
%ActivesheetRange = get(Activesheet,'Range',['A' num2str(ii)],['A' num2str(ii)]);

% TITLES
t4=datestr(now);
for l=1:23
scell=xlsColNum2Str(l);
R1=strcat(scell,'1');
R2=R1;
riko=strcat(R1,':',R2);
ActivesheetRange = get(Activesheet,'Range',riko{1});
set(ActivesheetRange, 'Value',title{l});
end

% DATE & TIME

R1=strcat('A','2');
R2=char(strcat('A','2'));
riko=strcat(R1,':',R2);
ActivesheetRange = get(Activesheet,'Range',riko);
tiko=t4(1:12);
set(ActivesheetRange, 'Value', tiko);
R1=strcat('A','3');
R2=char(strcat('A','3'));
riko=strcat(R1,':',R2);
diko=t4(13:20);
ActivesheetRange = get(Activesheet,'Range',riko);
set(ActivesheetRange, 'Value',diko);


% EXCEL SECTION ENDS

end % BIG LOOP END




%% SAVE WORKBOOK
% *Full path name should be used*
invoke( W, 'SaveAs', file_xls);


%% CLEAN UP
% Terminate the server session to which the handle is atttached
invoke( h, 'Quit' );
% Release all interfaces derived from the server
delete(h)
=========== CODE ENDS ============
From: someone on 19 Jul 2010 10:31
"Naftali Herscovici" <tuli01(a)hotmail.com> wrote in message <i21m6a$qlq$1(a)fred.mathworks.com>...
> Hello
>
> I am running an optimization and I would like to store the results of each iteration in a SEPARATE worksheet in excel.
> Please find below the short version of the code relevant to the excel part.
> I&#8217;m trying to get a collection of 50 sheets, named &#8220;Calc1&#8221; to &#8220;Calc50&#8221; with the appropriate data in each of them. For some reason After 2 counts the data gets overwritten on the same sheet. Please advice.
>
> Thanks
>
> Tuli
> ============ CODE STARTS ================
> % This works checking the loop
> %
> % Test_xbit=1
> %
> % X= VARIABLES VALUES
> % E:\Quick\20100316_0343PM_Create_Structure_Type_Organized_Symmetry_Arje\Matlab
> % Create_Structure_Type_Organized_Symmetry_Loop_20100513_0307PM.m - TBD
> % location of 3 quarters
> %
> % 1. Open file using Dialog
> % 2. Check existence of directory, lock and auto
> % 3. if exists then Delete
> % 4. Sets Project
> % 5. Sets Project
> % 6. Sets Design
> % 7. Creates a Box
> % 8. SaveAs
> % 9. Quit Application
> %
> % UPDATES
> %
> % 20100526 - LIFT LAYER 1 BY ddz TO AVOID SHORT
> % 20100524 - use fixed directories
> % 20100524 - defined Q=fo/(f2-f1) as goal function
> % 20100513 - Matlab_HFSS_Create_Box WORKS!
> % 20100514 - Change names to an array
> % 20100528 - Use Brad's routine
> % 20100528 - Export Z also
> % had 8 numbers now 16
> % made sure the connector is not shorted. ALWAYS the xbit of maximum index
> % THIS WORKS. SOLID PROBLEMS APPEAR IN 3rd CALC. MOVE TO HEMISPHERE
> % 20100531 0217PM works OK. now need change order of mirror
>
> % on layer 1 are zero
> clear all;
> clc
> global globstat icalc fidx B_P Q_P F_P Q_8
> icalc=0;
> path_fln=pwd;
> path_prj=strcat(pwd,'\prj\');
> filenames=textread('mypath.txt','%s');
> path=filenames(1);
> path=pwd;
> file=filenames(2);
> gicu{1}=strcat(path,'\Arje_Functions\');
> addpath(gicu{1});
> for n=6:23
> title{n}=filenames{n-3};
> end
> title{1}='Now';
> title{2}='Freq[GHz]';
> title{3}='S11 [dB]';
> title{4}='Re[Zin]';
> title{5}='Im[Zin]';
> %======================================= EXCEL INITIALIZATION STARTS
> %% PARAMETERS (if you make this a function, these should be inputs)
> %% OPEN EXCEL APPLICATION
> h = actxserver('Excel.Application');
> % Show the Excel window
> set(h, 'Visible', 1);
> %% INSERT NEW WORKBOOK
> W = h.Workbooks.Add;
>
> %======================================= EXCEL INITIALIZATION ENDS
> data_file1=cell(256);
> data_file2=cell(256);
> gicu{1}=cell(256);
> %^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> for p=1:50 %<<<<<<<<<<<<<<<<<<<<<<<< P LOOP STARTS >>>>>>>>>>>>>>>>>>>>>>>>
> %^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> file_prj=strcat('test',num2str(p),'.hfss');
> icalc=icalc+1;
> %% WORKBOOKS CONTAIN WORKSHEETS
> Sheets = h.ActiveWorkBook.Sheets;
> % Add a fourth sheet (by default, a workbook contains
> % three worksheets - add a new one before [], after #3)
>
> if icalc>2
> Sheets.Add( [], Sheets.Item(1) )
> end
>
> %% EXCEL SECTION STARTS
snip ...

I'm not sure, but from the description of you problem,
my first GUESS whould be to change:
> Sheets.Add( [], Sheets.Item(1) )
to something like:
> Sheets.Add( [], Sheets.Item(icalc) )

Again, thats just a wild guess.
From: Andy on 19 Jul 2010 10:47
someone has basically identified the issue. The line:

Sheets.Add( [], Sheets.Item(1) )

always adds your new sheet as the second sheet. This is not a problem (it will always add a new sheet), but later you have:

Sheets.Item(icalc).Name = ['Calc' int2str(icalc)];
% ...
Sheets.Item(icalc).Activate;

This will always update the name of the LAST sheet and then activate it. (Actually, it might be off by an index or two. Perhaps it always updates the third to last sheet or something like that. But in any case, it will update the same sheet every time, since you increase the sheet count and the icalc number in each iteration.)

If you just change these lines to:

Sheets.Item(2).Name = ['Calc' int2str(icalc)];
% ...
Sheets.Item(2).Activate;

then you should be fine. Alternatively, if you want Sheets.Item(icalc) to be named ['Calc' int2str(icalc)], then you have to keep track of the number of sheets and change the first line instead:

n = number of total sheets;
Sheets.Add( [], Sheets.Item(n) )

P.S. Don't create Sheets inside the loop. Create it just before the loop starts, since it doesn't change from one iteration to the next.
 | 
Pages: 1
Prev: latex equations with publish
Next: CRC check, Modbus for xPC Target