Trying to save data to excel with appropriate offset.

Hi there,

I'm trying to save an array to excel which changes size on every loop iteration. I'm currently using excel as a COM server and writing directly to it but I'm having trouble with the offset for each subsequent loop iteration.

At the moment the values save in a new row on every iteration. However I would like to save my data in one long continuous row (or column). So that the new data from each loop iteration just continues where the previous data finished.

I thought about introducing an offset of X cells but since the array changes size on every iteration there are sometimes large gaps (blank cells) in the excel file.

I think what I need is to calculate the size of the array within the loop on each iteration and have this as the offset. But I can't figure out how to do that!

Any help/ideas would be greatly appreciated!

Here is the code as it stands (there is an associated function file also but it works just fine). The bit I'm trying to fix is toward the end where %% Ampl %% is.

    clear all;

m=0;
z0=[0.;0.;1.5;1.];

    %# output file name
    fName = fullfile(pwd, 'AmplandKappa.xlsx');

    %# create Excel COM Server
    Excel = actxserver('Excel.Application');
    Excel.Visible = true;

    %# delete existing file
    if exist(fName, 'file'), delete(fName); end

    %# create new XLS file
    wb = Excel.Workbooks.Add();
    wb.Sheets.Item(1).Activate();

    % #iterations
    offset = 1;

    for K= 0.01:0.05:0.2;

    tspan=[0 2000];
    options = odeset('RelTol',1e-8);
    [t,z]=ode113(@SThetaEqu_varyK,tspan,z0,options,K);
    q=size(z(:,1));

    z0=[z(q(1),1);z(q(1),2);z(q(1),3);z(q(1),4)];

    tspan=[0 3000];
    [t,z]=ode113(@SThetaEqu_varyK,tspan,z0,options,K);
    ty=size(t)
    x=z(:,1);
    y=z(:,2);
    na=z(:,3);
    nb=z(:,4);
    q=size(z(:,1));
    z0=[z(q(1),1);z(q(1),2);z(q(1),3);z(q(1),4)];

    %%%%%%POINCARE SECTION%%%%%%%%%%
    %The Poincare section finds the maximum value in each step integration of
    %each oscillation

    n=1;
    h=0.1;
    xps=0.;
    yps=0.;
    naps=0.;
    nbps=0.;
    tps=0.;

    didt(1) = (1.-h)*na(1)-h*nb(1)+2.*K*cos(y(1))*exp(-x(1)/2); % First value obtained

    for j=2:ty(1)-1;           %subsequent values obtained based on first value
        didt(j) = (1.-h)*na(j)-h*nb(j)+2.*K*cos(y(j))*exp(-x(j)/2);
        if (didt(j)*didt(j-1)<0)
            if (didt(j)<0)
                xps(n) = x(j);
                yps(n) = y(j);
                naps(n) = na(j);
                nbps(n) = nb(j);
                tps(n) = t(j);
                n=n+1;
            end;
        end;
    end;

    K
    n
    if n > 1 ;
        Kappa=K-xps+xps;
        A=exp(xps/2.);

        %figure(2);
        scatter(Kappa,A,'.','black');

    end

      %%Ampl % %# calculate cell range to fit matrix (placed below previous one)
      cellRange = xlcalcrange('A1',0 , offset, size(A,1),size(A,2));

      offset = offset + size(A,1);

      %# insert matrix in sheet
      Excel.Range(cellRange).Select();
      Excel.Selection.Value = num2cell(A);
  end

%# save XLS file
wb.SaveAs(fName,1);

Thanks,

Calum

Answer 1

[Calum]

Solved: All I had to do was transpose the array that I was writing to excel so that it was nx1 not 1xn in it's dimensions.

Simply used

X=A.'

Cal